Registers event handlers that run when data is changed in worksheet, the selected range changes in a worksheet, or the worksheet is recalculated.

Drilldown by clicking cell.EXCEL.yaml

name: Drilldown by clicking cell
description: >-
  Registers event handlers that run when data is changed in worksheet, the
  selected range changes in a worksheet, or the worksheet is recalculated.
host: EXCEL
api_set: {}
script:
  content: >
    $("#setup").click(() => tryCatch(setup));


    $("#register-on-selection-changed-handler").click(() =>
    tryCatch(registerSelectionChangedHandler));

    $("#select-range").click(() => tryCatch(selectRange));


    $("#register-on-changed-handler").click(() =>
    tryCatch(registerOnChangedHandler));

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

    $("#recalculate").click(() => tryCatch(recalculate));


    var isExpand = false;


    async function registerSelectionChangedHandler() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        sheet.onSelectionChanged.add(onSelectionChange);
        await context.sync();
      });
    }


    async function selectRange() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        let range = sheet.getRange("B7:C7");
        range.select();

        await context.sync();
      });
    }


    async function onSelectionChange(event) {
      await Excel.run(async (context) => {
        if (event.address == "A7"){
          let sheet = context.workbook.worksheets.getItem("Sample");
          let salesTable = sheet.tables.getItemAt(0);
          //console.log("My Test" + salesTable.rows.count);
          var range = sheet.getRange("A7");
          if (isExpand == false){
            salesTable.rows.add(null, [
              ["    Beijing", 5000, 7000, 6544, 4377, "=SUM(B2:E2)"],
              ["    Shanghai", 400, 323, 276, 651, "=SUM(B3:E3)"],
              ["    Guangzhou", 12000, 8766, 8456, 9812, "=SUM(B4:E4)"],
              ["    Shenzhen", 1550, 1088, 692, 853, "=SUM(B5:E5)"],
            ]);
            isExpand = true;
            range.values = [["'[-] China"]];
            sheet.getRange("A8").select();
          }
          else{
            sheet.getRange("A8").select();
            range.values = [["'[+] China"]];
            var rangeDelete = sheet.getRange("A8:F11");
            rangeDelete.delete();
            rangeDelete.clear();
            isExpand = false;
          }

        }
        console.log("The selected range has changed to: " + event.address );
      });
    }


    async function registerOnCalculatedHandler() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        sheet.onCalculated.add(onCalculated);
        await context.sync();
        console.log("Added worksheet selection changed event handler.");
      });
    }


    async function recalculate() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        let randomResult = context.workbook.functions.randBetween(1, 3000).load("value");
        await context.sync();

        let row = sheet.tables.getItem("SalesTable").rows.getItemAt(0);
        let newValue = [["Frames", 5000, 7000, 6544, randomResult.value, "=SUM(B2:E2)"]];
        row.values = newValue;
        row.load("values");
        await context.sync();
      });
    }


    async function onCalculated(event) {
      await Excel.run(async (context) => {
        console.log("The worksheet has recalculated.");
      });
    }


    async function registerOnChangedHandler() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        sheet.onChanged.add(onChange);

        await context.sync();
        console.log("A handler has been registered for the onChanged event.");
      });
    }


    async function changeData() {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Sample");
        let range = sheet.getRange("B5");
        range.values = [[800]];
        range.format.autofitColumns();

        await context.sync();

        console.log("B5 value has been changed.");
      });
    }


    async function onChange(event) {
      await Excel.run(async (context) => {
        console.log("Handler for worksheet onChanged event has been triggered.  Data changed address : " + event.address);
      });
    }


    async function setup() {
      await Excel.run(async (context) => {
        let sheet = await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample");
        let salesTable = sheet.tables.add("A1:F1", true);
        salesTable.name = "SalesTable";

        salesTable.getHeaderRowRange().values = [["Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4", "Total"]];

        salesTable.rows.add(null, [
          ["USA", 5000, 7000, 6544, 4377, "=SUM(B2:E2)"],
          ["UK", 400, 323, 276, 651, "=SUM(B3:E3)"],
          ["Germany", 12000, 8766, 8456, 9812, "=SUM(B4:E4)"],
          ["Japan", 1550, 1088, 692, 853, "=SUM(B5:E5)"],
          ["Korea", 225, 600, 923, 544, "=SUM(B6:E6)"],
          ["'[+] China", 6005, 7634, 4589, 8765, "=SUM(B7:E7)"]
        ]);

        sheet.activate();
        await context.sync();
      });
    }


    /** Default helper for invoking an action and handling errors. */

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
      }
    }
  language: typescript
template:
  content: |-
    <section class="ms-font-m">
        <p>This sample shows how to register and use an event handler for the worksheet onSelectionChanged event.</p>
    </section>

    <section class="setup ms-font-m">
        <h3>Set up</h3>
        <button id="setup" class="ms-Button">
        <span class="ms-Button-label">Add sample data</span>
    </button>
    </section>

    <section class="samples ms-font-m">
        <h3>Try it out</h3>

        <p><b>Selection Changed</b></p><section class="samples ms-font-m">
        <button id="register-on-selection-changed-handler" class="ms-Button">
            <span class="ms-Button-label">onSelectionChanged</span>
        </button>

    </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

  @microsoft/[email protected]/dist/office.helpers.min.js
  @microsoft/[email protected]/dist/office.helpers.d.ts

  [email protected]
  @types/[email protected]