Skip to content

Instantly share code, notes, and snippets.

@emmaly
Created December 4, 2023 23:39
Show Gist options
  • Save emmaly/8d8d77b05cc22ae8f101df9a1ae5d92c to your computer and use it in GitHub Desktop.
Save emmaly/8d8d77b05cc22ae8f101df9a1ae5d92c to your computer and use it in GitHub Desktop.
Apps Script: Cached Key Helpers & Named Range Lookup
/**
* 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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment