You all know that trying to minimize calls to the spreadsheet API from Apps Script can dramatically speed things up. Let’s take a look at hiding and showing rows and columns, where we’re starting from a list of row/column numbers we want to operate on.

Let’s say we have arrays like this.

Hide these rows

Unhide these

(Note that my row number 0 is the first row of data after the column headings – so in Apps Script terms – row 0 is rowIndex 2.)

The obvious way would be

But that would mean making 21 API calls, and not taking advantage of the second argument to hideRows and showRows – the number of rows to hide/show.

So better if we could find a way of optimizing the array so that we minimize the number of calls by making use of consecutive row numbers. Here’s a function to do that.

Which transforms our arrays into this.

So now we call the API only 10 times rather than 21.

Does it make a difference ?
Heres’ the optimized version .. just over 2 seconds.

And the unoptimized version .. just under 8 seconds.

With just that small sample, we see a 4x improvement. In Apps Script, performance matters a lot if you don’t want to hit quota limits.

For more like this see Google Apps Scripts Snippets
Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.