如何在多个工作表(Google工作表)上运行脚本 [英] How to run a script on multiple sheets, Google Sheets

问题描述

我有一个脚本，希望在我的Google工作表中的特定选项卡上运行，但不一定要在所有选项卡上运行.我尝试执行两个名称不同的脚本，但是只有最后一个保存的脚本会运行.如何编写此脚本以在特定选项卡上运行?这是我的开始脚本:

I have a script that I would like to run on specific tabs in my Google worksheet, but not necessarily all of the tabs. I tried doing two differently named scripts, but only the last one that was saved will run. How can I code this script to run on specific tabs? This is my beginning script:

function onEdit() {
  var s = SpreadsheetApp.getActive().getSheetByName('Proposal');
  s.showRows(1, s.getMaxRows());

  s.getRange('B:B')
    .getValues()
    .forEach( function (r, i) {
      if (r[0] == 'Hide') 
        s.hideRows(i + 1);
    });
}

我尝试使用发布的另一个问题的建议进行这样的修改，但这没有用.

I tried modifying like this, using suggestions from another question posted, but it did not work.

function onEdit() {
  var tabs = [
        'Proposal',
        'Materials List - All',
        'Materials List - Shingles',
        'Materials List - Access',
        'Work Order'
  ];

  var ss=SpreadsheetApp.getActiveSpreadsheet();
  for (var i = 0; i < tabs.length; i++) {
    var sheet = ss.getSheetByName(tabs[i]);
    s.showRows(1, s.getMaxRows());

    s.getRange('B:B')
      .getValues()
      .forEach( function (r, i) {
        if (r[0] == 'Hide') 
          s.hideRows(i + 1);
      });
  }

有关如何正确执行此操作的任何建议?如果我决定更简单，该如何在所有选项卡上做到这一点?

Any suggestions on how to do this properly? How could I do this on all the tabs if I decided that was easier?

谢谢！

推荐答案

您可以通过2种方法来实现.使用排除"或仅".

You can do it 2 ways. Using 'exclude' or 'only'.

排除是指，如果当前工作表是其中之一，则不要这样做.

exclude means, do not do it if current sheet is one of these.

仅表示仅在当前工作表是其中之一时才这样做.

only means, do it only if current sheets is one of these.

使用以下代码并启用任何一个选项，然后尝试.要启用，请将相应的工作表名称添加到 excludes 或 only 数组中，并通过取出//取消注释行.它仅适用于当前工作表，不适用于电子表格中的所有工作表.

Use below code and enable any one option then try. To enable, add respective sheet names to excludes or only array and uncomment lines by taking out //. It'll work only on current sheet, not all sheets in the spreadsheets.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();

  // use only one of these 2 options

  // add sheets names to exclude, example ['Sheet1', 'Sheet2']
  // to exclude these sheets enable these 2 lines below by uncommenting
  // var excludes = [];
  // if (excludes.indexOf(s.getName()) != -1) return;

  // add sheet names to work on only these sheets, as above
  // to work on only these sheets enable these 2 lines below by uncommenting
  // var only = [];
  // if (only.indexOf(s.getName()) == -1) return;

  s.showRows(1, s.getMaxRows());
  s.getRange('B:B')
    .getValues()
    .forEach(function(r, i) {
      if (r[0] == 'Hide') s.hideRows(i + 1);
    });
}

编辑

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var only = [
    'Proposal',
    'Materials List - All',
    'Materials List - Shingles',
    'Materials List - Access',
    'Work Order'
  ];
  only.forEach(function(name) {
    var s = ss.getSheetByName(name);
    s.showRows(1, s.getMaxRows());
    s.getRange('B:B')
      .getValues()
      .forEach(function(r, i) {
        if (r[0] == 'Hide') s.hideRows(i + 1);
      });
  });
}

这篇关于如何在多个工作表(Google工作表)上运行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！