CODEWITHSUNDEEP
javascriptexceloffice365apioffice-js
Alex K
Alex K

Reputation: 3231

How to implement data validation in Excel using Office.js API

Is there way to implement data validation programmatically using Office.js API ?

enter image description here

Upvotes: 3

Views: 2117

Answers (2)

Vikas Ahire
Vikas Ahire

Reputation: 11

Here I have try to add data validation on excel cell within range.

 Excel.run(function (context) {
    var currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
    var expensesTable = currentWorksheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";
    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];
    expensesTable.rows.add(null /*add at the end*/, [
        ["1/1/2017", "The Phone Company", "Communications", "120"],
        ["1/2/2017", "Northwind Electric Cars", "Transportation", "142.33"],
        ["1/5/2017", "Best For You Organics Company", "Groceries", "27.9"],
        ["1/10/2017", "Coho Vineyard", "Restaurant", "33"],
        ["1/11/2017", "Bellows College", "Education", "350.1"],
        ["1/15/2017", "Trey Research", "Other", "135"],
        ["1/15/2017", "Best For You Organics Company", "Groceries", "97.88"]
    ]);
    var range = currentWorksheet.getRange("C2:C200");
    range.dataValidation.clear();
    range.dataValidation.rule = {
        list: {
            inCellDropDown: true,
            source: "Groceries, Education, Other,Transportation",
            autofitColumns: true
        }
    };
    //range.dataValidation.errorAlert = {
    //    message: "Sorry, only positive numbers are allowed",
    //    showAlert: true,
    //    style: "Stop",
    //    title: "Negative Number Entered"
    //};
    list.find();

    return context.sync();
}).catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

Upvotes: 0

Marc LaFleur
Marc LaFleur

Reputation: 33114

While you can absolutely implement your own data validation within an add-in, it would be distinct from the built-in data validation tool. There is currently no API for configuring Excel's data validation tool programmatically.

Upvotes: 2

Related Questions