1

I started to work with the Custom Functions with office js api, and I have a few questions.

I have a scenario, where I might need to refresh all the custom function formulas values after clicking on the add-in button in the excel Ribbon. Is there a way to do that from function-file.js or any other way in general?

Also what would be a good approach to share the state between custom-functions.js or any other part of your add-in? I have been trying to use OfficeRuntime.AsyncStorage for this purpose, but the problem is that office.js library do not have AsnycStorage defined in it, and AsyncStorage can only be accessed from custom functions, caused it is defined in the Custom functions runtime.

Zohaib
  • 363
  • 2
  • 4
  • 15

1 Answers1

1

thanks for asking about custom functions and trying them out! To pick up all changes in your functions (and see an update in the cells that use these functions), you will need to re-sideload the project. (See docs on sideloading here) We recognize that this process could be improved - so, our engineering team is working on a "live reload" that will be coming in the future.

RE: OfficeRuntime.AsyncStorage - it is accessible to all parts of an add-in (so, both custom functions and non-custom functions parts of an add-in, like UI elements such as the task pane can use it). We've got some documentation on AsyncStorage currently here.

Let me know if you have additional questions - thanks!

Michelle Scharlock
  • 41
  • 3
  • 1
    To add to this, you may also need to ensure you have the right reference in your taskpane to office.js. We are still working on publishing these preview bits to our official CDN, but for now make sure you reference: https://officedev.github.io/custom-functions/lib/office.js – Keyur Patel - MSFT Oct 25 '18 at 15:50
  • Hi @MichelleScharlock! Thanks for the answer. By refresh I do not meant to update the functions implementation in excel, but rather to initiate recalculation of the values for each cell of our custom functions, just like if we press 'CTRL + ALT + SHIFT + F9' manually. So is there a way to do the same from task pane or excel ribbon for example? I tried to do it using context.workbook.application.calculate("Full"); and though it did recalculated the built-in excel formula values, it did not recalculate the values for custom functions. – Zohaib Oct 26 '18 at 18:17
  • @KeyurPatel-MSFT I still cannot find the definition for AsyncStorage even in this link: https://officedev.github.io/custom-functions/lib/office.js – Zohaib Oct 26 '18 at 18:21
  • For your AsyncStorage q: AsyncStorage is actually not in office-js, it lives in a new type, OfficeRuntime. (https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-runtime) You can find the ref docs on it here (https://learn.microsoft.com/en-us/javascript/api/office-runtime/officeruntime.asyncstorage?view=office-js). – Michelle Scharlock Oct 26 '18 at 19:01
  • I'm not aware of a way to recalc functions from the taskpane or ribbon in the way that you describe. Because CFs run in their own runtime, anything using context shouldn't work (and it sounds like, didn't work, as expected). @KeyurPatel-MSFT, can you double check me on whether or not there is an instant recalc? – Michelle Scharlock Oct 26 '18 at 19:02
  • @MichelleScharlock if there is a separate API reference link to Office Runtime, can you please provide me with the link, cause I was unable to find it in the docs! – Zohaib Oct 27 '18 at 10:09
  • Sure thing, here's the link to API reference on Office Runtime: https://learn.microsoft.com/en-us/javascript/api/office-runtime?view=office-js – Michelle Scharlock Nov 06 '18 at 23:18