Consecutive HTTP API call returns VALUE! error in EXCEL using Office JS Custom function

Ambookan Varghese Jomy 6 Reputation points
2022-02-01T20:32:18.07+00:00

I'm using Office JS framework to get data from a REST API server endpoint using "Custom Function" in Excel(part of Office 365).
I'm using the YO generator mentioned in the tutorials(https://learn.microsoft.com/en-us/office/dev/add-ins/quickstarts/excel-custom-functions-quickstart?tabs=excel-windows)
I created a custom function which would call REST endpoint to get the data and the result will be placed in the cell. I used same function in multiple cells to get the data. After a threshold number of consecutive calls VALUE! error is returned in the cell. If I recalculate the VALUE! errored cell, it will return the data.

Please help me resolve this issue.

How to reproduce:

  1. Create Custom function project using the tutorial(https://learn.microsoft.com/en-us/office/dev/add-ins/quickstarts/excel-custom-functions-quickstart?tabs=excel-windows) and select language Typescript.
  2. Add following function to the \src\functions\functions.ts file. /**
    • Loads data from REST test service.
    • @customfunction TEST_REST_CALL
    • @Paramjeet Dahiya id ID
    • @returns response data.
      */
      export async function testRestCall(id: number) {
      if (isNaN(id) || id === null || id === undefined) {
      throw new CustomFunctions.Error(CustomFunctions.ErrorCode.invalidValue, Please provide a valid argument "ID");
      } var response = await fetch(https://jsonplaceholder.typicode.com/posts/${id});
      var data = await response.json();
      return data.title;
      }
  3. Use the function "TEST_REST_CALL(2)" in no less than 1000 cells.

You will get following VALUE! error for some of the cells.

170264-image.png

JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
862 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,439 questions
0 comments No comments
{count} vote