Uses the onChanged event of a table to determine the specifics of changes.

Deloitte Add Shape.EXCEL.yaml

name: Deloitte Add Shape
description: Uses the onChanged event of a table to determine the specifics of changes.
host: EXCEL
api_set: {}
script:
  content: >
    $("#setup").click(() => tryCatch(setup));

    $("#register-handler").click(() => tryCatch(registerChangeEventHandler));

    $("#add-Shape-Handler").click(() => tryCatch(addShape));


    // async function registerChangeEventHandler() {

    //   await Excel.run(async (context) => {

    //     const expensesTable =
    context.workbook.worksheets.getActiveWorksheet().tables.getItem("ExpensesTable");


    //     // This event fires when a user edits a cell in the table.

    //     expensesTable.onChanged.add(onTableChanged);

    //     await context.sync();


    //     console.log("Added onChanged handler");

    //   });

    // }


    async function registerChangeEventHandler() {
      await Excel.run(async (context) => {
        const sheets = context.workbook.worksheets;

        // This event fires when a user edits a cell in the table.
        sheets.onChanged.add(onWorksheetChanged);
        // sheets.onSelectionChanged.add(onSelectionChanged);
        await context.sync();
        console.log("Added onChanged handler for the sheet");
      });
    }


    async function onWorksheetChanged(eventArgs:
    Excel.WorksheetChangedEventArgs) {
      await Excel.run(async (context) => {
        console.log("starting");
        console.log(eventArgs);
        const details = eventArgs.details;
        const address = eventArgs.address;

        console.log(
          `Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),` +
            ` now is ${details.valueAfter}(${details.valueTypeAfter})`
        );
      });
    }


    async function onSelectionChanged(eventArgs:
    Excel.WorksheetSelectionChangedEventArgs) {
      await Excel.run(async (context) => {
        console.log("selection Changing");
        console.log(eventArgs);
        // const details = eventArgs.details;
        // const address = eventArgs.address;

        // console.log(
        //   `Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),` +
        //   ` now is ${details.valueAfter}(${details.valueTypeAfter})`
        // );
      });
    }


    async function addShape() {
      await Excel.run(async (context) => {
        try {
          console.log("Inside AddShape");
          const sheets = context.workbook.worksheets;
          const img =
            "iVBORw0KGgoAAAANSUhEUgAAACAAAAAgCAYAAABzenr0AAAAAXNSR0IArs4c6QAAAERlWElmTU0AKgAAAAgAAYdpAAQAAAABAAAAGgAAAAAAA6ABAAMAAAABAAEAAKACAAQAAAABAAAAIKADAAQAAAABAAAAIAAAAACshmLzAAAD80lEQVRYCcVXSWgTURj+3mSSSVNba6x7VFQoWKjbQRSsCy4HD4IgBRE8iKK2KiioBdGDIm4gIm548+BFBVHQi0tBPVTBBcWCilspdSWgtE2bNBn/fzrz8iaZjE2t+mDy/v17ee9///wj0M9hXkIo3opFAlhBLtUmMJZmfni0k7yd5haSX49OQJOoQ9LS/OaH/PxHx2mMTnZhnymwBibK/a1trcBPYeJiKIL9Qxrw2c+n4ALMkzDiPdgDgR0EXOoXpKBOoJN8j0cNHBTb0ONl57kA/tc9CVwl5zleTkXLBJqNEqz02o28BcRPYJqZwg0CiRUN5O/QJgJYHt2BF6qZpjL8zwcKXlZ3F8O2/kBk8SkgWKaGdeiYmcZNxnAEPMsF8Jlb2z6Af66Pq0VwwiIIoxzhmQ0watapGCodYwzGcoRyAVbCDfDMjekbnXjW3Nt2z8W7GMKwsGyhtQBrWzjbBzBEeDhCVaukZ++nR0h/fSp5T4KwnKPQ2YDvOU3KVdMQGDkDCIQ8/VVhaOpqCF3uKJIfbiEwJufypJO0qGfklulzpWttY9YLu8J9U4tMyfwjKJm9S8X5Yzrx6CgS93Zn41Cxooo5QuPyqoKzRWDopKzhIFF5MamqMrZGhYBru2tkOr+4+MFgvGIyNudAdS5A1/1GpD7ehvDJAREqR2TZOQjNSiOk46+QeLA3N5TFm5QDqdbbXrpq3X6ruZWpTqTeXnPLcrjw7EYJzqpE80EkX1/OsfJnGZuvofNK9bd2aQWMaRukJJOII/mqOHDb2VqADNRfQp+4FIGKydI8+fICkO6WfL8JEybvADcSRY3wjE0u++7n5118vxmBTzplYjudRZXLKVhKtX2JZxIKowLBKdmLk/7eAr2yBuCnwJBJSLmlDsbmFG6hZ6GqiNQeRnjWFlVUkA5UVmPIiksF9Y6i+8kpdN3d6rDO3KJxD+dwzqyVjnLIQZu9YjK2xg0ktV0/VaT0j/cqOyh0Xsy+UtxExwDEj+EMrWZzFoleRqNmUrcQzIqIiiw4hmBsniUzTRMd1+uQ6Whz2XgymRTSX/gNab+MiCLgs9GdqLfKGHev1CispXeC/UbMkMNjVyytbDz0sXOlrLf1DlJvrki+KIKa1VAJ9rMPX0NYzSJ1r0wXGkbNeqp8AanufnZO0kUThOU0qNYCOAC3zrQvzYWC6bFaqcp0fqZSnZe7Uu9LEIaFZRvJBXDfzq0zyT0PtatpO5LvbsI0M0g8PETHmfLFKaBsYwz1G8FKQtX4X7fleQvgxXC/9q8+TOQRqLvACRINYSHJDlBeuOunavg7us/3AMdyki7XxXMHVCPejf/ycaougum/9Xn+C/vrSyDuWh6dAAAAAElFTkSuQmCC";
          const curSheet = sheets.getActiveWorksheet();
          let ncrShape = curSheet.shapes.addImage(img);
          const curCell = context.workbook.getActiveCell();
          curCell.load("address,values");
          await context.sync();
          let value = "";
          console.log(curCell.values);
          if (Array.isArray(curCell.values)) {
            value = curCell.values[0][0];
          } else {
            value = curCell.values;
          }
          const currentCell = curCell.address;
          const currentCellAddress = currentCell.split("!").pop();
          console.log(currentCellAddress, currentCell);
          const nextCellAddress = getNextCell(currentCellAddress);
          console.log(nextCellAddress);
          const nextCellRange = curSheet.getRange(nextCellAddress);
          nextCellRange.load("top,left");
          await context.sync();
          const { left, top } = nextCellRange;
          console.log("Top, Left=", top, left);
          ncrShape.left = left;
          ncrShape.top = top;
          ncrShape.width = 12;
          ncrShape.height = 12;
          ncrShape.placement = "Absolute";

          await context.sync();
        } catch (error) {
          // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
          console.error(error);
        }
      });
    }


    function getNextCell(currentCell) {
      try {
        console.log("In Next Cell Address", currentCell);
        const regex = /[A-Z]/g;
        const numberRegex = /[0-9]/g;
        const chars = currentCell.match(regex);
        const nums = currentCell.match(numberRegex);
        let flag = true;
        let x = 1;
        while (flag) {
          if (chars[chars.length - x] === "Z") {
            if (chars.length - x === 0) {
              chars[chars.length - x] = "A";
              chars.unshift("A");
              flag = false;
            } else {
              chars[chars.length - x] = "A";
              x++;
            }
          } else {
            chars[chars.length - x] = String.fromCharCode(chars[chars.length - x].charCodeAt(0) + 1);
            flag = false;
          }
        }
        return chars.join("") + nums.join("");
      } catch (error) {
        console.log(error);
      }
    }


    async function onTableChanged(eventArgs: Excel.TableChangedEventArgs) {
      await Excel.run(async (context) => {
        const details = eventArgs.details;
        const address = eventArgs.address;

        console.log(
          `Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),` +
            ` now is ${details.valueAfter}(${details.valueTypeAfter})`
        );
      });
    }


    async function setup() {
      await Excel.run(async (context) => {
        context.workbook.worksheets.getItemOrNullObject("Sample").delete();
        const sheet = context.workbook.worksheets.add("Sample");

        const expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
        expensesTable.name = "ExpensesTable";

        expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

        expensesTable.rows.add(null /*add at the end*/, [
          ["4/1/2017", "The Phone Company", "Communications", "$120"],
          ["4/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
          ["4/5/2017", "Best For You Organics Company", "Groceries", "$27"],
          ["4/10/2017", "Coho Vineyard", "Restaurant", "$33"],
          ["4/11/2017", "Bellows College", "Education", "$350"],
          ["4/15/2017", "Trey Research", "Other", "$135"],
          ["4/15/2017", "Best For You Organics Company", "Groceries", "$97"]
        ]);

        sheet.getUsedRange().format.autofitColumns();
        sheet.activate();
        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: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to use table changed events.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Setup</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Create table</span>\n    </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"register-handler\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Register data change handler</span>\n    </button>\n\n\t<button id=\"add-Shape-Handler\" class=\"ms-Button\">\n\t        <span class=\"ms-Button-label\">Add Shape</span>\n\t    </button>\n\t<p />\n\n\n\t<p>Edit individual cells in the table to trigger the event and see the console output.</p>\n</section>"
  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

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

  [email protected]/client/core.min.js
  @types/core-js

  [email protected]
  @types/[email protected]