Skip to content

Instantly share code, notes, and snippets.

@troywweber7
Last active March 9, 2021 19:58
Show Gist options
  • Save troywweber7/ca5a4bcb15e07f6bf14f0b6b0b0e71fc to your computer and use it in GitHub Desktop.
Save troywweber7/ca5a4bcb15e07f6bf14f0b6b0b0e71fc to your computer and use it in GitHub Desktop.
Collection of migration scripts which aid in cleaning up legacy artifacts on MLI spreadsheets. Does not compromise any MLI proprietary practices or data.
name: MLI Migrations
description: >-
Collection of migration scripts which aid in cleaning up legacy artifacts on
MLI spreadsheets. Does not compromise any MLI proprietary practices or data.
host: EXCEL
api_set: {}
script:
content: |
$("#lab923").click(() => tryCatch(lab923));
$("#lab924").click(() => tryCatch(lab924));
$("#lab940").click(() => tryCatch(lab940));
$("#lab941").click(() => tryCatch(lab941));
function isScreenSheet(sheetName: string): boolean {
return (
sheetName.startsWith("HS-") ||
(sheetName.endsWith(" Screens") && (sheetName.startsWith("CL-") || sheetName.startsWith("AL-")))
);
}
async function lab923() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
sheet.load({ name: true });
const rangeName = "PsdRange";
const namedObject = sheet.names.getItemOrNullObject(rangeName);
namedObject.load({ type: true });
await context.sync();
if (!isScreenSheet(sheet.name)) {
return console.warn("Migration can only be run on screen sheets");
}
if (namedObject.isNullObject) {
return console.error(`Could not identify named range "${rangeName}" on sheet "${sheet.name}"`);
}
if (namedObject.type !== "Range") {
return console.error(`Named object "${rangeName}" is not the expected "Range" type`);
}
const range = namedObject.getRange();
range.load({ columnIndex: true, rowIndex: true });
const usedRange = sheet.getUsedRange();
usedRange.load({ columnCount: true, columnIndex: true });
await context.sync();
const expectedRowIndex = 6;
let rowDiff = expectedRowIndex - range.rowIndex;
const direction = rowDiff > 0 ? "down" : "up";
rowDiff = Math.abs(rowDiff);
if (rowDiff === 0) {
return console.log("Charts are already properly placed");
}
const columnCount = usedRange.columnIndex + usedRange.columnCount - range.columnIndex;
const shiftRange = sheet.getRangeByIndexes(0, range.columnIndex, rowDiff, columnCount);
direction === "down"
? shiftRange.insert(Excel.InsertShiftDirection.down)
: shiftRange.delete(Excel.DeleteShiftDirection.up);
console.warn(`Charts shifted ${direction} by ${rowDiff} rows`);
await context.sync();
});
}
function deleteNamedRange(item: Excel.NamedItem): string {
item.getRange().clear();
item.delete();
return item.name;
}
async function lab924() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
sheet.load({ name: true });
const { names } = sheet;
names.load({ name: true, type: true });
const detailsName = "Details";
const detailsItem = names.getItemOrNullObject(detailsName);
detailsItem.load({ type: true });
await context.sync();
if (!isScreenSheet(sheet.name)) {
return console.warn("Migration can only be run on screen sheets");
}
if (detailsItem.isNullObject) {
return console.error(`Could not identify named range "${detailsName}" on sheet "${sheet.name}"`);
}
if (detailsItem.type !== "Range") {
return console.error(`Named object "${detailsName}" is not the expected "Range" type`);
}
const detailsRange = detailsItem.getRange();
detailsRange.load({ columnCount: true, columnIndex: true, rowCount: true, rowIndex: true });
await context.sync();
const columnIndex = detailsRange.columnIndex + 2;
const hsNumRange = sheet.getRangeByIndexes(detailsRange.rowIndex + 3, columnIndex, 1, 1);
const tsNumRange = sheet.getRangeByIndexes(detailsRange.rowIndex + 4, columnIndex, 1, 1);
for (const r of [hsNumRange, tsNumRange]) {
r.load({ values: true });
}
await context.sync();
const noHs = !hsNumRange.values[0][0];
const noTs = !tsNumRange.values[0][0];
const namedRanges = names.items.filter((i) => i.type === Excel.NamedItemType.range);
const headRanges = namedRanges.filter((i) => i.name.startsWith("Head"));
const tailRanges = namedRanges.filter((i) => i.name.startsWith("Tail"));
const rbsfRanges = namedRanges.filter((i) => i.name.endsWith("RecoveryBySizeFraction"));
if (!noHs && !noTs) {
return console.log("No named ranges require deletion.");
}
if (noHs) {
if (headRanges.length) {
const deleted = headRanges.map(deleteNamedRange);
console.warn("Deleted head screen named ranges: " + deleted.join(", "));
} else {
console.log("Head screen named ranges already deleted.");
}
}
if (noTs) {
if (tailRanges.length) {
const deleted = tailRanges.map(deleteNamedRange);
console.warn("Deleted tail screen named ranges: " + deleted.join(", "));
} else {
console.log("Tail screen named ranges already deleted.");
}
}
if (noHs || noTs) {
if (rbsfRanges.length) {
const deleted = rbsfRanges.map(deleteNamedRange);
console.warn("Deleted RBSF named ranges: " + deleted.join(", "));
} else {
console.log("RBSF named ranges already deleted.");
}
}
await context.sync();
});
}
async function lab940() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
sheet.load({ name: true });
const { charts, shapes } = sheet;
charts.load({ name: true });
shapes.load({ name: true, placement: true });
await context.sync();
if (!isScreenSheet(sheet.name)) {
return console.warn("Migration can only be run on screen sheets");
}
const chartNames = charts.items.map((i) => i.name);
const shapesToUpdate = shapes.items.filter(
(i) => chartNames.indexOf(i.name) > -1 && i.placement !== Excel.Placement.oneCell
);
if (shapesToUpdate.length) {
const updatedNames = shapesToUpdate
.map((s) => {
s.placement = Excel.Placement.oneCell;
return s.name;
})
.join(", ");
console.warn(`Ensured ${shapesToUpdate.length} charts use one-cell placement: ${updatedNames}`);
} else {
console.log("All charts already use one-cell placement");
}
});
}
async function lab941() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
sheet.load({ name: true });
const { names } = sheet;
names.load({ name: true, type: true, comment: true });
await context.sync();
if (!isScreenSheet(sheet.name)) {
return console.warn("Migration can only be run on screen sheets");
}
const ending = "RecoveryBySizeFraction";
const rbsfItems = names.items.filter((i) => i.type === Excel.NamedItemType.range && i.name.endsWith(ending));
if (!rbsfItems.length) {
return console.log("No RBSF named ranges require renaming.");
}
const rbsfNames = rbsfItems.map((i) => i.name);
const prefixes = rbsfNames.map((n) => n.split(ending)[0]);
if (rbsfItems.length > 1) {
const prefixLengths = prefixes.map((p) => p.length);
const maxStringLength = Math.max(...prefixLengths);
prefixes[prefixLengths.indexOf(maxStringLength)] = "Composite";
}
for (const item of rbsfItems) {
const name = prefixes[rbsfItems.indexOf(item)] + "Rbsf";
console.warn(`Renamed "${item.name}" to "${name}".`);
sheet.names.add(name, item.getRange(), item.comment);
item.delete();
}
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: "<p>\n\t<b><a href=\"https://automli.atlassian.net/browse/LAB-923\" target=\"_blank\" rel=\"noopener noreferrer\">LAB-923</a></b>\n\t- Detects whether charts are on the correct row or not, and if not, knows how far to shift them up or down.\n</p>\n<div>\n\t<button id=\"lab923\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Migrate</span>\n </button>\n</div>\n<hr>\n\n<p>\n\t<b><a href=\"https://automli.atlassian.net/browse/LAB-940\" target=\"_blank\" rel=\"noopener noreferrer\">LAB-940</a></b>\n\t- Ensures that all chart-shapes are using one-cell placement instead of the default two-cell placement.\n</p>\n<div>\n\t<button id=\"lab940\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Migrate</span>\n </button>\n</div>\n<hr>\n\n<p>\n\t<b><a href=\"https://automli.atlassian.net/browse/LAB-924\" target=\"_blank\" rel=\"noopener noreferrer\">LAB-924</a></b>\n\t- Detects and deletes named ranges which should not exist.\n</p>\n<div>\n\t<button id=\"lab924\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Migrate</span>\n </button>\n</div>\n<hr>\n\n<p>\n\t<b><a href=\"https://automli.atlassian.net/browse/LAB-941\" target=\"_blank\" rel=\"noopener noreferrer\">LAB-941</a></b>\n\t- All recovery by size fraction tables will have their range names shortened and simplified.\n</p>\n<div>\n\t<button id=\"lab941\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Migrate</span>\n </button>\n</div>\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;
}
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