Google app script timeout ~ 5 minutes?

Question

My google app script is iterating through the user's google drive files and copying and sometimes moving files to other folders. The script is always stopped after 5 minutes with no error message in the log.

I am sorting tens or sometimes thousands files in one run.

Are there any settings or workarounds?

Answer 1

If you are using G Suite as a Business, Enterprise or EDU customer the execution time for running scripts is set to:

30 min / execution

See: https://developers.google.com/apps-script/guides/services/quotas

Answer 2

I have developed a Google Apps Script library that uses UserProperties and programmatic triggers to run a batch the extends beyond 6 minutes. You can import this library in your GAS project and wrap your code with the API so that it can run FOREVER (well not really, we are limited by quota's related to the number of hours that triggers can run)

Your can learn all about it here: http://patt0.blogspot.in/2014/08/continuous-batch-library-update-for.html

Answer 3

Quotas

The maximum execution time for a single script is 6 mins / execution
- https://developers.google.com/apps-script/guides/services/quotas

But there are other limitations to familiarize yourself with. For example, you're only allowed a total trigger runtime of 1 hour / day, so you can't just break up a long function into 12 different 5 minute blocks.

Optimization

That said, there are very few reasons why you'd really need to take six minutes to execute. JavaScript should have no problem sorting thousands of rows of data in a couple seconds. What's likely hurting your performance are service calls to Google Apps itself.

You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of reads and writes. Alternating read and write commands is slow. To speed up a script, read all data into an array with one command, perform any operations on the data in the array, and write the data out with one command.
- https://developers.google.com/apps-script/best_practices

Batching

The best thing you can possibly do is reduce the number of service calls. Google enables this by allowing batch versions of most of their API calls.

As a trivial example, Instead of this:

for (var i = 1; i <= 100; i++) {
  SpreadsheetApp.getActiveSheet().deleteRow(i);
}

Do this:

SpreadsheetApp.getActiveSheet().deleteRows(i, 100);

In the first loop, not only did you need 100 calls to deleteRow on the sheet, but you also needed to get the active sheet 100 times as well. The second variation should perform several orders of magnitude better than the first.

Interweaving Reads and Writes

Additionally, you should also be very careful to not go back and forth frequently between reading and writing. Not only will you lose potential gains in batch operations, but Google won't be able to use its built-in caching.

Every time you do a read, we must first empty (commit) the write cache to ensure that you're reading the latest data (you can force a write of the cache by calling SpreadsheetApp.flush()). Likewise, every time you do a write, we have to throw away the read cache because it's no longer valid. Therefore if you can avoid interleaving reads and writes, you'll get full benefit of the cache.
- http://googleappsscript.blogspot.com/2010/06/optimizing-spreadsheet-operations.html

For example, instead of this:

sheet.getRange("A1").setValue(1);
sheet.getRange("B1").setValue(2);
sheet.getRange("C1").setValue(3);
sheet.getRange("D1").setValue(4);

Do this:

sheet.getRange("A1:D1").setValues([[1,2,3,4]]);

Chaining Function Calls

As a last resort, if your function really can't finish in under six minutes, you can chain together calls or break up your function to work on a smaller segment of data.

You can store data in the Cache Service (temporary) or Properties Service (permanent) buckets for retrieval across executions (since Google Apps Scripts has a stateless execution).

If you want to kick off another event, you can create your own trigger with the Trigger Builder Class or setup a recurring trigger on a tight time table.

Answer 4

If you're a business customer, you can now sign up for Early Access to App Maker, which includes Flexible Quotas.

Under the flexible quota system, such hard quota limits are removed. Scripts do not stop when they reach a quota limit. Rather, they are delayed until quota becomes available, at which point the script execution resumes. Once quotas begin being used, they are refilled at a regular rate. For reasonable usage, script delays are rare.

Answer 5

Figure out a way to split up your work so it takes less than 6 minutes, as that's the limit for any script. On the first pass, you can iterate and store the list of files and folders in a spreadsheet and add a time-driven trigger for part 2.

In part 2, delete each entry in the list as you process it. When there are no items in the list, delete the trigger.

This is how I'm processing a sheet of about 1500 rows that gets spread to about a dozen different spreadsheets. Because of the number of calls to spreadsheets, it times out, but continues when the trigger runs again.