javascript - Store Google API results in cache indefinitely

Question

I am trying to have the code (a custom function for Google Sheets) below store the results of the API queries in a cache indefinitely.

// The cache key for "New York" and "new york  " should be same
const md5 = (key = '') => {
  const code = key.toLowerCase().replace(/s/g, '');
  return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key)
    .map((char) => (char + 256).toString(16).slice(-2))
    .join('');
};

const getCache = (key) => {
  return CacheService.getDocumentCache().get(md5(key));
};

// Store the results for 6 hours
const setCache = (key, value) => {
  const expirationInSeconds = 6 * 60 * 60;
  CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);
};

const GOOGLEMAPS_DISTANCE = (origin, destination, mode = 'driving') => {

  const key = ['distance', origin, destination, mode].join(',');
  // Is result in the internal cache?
  const value = getCache(key);
  // If yes, serve the cached result
  if (value !== null) return value;
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    GOOGLEMAPS_DISTANCE;
  }
  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
  // Store the result in internal cache for future
  setCache(key, distance);
  return distance;
};

Using the Google Maps API, the function is able to return the distances between two locations specified in a Google Sheet, and save the results in a cache for 6 hours. If the newly entered locations have previously been retrieved and cached, the cached result will be retrieved, otherwise, a new API query is sent.

According to Google's documentation on Class Cache, the results can only be stored in the internal cache for up to 6 hours with the put call:

expirationInSeconds:

Integer:

the maximum time the value remains in the cache, in seconds. The minimum is 1 second and the maximum is 21600 seconds (6 hours).

Although, I read about Place IDs (documentation) that could be a possible workaround for the 6 hour limit.

From the Places API Policies documentation:

Note that the place ID, used to uniquely identify a place, is exempt from the caching restriction. You can therefore store place ID values indefinitely. The place ID is returned in the place_id field in Places API responses.

Another possible workaround that I have found is using Properties in the Google Properties Service Documentation, but I can not seem to implement it into the code.

I would highly appreciate it if anyone would be kind enough to respond and help me with this.

Answer 1

You have 2 options:

• Use Properties to store data indefinitely.
• Keep using cache and set a trigger that every 5 hours (before cache expires) restores the cache value for other 6 hours.

https://developers.google.com/apps-script/reference/properties

To use properties, change your getCache and setCache functions for this:

const getProperty = (key) => {
  var scriptProperties = PropertiesService.getScriptProperties();
  return scriptProperties.getProperty(md5(key));
};

const setProperty = (key, value) => {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty(md5(key), value);
};