There are many rates and quotas in the Google Apps Script environment. One of the usual ways of dealing with it is to sleep between instructions, but that seems to be a rather blunt instrument. So here’s a way of adapting to rate limits using an exponential backoff technique, which I cover in some other contexts in this site in other languages, but it can also be applied to Google Apps Script. Here’s a general function to use to cover this scenario. In fact it is one of the utility functions provided for use by driver implementations in DataHandler, but you can adapt it to other things as well.
Exponential backoff technique
This is a fairly simple idea, where if something fails because of some temporary reason (for example rate limiting), you can wait a bit and try again. The exponential part is that you wait increasingly longer amounts of time between attempts. Eventually of course you give up. We are trying to collect each variant of recoverable error messages returned by the apps script apis – please take a look at the list and contribute if you have any new ones. Here’s a Google Apps Script implementation. There is a newer version available here – Exponential backoff
/**
* recursive rateLimitExpBackoff()
* @param {function} callBack some function to call that might return rate limit exception
* @param {number} [sleepFor=1000] optional amount of time to sleep for on the first failure in missliseconds
* @param {number} [maxAttempts=5] optional maximum number of amounts to try
* @param {number} [attempts=1] optional the attempt number of this instance - usually only used recursively and not user supplied
* @param {boolean} [optLogAttempts=false] log re-attempts to Logger
* @return {*} results of the callback
*/
function rateLimitExpBackoff ( callBack, sleepFor , maxAttempts, attempts , optLogAttempts ) {
// can handle multiple error conditions by expanding this list
function errorQualifies (errorText) {
return ["Exception: Service invoked too many times",
"Exception: Rate Limit Exceeded",
"Exception: Quota Error: User Rate Limit Exceeded",
"Service error: Spreadsheets",
"Exception: Internal error. Please try again.",
"Exception: Cannot execute AddColumn because another task",
"Execution failed: Service invoked too many times in a short time"]
.some(function(e){
return e.length > 15 && errorText.toString().slice(0,e.length) == e ;
});
}
// sleep start default is 1 seconds
sleepFor = Math.abs(sleepFor || 1000);
// attempt number
attempts = Math.abs(attempts || 1);
// maximum tries before giving up
maxAttempts = Math.abs(maxAttempts || 5);
// check properly constructed
if (!callBack || typeof(callBack) !== "function") {
throw ("you need to specify a function for rateLimitBackoff to execute");
}
// try to execute it
else {
try {
var r = callBack();
return r;
}
catch(err) {
if(optLogAttempts)Logger.log("backoff " + attempts + ":" +err);
// failed due to rate limiting?
if (errorQualifies(err)) {
//give up?
if (attempts > maxAttempts) {
throw (err + " (tried backing off " + (attempts-1) + " times");
}
else {
// wait for some amount of time based on how many times we've tried plus a small random bit to avoid races
Utilities.sleep (Math.pow(2,attempts)*sleepFor) + (Math.round(Math.random() * sleepFor));
// try again
return rateLimitExpBackoff ( callBack, sleepFor , maxAttempts , attempts+1,optLogAttempts);
}
}
else {
// some other error
throw (err);
}
}
}
}
How to use
For illustration, here’s an example of executing a bunch of Fusion Table SQL instructions, and dealing with rate limiting if it happens
fr.rows.forEach (function(d) {
var r = parentHandler.rateLimitExpBackoff ( function () {
return FusionTables.Query.sql("DELETE FROM " + siloId + " WHERE ROWID = '" + d[0] + "'"); } ) ;
});
Typically, this would fail after about 30 queries due to rate limiting. I don’t want to sleep between every call, but I do need to be able to slow down if I hit a rate limit. The exponential backoff will conditionally sleep for a while (gradually longer periods) if it hits the right kind of error. The only required argument is an anonymous function, which rateLimitExponentialBackoff() will repeatedly attempt to execute until it either succeeds, gives up or fails in some other way. See Database abstraction with google apps script for more on this.