Skip to content

Instantly share code, notes, and snippets.

@troywweber7
Last active April 1, 2022 18:03
Show Gist options
  • Save troywweber7/541c4747c079c95dc47916aa66efbe9c to your computer and use it in GitHub Desktop.
Save troywweber7/541c4747c079c95dc47916aa66efbe9c to your computer and use it in GitHub Desktop.
Scripted actions that are helpful when debugging an MLI project.
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