是否可以使用谷歌电子表格中的"Script Editor"使不同的代码在不同的选项卡上工作

Question

我只是在处理一个电子表格，其中包含基于不同条件的数据验证(相关数据验证(。我们刚刚创建，并且脚本已更新。但是，我无法使其在不同的选项卡上工作(适用于每个选项卡的脚本(。现在我已经更新了三个不同选项卡的脚本，但是，我的脚本仅适用于我以"Arvind"的名义创建的最后一个选项卡。您能否帮助我在应用程序中使用相同的脚本使工作表适用于具有不同名称的不同选项卡？复制(授权(脚本如下以供参考：

https://docs.google.com/spreadsheets/d/1rYmbsQYiS33aAoOqdRCa9rGVESXj5duC3V02fp1nxk0/edit#gid=820561296

---

function onEdit(e) {
    var sh = e.source.getActiveSheet(),
        allValues, list,
        /*'easy to change' variables*/
        sheet = 'James Main Sheet',
        sheetWithLists = 'Type of Request',
        rangeWithLists = 'B1:H', //assuming the first row to be the header row
        colValidation = 2, //the column with the 'first' validation
        secondValidationOffset = 1; //the offset from the above column / the column with the 'second' validation
    /*check conditions*/
    if (sh.getName() !== sheet || e.range.columnStart !== colValidation || e.range.rowStart < 2 || typeof e.value == 'object') return;
    /*get all values from the sheet with the lists (cached after the first run)*/
    allValues = getFromCache_(sheetWithLists, rangeWithLists)
    /*get the correct list(column) and remove the header*/
    list = allValues.map(function (v, i) {
        return v[allValues[0].indexOf(e.value)]
    }).splice(1);
    /*set the validation in offset column*/
    e.range.offset(0, secondValidationOffset)
        .setDataValidation(SpreadsheetApp.newDataValidation()
            .requireValueInList(list)
            .build());
}
/*Using caching mechanism to limit the calls to the sheet with the lists --> faster execution*/
function getFromCache_(sheetName, range) {
    var key = 'DD_' + sheetName,
        c = CacheService.getPublicCache(),
        d,
        t = c.get(key);
    if (t) {
        d = JSON.parse(t);
    } else {
        d = SpreadsheetApp.getActiveSpreadsheet()
            .getSheetByName(sheetName)
            .getRange(range)
            .getValues();
        c.put(key, JSON.stringify(d));
    }
    return d;
}
//Script courtesy of Top Contributor Jean-Pierre Verhulst

Answer 1

您的代码仅在最后一个工作表中工作的原因是以下 2 行代码：

sheet = 'James Main Sheet'
... 
if (sh.getName() !== sheet || e.range.columnStart !== colValidation || e.range.rowStart < 2 || typeof e.value == 'object') return;

工作表变量设置为 James Main sheet因此仅适用于该工作表。if 函数查看您是否是正在编辑的工作表sh.getName() !== sheet，如果名称不匹配，则使用return终止该工作表。

假设您此代码适用于除工作表以外的所有工作表Type of request。您将代码修改为此

sheet = 'Type of request'
... 
if (sh.getName() == sheet || e.range.columnStart !== colValidation || e.range.rowStart < 2 || typeof e.value == 'object') return;

最终代码将如下所示：

function onEdit(e) { var sh = e.source.getActiveSheet(), allValues, list,
    /*'easy to change' variables*/
    sheet = 'Type of request',
    sheetWithLists = 'Type of Request',
    rangeWithLists = 'B1:H', //assuming the first row to be the header row
    colValidation = 2, //the column with the 'first' validation
    secondValidationOffset = 1; //the offset from the above column / the column with the 'second' validation
/*check conditions, exit function if the sheet name is 'type of request'*/
if (sh.getName() == sheet || e.range.columnStart !== colValidation || e.range.rowStart < 2 || typeof e.value == 'object') return; 
/*get all values from the sheet with the lists (cached after the first run)*/
allValues = getFromCache_(sheetWithLists, rangeWithLists)
/*get the correct list(column) and remove the header*/
list = allValues.map(function (v, i) {
    return v[allValues[0].indexOf(e.value)]
}).splice(1);
/*set the validation in offset column*/
e.range.offset(0, secondValidationOffset)
    .setDataValidation(SpreadsheetApp.newDataValidation()
        .requireValueInList(list)
        .build());
}
/Using caching mechanism to limit the calls to the sheet with the lists --> faster execution/ function getFromCache_(sheetName, range) {
var key = 'DD_' + sheetName,
    c = CacheService.getPublicCache(),
    d,
    t = c.get(key);
if (t) {
    d = JSON.parse(t);
} else {
    d = SpreadsheetApp.getActiveSpreadsheet()
        .getSheetByName(sheetName)
        .getRange(range)
        .getValues();
    c.put(key, JSON.stringify(d));
}
return d;
}