Let’s say we have arrays like this.
Hide these rows
var hide = [3, 5, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 20]
Unhide these
var unhide = [0, 1, 2, 4, 6, 10, 19]
(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
hide.forEach(function (d) {
sheet.hideRows(d+2, 1)
});
unhide.forEach(function (d) {
sheet.showRows(d+2, 1)
});
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.
/**
* optimize consecitive rows for hiding/unhiding
* @param [list] list of numbers
* @return [{object}] optimized {start:x,count:y}
*/
function optimizeConsecutive (list) {
return list.reduce (function (p,c) {
if (!p.length || p[p.length-1].start + p[p.length-1].count !== c) {
p.push ({
start:c,
count:0
});
}
var prev = p[p.length-1];
prev.count ++;
return p;
} , []);
}
Which transforms our arrays into this.
[{
"start": 3,
"count": 1
}, {
"start": 5,
"count": 1
}, {
"start": 7,
"count": 3
}, {
"start": 11,
"count": 8
}, {
"start": 20,
"count": 1
}]
[{
"start": 0,
"count": 3
}, {
"start": 4,
"count": 1
}, {
"start": 6,
"count": 1
}, {
"start": 10,
"count": 1
}, {
"start": 19,
"count": 1
}]
So now we call the API only 10 times rather than 21.
optimizeConsecutive(hide).forEach(function (d) {
sheet.hideRows(d.start+2, d.count)
});
optimizeConsecutive(unhide).forEach(function (d) {
sheet.showRows(d.start+2, d.count)
});
Does it make a difference ?Heres’ the optimized version .. just over 2 seconds.
16-09-12 16:04:44:380 BST] Sheet.hideRows([5, 1]) [0.123 seconds] [16-09-12 16:04:44:591 BST] Sheet.hideRows([7, 1]) [0.21 seconds] [16-09-12 16:04:44:866 BST] Sheet.hideRows([9, 3]) [0.275 seconds] [16-09-12 16:04:45:482 BST] Sheet.hideRows([13, 8]) [0.615 seconds] [16-09-12 16:04:45:772 BST] Sheet.hideRows([22, 1]) [0.289 seconds] [16-09-12 16:04:45:959 BST] Sheet.showRows([2, 3]) [0.186 seconds] [16-09-12 16:04:46:142 BST] Sheet.showRows([6, 1]) [0.182 seconds] [16-09-12 16:04:46:274 BST] Sheet.showRows([8, 1]) [0.131 seconds] [16-09-12 16:04:46:408 BST] Sheet.showRows([12, 1]) [0.133 seconds] [16-09-12 16:04:46:572 BST] Sheet.showRows([21, 1]) [0.163 seconds]
And the unoptimized version .. just under 8 seconds.
[16-09-12 16:06:34:271 BST] Range.getValues() [0.092 seconds] [16-09-12 16:06:34:433 BST] Sheet.showRows([2, 1]) [0.157 seconds] [16-09-12 16:06:34:894 BST] Sheet.showRows([3, 1]) [0.46 seconds] [16-09-12 16:06:35:251 BST] Sheet.showRows([4, 1]) [0.356 seconds] [16-09-12 16:06:35:494 BST] Sheet.showRows([6, 1]) [0.242 seconds] [16-09-12 16:06:35:764 BST] Sheet.showRows([8, 1]) [0.27 seconds] [16-09-12 16:06:35:952 BST] Sheet.showRows([12, 1]) [0.187 seconds] [16-09-12 16:06:36:196 BST] Sheet.showRows([21, 1]) [0.243 seconds] [16-09-12 16:06:36:412 BST] Sheet.hideRows([5, 1]) [0.215 seconds] [16-09-12 16:06:36:741 BST] Sheet.hideRows([7, 1]) [0.329 seconds] [16-09-12 16:06:37:056 BST] Sheet.hideRows([9, 1]) [0.314 seconds] [16-09-12 16:06:37:437 BST] Sheet.hideRows([10, 1]) [0.38 seconds] [16-09-12 16:06:38:227 BST] Sheet.hideRows([11, 1]) [0.79 seconds] [16-09-12 16:06:39:119 BST] Sheet.hideRows([13, 1]) [0.891 seconds] [16-09-12 16:06:39:649 BST] Sheet.hideRows([14, 1]) [0.529 seconds] [16-09-12 16:06:39:948 BST] Sheet.hideRows([15, 1]) [0.298 seconds] [16-09-12 16:06:40:186 BST] Sheet.hideRows([16, 1]) [0.238 seconds] [16-09-12 16:06:40:452 BST] Sheet.hideRows([17, 1]) [0.265 seconds] [16-09-12 16:06:40:698 BST] Sheet.hideRows([18, 1]) [0.245 seconds] [16-09-12 16:06:40:920 BST] Sheet.hideRows([19, 1]) [0.222 seconds] [16-09-12 16:06:41:125 BST] Sheet.hideRows([20, 1]) [0.204 seconds] [16-09-12 16:06:42:199 BST] Sheet.hideRows([22, 1]) [1.073 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.