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?