根据单元格值隐藏工作表 [英] Hide Sheets Based on a Cell Value

问题描述

我对学习应用脚本非常陌生，并且经过/尝试编辑

I'm pretty new to learning app script and looked over/tried to edit this script, but I'm not getting my desired result. I have a sheet titled "Menu" where I'm wanting a user to select from three different drop down options in Cell A2 (e.g. Blue, Yellow, Green). I then want to hide the different sheets based on the selection. So if a user selects "Blue" I want only the sheets that start with the word "Blue" to be visible + the "Menu" sheet and the rest to be hidden. Same for Yellow and Green. As a note there are 13 sheets for each color.

对此有任何帮助，我们深表感谢.

Any help with this is much appreciated.

推荐答案

尝试以下代码:

function onEdit(e)
{
  //filter the range
  if (e.range.getA1Notation() == "A2")
  {
    // get value of cell (yellow||green||...)
    onlySheet(e.value)
  }
}

function onlySheet(str)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //get all sheets
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++)
  {
    //get the sheet name
    var name = sheets[i].getName();
    // check if the sheet is not the "Menu" sheet
    if (name != "Menu")
    {
      // check if the name of the sheet contains the value of the cell, here str
      //if it does then show sheet if it doesn't hide sheet
      if (name.match(new RegExp(str, "gi")))
        sheets[i].showSheet();
      else
        sheets[i].hideSheet();
    }
  }
}

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