Last active
March 9, 2021 19:58
-
-
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.
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 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