Chapter 4. Living with the Quotas

One of the daily irritations of Apps Script is being caught by quota limitations that cause scripts to fail. Quotas are imposed on many Apps Script services because they run on shared infrastructure. Runaway processes could have a serious impact on other Apps Script users, and this quota system is intended to protect the Apps Script community as a whole, even though individual users might be occasionally inconvenienced.

Another good reason for quotas is as a signal that an application is no longer suitable for the Apps Script environment, and should be moved to a paid and more scalable environment such as App Engine.

To VBA users, who have free reign over the local PC resources, this can come as somewhat of a culture shock, because these quotas deeply affect the approach to application structure. Although VBA has no direct corollary to this problem, it is important to understand how quotas affect Apps Script development.

The chapter looks at some ways to live with quotas, rate limits, and what Google calls limitations.

The Quotas

Many of the underlying APIs used by Apps Script have their own quotas, but Apps Script often has additional restrictions.

Daily Limits

Some services allow you to perform a maximum number of a particular operation per day; that is, when you exhaust this quota, you can no longer perform that specific operation that day. Table 4-1 lists the daily limits from the Apps Script dashboard at the time of writing.

Table 4-1. Daily limits from the Apps Script dashboard
Action Consumer
(gmail.com)		 Google Apps
for your domain		 Google Apps
for biz/edu/gov
Calendar events created 5,000 10,000 10,000
Contacts created 1,000 2,000 2,000
Documents created 250 500 1,500
Email recipients per day 100 100 1,500
Gmail read/write (excluding send) 20,000 40,000 50,000
Groups read 2,000 5,000 10,000
JDBC connection 10,000 10,000 50,000
JDBC failed connection 100 100 500
Property GET or SET 50,000 100,000 500,000
SOAP calls 5,000 20,000 20,000
Spreadsheets created 250 500 3,200
Triggers total runtime 1 hours 3 hours 6 hours
URLFetch calls 20,000 50,000 100,000
URLFetch data received 100 MB 100 MB 100 MB

Limitations

Another type of quota is known as a limitation by Google. Typically, these are size limitations for an operation’s maximum payload. These are hard limits, and there is not much that can be done other than to design applications to avoid hitting them. Table 4-2 lists the limitations at the time of this writing.

Table 4-2. Additional limitations from the Apps Script dashboard
Feature Consumer (gmail.com) Google Apps for your domain Google Apps for biz/edu/gov
Script runtime 6 min/execution 6 min/execution 6 min/execution
Email attachments 250/msg 250/msg 250/msg
Email body size 200 KB/msg 200 KB/msg 400 KB/msg
Email recipients per message 50/msg 50/msg 50/msg
Email total attachments size 25 MB/msg 25 MB/msg 25 MB/msg
Properties value size 9 KB/val 9 KB/val 9 KB/val
Properties total storage 500 KB/property store 500 KB/property store 500 KB/property store
Triggers 20/user/script 20/user/script 20/user/script
URL Fetch headers 100/call 100/call 100/call
URL Fetch header size 8 KB/call 8 KB/call 8 KB/call
URL Fetch POST size 10 MB/call 10 MB/call 10 MB/call
URL Fetch URL length 2 KB/call 2 KB/call 2 KB/call

Triggers

Being able to run processes at predetermined times is a great feature of Apps Script.

VBA provides Application.Ontime, and you can also use the Windows scheduler for this purpose, but in both cases, your PC needs to be on, and in the case of Application.Ontime it needs to be running Office.

Apps Script Triggers have some limitations (mentioned in Tables 4-1 and 4-2), but they also are less specifically controllable than their VBA equivalents.

The Trigger dialog is shown in Figure 4-1.

The Apps Script Trigger dialog
Figure 4-1. The Apps Script Trigger dialog

The times at which these scripts will actually run are only loosely estimated, and as a result they are not predictable enough to be able to use for scheduling dependent processes.

Triggers also run at a reduced priority, so processes that run interactively within the six-minute execution limit may take longer when executed as a trigger.

It’s important to remember that the six-minute execution window is elapsed time (not compute time).

Rate Limits

Rate limits control how rapidly certain operations can be executed, and in some ways are the trickiest to work around. The documentation for exactly which operations are subject to rate limits is hard to track down, but the consequence of hitting one is this error message:

Service invoked too many times in a short time

Throttling

Throttling is slowing down, or adapting a script’s demand profile to operate within the resource’s servicing capability.

Sleeping

The simple way to throttle scripts that might be affected by rate limits is simply to sleep after each operation. The examples in the Apps Script documentation are usually based on this approach, and usually show something like this (this example is getting all the messages associated with a Gmail thread):

JS

threads.forEach (function (d) {
  var messages = d.getMessages();
  doSomeThingWith(messages);
  Utilities.sleep(1000);
});

Although this is certainly the simplest approach, it could generate a lot of unnecessary waiting. Assuming that the rate limit on getting messages from Gmail is 1 per second, the actual time to return the getMessages and the doSomethingWith functions is going to be affected by the resources available at that time, in addition to their actual execution times.

One second may even already have elapsed by the time the sleep request is executed, meaning that the request might not even be needed. On the other hand, the response could be almost instantaneous, in which case not sleeping between calls risks script failure.

In reality, although it’s not documented, the rate doesn’t appear to be checked every second, but instead averaged over a longer period of time. This means that short bursts of activity can get away with more rapid operations than the rate limit implies, so there is all the more reason to avoid unnecessary idle time.

Sleeping counts toward the six-minute execution time, so a script that reads 200 threads from Gmail (a typical day’s worth of mail) would spend more than half of its available execution time doing nothing.

Exponential Backoff

Exponential backoff attempts the operation, and if it fails, waits a bit, then tries again until it succeeds or gives up. This means that waiting happens only if an error is signaled. The amount of wait time on each attempt:

  • Increases exponentially to allow the clearing of any blockage related to infrastructure overload or rate limit averages measured over a period of time.

  • Includes a random factor to avoid multiple processes getting locked into the same retry schedule.

Because this is such a common requirement in almost every script that accesses rate-limited resources, it’s a good idea to use a common library function for backoff.

It is used like this, with the function to repeatedly attempt being passed anonymously:

JS

threads.forEach (function (d) {
  var messages = Utils.rateLimitExpBackoff(function() {
    return d.getMessages();
  };
  doSomeThingWith(messages);
});

Exponential backoff waits only if there was a rate limited error signaled, then tries again. This minimizes both idle time and unpredictable failures.

Code for exponential backoff

The error messages can be internationalized for the kinds of errors that should be retried. An example of a quota error is given in Russian in the following snippet. To add your own language, just expand the list with the equivalent Apps Script error message.

These are the errors that have shown to be recoverable on retry:

JS

utils.tryAgain = "force backoff anyway";
utils.backoffErrors = [
  "Exception: Service invoked too many times",
  "Exception: Rate Limit Exceeded",
  "Exception: Quota Error: User Rate Limit Exceeded",
  "Service error: Spreadsheets",
  "Exception: User rate limit exceeded",
  "Exception: Internal error. Please try again.",
  "Exception: Cannot execute AddColumn because another task",
  "Service invoked too many times in a short time:",
  "Exception: Internal error.",
  "Exception: Превышен лимит: DriveApp.",
  utils.tryAgain
];

utils.rateLimitExpBackoff = function ( callBack, sleepFor ,
                maxAttempts, attempts , optLogAttempts , optChecker) {

  // can handle multiple error conditions by expanding this list
  function errorQualifies (errorText) {

    return utils.backoffErrors.some(function(e){
              return  errorText.toString().slice(0,e.length) == e  ;
            });
  }

  // sleep start default is 1 second
  sleepFor = Math.abs(sleepFor || 1000);

  // attempt number
  attempts = Math.abs(attempts || 1);

  // maximum tries before giving up
  maxAttempts = Math.abs(maxAttempts || 5);

  // make sure that the checker is really a function
  if (optChecker && typeof(callBack) !== "function") {
    throw ("if you specify a checker it must be a function");
  }

  // 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();

      // this is to find content based errors that might benefit from a retry
      return optChecker ? optChecker(r) : 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);
      }
    }
  }
};

Splitting

Some limitations apply to scripts rather than to a particular user. This means that by splitting the script in two, you can double the quota.

Libraries

The Properties service is a secure store, rather like the Windows registry. Chapter 5 is dedicated entirely to this service. Scripts each come with their own Properties service. You can mitigate the limitation on overall property size by using the Properties service of a library and passing it to the main script(s) that use it:

JS

// library
function getPropertyService() {
  return PropertiesService.getScriptProperties();
}

// main script
function work () {
  var libraryProps = Library.getPropertyService();
  var mainProps = PropertiesService.getScriptProperties();
}

Batching

Some scripts can be split so that they work on separate sections of the data being processed. Applying the preceding technique, you can use the Properties service of a common library to exchange progress information between scripts so one can pick up where the other left off.

Parallel Running

Similar to batching, a shared library Properties service can be used to orchestrate cooperating scripts, except in this case the scripts run simultaneously. Often you’ll use the trigger service to schedule simultaneous processes, occasionally using MapReduce-type techniques1 to consolidate and pass data from one phase to another.

Offloading

Apps Script runs on Google Servers, but HTML service tasks can be orchestrated by Apps Script to run on the local client. Generally speaking, compute-intensive tasks will run more quickly when executed locally. The HTML service can asynchronously fire off Apps Script tasks to deal with processes that require interaction with Apps Script services.

Carefully offloading work to the HTML service is a way of completely circumventing many of the server-side limitations, but it’s a complex topic that’s beyond the VBA/Apps Script scope.

Avoiding Service Calls

Many of the rate-limited services are related to fetching data externally or from Google services or APIs. In some cases, either the data will not have changed from one call to the next, or it is not critical that the very latest data be used. Apps Script provides a Cache service for rapid access to data, enabling the complete avoidance of quota-consuming service calls.

Cache Service

The Cache service allows data to be stored in key/value pairs. It is subject to limitations in data size, but is not constrained by rate limits. As a result, it’s often good practice to write service or external data that might be needed by this script to cache, and to first check the cache to see if it’s already there. Not only does this avoid service call quota consumption, but also cache is generally faster to access than Apps Script services or external data and doesn’t need to be reprocessed.

Cache scopes

The selection of which cache to use depends on the data visibility required. The cache visibility is limited to the current script, but within that there are three different caches, described in Table 4-3.

Table 4-3. Cache scopes
Cache Brief description
DocumentCache Applies to the current document
ScriptCache Applies to all users of this script
UserCache Applies to the current user of this script

A cache is obtained from the Cache service like so:

JS

var cache = CacheService.getScriptCache();

Data can be written like this:

JS

var data = getDataFromSomeService();
cache.put("mykey", data);

A future call to this same script would first check to see if the data was already in the cache, and if not, make the service call, thus avoiding the service call if there had been one made recently:

JS

var data = cache.get("mykey");
if (!data) {
  data = getDataFromSomeService();
  cache.put("mykey", data);
}

Cache data expiry

It’s important that cache data expires in order to provoke a refresh from time to time. By default, the cache will expire after 10 minutes, but you can change that like so (where the third parameter is the expiry time in seconds):2

JS

cache.put("mykey", data, 1200);

Sharing cache

Caches are scoped to a particular script, but there may be occasions when multiple scripts would want to benefit from each other’s cached results. You cannot share caches directly, but you can make them accessible through a shared library, using the same technique we used earlier for sharing the Properties services:

JS

// Library
function getCacheService() {
  return CacheService.getScriptCache();
}

// main script
var cache = Library.getCacheService();
var data = cache.get("mykey");
if (!data) {
  data = getDataFromSomeService();
  cache.put("mykey", data);
}

1 MapReduce is a technique where a workload is split into chunks (Map) and processed in parallel. The outputs from each thread are combined (Reduce) into a consolidated result. Examples of Apps Script implementations of MapReduce are covered on the Desktop Liberation site.

2 It is not guaranteed that the cache will last that long, as actual cache lifetime can be affected by resource availability in the Google infrastructure.

Get Going GAS now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial