In a previous post, I introduced the concept of local caching of Google Docs Spreadsheet Values to be able to optimize Google Apps Script without needing to change the structure of your code. This gave us speed increases about a factor 70 versus accessing values one by one.
Noticing that there are other Range methods, such as getBackgroundColors(), getComments() etc, all of which are really slow too, I wondered if they could also be cached. Of course they can, and with actually pretty minimal effort, I generalized the caching class to handle any method that can return a ‘Range shaped’ set of values or objects.
Here an example of one usage.
By default,
var x = sheetCache(wn).getValue(rn,cn);
will return the value at row rn, column cn for the worksheet identified by wn (where wn is a worksheet name, a worksheet, or a range)
However, this will return the background color
var x = sheetCache(wn,'getBackgroundColors').getValue(rn,cn);
and this, the horizontalAlignments
var x = sheetCache(wn,'getHorizontalAlignments').getValue(rn,cn);
and so on for all the Range Methods.
In all cases, any request for a particular type of data will cause the entire sheet to be read into local cache and future accesses will be from cache rather than having to make an internet call.
For more information on other usages, and to get all the code, see optimization
