Google Sheets: Hide rows in multiple sheets

0
votes

I'm trying to hide rows across a multiple sheets. So I have a 6 sheets, but there are 4 similar sheets, the other one is more likely a summary of that 4 sheets and the other one is the sheet with a button. Here's a sample for your reference: LINK

It is working if I will put a HIDE ROWS button on sheets one by one, but it will take a lot of time for me to do it since I need to put it on different spreadsheets. My idea is to put a button per spreadsheet or upon opening it, the rows that are empty will be hidden. The SHOW ROWS button is perfectly working with it.

Here's the script that I'm using: LINK

PROBLEM:

  • Hide rows across multiple sheets with similar templates
  • Summary sheets rows should be hidden together with 4 sheets (not same rows)

I hope that someone can help me. Thank you in advance!

1
google-apps-scriptgoogle-sheetsrow
About the sheets of "MS_Q1", "MS_Q2", "MS_Q3", "MS_Q4", the hide rows are the same. Is my understanding correct? And. can I ask you about the detail of not same rows of Summary sheets rows should be hidden together with 4 sheets (not same rows)? - Tanaike
@Tanaike Yes, it is correct. The summary sheet starts in different rows, but I want it also to be hidden when I click the hide row button. - cjvdg
Thank you for replying. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question, I apologize. - Tanaike
@Tanaike it kinda worked, but there's a problem. Row 9 & 10 are not hidden on the Q1, Q2, Q3, Q4. I edited the sample sheet for you to see it. - cjvdg

1 Answers

2
votes

I believe your goal as follows.

  • Using your script, you want to run the script for several sheets in a Google Spreadsheet, when a button is clicked.
    • In your sample Spreadsheet, you want to run the script for the sheets of "MS_Q1", "MS_Q2", "MS_Q3", "MS_Q4", "SUMMARY".
    • For "SUMMARY" sheet, the start row is different from others. It's 7.

Modification points:

  • In this case, for the script, I would like to propose to modify the function script_HideRows.
  • At first, the sheet names you want to run the script are set. And, using the sheet names, the script is run.

When above points are reflected to the script, it becomes as follows.

Modified script:

Please modify script_HideRows of the script as follows.

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

Note:

  • If the script is not changed even when you copied and pasted above script, please redeploy the Web Apps as new version.

References: