Google Apps Script access to Spreadsheets – up to 100 times faster

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

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.