CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Calvin Gaunce
Calvin Gaunce

Reputation: 366

Sequencing large scripts in Google Apps Script

We use Google Sheets to store and manipulate event-based data from the users of our web application. Recently, I've been using Google Apps Script to automate this data manipulation process, and now I'm looking for some advice.

My scripts were working well for a while and then late last week, I started receiving the error, 'Exceeded Maximum Execution Time'. I received this error on two of my scripts that worked fine the day before, with no edits in between. I'm working with a lot of data and have even hit the 2,000,000 cell limit on my spreadsheet a couple times.

My goal is to be able to sequence my seven scripts together so that I can fire them all with one trigger. How can I do this without hitting the max execution time error? Sometimes, my spreadsheet will crash when I just try to delete one of the sheets within it. Could the size of my spreadsheet be causing the scripts to fail?

I've done my best to follow best practices outlined in Google's documentation, but can anyone recommend best practices for spreadsheet and script organization?

Upvotes: 1

Views: 2336

Answers (2)

Spencer Easton
Spencer Easton

Reputation: 5782

You might be interested in a tool written by Bruce Mcpherson for massively parallel processing in Google Apps Script.

http://ramblings.mcpher.com/Home/excelquirks/htmlservice/parallel

http://ramblings.mcpher.com/Home/excelquirks/htmlservice/parallel/implement

Upvotes: 5

Gerardo
Gerardo

Reputation: 3845

Here you can find the documentation related to quotas on apps script, as you can see the script runtime is 6 mins after that, the server will stop the script and throw an exception as, as you are experiencing.

If you are already applying Google's best practices, try to find where are your bottle necks. -Do you need to read the complete range of data or just a part of it? -Is there any information that you could store (e.g. in cache or properties) so you won't have to calculate or read again?. -Are you tracking the time for each script? -Are you reading the whole range of data on every script? or reading it once and passing it as parameter to the other scripts?

hope this helps.

Upvotes: 1

Related Questions