Hide rows where column F is not empty

Question

I'm not a scripting expert by any means, but I put together some code that I needed for Google Sheets. The good news is that it actually works!

However, it's noticeably slow to run (about 15 seconds). I'm sure it's not optimized to deal with arrays and other fun things that I don't fully understand...

function ShowHideClosedCards() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();

  strVisible = sheet.getRange('N1').getValue(); //Get the value of N1
  if (strVisible == '** Closed accounts are visible **') { //If the closed accounts are visible...
      for( i=6 ; i<=lastRow ; i++) { // Start with row 6 and continue through the last row: i <= lastRow
       var status = sheet.getRange("F"+i).getValue(); //Get the value of the cell.
       if (status !== "") { // If there's something in the "Date Closed" cell, then that should mean it's closed
         sheet.hideRows(i); // Hide the row
       } 
       }
       sheet.getRange('N1').setValue('** Closed accounts are hidden **');
  } else {  // Otherwise, assume the closed accounts are hidden...
       for( i=6 ; i<=lastRow ; i++) { // Start with row 6 and continue through the last row: i <= lastRow
       var status = sheet.getRange("F"+i).getValue(); //Get the value of the cell.
       if (status !== "") { // If there's something in the "Date Closed" cell, then that should mean it's closed
         sheet.showRows(i); // Show the row
       } 
       }
       sheet.getRange('N1').setValue('** Closed accounts are visible **');
  } 
}

The gist of it is that it should go through each row (from 6 through 500) and if there's anything in column F of that row, hide the row. Then it marks one cell (N1) to let the user know that the closed accounts are hidden.

If the user runs the code again, it should do the same thing, but unhide those rows (and only those rows). Then it marks N1 again to let the user know that the closed accounts are visible.

Any suggestions on how to get it to run faster?

Answer 1

In order to improve the performance of your script you should read cell values from an array that represents the entire range that your are going to scan.

This means that instead of repetedly calling

var status = sheet.getRange("F"+i).getValue(); //Get the value of the cell.

you should get everything in a single call like this one

var values = getRange(row, column, numRows, numColumns).getValues();

it'll give you an array that you can iterate very fast. Example:

for (var row in values) {
  for (var col in values[row]) {
      ...
  }
}

---

Hiding rows can be a little bit tricky if you want to optimize it becasue it requries some additional work. If you do it for each row separately then you'll loose some performance agian. This means that this is not good:

sheet.hideRows(i); // Hide the row

In my scripts that work with a few hundereds or thousands rows hiding some of them lasts no longer than 1-2 seconds.

Here's how it goes:

I have a special column that I call Visible (or whatever). I use it to store a boolean value.

When I run a loop I store the flag in a second array like var visibles = []; instead of calling hideRows each time.

After the loop is finished I update the Visible column with:

// update row visibility
var visibleRange = sheet.getRange(row, column, numRows, numColumns);
visibleRange.setValues(visibles);

This runs super fast.

Next you have to unhide all rows with:

// show all rows
sheet.showRows(rowIndex, numRows);

You have to sort them by visibility:

// sort by 'visible' and make 'true' (visible) rows first
sheet.sort(..., false);  

Sorting is necessary so that you can finally hide an entire range in a blink of an eye:

// hide 'false' (not-visible) rows
var rangeToHide = sheet.getRange(.., .., ..);
SpreadsheetApp.getActiveSpreadsheet().hideRow(rangeToHide);

If you need a different order you can sort it again after hiding.

With this solution it requires just a couple of calls which will make your script really smooth.

---

You can read about a couople more optimization tipps on Google Apps Script - Best Practices