根据复选框隐藏行-Google App脚本 [英] Hide Rows Based on Check Boxes - Google App Script

问题描述

我正在尝试根据"C"列中选中的复选框隐藏某些行.

I'm trying to hide certain rows based on checkboxes that are checked in column 'C'.

简单地说，如果选中了选项A"，我只想显示第11-13行和第22行.如果选中了选项B".我只想显示第14-15行和第22行，等等.

Simply put if 'Option A' is checked, I only want to show rows 11 - 13, and row 22. If 'Option B' is checked. I only want to show rows 14 - 15, and row 22, etc.

我也想取消选择后恢复它们.这比起任何其他事情来说，都是更多的学习练习，因此，我稍后将介绍选中多个复选框".

I also would like to have them revert back after I uncheck. This is more of a learning exercise than anything, so I will get to 'checking multiple boxes' later.

我想知道那里是否也应该有一些"for"逻辑?我只是不确定在哪里...

I'm wondering if there should be some 'for' logic in there as well? I'm just not sure where...

任何帮助或建议将不胜感激！如有需要，我们很乐意提供更多详细信息.

Any help or recommendations would be greatly appreciated! Happy to get into more detail if need be.

非常感谢

-M

推荐答案

此修改如何?我认为您的情况有几个答案.因此，请将此视为其中之一.

How about this modification? I think that there are several answers for your situation. So please think of this as one of them.

• 使用onEdit()运行脚本. @I'-'I提到了这一点.
• 检索所有复选框的值.
• 显示和隐藏与每个复选框相对应的行.

• Run the script using onEdit(). This was mentioned by @I'-'I.
• Retrieve the values of all checkboxes.
• the rows which correspond to each checkbox are shown and hidden.

使用此修改后的脚本时，

When you use this modified script,

• 请将此脚本复制并粘贴到您的脚本编辑器中，然后保存.当您选中工作表上的复选框时，将显示和隐藏行.
  • 使用此脚本之前，请确认项目中没有onEdit()函数.
  • Please copy and paste this script to your script editor, and save it. When you check to the checkbox on the sheet, the rows are shown and hidden.
    • Before you use this script, please confirm that there are no onEdit() functions in your project.

    function onEdit(e) {
      var cfg = { // Please set this object.
        C7: {startRow: 11, endRow: 13},
        C8: {startRow: 14, endRow: 16},
        C9: {startRow: 17, endRow: 19},
        C10: {startRow: 20, endRow: 21}
      };
    
      var activeRange = e.range.getA1Notation();
      var ranges = Object.keys(cfg);
      if (cfg[activeRange]) {
        var sheet = e.source.getActiveSheet();
        var values = sheet.getRange(ranges[0] + ":" + ranges[ranges.length - 1]).getValues();
        values.forEach(function(e, i) {
          if (e[0]) {
            sheet.showRows(cfg[ranges[i]].startRow, cfg[ranges[i]].endRow - cfg[ranges[i]].startRow);
          } else {
            sheet.hideRows(cfg[ranges[i]].startRow, cfg[ranges[i]].endRow - cfg[ranges[i]].startRow);
          }
        });
      }
    }
    

    注意:

    • 在此修改后的脚本中，如果多个复选框为true，则显示与每个复选框相对应的行.
    • 如果要在特定工作表上运行脚本，请修改为if (cfg[activeRange] && e.source.getSheetName() == "### sheet name ###") {.

    Note :

    • In this modified script, if several checkboxes are true, the rows which correspond to each checkbox are shown.
    • If you want to run the script at the specific sheet, please modify to if (cfg[activeRange] && e.source.getSheetName() == "### sheet name ###") {.
    • 事件对象

    如果我误解了您的问题，请告诉我.我想修改它.

    If I misunderstand your question, please tell me. I would like to modify it.

    当用户打开电子表格时，您希望不隐藏就显示所有行.如果我的理解是正确的，那该怎么办?

    You want to show all rows without hiding, when users open the Spreadsheet. If my understanding is correct, how about this?

    function onOpen(e) {
      var cfg = { // Please set this object.
        C7: {startRow: 11, endRow: 13},
        C8: {startRow: 14, endRow: 16},
        C9: {startRow: 17, endRow: 19},
        C10: {startRow: 20, endRow: 21}
      };
      var sheet = e.source.getSheetByName("### sheet name ##");
      var ranges = Object.keys(cfg);
      sheet.showRows(cfg[ranges[0]].startRow, cfg[ranges[ranges.length - 1]].endRow - cfg[ranges[0]].startRow);
      sheet.getRange(ranges[0] + ":" + ranges[ranges.length - 1]).setValue(true);
    }
    

    • 使用此功能时，请将其复制并粘贴到脚本编辑器中.
    • onOpen()可用于在打开电子表格时运行.
    • 您还可以将cfg用作全局变量.
    • 将sheet.getRange(ranges[0] + ":" + ranges[ranges.length - 1]).setValue(true);的true修改为false时，打开电子表格时，所有复选框均未选中，并显示所有行.
    • When you use this, please copy and paste it in your script editor.
    • onOpen() can be used for running when the spreadsheet is opened.
    • You can also use cfg as a global variable.
    • When true of sheet.getRange(ranges[0] + ":" + ranges[ranges.length - 1]).setValue(true); is modified to false, when the spreadsheet is opened, all checkboxes are no check and all rows are shown.

    这篇关于根据复选框隐藏行-Google App脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！