office-addinsexcel-addinscustom-functions-excel

Excel Add in - Refresh all custom functions in a specific sheet

I am just curious how do I manually refresh all custom functions of my addin in a specific sheet using JavaScript? I have tried to search online through Microsoft docs but could not find any docs mentioning this. Thanks

Solution

  • You can use Worksheet.calculate() API to refresh all custom functions in a specific sheet. The following is an example function.

    async function reCalculateSheet() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("SampleSheet");
    sheet.calculate(false);
    await context.sync();
  });
}

    Besides, I recommend you a Microsoft office add-in “Script Lab” where you can search the sample of the scenario you need and experiment with the Office JavaScript API without ever leaving Excel.