Registers event handlers that run when a table is changed or selected.

money-add-row.EXCEL.yaml

name: money-add-row
description: Registers event handlers that run when a table is changed or selected.
host: EXCEL
api_set: {}
script:
  content: "$(\"#setup\").click(() => tryCatch(setup));\n$(\"#register-on-changed-handler\").click(() => tryCatch(registerOnChangedHandler));\n$(\"#change-data\").click(() => tryCatch(changeData));\n\nasync function registerOnChangedHandler() {\n  await Excel.run(async (context) => {\n    let table = context.workbook.tables.getItemAt(0);\n    table.onChanged.add(onChange);\n\n    await context.sync();\n    console.log(\"A handler has been registered for the onChanged event\");\n  });\n}\n\nasync function changeData() {\n  await Excel.run(async (context) => {\n    let sheet = context.workbook.worksheets.getItem(\"Sample\");\n    let range = sheet.getRange(\"B2\");\n    range.values = [[\"test\"]];\n    range.format.autofitColumns();\n\n    await context.sync();\n    console.log(\"B2 value has been changed.\");\n  });\n\n  await Excel.run(async (context) => {\n    let sheet = context.workbook.worksheets.getItem(\"Sample\");\n    let range = sheet.getRange(\"B3\");\n    range.values = [[\"Expense\"]];\n    range.format.autofitColumns();\n\n    await context.sync();\n    console.log(\"B3 value has been changed.\");\n  });\n}\n\nasync function onChange(event) {\n  return Excel.run(async (context) => {\n    console.log(\"onChange!\")\n\n    const sheet = context.workbook.worksheets.getItemOrNullObject(\"Sample\")\n\n    await context.sync()\n\n    if (sheet.isNullObject) {\n      console.log(\"sheet not found \U0001F62D\")\n      return\n    }\n    \n    console.log(\"got sheet!\")\n\n    const table = sheet.tables.getItemOrNullObject(\"SalesTable\")\n\n    await context.sync()\n\n    if (table.isNullObject) {\n      console.log(\"table not found \U0001F62D\")\n      return\n    }\n\n    console.log(\"got table!\")\n\n    const range = event.getRangeOrNullObject(context)\n\n    const tableRange = table.getDataBodyRange()\n    tableRange.load('rowIndex')\n    range.load('rowIndex')\n\n    await context.sync()\n\n    const tableRowIndex = tableRange.rowIndex\n    const rangeRowIndex = range.rowIndex\n\n    const index = rangeRowIndex - tableRowIndex\n\n    console.log(`row index: ${index}`)\n\n    range.load('rowCount')\n    table.load('rows')\n\n    await context.sync()\n\n    const rowCount = range.rowCount\n    const allRows = table.rows\n\n    const rows: Excel.TableRow[] = []\n\n    for (let i = index; i < index + rowCount; i++) {\n      rows.push(allRows.getItemAt(i))\n    }\n\n    console.log(`row count: ${rows.length}`)\n\n    for (let i = 0; i < rows.length; i++) {\n      console.log(`row: ${i}`)\n\n      const row = rows[i]\n      row.load('values')\n      await context.sync()\n\n      console.log(`row ${i} values: ${row.values}`)\n    }\n\n    // let changedCell = event.getRange(context);\n    // changedCell.load(\"values\");\n    // await context.sync();\n    // let value = changedCell.values;\n    // console.log(\n    //   \"Handler for table onChanged event has been triggered. The request address:\" +\n    //     event.address +\n    //     \",data: \" +\n    //     value[0][0]\n    // );\n  });\n}\n\nasync function setup() {\n  await Excel.run(async (context) => {\n    context.workbook.worksheets.getItemOrNullObject(\"Sample\").delete();\n    const sheet = context.workbook.worksheets.add(\"Sample\");\n\n    let salesTable = sheet.tables.add(\"A1:B1\", true);\n    salesTable.name = \"SalesTable\";\n\n    salesTable.getHeaderRowRange().values = [[\"Category Name\", \"Category Type\"]];\n\n    salesTable.rows.add(null, [\n      [\"foo\", \"Expense\"],\n      [\"bar\", \"Transfer\"],\n      [\"baz\", \"Transfer\"],\n      [\"qux\", \"Expense\"],\n      [\"xyz\", \"Transfer\"]\n    ]);\n\n    sheet.getUsedRange().format.autofitColumns();\n    sheet.getUsedRange().format.autofitRows();\n\n    sheet.activate();\n    await context.sync();\n  });\n}\n\n/** Default helper for invoking an action and handling errors. */\nasync function tryCatch(callback) {\n  try {\n    await callback();\n  } catch (error) {\n    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.\n    console.error(error);\n  }\n}\n"
  language: typescript
template:
  content: |+
    <section class="ms-font-m">
        <p>This sample shows how to register and use event handlers for table onChanged and onSelectionChanged events.</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>
        <button id="register-on-changed-handler" class="ms-Button">
            <span class="ms-Button-label">Register onChanged event handler</span>
        </button>
    </section>

    <section class="samples ms-font-m">
        <p>Changing data in a table triggers the data changed event. You can change the data manually or programmatically.</p>
        <button id="change-data" class="ms-Button">
            <span class="ms-Button-label">Change data</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

  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