根据用户输入单元格隐藏列 [英] hide columns based on user input cell

问题描述

大家好(我是脚本新手)

hi all (I am new to scripting),

这是我需要帮助的工作表示例.

here is a sample of my sheet I need help with.

https://docs.google.com/spreadsheets/d/1iypkWdUsSkow9m8nTSFS25HgY1d5vsVMZvjEynwQJcM/edit?usp = sharing

我需要的是一个将在onedit上运行的脚本.

what i need is a script that will run onedit.

用户有5个可选字段，以黄色突出显示.只有在编辑单元格F3时，脚本才应运行.

the user has 5 selectable fields, highlighted in yellow. the script should run only if cell F3 is edited.

它应该做的是隐藏第1行与F3不匹配的所有列.

and what it should do is hide all column where row 1 does not match F3.

请帮助！

推荐答案

我编写了这个小函数，该函数根据 F3 中下拉列表的值来隐藏或取消隐藏列，并且仅在以下情况下运行单元格被修改.

I wrote this small function that hides or unhides columns depending on the value of the dropdown in F3, and that only runs when this cell is modified.

将此复制到绑定到电子表格的脚本中:

Copy this to the script bound to your spreadsheet:

function onEdit(e) {
  var ss = e.source; // Spreadsheet that triggered the function
  var sheet = ss.getActiveSheet();
  var sheetName = "Sheet1"; // Change accordingly
  var range = e.range; // Range that was edited
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 6;
  var row = 3;  
  // Check that the edited cell is F3 and the edited sheet is the one you want:
  if(column == editedColumn && row == editedRow && sheet.getName() == sheetName) {
    var dropdownValue = range.getValue(); // Value of F3
    // Index of last column with content. If you want to hide the blank columns after that, use sheet.getMaxColumns() instead:
    var cols = sheet.getLastColumn();
    // First row values:
    var headers = sheet.getRange(1, 1, 1, cols).getValues()[0];
    // Looping through each column (hiding or unhiding depending on whether value matches):
    for(var i = 1; i < cols; i++) {
      if(headers[i] != dropdownValue) {
        sheet.hideColumns(i + 1);
      } else {
        sheet.showColumns(i + 1);
      }
    }
  }
}

请告诉我这是否适合您.

Please tell me if that works for you.

这篇关于根据用户输入单元格隐藏列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！