3

I am trying to use JavaScript API for Excel.

Does anyone know how to trigger calculation for a cell, a range, a worksheet or a workbook?

For instance, the following code selects a range, but how can we re-calculate the range?

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "F5:F10"; 
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.select();
    return ctx.sync(); 
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});
SoftTimur
  • 5,630
  • 38
  • 140
  • 292

1 Answers1

2

You can re-calculate the workbook, which should accomplish your scenario. For the JS API, it's at the application level:

Excel.run(function (ctx) { 
    ctx.workbook.application.calculate('Full');
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

That being said, unless your workbook is in manual calculation mode, there should be no need for you to force a recalc. All dependent cells should get updated automatically.

More info on the API here: https://github.com/OfficeDev/office-js-docs/blob/master/excel/resources/application.md#calculatecalculationtype-string

Hope this helps!

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

Michael Zlatkovsky - Microsoft
  • 8,424
  • 1
  • 33
  • 46
  • In VBA, we can [recalculating a specified range](https://msdn.microsoft.com/en-us/library/office/ff834613.aspx?f=255&MSPPError=-2147217396), are you sure it is impossible by JavaScript API? It would be a shame... – SoftTimur May 08 '16 at 10:10
  • 1
    The Excel COM (VBA & .NET Interop) interfaces expose calculation at application level (all open workbooks), individual worksheet, and range (with or without dependencies) and allows control of calculation mode. the JS API currently does not allow for controlling calculation and only enables calculation at the application level even though the JS API only works at single workbook level. Hopefully the JS API will align more closely with all the other EXCEL APIs at some time in the future. – Charles Williams May 08 '16 at 20:58
  • 1
    That's a fair point regarding application-level recalc versus only workbook-level access. I'll mention it to the team, to see if we can add worksheet-level, workbook-level, or even range-level recalc. – Michael Zlatkovsky - Microsoft May 09 '16 at 17:05
  • Thank you Michael and Charles... I think `formulas` and their `calculation` are essential components to make Excel a programming language rather than a database. So it is really worth making it available (at different levels)... – SoftTimur May 09 '16 at 17:38
  • One thing is recalculating formulas. Another need is to be able to toggle calculation from mannual to automatic, just like VBA. If we paste and sync large amount of data from code, the sync operation takes a lot of time. We should be able to: 1. Turn off calculation by setting calculationMode to "mannual" 2. Then paste data from code. 3. Turn on calculation by setting calculationMode to "automatic". Awaiting for something similar. PS: Appologies! Previous comment was half. Deleted it. Pressing enter didn't put a new line instead submitted. Can this comment be a new question? – sidnc86 Sep 23 '16 at 10:01
  • @sidnc86, if you want, you can add a new question. But I think I captured the gist of what you want. Let me send that feedback to the team, and we can discuss it and put it on our backlog. – Michael Zlatkovsky - Microsoft Sep 24 '16 at 00:31
  • @MichaelZlatkovsky-Microsoft this is probably caused by historical reasons but just letting you know that the link shared in the post does not work (404). I am guessing this is the updated one? https://github.com/OfficeDev/office-js-docs-reference cheers – st35ly Apr 03 '22 at 22:33