I’m trying to rewrite some old VBA code to Office Script.
One of the steps is to set data validation on several columns.
With the script I am able to loop all columns of a named table.
So my idea was to use a switch on the column names to set the related data validation.
So far so good – but: all defined rules set in the ForEach loop are somehow not done when calling the setRule() but all columns do have afterwards the last defined rule (here: the one from column name “Lagerstatus”.
Does anyone knows how to solve this problem?
Thanks in advance!
Best regeards,
Pascal

Code snippet:

    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" } });
            case "Hersteller":
                dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF_SYNC!$B$6:$B$1048576" } }); 1
            case "Ersatzteil":
                dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF!$A$6:$A$7" } });
            case "Bestell Index":
                //errorMessage = "Ungültiger Bestell-Index gemäss Referenz";
                dataValidation.setRule({ list: { inCellDropDown: false, source: "=REF_IDX!$A$6:$A$1048576" } });
            case "Bestellt von":
                dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF_SYNC!$A$6:$A$1048576" } });
            case "Bestellstatus":
                dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF!$B$6:$B$1048576" } });
            case "Lagerstatus":
                dataValidation.setRule({ list: { inCellDropDown: true, source: "=REF!$B$6:$B$1048576" } });
        }
    });