Can Apps Script have priority of calculation over Google Sheets formulas calculation?

user_16969202 0
Viewed 54

To put it simply, when google sheets show that green loading bar on the top right of the screen, my scripts run VERY slowly and often time out. I'm talking about over 85% error rate here. However, if the google sheets is not loading, it executes the script in just a few seconds.

Can I insert in the script something that would make google sheets prioritize the Script over the spreadsheet calculation?

(I'm aware this can lead to potential errors as it might transfer data before google sheets finishes updating.)

I'm also accepting any alternatives that would help the scripts to not time out (excluding turning the spreadsheets lighter. All steps to this have already been taken, but I'm in a startup that's still structuring its DWHs so there's some amount of data still stored in google sheets and I need to run Apps Scripts to move the data between spreadsheets)

Thanks in advance for all help.

google-apps-script google-sheets google-apps
1 Answers

From the question

Can I insert in the script something that would make google sheets prioritize the Script over the spreadsheet calculation?

There is no such thing.

A general option is to replace all the formulas by their results before running the script. This could be done sheet by sheet manually, by doing Copy & Paste Special > as Values only to avoid having the timeout problem of doing this by using another script but if your sheets are big and there are calculation chains across them, uses volatile functions, this might require a lot of CPU / RAM power, so close all the apps including those running on the background, other web-browser tabs, etc. before attempting this.

References

user_15954510
Related