根据单元格值隐藏多张纸 [英] Hide multiple sheets based on a cell value

问题描述

下午好，

我对Google表格的脚本非常陌生(习惯于Excel差异)，并试图隐藏多个表格(第1周"，第2周"，第3周" ...一直到第9周) )，并且仅显示一个基于单元格的图片(表格控制面板"中的j10).如果j10的值为"4"，则仅显示第4周".

I am fairly new to scripts for Google Sheets (getting used to Excel differences) and am trying to hide multiple sheets ("Week 1", "Week 2", "Week 3"... all the way to week 9) and only show one based on a cell (j10 in a sheet "Control Panel"). If j10 has a "4", only "Week 4" will show.

我看到了一次基于在这个古老的Stack Overflow问题上，使用9个"if语句"，每个语句有9条命令来隐藏除一个"Week"表之外的所有表.基本上，我希望将代码简化得不太慢.

I see a way to do this one sheet at a time based on this old Stack Overflow question using 9 "if statements" and each statement having 9 commands to either hide all but one "Week" sheet. Basically, I'm looking to considerably simplify that code to not be so slow.

所以我尝试使用此帖子是通过循环命令在hidesheet()方法存在之前编写的，但是我遇到了麻烦.

So I tried to use some script from this post that was written before the hidesheet() method existed by looping a command, but I'm having trouble.

谢谢您的考虑！

推荐答案

我假设您不想隐藏控制面板"工作表?尝试以下脚本

I assumed you don't want to hide the sheet 'Control Panel' ? Try below script

function onEdit(e) {
if (e.source.getActiveSheet()
    .getName() == 'Control Panel' && e.range.getA1Notation() == 'J10' && e.value > 0 && e.value < 10) {
    e.source.getSheets()
        .forEach(function (sh) {
            (sh.getName() !== 'Week ' + e.value && sh.getName() !== 'Control Panel') ? sh.hideSheet() : sh.showSheet();
        })
}
}

注意:此脚本使用一个简单的onEdit触发器.因此，请勿尝试从脚本编辑器中运行它(通过单击播放"按钮).相反，请转到工作表控制面板"，然后在J10中输入一个值(> 0和<9).

NOTE: this script uses a simple onEdit trigger. So don't try to run it from the script editor (by clicking the 'play' button). Instead, go to sheet 'Control Panel' and enter a value (> 0 and < 9) in J10.

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