Created
September 17, 2021 14:41
-
-
Save webartoli/e8aaa1922f82d7bda2cc4636405b9472 to your computer and use it in GitHub Desktop.
Sets data validation rules on ranges, prompts users to enter valid data, and displays messages when invalid data is entered.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name: Data validation | |
description: >- | |
Sets data validation rules on ranges, prompts users to enter valid data, and | |
displays messages when invalid data is entered. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#require-approved-location").click(() => tryCatch(requireApprovedName)); | |
async function requireApprovedName() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getActiveWorksheet(); | |
const sourceRange = sheet.getRange("A1"); | |
const targetRange = sheet.getRange("B1"); | |
sourceRange.dataValidation.load("rule"); | |
await context.sync(); | |
const src = sourceRange.dataValidation.rule.list.source; | |
console.log(">>> Data Validation: rule.list.source", src); | |
// When you are developing, it is a good practice to | |
// clear the dataValidation object with each run of your code. | |
targetRange.dataValidation.clear(); | |
targetRange.dataValidation.rule = { | |
list: { | |
inCellDropDown: true, | |
source: src | |
} | |
}; | |
await context.sync(); | |
}); | |
} | |
/** Default helper for invoking an action and handling errors. */ | |
async function tryCatch(callback) { | |
try { | |
await callback(); | |
} catch (error) { | |
// Note: In a production add-in, you'd want to notify the user through your add-in's UI. | |
console.error(error); | |
} | |
} | |
language: typescript | |
template: | |
content: "<section class=\"ms-font-m\">\n\t<p class=\"ms-font-m\">This sample shows a bug appling data validation to cells.</p>\n</section>\n\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<p class=\"ms-font-m\">In order to setup this sample you need to perform this manually</p>\n\t<ul>\n\t\t<li>Please ensure that your system region and language is it_IT</li>\n\t\t<li>Select A1 cell</li>\n\t\t<li>Set data validation whith this list <code>1, Jan;2, Feb;3, Mar<code></li>\n\t\t</ul>\n\t\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Copy paste</h3>\n\t<p class=\"ms-font-m\">Press <b>Apply</b> to copy data validation from `A1` to `B1`</p>\n\t<button id=\"require-approved-location\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Apply</span>\n </button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Check</h3>\n\t<p class=\"ms-font-m\">Open `B1` dropdown menu: should be equal than `A1` but it is not!</p>\n</section>" | |
language: html | |
style: | |
content: |- | |
section.samples { | |
margin-top: 20px; | |
} | |
section.samples .ms-Button, section.setup .ms-Button { | |
display: block; | |
margin-bottom: 5px; | |
margin-left: 20px; | |
min-width: 80px; | |
} | |
language: css | |
libraries: | | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
@types/office-js | |
office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
core-js@2.4.1/client/core.min.js | |
@types/core-js | |
jquery@3.1.1 | |
@types/jquery@3.3.1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment