Last active
May 22, 2020 13:51
-
-
Save Senipah/891e6aaa7adb100cd40c4d921c8cb4f1 to your computer and use it in GitHub Desktop.
Example for: https://www.reddit.com/r/OfficeJs/comments/goe7wj/reset_drop_down_menu_in_excel_using_officejs/
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: 48098659-officejs-add-and-update-binding-event-handler | |
description: '' | |
host: EXCEL | |
api_set: {} | |
script: | |
content: > | |
$("#register-data-changed-handler").click(() => | |
tryCatch(registerDataChangedHandler_JS)); | |
function registerDataChangedHandler_JS() { | |
return Excel.run(function(context) { | |
var dropdown = context.workbook.bindings.add("Sheet1!A1", "Range", "dropdown1"); | |
// add your dependent dropdown | |
context.workbook.bindings.add("Sheet1!B22", "Range", "dropdown2"); | |
dropdown.onDataChanged.add(onBindingDataChanged_JS); | |
return context.sync().then(function() { | |
console.log("Created binding and added onDataChanged event for the binding."); | |
}); | |
}); | |
} | |
function onBindingDataChanged_JS(eventArgs) { | |
return Excel.run(function(context) { | |
const dependent = context.workbook.bindings.getItem("dropdown2").getRange(); | |
// Set this to the default value in your dependent dropdown | |
const defaultValue = "Option 1" | |
dependent.values = [[defaultValue]] | |
console.log("Dependent Reset") | |
return context.sync(); | |
}); | |
} | |
async function registerDataChangedHandler_TS() { | |
await Excel.run(async (context) => { | |
const myBinding = context.workbook.bindings.add("Sheet1!A1:A2", "range", "A_binding"); | |
myBinding.onDataChanged.add(onBindingDataChanged_TS); | |
await context.sync(); | |
console.log("Created binding and added onDataChanged event for the binding."); | |
}); | |
} | |
async function onBindingDataChanged_TS(eventArgs) { | |
await Excel.run(async (context) => { | |
console.log("Cells were updated!"); | |
await context.sync(); | |
}); | |
} | |
/** Default helper for invoking an action and handling errors. */ | |
async function tryCatch(callback) { | |
try { | |
await callback(); | |
} catch (error) { | |
OfficeHelpers.UI.notify(error); | |
OfficeHelpers.Utilities.log(error); | |
} | |
} | |
language: typescript | |
template: | |
content: "<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n</section>\n\n<section class=\"ms-font-m\">\n <p>Click the following button to create a binding for range Sheet1!A1:A2 and add an onDataChanged event for that binding:</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n <button id=\"register-data-changed-handler\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Register data-changed handler</span>\n </button>\n</section>\n\n<section class=\"ms-font-m\">\n\t<p>After you've clicked the button, change value(s) in cell A1 or A2 and view the console message(s) as proof that the onDataChanged event fired.</p>\n</section>" | |
language: html | |
style: | |
content: | | |
/* Your style goes here */ | |
language: css | |
libraries: | | |
# Office.js | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
# CSS Libraries | |
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 | |
# NPM libraries | |
core-js@2.4.1/client/core.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
jquery@3.1.1 | |
# IntelliSense: @types/library or node_modules paths or URL to d.ts files | |
@types/office-js | |
@types/core-js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
@types/jquery |
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: dependent-dropdown-binding | |
description: >- | |
Example for: | |
https://www.reddit.com/r/OfficeJs/comments/goe7wj/reset_drop_down_menu_in_excel_using_officejs/ | |
host: EXCEL | |
api_set: {} | |
script: | |
content: > | |
$("#register-data-changed-handler").click(() => | |
tryCatch(registerDataChangedHandler_JS)); | |
function registerDataChangedHandler_JS() { | |
return Excel.run(function(context) { | |
var dropdown = context.workbook.bindings.add("Sheet1!A1", "Range", "dropdown1"); | |
// add your dependent dropdown | |
context.workbook.bindings.add("Sheet1!B22", "Range", "dropdown2"); | |
dropdown.onDataChanged.add(onBindingDataChanged_JS); | |
return context.sync().then(function() { | |
console.log("Created binding and added onDataChanged event for the binding."); | |
}); | |
}); | |
} | |
function onBindingDataChanged_JS(eventArgs) { | |
return Excel.run(function(context) { | |
const dependent = context.workbook.bindings.getItem("dropdown2").getRange(); | |
// Set this to the default value in your dependent dropdown | |
const defaultValue = "Option 1" | |
dependent.values = [[defaultValue]] | |
console.log("Dependent Reset") | |
return context.sync(); | |
}); | |
} | |
async function registerDataChangedHandler_TS() { | |
await Excel.run(async (context) => { | |
const myBinding = context.workbook.bindings.add("Sheet1!A1:A2", "range", "A_binding"); | |
myBinding.onDataChanged.add(onBindingDataChanged_TS); | |
await context.sync(); | |
console.log("Created binding and added onDataChanged event for the binding."); | |
}); | |
} | |
async function onBindingDataChanged_TS(eventArgs) { | |
await Excel.run(async (context) => { | |
console.log("Cells were updated!"); | |
await context.sync(); | |
}); | |
} | |
/** Default helper for invoking an action and handling errors. */ | |
async function tryCatch(callback) { | |
try { | |
await callback(); | |
} catch (error) { | |
OfficeHelpers.UI.notify(error); | |
OfficeHelpers.Utilities.log(error); | |
} | |
} | |
language: typescript | |
template: | |
content: "<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n</section>\n\n<section class=\"ms-font-m\">\n <p>Click the following button to create a binding for range Sheet1!A1:A2 and add an onDataChanged event for that binding:</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n <button id=\"register-data-changed-handler\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Register data-changed handler</span>\n </button>\n</section>\n\n<section class=\"ms-font-m\">\n\t<p>After you've clicked the button, change value(s) in cell A1 or A2 and view the console message(s) as proof that the onDataChanged event fired.</p>\n</section>" | |
language: html | |
style: | |
content: | | |
/* Your style goes here */ | |
language: css | |
libraries: | | |
# Office.js | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
# CSS Libraries | |
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 | |
# NPM libraries | |
core-js@2.4.1/client/core.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
jquery@3.1.1 | |
# IntelliSense: @types/library or node_modules paths or URL to d.ts files | |
@types/office-js | |
@types/core-js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
@types/jquery |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment