autorun of script in google sheets

1
votes

I use the Sheet() function in excel which is not supported in google sheets.

I found a code snippet to simulate the function

function SHEET() {return SpreadsheetApp.getActiveSheet().getIndex(); }

I set a trigger for it to run SHEET from Worksheet On Open however when I open the sheet, anywhere there is a formula such as

=INDIRECT(OFFSET(Balancing!P1,SHEET(A1)-3,0,1,1)&"!j201")

the cell shows loading and never loads. I have to open the script editor and run it. When it does run, it works great.

Does anyone know how to make this happen automatically?

1
google-apps-scriptgoogle-sheetscustom-functiongoogle-sheets-formula
Your custom function doesn't include an argument but the formula includes and argument for it. Try replacing SHEET(A1) by SHEET() – Rubén
to clarify the formula is in a cell on the sheet not in the script. It is my understanding (I am very new to this mind you) that if the argument is left blank then the script won't care. – Bill Flippen
That is clear for me. Have you tried what I suggested? – Rubén
sorry for the delay. It may ave been a browser based issue. I didn't have to do your suggestion, as the next time I open the sheet a week later, it paused for ~10 seconds saying "loading" and then all was working just as expected – Bill Flippen

1 Answers

0
votes

Like I mentioned in a comment, leaving the code and sheets unmodified, I opened the sheet later and after a few seconds, the script ran upon open and all of the formulas worked.