r/GoogleAppsScript

Hello all.

I'm hoping someone here can help me because I'm at my wit's end. Long story short, I created and maintain (keep running) a spreadsheet for work that we use to track our service/breakdown department's performance and SLA compliance. Rather than scroll all the way to the bottom of the sheet and manually drag formulae into the new row, I've created a script that inserts a new row and enters the formulae into the top of the sheet. I have the code entering the formulae, instead of copying it, because it works faster than having a script that copy-pastes only the formula into the new row. And my script works a treat, EXCEPT for whatever is happening in column O.

When I remove the code for O, it works just fine. But with the code for column O included it has a shart attack and won't proceed. I've tried moving the data in O to L and the same thing happens. The script works fine until it has to process THAT particular column, then it just stops. Again, removing O from the code makes it work 100%. The code for O and H is almost identical and for all intents and purposes it is, the maths in the formula is a little different is all. I have tried many different versions and functions but the result is the same. It stops at O. I have even tested O with using the SAME EXACT CODE from another work column and it still will not work.

Oddly enough though, a script that simply copy-pastes row 6 into the newly inserted row 5 WILL copy column O, but also all the manually entered data as well which I don't want.

I think I've explained myself well enough. Please ask any questions you may have. My script is below.

https://preview.redd.it/hgxuv63p672h1.png?width=1181&format=png&auto=webp&s=be595498cc1856757dba7ec10da3b5a6524512b0

I built a small lead gen tool for Google Sheets. It’s pretty simple: you add a list of websites, and it fetches all publicly available emails from those sites.

The tool itself is completed. Descriptions, visuals, landing page and listing content are all ready too.

My problem is getting it published publicly on Google Marketplace. I’ve tried using AI to figure it out, but I’m completely stuck at the Google Cloud / OAuth / integration part. Honestly, it’s confusing as hell and I don’t know how to move forward.

Has anyone here published a Google Sheets Add-on before and can guide me on the process? Especially the Google Cloud setup and what actually needs to be configured to get approved.

Hey everyone,

I wanted to open a discussion on handling one of the most notorious and frustrating limitations in Google Apps Script add-on development: the multiple accounts Authuser=0 trap.

The Context: My team recently launched a Workspace management add-on. Shortly after launch, we onboarded an enterprise client who left a great review but immediately reported a frustrating "nitpick": he's logged into multiple Google Workspace accounts, while he's accessing the add-on with his second logged account, the sidebar of our add-on was selecting the default account of their browser rather than the account actually linked to the active spreadsheet.

After consulting with my lead dev, we realised we were dealing with a platform-level infrastructure issue, and not a bug in our code.

The Trap: As most of you know, when a user is juggling multiple Google accounts in one Chrome window (e.g., u/0/, u/1/), Google Apps Script's HtmlService often gets completely confused by the session cookies. If they open the sheet with a secondary account, the underlying iframe still forces the add-on to authenticate using the Default account (authuser=0).

The result is massive user confusion, as the sidebar displays data or permissions for the completely wrong account.

No Workaround Found: We searched high and low but couldn't find a native programmatic patch to force the iframe to respect the active document's user context. So we advised the client to perform their admin operations inside a dedicated Chrome Profile or an Incognito window.

My Questions for the Community: Since we want to provide the smoothest UX possible, I’m curious how other devs here are tackling this:

1. The "Holy Grail" Fix: Has anyone found a reliable native workaround, undocumented parameter, or JS hack to force HtmlService to respect the active authuser index?
2. User Experience (UX): Do you preemptively warn users about this in your UI/onboarding flow, or do you just document it in your FAQs and wait for the support tickets to roll in?
3. Google's Roadmap: Has anyone who talks to Googlers heard any whispers on whether this is ever getting patched at the infrastructure level?

Would love to hear your thoughts, workarounds, and war stories regarding this bug!

I’m working on a complex spreadsheet with onEdit automations that range from slow to very slow.

I suspect half the problem is repeated fetching from the sheet. Since this data changes very little, I want to use an onOpen function to pre-fetch it and then hold onto it in some kind of data store.

• Long term, the data lives on the spreadsheet.
• It’s both structured and unstructured. There isn’t a ton of it. 
• The sheet only ever has one user at a time.
• During a typical session of usage, the computer will be put to sleep and woken up repeatedly.

I know frontend frameworks deal with this is a paradigm a lot, but I don’t have any experience with it. What’s the appropriate tool, here—Cache Service, Properties, localStorage, or something else?

Ok so im painfully new to javascript and im trying to get two automations going... on the same sheet of course,

The first task sorts the workbook into corresponding sheets and deletes the row as it moves. That one runs fine with the current code.

Im also trying to sort those sheets by multiple columns after the row is moved. Every sheet except one ... The only way ive been able to think to do that is to create a separate task for each of the sheets I need to sort and omit the one I do not.

Of course that ran me smack into the dreaded "multiple onEdit" problem. I was told you can get around it by bundling the tasks into one Onedit "main" and it should run subsequently.

Thing is, it runs the first task, then wont run any of the subsequent. Says "secondtask is undefined" during execution errors.

Ive seen others running with this same work around on youtube so im really not sure why mine doesnt work. I'm sure its a simple fix maybe but I cant seem to find an answer as to why the first task runs but not the second.

I ran it through a javascript validation and it says "parsing error unexpected token const" at line 29 which is

const sheetName = "Colony Maintenance";

Here's the full code, I put some notation in to make it easier to understand what i was trying to do.

Any help would be greatly appreciated!

function onEdit(e) { 
  firstTask(e);
  secondTask(e);
  thirdTask(e); 
  forthTask(e); 
  fifthTask(e); 
  sixthTask(e); 
} 


function firstTask(e) { 
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();


  if (col == 1 && val !=''){
   let ss = SpreadsheetApp.getActiveSpreadsheet();
   let sheet = ss.getSheetByName (source.getName());
   let targetSheet = ss.getSheetByName(val);
   let data = sheet.getRange(row, 1,1, sheet.getLastColumn()).getValues();


  targetSheet.appendRow(data [0]);
  sheet.deleteRow(row);
  }
 
//this is where the code stops working

function secondTask(e) {    
  //Change to the sheet you want to sort's name
    const sheetName = "Colony Maintenance"; 
    const sheet = e.source.getSheetByName(sheetName);
 
  // Only proceed if the edit happened on the target sheet
  if (e.source.getActiveSheet().getName() !== sheetName) return;

  // Define the range to sort (starting from row 2 to skip headers)
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  // Sort by multiple columns:
  // Priority 1: Column 1 (Ascending= a-z= true)
  // Priority 2: Column 3 (Descending= z-a =false)

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function thirdTask(e) {
    const sheetName = "Veterinary Care"; 
    const sheet = e.source.getSheetByName(sheetName);

  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function forthTask(e) {
    const sheetName = "Sales"; 
    const sheet = e.source.getSheetByName(sheetName);

  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function fifthTask(e) { 
    const sheetName = "Breeding"; 
    const sheet = e.source.getSheetByName(sheetName);

  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function sixthTask(e) { 
    const sheetName = "Home Life"; 
    const sheet = e.source.getSheetByName(sheetName);
 
  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }
}