Best Practices

This document lists best practices that will help you improve the performance of your scripts.

Minimize calls to other services

Using JavaScript operations within your script is considerably faster than calling other services. Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Sheets, Docs, Sites, Translate, UrlFetch, and so on. Your scripts will run faster if you can find ways to minimize the calls the scripts make to those services.

Consider collaborating with shared drives

If you are working on a script project with other developers, you can collaborate on Apps Script projects with shared drives. Files in a shared drive are owned by the group, rather than individuals. This makes development and maintenance of the project easier.

Use batch operations

Scripts commonly need to read in data from a spreadsheet, perform calculations, and then write out the results of the data to a spreadsheet. Google Apps Script already has some built-in optimization, such as using look-ahead caching to retrieve what a script is likely to get and write caching to save what is likely to be set.

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.

Here's an example — an example you should not follow or use. A script uses the following code to set the background colors of every cell in a 100 x 100 spreadsheet grid. It uses as function named getColorFromCoordinates() (not shown here) to determine what color to use for each cell:

  // DO NOT USE THIS CODE. It is an example of SLOW, INEFFICIENT code.
  // FOR DEMONSTRATION ONLY
  var cell = sheet.getRange('a1');
  for (var y = 0; y < 100; y++) {
    xcoord = xmin;
    for (var x = 0; x < 100; x++) {
      var c = getColorFromCoordinates(xcoord, ycoord);
      cell.offset(y, x).setBackgroundColor(c);
      xcoord += xincrement;
    }
    ycoord -= yincrement;
    SpreadsheetApp.flush();
  }

The script is inefficient: it loops through 100 rows and 100 columns, writing consecutively to 10,000 cells. The Google Apps Script write-back cache helps, because it forces a write-back using flush at the end of every line. Because of the caching, there are only 100 calls to the Spreadsheet.

But the code can be made much more efficient by batching the calls. Here's a rewrite in which the cell range is read into an array called colors, the color assignment operation is performed on the data in the array, and the values in the array are written out to the spreadsheet:

  // OKAY TO USE THIS EXAMPLE or code based on it.
  var cell = sheet.getRange('a1');
  var colors = new Array(100);
  for (var y = 0; y < 100; y++) {
    xcoord = xmin;
    colors[y] = new Array(100);
    for (var x = 0; x < 100; x++) {
      colors[y][x] = getColorFromCoordinates(xcoord, ycoord);
      xcoord += xincrement;
    }
    ycoord -= yincrement;
  }
  sheet.getRange(1, 1, 100, 100).setBackgrounds(colors);

The inefficient code takes about 70 seconds to run. The efficient code runs in just 1 second!

Avoid libraries in UI-heavy scripts

Libraries are a convenient way to reuse code, but they slightly increase the time it takes to start the script. This delay isn't noticeable for relatively long-running scripts (like a utility script to clean up your Google Drive files), but for client-side HTML Service user interfaces that make repeated, short-running google.script.run calls, the delay will affect every call. Because of this issue, libraries should be used sparingly in add-ons, and you may want to avoid them in non-add-on scripts that make lots of google.script.run calls.

Use the Cache service

You can use the Cache Service to cache resources between script executions. By caching data, you can reduce the number of times or frequency with which you have to fetch the data. Consider the scenario where you have an RSS feed at example.com that takes 20 seconds to fetch, and you want to speed up access on the average request. The example below shows how to use the Cache Service to speed up access to this data.

  function getRssFeed() {
    var cache = CacheService.getScriptCache();
    var cached = cache.get("rss-feed-contents");
    if (cached != null) {
      return cached;
    }
    // This fetch takes 20 seconds:
    var result = UrlFetchApp.fetch("http://example.com/my-slow-rss-feed.xml");
    var contents = result.getContentText();
    cache.put("rss-feed-contents", contents, 1500); // cache for 25 minutes
    return contents;
  }

Now, while you'll have to still wait 20 seconds if the item is not in cache, subsequent accesses will be very fast until the item expires out of the cache in 25 minutes.