Power Platform Community Forum Thread Details

I have the follwig problem. I start a Flow to copy something in excel, with a excel script.

Sometime it runs, sometimes I got a gateway error.

I copy the office script here:

function main(workbook: ExcelScript.Workbook, wsName: string, startCell: string, strArr: string) {
  try {
    let newDataArr: string[][] = JSON.parse(strArr);
    let ws = workbook.getWorksheet(wsName);
    let dataArr = ws.getRange(startCell).getSurroundingRegion().getValues() as string[][];
    let maxWidth = Math.max(
      dataArr[0] ? dataArr[0].length : 0,
      newDataArr[0] ? newDataArr[0].length : 0
    );
    // Fülle die Arrays auf gleiche Länge auf
    newDataArr.forEach(row => {
      while (row.length < maxWidth) {
        row.push("");
      }
    });
    dataArr.forEach(row => {
      while (row.length < maxWidth) {
        row.push("");
      }
    });
    // Kombiniere die Daten
    dataArr = dataArr.concat(newDataArr);
    let totalRows = dataArr.length;
    let batchSize = 100; // Weiter reduzierte Batch-Größe
    // Setze die Werte in Batches
    for (let startRow = 0; startRow < totalRows; startRow += batchSize) {
      let endRow = Math.min(startRow + batchSize, totalRows);
      let batchData = dataArr.slice(startRow, endRow);
      let batchRange = ws.getRangeByIndexes(startRow, 0, batchData.length, maxWidth);
      batchRange.setValues(batchData);
      console.log(`Batch von Zeile ${startRow} bis Zeile ${endRow} erfolgreich gesetzt.`);
    }
    console.log("Werte erfolgreich gesetzt");
  } catch (error) {
    console.log("Fehler aufgetreten: " + error.message);
  }
}
 
the last error:


Action 'Run_script_from_SharePoint_library_2' failed: BadGateway
 
504
 
{

"Cache-Control": "no-store, no-cache",

"Pragma": "no-cache",

"Strict-Transport-Security": "max-age=31536000; includeSubDomains",

"x-ms-datasourceerror": "True",

"x-ms-request-id": "3ed3d8ad-832b-431a-9887-399a0700a662",

"X-Content-Type-Options": "nosniff",

"X-Frame-Options": "DENY",

"x-ms-environment-id": "default-2bd676ce-2ad6-4cda-b8ea-f427e49c4c69",

"x-ms-tenant-id": "2bd676ce-2ad6-4cda-b8ea-f427e49c4c69",

"x-ms-dlp-re": "-|-",

"x-ms-dlp-gu": "-|-",

"Timing-Allow-Origin": "*",

"x-ms-apihub-cached-response": "false",

"x-ms-apihub-obo": "false",

"Date": "Wed, 20 Nov 2024 08:48:41 GMT",

"Content-Length": "553",

"Content-Type": "application/json",

"Expires": "-1"

}
 
 {
"error": {
"code": 504,
"source": "flow-apim-europe-002-westeurope-01.azure-apim.net",
"clientRequestId": "3ed3d8ad-832b-431a-9887-399a0700a662",
"message": "BadGateway",
"innerError": {
"status": 504,
"message": "Your Office Script has timed out. Please try again.\r\nclientRequestId: 3ed3d8ad-832b-431a-9887-399a0700a662",
"error": {
"message": "Your Office Script has timed out. Please try again."
},
"source": "excelonline-we.azconn-we-002.p.azurewebsites.net"
}
}
}