如何让Google Sheets App Script排除指定标签页运行或仅在选定标签页执行

方案一：排除指定的3个标签页 #

要让脚本在所有标签页运行，但跳过3个特定标签，修改原代码的判断条件即可：

function dropdown() {
    var activeCell = SpreadsheetApp.getActiveSheet().getActiveRange();
    var activeRow = activeCell.getRow();
    var activeCol = activeCell.getColumn();
    var activeValue = activeCell.getValue();
    var activeSheet = activeCell.getSheet();
    
    // 替换成你要排除的3个标签页名称
    const excludeSheets = ["标签页1", "标签页2", "标签页3"];
    
    // 条件：当前标签不在排除列表，且行号>1、列号=9
    if (!excludeSheets.includes(activeSheet.getName()) && activeRow > 1 && activeCol == 9) {
        var worksheet = SpreadsheetApp.getActiveSpreadsheet();
        var spreadsheet = worksheet.getSheetByName("Data");
        var data = spreadsheet.getDataRange().getValues();
        var list = data.filter(row => row[0] == activeValue).map(row => row[1]);
        var validation = SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build();
        activeCell.offset(0, 1).setDataValidation(validation);
    }
}

方案二：仅在选定的标签页运行 #

如果只想让脚本在指定的几个标签页生效，定义允许运行的标签列表即可：

function dropdown() {
    var activeCell = SpreadsheetApp.getActiveSheet().getActiveRange();
    var activeRow = activeCell.getRow();
    var activeCol = activeCell.getColumn();
    var activeValue = activeCell.getValue();
    var activeSheet = activeCell.getSheet();
    
    // 替换成你选定的标签页名称
    const includeSheets = ["标签页A", "标签页B", "标签页C"];
    
    // 条件：当前标签在允许列表，且行号>1、列号=9
    if (includeSheets.includes(activeSheet.getName()) && activeRow > 1 && activeCol == 9) {
        var worksheet = SpreadsheetApp.getActiveSpreadsheet();
        var spreadsheet = worksheet.getSheetByName("Data");
        var data = spreadsheet.getDataRange().getValues();
        var list = data.filter(row => row[0] == activeValue).map(row => row[1]);
        var validation = SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build();
        activeCell.offset(0, 1).setDataValidation(validation);
    }
}

注意事项 #

• 标签页名称要和Google Sheet中实际名称完全一致，包括大小写、空格
• Data标签页是脚本获取下拉数据的来源，确保该标签存在且数据格式正确
• 原脚本核心逻辑（根据当前单元格值生成右侧下拉列表）保持不变，仅修改了标签页判断条件

内容的提问来源于stack exchange，提问作者Umesh Kharediya