CODEWITHSUNDEEP
office-js
Luis Pedraza
Luis Pedraza

Reputation: 35

Where to call Office.initialize with Excel JS custom functions files?

I'm creating a custom function in Excel JS that I want to use other Office.js features with. I'm trying to initialize Office.js in the custom function like so:

async function getData(indicator, date, handler) {
await Office.onReady();
if (date !== "earliest" && date !== "latest" && !date.includes("-")) {
  const newDate = new Date((parseFloat(date) - (25567 + 2))*86400*1000); // Converts Excel date, days since 1900, to YYYY-MM-DD as per https://gist.github.com/christopherscott/2782634
  date = formatDate(newDate)
}
dd_value = new DDValue("A1", indicator, date)
console.log(dd_value)
try {
  await Excel.run(function (context) {
      var range = context.workbook.getSelectedRange();
      range.load("address");

      return context.sync()
          .then(function () {
              console.log(`The address of the selected range is "${range.address}"`);
          });
  }).catch(errorHandlerFunction);
} catch(error) {
  console.log("ERROR")
  console.log(error)
}
}

However, I'm getting the following errors:

Uncaught TypeError: Cannot redefine property: context

Uncaught Error: Office.js has not fully loaded. Your app must call "Office.onReady()" as part of it's loading sequence (or set the "Office.initialize" function). If your app has this functionality, try reloading this page.

I've also tried to initialize Office.js in the index.html page (just a script in the header), but this hasn't worked either.

Has anyone tried to implement the other Office.js features in custom functions yet? How did the Office.js initialization work for you?

Thanks in advance

Upvotes: 0

Views: 788

Answers (1)

Keyur Patel - MSFT
Keyur Patel - MSFT

Reputation: 804

this isn’t possible today, but is on our radar.

a) We are actively developing the feature to pass in the address of the calling cell of the function (selected range doesn’t work in this context as the user could have selected a different cell than your function is entered on).

b) if you need additional APIs, would like to know your scenario? We will expose office.js to custom functions in some fashion but we are generally being cautious here as calling back to excel during a calculation can lead to unpredictable results (as exists today for XLLand VBA UDFs).

Thanks!

Upvotes: 1

Related Questions