Apps Script: Cached Key Helpers & Named Range Lookup

code.gs

/**
 * Retrieves a value from the cache if it's still valid.
 * @param {string} key - The key for the cached data.
 * @param {number} expirationInMinutes - The time after which data should be considered stale.
 * @returns {any} - The cached data, or null if it's stale or not present.
 */
function getCachedValue(key, expirationInMinutes) {
  Logger.log("getCachedValue(%s, %s)", key, expirationInMinutes.toFixed(0));

  var cache = PropertiesService.getScriptProperties();
  var cached = cache.getProperty(key);

  if (!cached) {
    return null; // No data cached under this key.
  }

  var cachedData = JSON.parse(cached);
  var currentTime = new Date().getTime();

  if (currentTime - cachedData.timestamp > expirationInMinutes * 60000) {
    return null; // Cached data is stale.
  }

  return cachedData.value;
}

/**
 * Writes a value to the cache with a timestamp.
 * @param {string} key - The key under which to store the data.
 * @param {any} value - The data to be cached.
 */
function setCachedValue(key, value) {
  Logger.log("setCachedValue(%s, typeof:{%s})", key, typeof value);

  var cache = PropertiesService.getScriptProperties();
  var dataToCache = {
    timestamp: new Date().getTime(),
    value: value
  };

  cache.setProperty(key, JSON.stringify(dataToCache));
}

/**
 * Retrieves a value from the cache, or updates it using a callback function if it's stale or non-existent.
 * @param {string} key - The key for the cached data.
 * @param {number} expirationInMinutes - The time after which data should be considered stale.
 * @param {Function} updateCallback - A callback function that returns the new data to be cached.
 * @returns {any} - The cached or newly fetched data.
 */
function getCachedOrUpdatedValue(key, expirationInMinutes, updateCallback) {
  Logger.log("getCachedOrUpdatedValue(%s, %s, typeof:{%s})", key, expirationInMinutes.toFixed(0), typeof updateCallback);

  var cachedValue = getCachedValue(key, expirationInMinutes);
  
  if (cachedValue !== null) {
    return cachedValue; // Return cached value if valid.
  } 

  // Cached data is stale or not present, fetch new data using callback.
  var newValue = updateCallback();
  setCachedValue(key, newValue); // Cache the new value.
  return newValue;
}

/**
 * Gets the A1 notation of a named range.
 * @param {Spreadsheet.Sheet} sheet - The sheet where the named range is.
 * @param {string} namedRange - The name of the range.
 * @returns {string} - The A1 notation of the named range or null if not found.
 */
function getNamedRangeA1Notation(sheet, namedRange) {
  var range = sheet.getRange(namedRange);
  return range ? range.getA1Notation() : null;
}

/**
 * Reconstitutes a named range from its A1 notation.
 * @param {Spreadsheet.Sheet} sheet - The sheet where the named range should be.
 * @param {string} a1Notation - The A1 notation string of the range.
 * @returns {Spreadsheet.Range} - The Range object or null if not valid.
 */
function reconstituteNamedRangeFromA1(sheet, a1Notation) {
  try {
    return sheet.getRange(a1Notation);
  } catch (e) {
    // Invalid A1 notation or other error.
    return null;
  }
}

/**
 * Retrieves a named range from cache, or updates it using the A1 notation if it's stale or non-existent.
 * @param {Spreadsheet.Sheet} sheet - The sheet where the named range is.
 * @param {string} namedRangeName - The name of the named range.
 * @param {number} expirationInMinutes - The time after which the cached data should be considered stale.
 * @returns {Spreadsheet.Range} - The named Range object or null if not valid.
 */
function getCachedOrUpdatedNamedRange(sheet, namedRangeName, expirationInMinutes) {
  var cachedNamedRangeA1 = getCachedOrUpdatedValue(
    `namedRange_${namedRangeName}`,
    expirationInMinutes,
    () => getNamedRangeA1Notation(sheet, namedRangeName)
  );

  if (!cachedNamedRangeA1) {
    return null; // Unable to find or cache the named range.
  }

  return reconstituteNamedRangeFromA1(sheet, cachedNamedRangeA1);
}