Is there way to implement data validation programmatically using Office.js API ?
Add a comment
|
2 Answers
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.
4 Comments
Ben Cook
Is it possible to get the "drop-down list" functionality in a cell without Excel data validation through Office JS? In other words, I want the drop-down not so much for data validation purposes as for making data entry easier for users.
Michael Zlatkovsky
That being said, it is on the team's radar for the not-too-distant future.
Valip
@MichaelZlatkovsky-Microsoft are there any updates on this?
JimbobTheSailor
Its been 7 years since the last post, and yes you can now add data validation programmatically using OfficeJS. learn.microsoft.com/en-us/office/dev/add-ins/excel/…
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));
}
});