Google Sheets: Hide rows if a certain cell is empty

0
votes

I'm working now on hiding a row if a certain cell is empty. The code for hiding a rows works, but I encountered a problem with it. Here's a sample spreadsheet that I'm trying to do: LINK

As you can see, it will work if column A didn't have any value on it. enter image description here

But when I put something in column A, it will look like this. enter image description here

Here's the code for hiding the rows:

var startRow = 6;
var colToCheck = 2;

function script_HideRows() {
  var sheetNames = ["MS_Q1", "MS_Q2", "MS_Q3", "MS_Q4", "SUMMARY"];  // Please set the sheet names here. In this case, 4 sheets are used.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(sheet => {
    var sheetName = sheet.getSheetName();
    if (sheetNames.includes(sheetName)) {
      if (sheetName == "SUMMARY") {  // When the sheet is "SUMMARY", the start row is changed.
        startRow = 7;
      }
      var numRows = sheet.getLastRow();
      var elements = sheet.getRange(startRow, colToCheck, numRows).getValues();
     
      for (var i=0; i<(numRows - startRow); 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);
    }
  });
}

I got that code from here: LINK

I just want to hide all the rows that have an empty cell within the column B.

I hope someone can help me. Sorry for the poor English. Thank you in advance!

1
google-apps-scriptgoogle-sheetsrows
1) you haven't posted the full code, there are functions and variables used in this code snippet that are not defined anywhere. 2) I checked the sheet you shared, and when you select var colToCheck = 2 which means column B, the code works fine. 3) Having said all that, what is the error you are encountering? Provide a minimal reproducible example. – soMario
From your script in your question, I guessed the modification point as an answer. Could you please confirm it? If that was not the direct solution of your issue, I apologize. – Tanaike

1 Answers

2
votes

I thought that in your script, numRows - startRow might be different from the length of elements. So in this case, how about the following modification?

From:

for (var i=0; i<(numRows - startRow); i++) {

To:

for (var i=0; i < elements.length; i++) {