如何将 Google Apps 脚本应用于多张工作表

Question

我想将以下代码应用于同一 Google 表格中的多个选项卡。在第一个 var 中定义不同的选项卡，或在同一项目中应用多个 .gs 文件，每个文件具有不同的 var 都没有成功。

var naamWerkbladKasboek = "Kasboek"; 

var naamWerkbladOpties = "Opties";

var eersteKolomMetInput = 2; 

var tweedeKolomMetInput = 3; 

var derdeKolomMetInput = 4; 

var werkblad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(naamWerkbladKasboek);

var werkbladOpties = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(naamWerkbladOpties);

var Opties = werkbladOpties.getRange(2, 1, werkbladOpties.getLastRow()-1, 3).getValues();

function onEdit(e){

  var activeCell = e.range;

  var val = activeCell.getValue();

  var row = activeCell.getRow();

  var column = activeCell.getColumn();

  var werkbladName = activeCell.getSheet().getName();

  

  if(werkbladName === naamWerkbladKasboek && column === eersteKolomMetInput && row > 1){

    applyFirstLevelValidation(val, row);

  } else if(werkbladName === naamWerkbladKasboek && column === tweedeKolomMetInput && row > 1){ 

    applySecondLevelValidation(val, row);

  }

} 

function applyFirstLevelValidation(val, row){

  

  if(val === ""){

      werkblad.getRange(row, tweedeKolomMetInput).clearContent();

      werkblad.getRange(row, tweedeKolomMetInput).clearDataValidations();

      werkblad.getRange(row, derdeKolomMetInput).clearContent();

      werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();

    } 

我已经尝试使用 only 和 exclude 标签，如下所示：How to run a script on multiple sheets, Google Sheets还尝试使用数组，如下所示： https: //webapps.stackexchange.com/questions/115076/ how-to-run-script-on-multiple-google-sheet-tabs但都无济于事。有人可以指出我正确的方向吗？

Answer 1

在onEdit你检查当前工作表的名称是否包含“Kasboek”function onEdit(e){  var activeCell = e.range;  var activeSheet = activeCell.getSheet();    if(activeSheet.getName().includes("Kasboek")){    KasboekModification(activeCell);  } else {    //code here if changes need to be made on other sheets  }}之后，您就拥有了function KasboekModification(cell){}可以发挥当前工作表魔力的功能。

Answer 2

回答：onEdit(e)如果已编辑的工作表不是要编辑的预定义工作表之一，一个简单的解决方案是返回。代码示例：只做：_var only = ["Sheet1Name", "Sheet2Name", "Sheet3Name"]; // add sheet names as desiredfunction onEdit(e) {&nbsp; if (!(only.includes(e.range.getSheet().getName())) {&nbsp; &nbsp; return;&nbsp; }&nbsp; // put the rest of your onEdit function here}或排除：var exclude = ["Sheet4Name", "Sheet5Name", "Sheet6Name"]; // add sheet names as desiredfunction onEdit(e) {&nbsp; if (exclude.includes(e.range.getSheet().getName())) {&nbsp; &nbsp; return;&nbsp; }&nbsp; // put the rest of your onEdit function here}您的用例示例：var only = ["Kasboek", "Kasboek2", "Kasboek3"];function onEdit(e) {&nbsp; if (!(only.includes(e.range.getSheet().getName())) {&nbsp; &nbsp; return;&nbsp; }&nbsp; var activeCell = e.range;&nbsp; var val = activeCell.getValue();&nbsp; var row = activeCell.getRow();&nbsp; if (row <= 1) return;&nbsp; var column = activeCell.getColumn();&nbsp; var eersteKolomMetInput = 2;&nbsp;&nbsp; var tweedeKolomMetInput = 3;&nbsp;&nbsp; var derdeKolomMetInput = 4;&nbsp;&nbsp;&nbsp; if (column === eersteKolomMetInput) {&nbsp; &nbsp; applyFirstLevelValidation(val, row);&nbsp; }&nbsp;&nbsp; else if (column === tweedeKolomMetInput) {&nbsp; &nbsp; applySecondLevelValidation(val, row);&nbsp; }}&nbsp;function applyFirstLevelValidation(val, row) {&nbsp; // ...}function applySecondLevelValidation(val, row) {&nbsp; // ...}

Answer 3

TypeError：无法读取未定义的属性“范围”（第 7 行，文件“multipledatavalidation”）第 7 行；如果 (exclude.includes(e.range.getSheet().getName())) { 返回;应该注意的是，我的工作表上还有另一个脚本在运行。一个可以让我在锁定到位的情况下复制工作表。我希望能够在我制作的原始工作表的所有副本上运行此代码。这是我的工作表的共享、可编辑版本的链接。&nbsp; var optionsWsName = "Backend-Prices";&nbsp; &nbsp; &nbsp; var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);&nbsp; &nbsp; &nbsp; var exclude = ["BackendImportedData", "donotedit", "BackendDataSort", "09/20_DS7"]; // add sheet names as desired&nbsp; &nbsp;&nbsp;// function to update each time the file is Edited&nbsp;function onEdit(e){&nbsp; &nbsp; &nbsp; if (exclude.includes(e.range.getSheet().getName())) {&nbsp; &nbsp; &nbsp; &nbsp; return;&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; var Options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1, 5).getValues();&nbsp; &nbsp; &nbsp; var activeCell = e.range;&nbsp; &nbsp; &nbsp; var val = activeCell.getValue();&nbsp; &nbsp; &nbsp; var r = activeCell.getRow();&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; if (r <= 8) return;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; var c = activeCell.getColumn();&nbsp; &nbsp; &nbsp; var FirstLevelColumn = 1;&nbsp; &nbsp; &nbsp; var SecondLevelColumn = 2;&nbsp;&nbsp; &nbsp; &nbsp; var ThirdLevelColumn = 3;&nbsp;&nbsp; &nbsp; &nbsp; var FourthLevelColumn = 4;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; if(c === FirstLevelColumn){&nbsp; &nbsp; &nbsp; &nbsp; applyFirstLevelValidation(val, r);&nbsp; &nbsp; &nbsp; } else if(c === SecondLevelColumn){&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; applySecondLevelValidation(val, r);&nbsp; &nbsp; &nbsp; } else if(c === ThirdLevelColumn){&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; applyThirdLevelValidation(val, r);&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp;&nbsp;}//end onEdit&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; // function for second level of data validation to work correctly&nbsp; &nbsp;&nbsp;function applyFirstLevelValidation(val, r){&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; if(val === ""){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, SecondLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, SecondLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, SecondLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, SecondLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var filteredOptions = Options.filter(function(o){ return o[0] === val });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var listToApply = filteredOptions.map(function(o){ return o[1] });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var cell = ws.getRange(r, SecondLevelColumn);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; applyValidationToCell(listToApply,cell);&nbsp; &nbsp; &nbsp; &nbsp; }}&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; // function for third level of data validation to work correctly&nbsp; &nbsp;&nbsp;function applySecondLevelValidation(val, r){&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; if(val === ""){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, ThirdLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var firstlevelColValue = ws.getRange(r, FirstLevelColumn).getValue();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var filteredOptions = Options.filter(function(o){ return o[0] === firstlevelColValue && o[1] === val });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var listToApply = filteredOptions.map(function(o){ return o[2] });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var cell = ws.getRange(r, ThirdLevelColumn);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; applyValidationToCell(listToApply,cell);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;}&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; // function for fourth level of data validation to work correctly&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; function applyThirdLevelValidation(val, r){&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; if(val === ""){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearDataValidations();&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ws.getRange(r, FourthLevelColumn).clearContent();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var firstlevelColValue = ws.getRange(r, FirstLevelColumn).getValue();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var secondlevelColValue = ws.getRange(r, SecondLevelColumn).getValue();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var filteredOptions = Options.filter(function(o){ return o[0] === firstlevelColValue && o[1] === secondlevelColValue && o[2] === val });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var listToApply = filteredOptions.map(function(o){ return o[3] });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var cell = ws.getRange(r, FourthLevelColumn);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; applyValidationToCell(listToApply,cell);&nbsp; &nbsp; &nbsp; &nbsp; }}&nbsp; &nbsp;&nbsp;function applyValidationToCell(list,cell){&nbsp; &nbsp; &nbsp;&nbsp;var rule = SpreadsheetApp&nbsp;.newDataValidation()&nbsp;.requireValueInList(list)&nbsp;.setAllowInvalid(false)&nbsp;.build();&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;cell.setDataValidation(rule)&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; }