Office Script help with writing http api call

[AdamHelbing]

Hi!!  I'm struggling here and need some help from the community.  

First, some background.

We currently have an Excel macro that makes an api call.  The output of the macro works great and has for many years now.  However, the performance for the end users is not great.  The call looks up quite a bit of data and returns to the excel sheet.  If they have to get this data for several different people across several different parameters, it can take a while.  

We recently started to look at Power Automate to help with the automation so all the users need to do is fill out a quick MS form to provide the parameters for the data they want to return.  The flow we have works... for the most part.  When it works, the body of the HTTP action is a csv file with the first row being the column headers, and we have the flow create a file where the body of the file is the body of the returned HTTP action.

However, roughly half of the time the data being returned through the HTTP action is too large of a message for Power Automate and fails to provide the requested data set.  

Error message from the HTTP action

Now for the fun.  

I got the idea to try Office Scripts.  That way the users can still fill out a form to provide the context of the data they want, and then Power Automate could execute the office script to get to the data.  Still not 100% sure if that will work with these large data returns, but I thought I would give a try.  

That has led me here....

I am struggling with how to write this script, and could really use some guidance / education.  

Here is what I have so far, and I cant really tell if its working so far and i just need to build more of it out, or if it its not returning anything.  

async function main(workbook: ExcelScript.Workbook) {

  // Get the current sheet.

  let currentSheet = workbook.getActiveWorksheet();

   // Create selection of parameters for the fetch URL.

  const refdoc = "'2516'"; 

  // Construct the URL for the fetch call.

  const strQuery = `https://cantshare.thissites.net/api/flashQuery?sap=i.RefDoc=${refdoc}`;

  console.log(strQuery);

  // Resolve the Promises returned by the fetch operation.

  const response = await fetch(strQuery, {

    method: "GET",

      mode: "no-cors",

        headers: {

      "X-Functions-Key": "omittedForPost"

  }});

}

For additional reference on this api and what it returns when it can return the data.  No sensitive data here.  And this is what I am trying to get done with this office script.  call the api, return the data, and write it the sheet.  

Any help that any one of you can provide will truly be appreciated.  

Thanks

Adam

[lbendlin]

I may or may not have bad news for you 

External API call support in Office Scripts - Office Scripts | Microsoft Learn

[takolota]

What do you mean by the data is too large for Power Automate to handle?

Does it exceed 100MB?

If not, there should be more ways to design the flow so it can process the data.

[AdamHelbing]

Sorry about that. I have edited the original post to include the HTTP action error message for reference.  The issue is the amount of data being returned through the API call itself is too great.  The error in the post is taken from a two action flow.  Manual trigger, and then the HTTP action.  

[AdamHelbing]

Yeah, I saw this too, and that will probably be an issue with creating the solution I have in mind.  But I would still like to learn how to write the Office Script to explore performance differences between the macro and the script.  

[takolota]

@AdamHelbing 

Thanks for the update, that does seem to be the 100MB limit.

To get around this you may want to try finding a way to split the data returned into two calls.

From there you could try appending them together in a compose which should have a 200MB limit, but later actions you need may still have the 100MB limit, so you may actually either need to split it up into 2 CSV files or have the flow create an Excel file & add the CSV rows to the Excel in batches. That way you could then provide a sharing link to the Excel file with all the combined data.