Filters data based on date range

Date Filtering.EXCEL.yaml

name: Date Filtering
description: Filters data based on date range
host: EXCEL
api_set: {}
script:
  content: |
    tryCatch(registerOnChangedHandler);

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

        await context.sync();
        console.log("Added worksheet changed event handler.");
      });
    }

    async function onChanged(event) {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Work Entry");
        let fromDate = sheet.getRange("B2");
        fromDate.load("values");
        let toDate = sheet.getRange("C2");
        toDate.load("values");
        let tblQAWork = sheet.tables.getItem("QAWork");

        return context.sync().then(async function () {
          if (event.address == "B2" || event.address == "C2" || true) {
            const fromDateVal: string = fromDate.values[0][0].toString();
            const toDateVal: string = toDate.values[0][0].toString();
            tblQAWork.autoFilter.remove();
            
            let criteria = {
              filterOn: Excel.FilterOn.custom,
              criterion1: "",
              criterion2: ""
            };

            if (fromDateVal.trim().length > 0 && !isNaN(fromDateVal)) {
              criteria.criterion1 = ">=" + fromDateVal;
            }

            if (toDateVal.trim().length > 0 && !isNaN(toDateVal)) {
              criteria.criterion2 = "<=" + toDateVal;
            }

            if (criteria.criterion1.length > 0 || criteria.criterion2.length > 0) {
              tblQAWork.autoFilter.apply(tblQAWork.getRange(), 0, criteria);
            }
          }

          return 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">
        <p>This sample shows how to register and use an event handler for the worksheet onSelectionChanged event.</p>
    </section>
  language: html
style:
  content: ''
  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]

Worksheet events.EXCEL.yaml

name: Worksheet events
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: |
    tryCatch(registerOnChangedHandler);

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

        await context.sync();
        console.log("Added worksheet changed event handler.");
      });
    }

    async function onChanged(event) {
      await Excel.run(async (context) => {
        let sheet = context.workbook.worksheets.getItem("Work Entry");
        let fromDate = sheet.getRange("B2");
        fromDate.load("values");
        let toDate = sheet.getRange("C2");
        toDate.load("values");
        let tblQAWork = sheet.tables.getItem("QAWork");

        return context.sync().then(async function () {
          if (event.address == "B2" || event.address == "C2" || true) {
            const fromDateVal: string = fromDate.values[0][0].toString();
            const toDateVal: string = toDate.values[0][0].toString();
            tblQAWork.autoFilter.remove();
            
            let criteria = {
              filterOn: Excel.FilterOn.custom,
              criterion1: "",
              criterion2: ""
            };

            if (fromDateVal.trim().length > 0 && !isNaN(fromDateVal)) {
              criteria.criterion1 = ">=" + fromDateVal;
            }

            if (toDateVal.trim().length > 0 && !isNaN(toDateVal)) {
              criteria.criterion2 = "<=" + toDateVal;
            }

            if (criteria.criterion1.length > 0 || criteria.criterion2.length > 0) {
              tblQAWork.autoFilter.apply(tblQAWork.getRange(), 0, criteria);
            }
          }

          return 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">
        <p>This sample shows how to register and use an event handler for the worksheet onSelectionChanged event.</p>
    </section>
  language: html
style:
  content: ''
  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]