Tag: apps script tricks

I love Apps Script. But if you use it regularly, you are probably familiar with slow execution times, especially when your script repeatedly calls APIs, BigQuery, or other external services.

When an Apps Script application repeatedly needs the same data, fetching or computing that data again every time can be wasteful.

This is where caching becomes useful.

What is a cache?

A cache is temporary storage used to keep data that your application is likely to need again soon.

A simple analogy is your kitchen. You could put your coffee mug back into a cupboard every time you take a sip, but if you know you will need it again in two minutes, it makes more sense to leave it on the counter.
The cupboard is your permanent storage.
The counter is your cache.

In Apps Script, caching can be an effective way to reduce execution time and unnecessary calls to external services.
Google describes the Apps Script Cache service as a way to temporarily store results that take time to fetch or compute. Examples include API responses, BigQuery results, calculated datasets, reference data, or any other information that is requested frequently but does not need to be recalculated every time.

Where is CacheService data actually stored?

When you write:

const cache = CacheService.getScriptCache();

Apps Script gives you access to temporary server-side cache storage managed by Google.

The data is therefore not stored in your Google Sheet, Google Drive, browser, script properties, or local computer.

Google manages the storage infrastructure behind CacheService. You interact with it only through methods such as:

cache.get(key);
cache.put(key, value);
cache.remove(key);

You cannot open the cache somewhere and inspect its content like you would a spreadsheet or database.

More importantly, the cache should never be considered permanent storage.

Google explicitly states that cached data is not guaranteed to remain available until its expiration time. A call to get() can therefore return null even if you previously stored the value. Location must always be able to recreate the data when the cache is empty.

How does CacheService work?

CacheService stores temporary data as key/value pairs.

const cache = CacheService.getScriptCache();

cache.put("language", "en");

const language = cache.get("language");

If the key exists, get() returns its value. Otherwise, it returns null.

CacheService stores values as strings, so objects and arrays should usually be converted to JSON:

cache.put("products", JSON.stringify(products));

const products = JSON.parse(cache.get("products"));

Apps Script provides three cache scopes:

CacheService.getScriptCache();

Script cache is shared by all users of the script. It is useful for common data such as BigQuery results, API responses, or reference data.

CacheService.getUserCache();

User cache is specific to each user and script. It is useful for user preferences, filters, or recent activity.

CacheService.getDocumentCache();

Document cache is specific to the script and its container document, such as a Google Sheet. It returns null when there is no container document.

In short, the scope determines who can access the cached value, while the key determines which value you want to retrieve.

A practical example

Suppose our application regularly requests a sales summary from BigQuery.

Without caching, every call executes the query again.

Instead, we can first check whether we already have a recent result.

/**
 * Returns a sales summary from BigQuery, using CacheService
 * to avoid executing the query unnecessarily.
 *
 * @return {Array<Object>} Sales summary rows.
 */
function getSalesSummary() {
  const cache = CacheService.getScriptCache();
  const cacheKey = "sales_summary";

  const cachedData = cache.get(cacheKey);

  if (cachedData) {
    return JSON.parse(cachedData);
  }

  const projectId = "YOUR_PROJECT_ID";

  const request = {
    query: `
      SELECT
        country,
        SUM(amount) AS total_sales
      FROM \`project.dataset.sales\`
      GROUP BY country
    `,
    useLegacySql: false
  };

  const response = BigQuery.Jobs.query(request, projectId);

  const data = response.rows || [];

  cache.put(
    cacheKey,
    JSON.stringify(data),
    300
  );

  return data;
}

The 300 passed to put() represents seconds.
So the result is requested to remain cached for five minutes.
The workflow becomes:

BigQuery is never contacted during the second request. This can reduce execution time, external API calls, BigQuery usage, and unnecessary computation.

Going further, caching BigQuery queries

In a real application, using a fixed cache key such as sales_summary is often not sufficient.
Different SQL queries produce different results.
A better solution is to generate a cache key from the query itself.
That is the strategy I use the most. The SQL query is converted into a SHA-256 hash:

const queryHash = Utilities.computeDigest(
  Utilities.DigestAlgorithm.SHA_256,
  query
);

const cacheKey =
  `bq_cache_${SuperBigQuery.convertByteArrayToHex(queryHash)}`;

Then the script checks the script-level cache before calling BigQuery:

const cache = CacheService.getScriptCache();
const cachedData = SuperBigQuery.retrieveFromCache(cache, cacheKey);

if (cachedData) {
  return JSON.parse(cachedData);
}

If the query has already been executed recently, the cached result is returned.
Otherwise BigQuery is called and the result is cached afterward:

SuperBigQuery.storeInCache(
  cache,
  cacheKey,
  JSON.stringify(finalData),
  cacheExpirationMinutes
);

This means that identical SQL queries automatically share the same cached result, while different queries generate different cache keys.
This is particularly useful when several users of an application repeatedly request the same dataset.
Because the implementation uses getScriptCache(), the cached BigQuery result can be reused by all users of the script rather than being recreated separately for each user.
CacheService is designed for relatively small, temporary data.

Limitations

According to the current Apps Script documentation:

• A cache key can contain up to 250 characters.
• A single cached value can contain up to 100 KB.
• A cache can hold up to 1,000 items.
• The default expiration is 600 seconds, or 10 minutes.
• A custom expiration can be between 1 second and 21,600 seconds, or 6 hours. The expiration value is also a request rather than a guarantee. Google may remove cached data earlier. This is why CacheService should be viewed as an optimization layer, not as a database.

A query can easily return more than the 100 KB allowed for a single cache entry…
One possible solution is to split the serialized result into several cache entries.

Here is an example of function to achieve this outcom.

/**
 * Stores data in the cache, splitting the value into chunks if it is too large.
 *
 * @param {Cache} cache - The cache service.
 * @param {string} cacheKey - The key used to store the data.
 * @param {string} data - The data to store.
 * @param {number} cacheExpirationMinutes - The cache expiration time in minutes.
 */
static storeInCache(cache, cacheKey, data, cacheExpirationMinutes) {
  const MAX_SIZE = 100 * 1024; // Maximum size of 100 KB per cache entry

  if (data.length > MAX_SIZE) {
    // Split the data into chunks
    const parts = Math.ceil(data.length / MAX_SIZE);

    for (let i = 0; i < parts; i++) {
      const partKey = `${cacheKey}_part_${i}`;
      const partData = data.substring(i * MAX_SIZE, (i + 1) * MAX_SIZE);
      cache.put(partKey, partData, cacheExpirationMinutes * 60);
    }

    // Store the number of chunks
    cache.put(`${cacheKey}_parts`, parts.toString(), cacheExpirationMinutes * 60);
  } else {
    cache.put(cacheKey, data, cacheExpirationMinutes * 60);
  }
}

And then, retrieve your data as follows.

/**
 * Retrieves data from the cache, rebuilding it from chunks if necessary.
 *
 * @param {Cache} cache - The cache service.
 * @param {string} cacheKey - The key used to retrieve the data.
 *
 * @returns {string|null} The reconstructed data, or null if it does not exist.
 */
static retrieveFromCache(cache, cacheKey) {
  const parts = cache.get(`${cacheKey}_parts`);

  if (parts) {
    // Rebuild the data from its chunks
    let fullData = '';

    for (let i = 0; i < parseInt(parts, 10); i++) {
      const partKey = `${cacheKey}_part_${i}`;
      const partData = cache.get(partKey);

      if (partData) {
        fullData += partData;
      } else {
        return null; // Return null if any chunk is missing
      }
    }

    return fullData;
  } else {
    // No chunks found, try to retrieve the complete data
    return cache.get(cacheKey);
  }
}

Conclusion

CacheService is one of the simplest ways to improve the performance of an Apps Script application when the same expensive data is requested repeatedly.

The most important rule is to treat the cache as temporary and optional.

Your cache should make your application faster when the data is available, but your application should continue to work correctly when it is not.