Optimizing showing and hiding rows and columns

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
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.


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. 



Comments