javascriptexceluser-defined-functionsoffice-jsoffice-js-helpers

Does "Excel.run" Not Work For User Defined Functions?


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


Solution

  • 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.