Hide rows in multiple sheets using array in Google Script Apps

Question

I'm trying to hide rows in multiple sheets in Google Sheet. For your reference, here is a sample: LINK.

The script I'm currently using only works in two sheets (STUDENT and ATTENDANCE shhets). However, I still have four sheets (CORE VALUES, NARRATIVE REPORT, GRADING SHEET, and RANKING SHEET) that need to hide rows.

I'm not sure what's wrong because I tried the same script on different files and it worked. I only edit this portion var sheetNames = ["STUDENTS", "RANKING", "GRADING SHEET", "CORE VALUES", "ATTENDANCE", "NARRATIVE REPORT"]; because the sheet names are different.

function HideRows() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  UrlFetchApp.fetch(url + "?run=script_HideRows&sheetName=" + activeSheet.getSheetName(), {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()},'muteHttpExceptions': true});
}

var startRow = 6;
var colToCheck = 2;

function script_HideRows() {
  var sheetNames = ["STUDENTS", "RANKING", "GRADING SHEET", "CORE VALUES", "ATTENDANCE", "NARRATIVE REPORT"];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(sheet => {
    var sheetName = sheet.getSheetName();
    if (sheetNames.includes(sheetName)) {
      if (sheetName == "RANKING") {
        startRow = 10;
      }
      var numRows = sheet.getLastRow();
      var elements = sheet.getRange(startRow, colToCheck, numRows).getValues();
     
      for (var i=0; i < elements.length; i++) {
        if (shouldHideRow(sheet, i, elements[i][0])) {
          sheet.hideRows(startRow + i);
        }
      }
      // Hide the rest of the rows
      var totalNumRows = sheet.getMaxRows();
      if (totalNumRows > numRows)
        sheet.hideRows(numRows+1, totalNumRows - numRows);
    }
  });
}

function shouldHideRow(ss, rowIndex, rowValue) {
  if (rowValue == "" || rowValue == '#REF!') return true; // <--- Added
  if (rowValue != '') return false;
  if (ss.getRange(startRow + rowIndex, colToCheck, 1, 1).isPartOfMerge()) return false;
  if (ss.getRange(startRow + rowIndex + 1, colToCheck, 1, 1).isPartOfMerge()) return false;
  return true;
}

Answer 1

Although I'm not sure whether I could correctly understand your question, when I saw your script and your provided Spreadsheet, I was worried that when startRow + i of sheet.hideRows(startRow + i) is more than the maximum row of the sheet, an error might occur. So, how about the following modification?

From:

for (var i=0; i < elements.length; i++) {
  if (shouldHideRow(sheet, i, elements[i][0])) {
    sheet.hideRows(startRow + i);
  }
}
// Hide the rest of the rows
var totalNumRows = sheet.getMaxRows();
if (totalNumRows > numRows)
  sheet.hideRows(numRows+1, totalNumRows - numRows);

To:

var totalNumRows = sheet.getMaxRows();
for (var i = 0; i < elements.length; i++) {
  if (shouldHideRow(sheet, i, elements[i][0]) && startRow + i <= totalNumRows) {
    sheet.hideRows(startRow + i);
  }
}
if (totalNumRows > numRows) {
  sheet.hideRows(numRows + 1, totalNumRows - numRows);
}

• By this modification, sheet.hideRows(startRow + i) is run when startRow + i is less than maxRows.