What is caching?

A cache is a place where you store data that you want to access quickly. The process of storing data in the cache is called caching.

When should you use a cache?

Caching is primarily used to make applications and websites faster. For example, your web browser has an in-built cache where it stores content from websites that you visit. When you visit one of these websites again, it will load quickly since the browser is reading from its cache and it doesn't have to download the content again from the website's servers.

Caching can also reduce costs. For example, imagine a computation that is very expensive to perform because it requires processing lots of data. In this case, if 10 users want to perform the same computation then you can save money by caching the result that was obtained after the first computation and avoid having to re-compute the same information subsequently. Now, if a user wants to perform a different computation, one whose result wasn't previously cached, you will have to incur cost. However, you can avoid spending money on computing the same thing over and over again.

When should you NOT use a cache?

If your website or application relies on data that is real-time (e.g. financial applications involving trading equities), then the data in your cache could be stale and using this data could lead to errors. Caching may not be useful if the data you are trying to cache changes rapidly and your business use case relies on real-time information. By the time you store data in the cache, it may already be stale.

Understanding caching using Google Sheets

Let's imagine you work at a used car dealership and you're analyzing sales from the past year to figure out which used car models are most popular.

You export sales data into a spreadsheet that contains the following columns:

• id: The ordinal number of the sale that year (1 = first sale that year, 2 = second sale and so on).

• firstName: The first name of the customer.

• lastName: The last name of the customer.

• carMake: The make of the car.

• carModel: The model of the car.

• carYear: The year of the car.

Let's also imagine that your dealership made 1000 sales last year. So the spreadsheet has 1001 rows (including the header row).

Now, in order to analyze this data, you decide to write a custom function that you can use in the spreadsheet. This function will read all the rows in the spreadsheet, perform some calculations and then return a result that will then be displayed in the cell containing the formula.

Here is how that function might look:

//@OnlyCurrentDoc

function loadDataFromSheet() {
 return SpreadsheetApp
   .getActive()
   .getSheetByName("Data")
   .getDataRange()
   .getValues();
}

/**
* @customfunction
*/
function LOAD_DATA() {
 let data = loadDataFromSheet();
 //Do some processing on the data.
 //Return a value.
}

The function loadDataFromSheet() reads 1001 rows from your spreadsheet each time it is called. This could take time. So let's modify the function to simply return the amount of time it takes to load data.

/**
 * @customfunction
 */
function LOAD_DATA() {
  let startTime = new Date();
  let data = loadDataFromSheet();
  let endTime = new Date();
  return endTime - startTime;
}

Now, when you use the function LOAD_DATA() in a spreadsheet formula, you will see the time in milliseconds that it took to load all 1001 rows from the spreadsheet. There is no caching being done. Each time, the function loads the same amount of data and then returns the time it took to load that data.

Next, let's create another function called LOAD_DATA_WITH_CACHING() that uses caching.

The LOAD_DATA_WITH_CACHING() function uses a document-level cache to store the data we loaded from the sheet. First, the function checks to see if the data is present in the cache. If yes, it loads the data directly from the cache. Otherwise, it loads data from the spreadsheet using the same loadDataFromSheet() function that we used before. The LOAD_DATA_WITH_CACHING() function also returns the time in milliseconds that it took to load the data.

/**
 * @customfunction
 */
function LOAD_DATA_WITH_CACHING() {
  let startTime = new Date();
  let cache = PropertiesService.getDocumentProperties();
  let data = cache.getProperty("data");
  if(data === null) {
    data = loadDataFromSheet();
    cache.setProperty("data", data);
  }
  let endTime = new Date();
  return endTime - startTime;
}

Now comes the fun part. Let's try loading data 15 times without caching and 15 times with caching.

The chart below plots results from one of my runs. The x-axis tracks the iterations (1 to 15) and the y-axis shows the time in milliseconds that it took. As you can see, the first run takes about the same amount of time whether you use caching or not. However, when you cache data, subsequent runs are a LOT faster!

Clearing the cache

To delete data you've previously cached, use the deleteProperty() method to delete data associated with a specific key, and deleteAllProperties() to delete all cached data.

The function clearCache() below deletes all the data cached in the script-level cache.

function clearCache() {
 let cache = PropertiesService.getScriptProperties();
 cache.deleteAllProperties();
}

The function clearData() below deletes data associated with the key "data" in the script-level cache.

function clearData() {
  let cache = PropertiesService.getScriptProperties();
  cache.deleteProperty("data");
}

Conclusion

In this tutorial, you learned about caches and caching. A cache is a place where you store data that you want to access quickly. Caching is the process of storing data in the cache. Caching is primarily used to make applications and websites faster. In the example above, the time it took to load data reduced 10X when caching was used.

Thanks for reading!