Hiding and unhiding spreadsheet rows using Google Apps Script

Question

I am completely new in writing scripts and I have just learned about Google Script Editor. I am currently trying to write one to automatically hide rows based on values on my spreadsheet. I managed to do it looking at various websites but I also want to unhide the rows if the value happens to change.

I'm new to this so I have copied the script I currently have but I think I have a problem in line 12 (the 'else' part).

   function onEdit() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = ss.getActiveSheet();
     var LastRow = Sheet.getLastRow();
     for( i=1 ; i<=LastRow ; i++) { // i <= lastrow
       var status = Sheet.getRange("A"+i).getValue(); // Change A to 
the completed column
       if (status == 0) {                                          //     status == 0
         Sheet.hideRows(i);
       else {
           Sheet.unhideRows(i);
              }
       }
     }
   }

I would kindly like someone to help me with writing the correct code at the end to be able to unhide rows that were previously hidden based on values. I happen to work with data that's constantly changing and hiding/unhiding rows is very important to me. Thank you

How would the status change from 0 to something else, if the rows were hidden? — TheMaster

chuckx chuckx · Accepted Answer · 2019-09-08T06:27:10

You need to close the if block before starting the else clause. If you format your code cleanly, it's easier to notice such syntax errors.
The Sheet class does not have an unhideRows() method.

There are some important differences to note between the methods used for hiding vs. unhiding rows.

For hiding, these methods are provided:

For unhiding, a single method is provided:

unhideRow(row).

As mentioned above, there are no unhide equivalents to the hideRows() methods.

hideRow(row) and unhideRow(row) both take a Range object as an argument, as opposed to a row number.

Therefore you need to store the Range object returned from the getRange() method, so you can provide it as an argument when you call hideRows().

Here's a cleaned up and corrected version:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet = ss.getActiveSheet();
  var LastRow = Sheet.getLastRow();
  for (i = 1; i <= LastRow; i++) {
    var statusRange = Sheet.getRange("A" + i);
    if (statusRange.getValue() == 0) {
      Sheet.hideRows(i);
    } else {
      Sheet.unhideRow(statusRange);
    }
  }
}

Hiding and unhiding spreadsheet rows using Google Apps Script

1 Answers