如何将Google Apps脚本应用于多个工作表 [英] How to apply Google Apps Script to multiple sheets
本文介绍了如何将Google Apps脚本应用于多个工作表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想将以下代码应用于同一Google表格中的多个标签.在第一个变量中定义不同的选项卡，或在同一项目中应用多个.gs文件，每个文件具有不同的变量，均不成功.
I want to apply the following code to multiple tabs in the same Google Sheet. Defining different Tabs in the first var, or applying multiple .gs files in the same project each with a different var did not succeed.
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();
} else {
werkblad.getRange(row, tweedeKolomMetInput).clearContent();
werkblad.getRange(row, tweedeKolomMetInput).clearDataValidations();
werkblad.getRange(row, derdeKolomMetInput).clearContent();
werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
var gefilterdeOpties = Opties.filter(function(o){ return o[0] === val });
var listToApply = gefilterdeOpties.map(function(o){ return o[1] });
var cell = werkblad.getRange(row, tweedeKolomMetInput);
applyValidationToCell(listToApply,cell);
}
}
function applySecondLevelValidation(val, row){
if(val === ""){
werkblad.getRange(row, derdeKolomMetInput).clearContent();
werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
} else {
werkblad.getRange(row, derdeKolomMetInput).clearContent();
var waardeEersteKolomMetInput = werkblad.getRange(row, eersteKolomMetInput).getValue();
var gefilterdeOpties = Opties.filter(function(o){ return o[0] === waardeEersteKolomMetInput && o[1] === val });
var listToApply = gefilterdeOpties.map(function(o){ return o[2] });
var cell = werkblad.getRange(row, derdeKolomMetInput);
applyValidationToCell(listToApply,cell);
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule)
}
我已经尝试使用此处所示的only和exclude标签: https://webapps.stackexchange.com/questions/115076/how-to-run-script-on-multiple-google-sheet-tabs，但都无济于事.有人可以指出我正确的方向吗?
I have tried working with the only and exclude tags as indicated here: How to run a script on multiple sheets, Google Sheets and also tried working with an array as indicated here: https://webapps.stackexchange.com/questions/115076/how-to-run-script-on-multiple-google-sheet-tabs but both to no avail. Can someone please point me in the right direction?
作为示例，我可以展示如何尝试使用唯一标签
As an example I can show how I tried to work with the only tag
//**EDIT**
var only = [
"Kasboek1",
"Kasboek2",
"Kasboek3"
];
var naamWerkbladOpties = "Opties";
var eersteKolomMetInput = 2;
var tweedeKolomMetInput = 3;
var derdeKolomMetInput = 4;
var werkblad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(only);
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 === only && column === eersteKolomMetInput && row > 1){
applyFirstLevelValidation(val, row);
} else if(werkbladName === only && 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();
} else {
werkblad.getRange(row, tweedeKolomMetInput).clearContent();
werkblad.getRange(row, tweedeKolomMetInput).clearDataValidations();
werkblad.getRange(row, derdeKolomMetInput).clearContent();
werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
var gefilterdeOpties = Opties.filter(function(o){ return o[0] === val });
var listToApply = gefilterdeOpties.map(function(o){ return o[1] });
var cell = werkblad.getRange(row, tweedeKolomMetInput);
applyValidationToCell(listToApply,cell);
}
}
function applySecondLevelValidation(val, row){
if(val === ""){
werkblad.getRange(row, derdeKolomMetInput).clearContent();
werkblad.getRange(row, derdeKolomMetInput).clearDataValidations();
} else {
werkblad.getRange(row, derdeKolomMetInput).clearContent();
var waardeEersteKolomMetInput = werkblad.getRange(row, eersteKolomMetInput).getValue();
var gefilterdeOpties = Opties.filter(function(o){ return o[0] === waardeEersteKolomMetInput && o[1] === val });
var listToApply = gefilterdeOpties.map(function(o){ return o[2] });
var cell = werkblad.getRange(row, derdeKolomMetInput);
applyValidationToCell(listToApply,cell);
}
}
function applyValidationToCell(list,cell){
var rule = SpreadsheetApp
.newDataValidation()
.requireValueInList(list)
.setAllowInvalid(false)
.build();
cell.setDataValidation(rule)
}
在 onEdit 中的
推荐答案
中，您检查当前工作表的名称是否包含"Kasboek"
in the onEdit you check if the name of the current sheet contains "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
}
}
此后，您就有了函数KasboekModification(cell){} 函数，您可以在其中发挥当前工作表的魔力.
After that you have your function KasboekModification(cell){} function where you can do the magic of your current sheet.
这篇关于如何将Google Apps脚本应用于多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
