Hide rows if cell in column C is empty-CodePudding

I have a spreadsheet with a Home Page which has buttons to open several sheets, including the "Admin Drivers" sheet.

The "Admin Drivers" sheet has 2000 unique Employee IDs in column A.

An ArrayFormula/ImportRange formula looks up the Employee ID in another spreadsheet and automatically populates columns B, C and D.

I am trying to get the Apps Script code behind the button to show all rows and then hide all rows where columns B, C and D have not been populated. The formula will always populate all 3 columns B, C and D, so the script could point to either of the 3 columns. This is my script which points to column C:

//Admin Drivers Button
function GoToAdminDriversSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Admin Drivers");
  var data = sheet.getDataRange().getValues();
  sheet.showRows(3 , 2000);
    for(var i = 1; i < data.length; i  ) {
    //If column C is " " then hide the row.
    if(data[i][2] === " ") {
      sheet.hideRows(2, 2000);
    
    }
  }
}

The script opens the sheet and shows all rows but I just cannot get it to hide rows when column C is blank.

CodePudding user response：

Try this:

function GoToAdminDriversSheet() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Admin Drivers");
  var vs = sh.getRange(3, 1, sh.getLastRow() - 2, sh.getLastColumn()).getValues();
  sh.showRows(3, 2000);
  vs.forEach((r, i) => {
    if (r[2] === '') {
      sh.hideRows(i   3);
    }
  });
}