Excel JS API: write, calculate and load values from cells with custom functions

Question

The intention is to write the formula (custom function) to the cell, calculate it, load values and retrieve them in a single function.

function myFunc() {
    Excel.run(function (ctx) {
        var fExcel = '=SUM(1,2)';
        var fCustom = '=custFunc()';

        var rng = ctx.workbook.worksheets.getActiveWorksheet().getRange('A1');

        //rng.formulas = [[fExcel]]; // works OK          
        rng.formulas = [[fCustom]]; // values are #GETTING_DATA

        // try different calc calls

        rng.load("values");

        return ctx.sync().then(function () {
            console.log(rng.values);
        });
    });
}

For built-in Excel functions, everything works as expected and console logs a value 3 after ctx.sync(). With custom functions (that send a request to the external server to compute the result) the values are '#GETTING_DATA'. I've tried all the following things before rng.load("values"); to trigger the calculation, but nothing have worked so far:

rng.calculate();
var s = ctx.workbook.worksheets.getActiveWorksheet(); s.calculate(true);
ctx.workbook.application.calculate('Full');

Is there a way to trigger the calculation of custom functions and make sure that the values are available after the ctx.sync()?

score 1 · Answer 1 · answered Feb 12 '19 at 18:09

Interesting scenario!

Today, this may be feasible leveraging the onCalculate event but the caveat is you it will fire 2x when you're custom function is calculating.

This is because your custom function first will show a #GETTING_DATA, while it calculates in the background.
- This gives the user back control while your functions are still evaluating, allowing the application to be more responsive. This behavior differs from VBA or XLL UDFs that could hang Excel.
When Excel is done with calculation, it will fire the calculation event again. This is when the results come back in by resolving the promise.

This Script lab gist should give you an indication of how it works:

/*This gist works in combination with any registered Excel JS Custom function*/

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

var rangeToCheck;

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();

    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);
  }
}

Excel JS API: write, calculate and load values from cells with custom functions

1 Answers1