Office Script批量设置列数据验证规则覆盖问题求助

问题根源 #

你的switch语句里每个case都没加break，JavaScript的switch会在匹配到第一个符合的case后，继续执行后续所有case的代码，直到遇到break或者switch结束。不管当前列是哪个，最后都会执行到Lagerstatus的setRule，把之前的规则覆盖掉，所以所有列都应用了最后一个规则。

解决方法 #

给每个case块末尾加上break，让代码在匹配到对应列名后停止执行后续case逻辑。

修改后的代码 #

let sbOrderTable = workbook.getTable("mainOrderTable");
const errorAlertStype = ExcelScript.DataValidationAlertStyle.stop;

sbOrderTable.getColumns().forEach(column => {
    let columnName = column.getName();
    let errorMessage = "Nur Einträge aus dem PullDown-Menü sind zulässig";
    let dataValidation: ExcelScript.DataValidation;
    
    dataValidation = column.getRange().getDataValidation();
    dataValidation.clear();
    dataValidation.setIgnoreBlanks(true);
    dataValidation.setPrompt({ showPrompt: false, title: "", message: "" });
    dataValidation.setErrorAlert({ showAlert: true, title: columnName, message: errorMessage, style: errorAlertStype });

    switch (columnName) {
        case "Lieferant":
            dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF_SYNC!$D$6:$D$1048576" } });
            break;
        case "Hersteller":
            dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF_SYNC!$B$6:$B$1048576" } });
            break;
        case "Ersatzteil":
            dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF!$A$6:$A$7" } });
            break;
        case "Bestell Index":
            //errorMessage = "Ungültiger Bestell-Index gemäss Referenz";
            dataValidation.setRule({ list: { inCellDropDown: false, source: "=REF_IDX!$A$6:$A$1048576" } });
            break;
        case "Bestellt von":
            dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF_SYNC!$A$6:$A$1048576" } });
            break;
        case "Bestellstatus":
            dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF!$B$6:$B$1048576" } });
            break;
        case "Lagerstatus":
            dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF!$B$6:$B$1048576" } });
            break;
    }
});

优化方案（避免switch贯穿问题） #

可以用对象映射的方式替代switch，代码更简洁且不会出现贯穿问题：

let sbOrderTable = workbook.getTable("mainOrderTable");
const errorAlertStype = ExcelScript.DataValidationAlertStyle.stop;

// 定义列名到验证规则的映射表
const validationRules = {
    "Lieferant": { list: { inCellDropDown: true, source: "=REF_SYNC!$D$6:$D$1048576" } },
    "Hersteller": { list: { inCellDropDown: true, source: "=REF_SYNC!$B$6:$B$1048576" } },
    "Ersatzteil": { list: { inCellDropDown: true, source: "=REF!$A$6:$A$7" } },
    "Bestell Index": { list: { inCellDropDown: false, source: "=REF_IDX!$A$6:$A$1048576" } },
    "Bestellt von": { list: { inCellDropDown: true, source: "=REF_SYNC!$A$6:$A$1048576" } },
    "Bestellstatus": { list: { inCellDropDown: true, source: "=REF!$B$6:$B$1048576" } },
    "Lagerstatus": { list: { inCellDropDown: true, source: "=REF!$B$6:$B$1048576" } }
};

sbOrderTable.getColumns().forEach(column => {
    let columnName = column.getName();
    let errorMessage = "Nur Einträge aus dem PullDown-Menü sind zulässig";
    let dataValidation = column.getRange().getDataValidation();
    
    dataValidation.clear();
    dataValidation.setIgnoreBlanks(true);
    dataValidation.setPrompt({ showPrompt: false, title: "", message: "" });
    dataValidation.setErrorAlert({ showAlert: true, title: columnName, message: errorMessage, style: errorAlertStype });

    // 根据列名匹配规则并设置
    const rule = validationRules[columnName];
    if (rule) {
        dataValidation.setRule(rule);
    }
});

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