如何将 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 desired

function onEdit(e) {

  if (!(only.includes(e.range.getSheet().getName())) {

    return;

  }

  // put the rest of your onEdit function here

}

或排除：

var exclude = ["Sheet4Name", "Sheet5Name", "Sheet6Name"]; // add sheet names as desired

function onEdit(e) {

  if (exclude.includes(e.range.getSheet().getName())) {

    return;

  }

  // put the rest of your onEdit function here

}

您的用例示例：

var only = ["Kasboek", "Kasboek2", "Kasboek3"];

function onEdit(e) {

  if (!(only.includes(e.range.getSheet().getName())) {

    return;

  }

  var activeCell = e.range;

  var val = activeCell.getValue();

  var row = activeCell.getRow();

  if (row <= 1) return;

  var column = activeCell.getColumn();

  var eersteKolomMetInput = 2; 

  var tweedeKolomMetInput = 3; 

  var derdeKolomMetInput = 4;

  

  if (column === eersteKolomMetInput) {

    applyFirstLevelValidation(val, row);

  } 

  else if (column === tweedeKolomMetInput) {

    applySecondLevelValidation(val, row);

  }

} 

function applyFirstLevelValidation(val, row) {

  // ...

}

function applySecondLevelValidation(val, row) {

  // ...

}

Answer 3

TypeError：无法读取未定义的属性“范围”（第 7 行，文件“multipledatavalidation”）

第 7 行；如果 (exclude.includes(e.range.getSheet().getName())) { 返回;

应该注意的是，我的工作表上还有另一个脚本在运行。一个可以让我在锁定到位的情况下复制工作表。我希望能够在我制作的原始工作表的所有副本上运行此代码。这是我的工作表的共享、可编辑版本的链接。

  var optionsWsName = "Backend-Prices";

      var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);

      var exclude = ["BackendImportedData", "donotedit", "BackendDataSort", "09/20_DS7"]; // add sheet names as desired

    

// function to update each time the file is Edited 

function onEdit(e){

      if (exclude.includes(e.range.getSheet().getName())) {

        return;

      }

      

      var Options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1, 5).getValues();

      var activeCell = e.range;

      var val = activeCell.getValue();

      var r = activeCell.getRow();

    

      if (r <= 8) return;

    

      var c = activeCell.getColumn();

      var FirstLevelColumn = 1;

      var SecondLevelColumn = 2; 

      var ThirdLevelColumn = 3; 

      var FourthLevelColumn = 4;

      

      

      if(c === FirstLevelColumn){

        applyFirstLevelValidation(val, r);

      } else if(c === SecondLevelColumn){ 

        applySecondLevelValidation(val, r);

      } else if(c === ThirdLevelColumn){ 

        applyThirdLevelValidation(val, r);

      }

    

}//end onEdit

    

    

    

    // function for second level of data validation to work correctly

    

function applyFirstLevelValidation(val, r){

      

      if(val === ""){

          ws.getRange(r, SecondLevelColumn).clearContent();

          ws.getRange(r, SecondLevelColumn).clearDataValidations();

          ws.getRange(r, ThirdLevelColumn).clearContent();

          ws.getRange(r, ThirdLevelColumn).clearDataValidations();

          ws.getRange(r, FourthLevelColumn).clearContent();

          ws.getRange(r, FourthLevelColumn).clearDataValidations();

        } else {

          ws.getRange(r, SecondLevelColumn).clearContent();

          ws.getRange(r, SecondLevelColumn).clearDataValidations();

          ws.getRange(r, ThirdLevelColumn).clearContent();

          ws.getRange(r, ThirdLevelColumn).clearDataValidations();

          ws.getRange(r, FourthLevelColumn).clearContent();

          ws.getRange(r, FourthLevelColumn).clearDataValidations();

          var filteredOptions = Options.filter(function(o){ return o[0] === val });

          var listToApply = filteredOptions.map(function(o){ return o[1] });

          var cell = ws.getRange(r, SecondLevelColumn);

          applyValidationToCell(listToApply,cell);

        }

}

    

    // function for third level of data validation to work correctly

    

function applySecondLevelValidation(val, r){

      

      if(val === ""){

          ws.getRange(r, ThirdLevelColumn).clearContent();

          ws.getRange(r, ThirdLevelColumn).clearDataValidations();

          ws.getRange(r, FourthLevelColumn).clearContent();

          ws.getRange(r, FourthLevelColumn).clearDataValidations();

        } else {

          ws.getRange(r, ThirdLevelColumn).clearContent();

          ws.getRange(r, ThirdLevelColumn).clearDataValidations();

          ws.getRange(r, FourthLevelColumn).clearContent();

          ws.getRange(r, FourthLevelColumn).clearDataValidations();

          var firstlevelColValue = ws.getRange(r, FirstLevelColumn).getValue();

          var filteredOptions = Options.filter(function(o){ return o[0] === firstlevelColValue && o[1] === val });

          var listToApply = filteredOptions.map(function(o){ return o[2] });

          var cell = ws.getRange(r, ThirdLevelColumn);

          applyValidationToCell(listToApply,cell);

        }

        

}

    

    

    // function for fourth level of data validation to work correctly

    

    function applyThirdLevelValidation(val, r){

      

      if(val === ""){

          ws.getRange(r, FourthLevelColumn).clearContent();

          ws.getRange(r, FourthLevelColumn).clearDataValidations();

        } else {

          ws.getRange(r, FourthLevelColumn).clearContent();

          var firstlevelColValue = ws.getRange(r, FirstLevelColumn).getValue();

          var secondlevelColValue = ws.getRange(r, SecondLevelColumn).getValue();

          var filteredOptions = Options.filter(function(o){ return o[0] === firstlevelColValue && o[1] === secondlevelColValue && o[2] === val });

          var listToApply = filteredOptions.map(function(o){ return o[3] });

          var cell = ws.getRange(r, FourthLevelColumn);

          applyValidationToCell(listToApply,cell);

        }

}

    

function applyValidationToCell(list,cell){

      

var rule = SpreadsheetApp

 .newDataValidation()

 .requireValueInList(list)

 .setAllowInvalid(false)

 .build();

      

 cell.setDataValidation(rule)

    

    }