0

I am using PowerAutomate to add data to a report each day. The data comes in on email as a CSV attachment, this is parsed with an Azure function into a 2D array and stringified to JSON, this is then passed to an excel script that parses the JSON, truncates the row arrays to the correct width, adds formulas to the row arrays for calculated columns and uses the table.addRows() function to add the data in to the table.

This happens on three different tables from three different reports that come in (spaced 30 mins apart).

  • One is small, adding ~10 rows a day, with no added formula columns, this is always fine and successful in PowerAutomate.
  • The second is ~150 rows a day, with one added formula column. This always succeeds in adding the data, but sometimes the PowerAutomate connector fails with 504 BadGateway - Request to Graph API has timed out.
  • The third is ~150 rows a day, with a few added formula columns. This usually fails, either successfully adding the date but returning the error above, or by failing to add the data and returning We were unable to run the script. Please try again. Office JS error: Line 44: Table addRows: The request failed with status code of 504, error code UnknownError

Here is the function itself (specifically the third example):

function main(workbook: ExcelScript.Workbook, inputData: string) {

  // Get formula columns as array of strings
  const additionalData = workbook.getWorksheet(`contact_time`).getRange("W2:AG2").getFormulas()[0]

  // Parse inputData to get actual array
  const dataToAdd: Array<Array<string>> = JSON.parse(inputData)

  // Remove first row's data as is just headers
  dataToAdd.shift()

  if (dataToAdd.length == 0) {
    return
  }

  // Truncate each input array row and add formula array columns on to the end
  for (const row of dataToAdd) {
    row.length = 22
    for (const column of additionalData) {
      row.push(column)
    }
  }

  // Add data to table
  workbook.getTable("t").addRows(null, dataToAdd)
}

In order to try and optimise this I have

  • in the script, accessed the workbook the minimum number of times possible for fewer requests
  • removed the call to .getFormulas() and hardcoded an array instead
  • changed the PowerAutomate timeout to P1D and retries to none
  • turned off recalculation for the workbook/set calculation mode to manual (both at workbook level and at the start of the script itself)

This works 100% of the time if I paste in the JSON to the function and run it in the browser. I can't work out if this is due to the actual function timing out, PowerAutomate failing to wait long enough for the result, some timeout in the Graph API, or something else.

The workbook is under 5mb in size, the table that fails most often has about 13.000 rows.

Edit:

The workbook I am adding to has worksheets for these three tables, plus a few more with analysis tables as well. I also tested removing different sheets, references etc. The slowdown seems to be the formula columns that already exist in the table, as only by completely removing these columns was the function successful in every instance. Now instead of timing out in 90 seconds, it completes in 8 seconds without any issues. Why would this affect the speed with recalculation turned off?

sambr
  • 3
  • 1
  • 3
  • This may be a stupid question, but have you checked inputData is what you expect and when it's parsed dataToAdd is what you expect? – norie Apr 15 '21 at 10:37
  • @norie yep done this with logging and other checks, when there's actually an error with the data the errors are pretty immediate, and good at saying what was wrong. Too many or too few items in the row array fails at the `.addRows()` within a second or two, so I know that it's the actual `.addRows()`ing that is what's taking the time. – sambr Apr 15 '21 at 11:00
  • FWIW, in VBA setting calculation to manual is standard practice when trying to improve efficiency, though I've never heard of automatic calculation actually breaking code. – norie Apr 15 '21 at 13:39
  • At the moment with calculation on or off the result is the same. However if I paste the formulas as text for the previous rows, it now works fine. There seems to be some issue with the API or backend if having formulas in a table affects speed even with calculation set to manual. – sambr Apr 16 '21 at 08:16

2 Answers2

0

This is probably happening due to the table running auto-recalcs when a new row is added to the table. Graph can timeout when doing this for a table with a decent size. We can decouple the recalc operations from the table operations by setting the calculation option to Manual, then back to Automatic after the table operations are finished to initiate the recalc.

// At the beginning of the script right after main() function,
...
// Set calculation mode to manual
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);
// do your processing...
// Set calculation mode back to automatic
workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);
WeffJen
  • 70
  • 4
  • Unfortunately this does not work, as stated in the question recalculation is turned off for the whole workbook. I have edited to make this clearer. With this at the beginning of the script the behaviour and error is the same. – sambr Apr 16 '21 at 08:12
0

From further testing it seems that even with calculation set to manual, any table with formulas in it causes slowness when using table.addRows(), and either the Graph API or the PowerAutomate connector is unable to wait long enough for a response.

The only solution I have found is to minimise or eliminate formulas that

  • are in the table being added to
  • reference the table being added to

When I pasted the formulas in the problem table as values and re-tried to add rows, it worked first time without any alteration to anything else.

In my case I require the calculated columns in my table directly, so I am periodically replacing the formulas that are added by the script with values. This could be done manually, or with a script by using range.setFormulas(range.getValues()) on a range that you have specified (I recommend to limit this range to what you need as even in the browser it only completed about 1000 rows before timing out, so you should not use it on the whole table).

sambr
  • 3
  • 1
  • 3