0

Is there a way to use Excel.run to fetch data from a workbook and then use that data to calculate a UDF along with the parameters? I know that you can fetch data from the internet and come up with a number (https://learn.microsoft.com/en-us/office/dev/add-ins/tutorials/excel-tutorial-create-custom-functions?tabs=excel-windows Please see header "Create a custom function that requests data from the web") My Excel Example below

/**
 * Bending (Minutes)
 * @customfunction bendingMinutes
 * @param {number} lotRun
 * @param {number} bendLengthInches
 * @param {number} numOfBends 
 * @param {number} easy1OrHard2
 * @returns {number} bendingMinutes
 */
async function bendingMinutes(lotRun, bendLengthInches, numOfBends, easy1OrHard2) {
  try {
    let procEff;
    let secsPerBend;
    await Excel.run(async (context) => {
      const rangeProc = context.workbook.worksheets.getItem("Table").getRange("A2:B7");
      const rangeSecs = context.workbook.worksheets.getItem("Table").getRange("D2:E7");
      procEff = context.workbook.functions.lookup(lotRun, rangeProc);
      secsPerBend = context.workbook.functions.lookup(bendLengthInches, rangeSecs);
      procEff.load('value');
      secsPerBend.load('value');
      await context.sync();
      const response = await fetch(procEff.value);
      const response1 = await fetch(secsPerBend.value);
      console.log(procEff.value);
      console.log(secsPerBend.value);
    });
    if (easy1OrHard2 == 1) {
      return (secsPerBend.value * numOfBends / procEff.value / 60);
    } else {
      return ((secsPerBend.value * numOfBends / procEff.value / 60) * 1.2);
    }
  } catch (errorHandlerFunction) {
  };
}

enter image description here

enter image description here

Ethan
  • 808
  • 3
  • 21

1 Answers1

3

Currently custom function doesn't support call Excel JS APIs. You can use SharedApp mode to call Excel JS APIs in custom function( https://learn.microsoft.com/en-us/office/dev/add-ins/tutorials/share-data-and-events-between-custom-functions-and-the-task-pane-tutorial ) May I know why you want to get the table information from API instead of arguments? If you are using API to get them, custom function will not be recalculated if the value in the table is updated.

ruoyingl
  • 121
  • 3
  • Hi ruoyingl! Thank you for your reply. I read that link and have better clarification. How can I implements calls to the Office APIs using the onCalculated event? – Ethan Dec 13 '19 at 14:32
  • Are you trying to call APIs after the custom function execution? You can register onCalculated event and all APIs directly in the event handler. This logic should be written in your add-in (instead of in custom function). Please let me know if this is what you are asking for. – ruoyingl Dec 16 '19 at 05:17
  • hi ruoyingl, yes that is exactly what i need to do. How would I write the logic in the add-in rather than the custom function? I have researched the onCalculated event and have had trouble trying to comprehend it – Ethan Dec 16 '19 at 13:19
  • I got your point. The add-in should be able to know when the custom function execution completed. There are ongoing works to enable this scenario but not available now. Thanks for the catching and the document should update. For now, you can try to call APIs from custom function in SharedApp mode. – ruoyingl Dec 17 '19 at 06:02