Gets and sets values and formulas for a range.

Formula Builder Pattern.EXCEL.yaml

name: Formula Builder Pattern
description: Gets and sets values and formulas for a range.
host: EXCEL
api_set: {}
script:
  content: >
    /*This gist works in combination with any registered Excel JS Custom
    function*/


    $("#set-formulas").click(() => tryCatch(setFormulas));

    $("#calc").click(() => tryCatch(refreshFunctions));


    var rangeToCheck;



    function refreshFunctions() {
      Excel.run(function (ctx) {
        var sheet = ctx.workbook.worksheets.getActiveWorksheet();
        return ctx.sync().then(function () {
          
          //aveToAsyncStorage("calcMode", "RefreshUDFS");
          //sheet.calculate(true);
          ctx.workbook.application.calculate("FullRebuild");//tried with Full as well
          return ctx.sync();
        });
      }).catch(function (error) { console.log("Error", error); });
    }



    async function setFormulas() {
      await Excel.run(async (context) => {
        //register for event
        context.workbook.worksheets.getActiveWorksheet().onCalculated.add(handleCalculate);

        //write to grid
        const sheet = context.workbook.worksheets.getItem("Sheet1");

        rangeToCheck = "A1";
        const range = sheet.getRange(rangeToCheck);
        range.formulas = [["=CONTOSO.CONTAINS(A1, Days)"]];
        range.format.autofitColumns();

        await context.sync();
      });
    }


    async function handleCalculate(event) {
      //read cell
      console.log("calc ended - begin");

      console.log("Change type of event: " + event.changeType);
      console.log("Address of event: " + event.address);
      console.log("Source of event: " + event.source);

      //Read A1 and log it back to the console
      await Excel.run(async (context) => {
        //write to grid
        const sheet = context.workbook.worksheets.getItem("Sheet1");

        const range = sheet.getRange(rangeToCheck);
        range.load("values");

        await context.sync();

        console.log("Name of the changed table: " + range.values);

        if (range.values.toString() != "GETTING_DATA") {
          console.log("Success: " + range.values);
        }
      });
    }


    /** 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\">\n    <p>This sample shows how to set and get values and formulas for a range.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n    <h3>Try it out</h3>\n\n    <button id=\"set-formulas\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Set formulas</span>\n    </button>\n\t\t<button id=\"calc\" class=\"ms-Button\">\n\t\t        <span class=\"ms-Button-label\">Calc</span>\n\t\t    </button>\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;
    }

    #set-formula {

        background-color:blue;
    }
  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

  @microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js
  @microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts

  jquery@3.1.1
  @types/jquery@3.3.1