cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SamTimminsEvo
New Member

Excel Adding Columns: Graph API has timed out

Hi All,

 

I am attempting to create a power automate task that takes an emailed excel file, adds two columns with static contents for every row, and then saves the file to a local folder.

 

What I have created already worked for my test files - however it now throws an error when the spreadsheet is much larger (6000ish rows). The flow runs for approximately 20 minutes and then returns the error code 504, with message "Request to Graph API has timed out"

 

My Question is - is there a way to change these timeout settings as a workaround (I don't mind if it takes a long time to finish), or is there an alternative method within Power Automate / script suggestions that can accomplish this within the timeout limit?

 

Thanks!

 

"error": {
            "code": 504,
            "source": "australia-001.azure-apim.net",
            "clientRequestId": "--",
            "message": "BadGateway",
            "innerError": {
                "status": 504,
                "message": "Request to Graph API has timed out.\r\nclientRequestId: --",
                "error": {
                    "message": "Request to Graph API has timed out."
                },
                "source": "excelonline-ae.azconn-ae.p.azurewebsites.net"

 

Here is the script I am using:

 

function main(workbook: ExcelScript.Workbook) {

  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);

  // Adding Class Column
  let range = workbook.getActiveWorksheet().getUsedRange();

  let rows = range.getRowCount();
  let cols = range.getColumnCount();

  range.getCell(0, cols).setValue("class");

  for (let row = 1; row < rows; row++) {
    range.getCell(row, cols).setValue(8);
  }

let updateCols = cols + 1

  range.getCell(0, updateCols).setValue("update");

  for (let row = 1; row < rows; row++) {
    range.getCell(row, updateCols).setValue("Y");
  }

  workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic); 

}

 

Flow steps for background:

Flow_error.JPG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
raijo_john
Regular Visitor

Hi Sam, 

If I am not wrong, your script is trying to update the values cell by cell. From my limited knowledge and what I have experienced so far, this approach may not work for the large data set. 

 

Change the approach to update the excel in batches for range of values. It should work.

It worked for me and I could update 300K records with the batch approach. 

 

There is MS documented example... 

https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset?view=offic...

View solution in original post

1 REPLY 1
raijo_john
Regular Visitor

Hi Sam, 

If I am not wrong, your script is trying to update the values cell by cell. From my limited knowledge and what I have experienced so far, this approach may not work for the large data set. 

 

Change the approach to update the excel in batches for range of values. It should work.

It worked for me and I could update 300K records with the batch approach. 

 

There is MS documented example... 

https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset?view=offic...

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,992)