Collection of migration scripts which aid in cleaning up legacy artifacts on MLI spreadsheets. Does not compromise any MLI proprietary practices or data.

MLI Migrations.EXCEL.yaml

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