CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mohdiouf
Mohdiouf

Reputation: 41

Hiding and unhiding spreadsheet rows using Google Apps Script

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

Upvotes: 4

Views: 3675

Answers (1)

chuckx
chuckx

Reputation: 6877

  • 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:

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);
    }
  }
}

Upvotes: 3

Related Questions