Power Platform Community Forum Thread Details

"message": "The request failed with status code of 503."

"code": "ConnectionFailure"

"type": "Worksheet"

"method": "getRange"

"line":102

Just writing to a worksheet, updating the whole thing. The worksheet is over 140,000 records long. So, I copy it one go as such:

let myWorkbook = workbook.getActiveWorksheet()
let lastRow: number = myWorkbook.getUsedRange().getLastRow().getRowIndex() + 1
let values = myWorkbook.getRange(`A2:V${lastRow}`).getTexts()

Now, values is a 2D array. I process it how I need, then have to write it back to the worksheet.

myWorkbook.getRange(`A2:V${values.length + 1}`).setValues(values)

Not so fast, says Automate. Yeah, ok. 140,000 is too much for internal memory to write. I get it.

So, I chunk it:

const CHUNK_SIZE: number = 500
const values_length: number = values.length
let chunk_arrays: string[][][] = []
 
for (let q = 0; q < values_length; q += CHUNK_SIZE) {
 chunk_arrays.push(values.slice(q, q + CHUNK_SIZE))
}

Now, the 2d array of 140,000 is broken up into chunks of 500.

let upper_range: number
let lower_range: number = 1
for (let e in chunk_arrays) {
 upper_range = (lower_range + 1)
 lower_range = (upper_range + chunk_arrays[e].length)
 /*102*/ myWorksheet.getRange(`A${upper_range}:V${lower_range}`).setValues(chunk_arrays[e])
}

It works in desktop Excel, not in Run-Script-(Business)-Power-Automate-Excel-TypeScript.

503, anyone? Also, sometimes it tries to run for 25+ minutes in flow before crashing saying "BadGateway".

Categories:

Using connectors