Custom SHEETNAME function not working in Arrayformula

Question

I have been successfully deploying the sheetname function outlined in Display sheet name in Google Spreadsheets for a couple of years. It has allowed me to have several sheets collecting information from different forms in one sheets file, with array formula filling in the name of the sheet in one column. Then I have a summary sheet that collects all of the separate form-filed sheets into one table of data. It has worked pretty well. But of late the sheetname function has not been reliably working. I have tried the GoogleClock(), and later the Now() parameter as suggested by the original discussion. But that now produces an error, which it did not previously do. When I change the array formula back, it works correctly. But this is a 20 tab Google sheet, and I don't want to have to force the function to work by hand on every sheet.

My app script function looks like this:

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

The formula in the sheet looks like this:

=arrayformula(if(row($A:$A)=1,"Production",if($A:$A="","",Sheetname())))

Like I say, it works when I manipulate it (change the function and change it back). But left on its own as users submit form answers, it stops working and just says "Loading"

Any ideas?

Answer 1

AFAIK Google Sheets custom functions can't be used inside ARRAYFORMULA to return an array of values, but if it was working for you, it's good to know.

As you are using a time execution expensive formula it's very likely that you are having the "Loading..." error because the your script exceeded the corresponding quota, and that the description of the error is Script invoked too many times per second for this Google user account.. From Google Apps Script > Exception messages:

• Script invoked too many times per second for this Google user account. This indicates that the script began executing too many times in a short period. It most commonly occurs for custom functions that are called repeatedly in a single spreadsheet. To avoid this error, code your custom functions so that they only need to be called once per range of data, as explained in the guide to custom functions.

The solution for this specific case could be as simple as adding =SheetName() to one cell on each of the sheets of your spreadsheet, let say B1, then use the following formula:

=arrayformula(if(row($A:$A)=1,"Production",if($A:$A="","",B1)))

If the above formula doesn't work, then you could create a custom function that does the whole work of your formula.

Notes:

• GoogleClock() function was deprecated several years ago.
• Custom functions can't use volatile functions, like NOW(), as arguments

Answer 2

Your formula has unnecessary stuff in it that only prolong the overall load time with every new non-empty row until the limit is reached and you got yourself an (Loading...) error.

Paste this formula into any suitable empty cell on your sheet (let's say X1):

=SHEETNAME(TODAY())

And then use this formula instead of yours:

={"Production"; ARRAYFORMULA(IF(LEN(A2:A), X1, ))}

---

As an alternative to:

function SHEETNAME() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

you can use (if the sheet order is not subjective to sheet position/order changes):

function SHEET(input) {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ;
  if( (input>0) && (input <= sheets.length)) return sheets[(input-1)].getName() ;
  else return "invalid sheet #" ;
}
catch( err ) {
  return "#ERROR!" 
}
}