Google表格:隐藏多个表格中的行 [英] Google Sheets: Hide rows in multiple sheets

问题描述

我正试图隐藏多张纸上的行.所以我有6张纸，但有4张类似的纸，另一张更可能是这4张纸的摘要，另一张是带有按钮的纸.以下是供您参考的示例: LINK

如果我将隐藏行"按钮一张一张地放在工作表上是可行的，但是我需要花费很多时间，因为我需要将其放在不同的电子表格中.我的想法是为每个电子表格放置一个按钮，或者在打开它时，将隐藏空白行. SHOW ROWS按钮可以完美地使用它.

这是我正在使用的脚本: LINK

问题:

• 使用相似的模板隐藏多个工作表中的行
• 摘要表行应与4张表一起隐藏(不是同一行)

我希望有人能帮助我.预先谢谢你！

解决方案

我相信您的目标如下.

• 使用脚本，您想在单击按钮时为Google Spreadsheet中的多个工作表运行脚本.
  • 在示例电子表格中，您要为"MS_Q1", "MS_Q2", "MS_Q3", "MS_Q4", "SUMMARY"的工作表运行脚本.
  • 对于摘要"；工作表中，开始行与其他行不同. 7.

修改点:

• 在这种情况下，对于脚本，我想建议修改功能script_HideRows.
• 首先，设置要运行脚本的工作表名称.然后，使用工作表名称运行脚本.

当以上几点反映到脚本中时，它如下所示.

修改后的脚本:

请按如下所示修改脚本的script_HideRows.

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

注意:

• 如果即使您复制并粘贴了上面的脚本，该脚本也没有更改，请重新部署Web Apps作为新版本.

参考文献:

• getSheets()
• includes()

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!

解决方案

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:

• getSheets()
• includes()

这篇关于Google表格:隐藏多个表格中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！