Speeding up Google Apps Script

It’s well known that reading all the values at once from a Google Spreadsheet is much more efficient than doing it one by one. You can work round this if you are starting from scratch, but what if you are migrating from Excel/VBA and want to minimize the structural changes? Of course in Excel, it’s also more efficient to read all the values at once, but not the order of magnitude you find with Google Apps Script.

Caching Values

It occurred to me that it would be pretty straightforward to abstract getting spreadsheet values using a caching mechanism, so I put one together for a complex project migration from Excel to Google Apps Script. You can get the code for the getValues() cache here.

How much faster

I took a small sheet, a few hundred cells, and found that using this caching mechanism, it was a factor of 70 times faster.


Heres the test results

Heres the test code

 

About brucemcp 223 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.

1 Comment

Leave a Reply

Your email address will not be published.


*


thirteen − 10 =