Last active
April 1, 2022 18:03
-
-
Save troywweber7/541c4747c079c95dc47916aa66efbe9c to your computer and use it in GitHub Desktop.
Scripted actions that are helpful when debugging an MLI project.
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: MLI Debug | |
description: Scripted actions that are helpful when debugging an MLI project. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
const rangeNameInput = $("#range-name-input"); | |
$("#name-range").click(() => tryCatch(nameRange)); | |
async function nameRange() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getActiveWorksheet(); | |
const range = context.workbook.getSelectedRange(); | |
const name = rangeNameInput.val() as string; | |
if (!name) throw Error("Range name must be provided"); | |
sheet.names.add(name, range, "Added via by ScriptLab"); | |
await context.sync(); | |
}); | |
} | |
const fillSheetsButton = $("#fill-sheets"); | |
const recreateCheckbox = $("#recreate-checkbox"); | |
const runSheetsButton = $("#run-sheets"); | |
const runLabel = $("#run-label"); | |
const sheetsInput = $("#sheets-input"); | |
recreateCheckbox.change(() => | |
runLabel.html("Delete" + (recreateCheckbox.prop("checked") ? " / Re-Create" : "") + " Sheets") | |
); | |
fillSheetsButton.click(() => tryCatch(fillSheets)); | |
runSheetsButton.click(() => tryCatch(reCreateSheets)); | |
async function fillSheets() { | |
await Excel.run(async (context) => { | |
const sheets = context.workbook.worksheets; | |
sheets.load({ name: true }); | |
await context.sync(); | |
const names = sheets.items.map((i) => i.name); | |
sheetsInput.val(names.join("\n")); | |
}); | |
} | |
async function reCreateSheets() { | |
await Excel.run(async (context) => { | |
const sheets = context.workbook.worksheets; | |
sheets.load({ name: true }); | |
await context.sync(); | |
const sheetNames: string[] = (sheetsInput.val() as string).split("\n").filter((n) => n.trim()); | |
const recreate: boolean = recreateCheckbox.prop("checked"); | |
for (const name of sheetNames) { | |
const sheet = sheets.items.find((s) => s.name === name); | |
if (sheet) { | |
sheet.delete(); | |
console.warn("deleted: " + name); | |
} | |
if (recreate) { | |
sheets.add(name); | |
console.log("created: " + name); | |
} | |
} | |
if (recreate) { | |
sheets.getItem(sheetNames[sheetNames.length - 1]).activate(); | |
} | |
await context.sync(); | |
}); | |
} | |
$("#log-range-data").click(() => tryCatch(logRangeData)); | |
async function logRangeData() { | |
await Excel.run(async (context) => { | |
const range = context.workbook.getSelectedRange(); | |
range.load(); | |
await context.sync(); | |
console.log(range.toJSON()); | |
}); | |
} | |
$("#no-highlight").click(() => tryCatch(noHighlight)); | |
async function noHighlight() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getActiveWorksheet(); | |
const range = sheet.getUsedRange(); | |
range.format.fill.clear(); | |
sheet.tabColor = ""; | |
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: "<details>\n\t<summary>Create Named Range</summary>\n\t<p>Select a range, type a name below, and then click the button.</p>\n\t<div>\n\t\t<input id=\"range-name-input\" type=\"text\" placeholder=\"Name selected range...\">\n\t</div>\n\t\t<button id=\"name-range\" class=\"ms-Button\">\n\t\t\t<span id=\"name-range-label\" class=\"ms-Button-label\">Create Named Range</span>\n\t</button>\n</details>\n\n<hr>\n\n<details>\n\t<summary>Delete / Re-Create Sheets</summary>\n\t<p>Get list of curent sheets, edit said list, and then delete or recreate said sheets.</p>\n\t<div>\n\t\t<button id=\"fill-sheets\" class=\"ms-Button\">\n\t\t\t<span class=\"ms-Button-label\">Get Current Sheets</span>\n\t\t</button>\n\t</div>\n\t<div>\n\t\t<textarea id=\"sheets-input\" placeholder=\"Enter sheet names, one per line...\"></textarea>\n\t</div>\n\t<div>\n\t\t<input id=\"recreate-checkbox\" type=\"checkbox\" checked>\n\t\t<label for=\"recreate-checkbox\">recreate sheets</label>\n\t</div>\n\t<div>\n\t\t<button id=\"run-sheets\" class=\"ms-Button\">\n\t <span id=\"run-label\" class=\"ms-Button-label\">Delete / Re-Create Sheets</span>\n\t\t</button>\n\t</div>\n</details>\n\n<hr>\n\n<details>\n\t<summary>Log Selected Range Data</summary>\n\t<p>Logs useful information about whatever range is currently selected on the sheet.</p>\n\t<button id=\"log-range-data\" class=\"ms-Button\">\n\t <span class=\"ms-Button-label\">Log Selected Range Data</span>\n\t</button>\n</details>\n\n<hr>\n\n<details>\n\t<summary>Remove Highlighting</summary>\n\t<p>Removes all Highlighting on the sheet, including any tab highlighting.</p>\n\t<button id=\"no-highlight\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Remove Highlighting</span>\n\t</button>\n</details>\n\n<hr>" | |
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; | |
} | |
input:not([type=checkbox]), textarea { | |
width: calc(100% - 6px); | |
} | |
textarea{ | |
min-height: 200px; | |
} | |
div{ | |
margin-bottom: 0.5rem | |
} | |
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