Power Platform Community Forum Thread Details

I'm working with office script in Excel online and power automate.

1. My requirement is find the duplicate entries and some other data from Excel through Office script and needs to highlight it.

2. Excel File having greater than 2000 rows. Its not a table just rows and column in Excel sheet.

3. I completed script and also I run the script through excel automate option. its working as expected.
4. But I tried to run the script using power automate. Run Script connector its shows always bad gateway error 504.just like as the below screenshot...
5. For 1000 rows its works sometimes. having problems only after the 1001th record. I cant figure it out what I'm missing. My file is less than 5 mb also. But I still getting this error.
6. I also pasted my script below. help me to figure out this..

let errInSR=false;

let errSRCount =0;

let mainsheetrange = weeklyworksheet.getRange("C5:C2000");

let mainsheetrowscount = mainsheetrange.getRowCount();

for (var j = 0; j < mainsheetrowscount ;j++)

{

for (var k = 0; k < 1; k++)

{

mainsheetrange.getCell(j, 2).getFormat().getFill().setColor("white");

mainsheetrange.getCell(j, k).getFormat().getFill().setColor("white");

mainsheetrange.getCell(j, 1).getFormat().getFill().setColor("white");

/* start SR Check */

if (mainsheetrange.getCell(j, 0).getValue())

{

if (arrSRValues['Values'].indexOf(mainsheetrange.getCell(j, 0).getValue()) !== -1) {

}

else

{

errInSR = true;

mainsheetrange.getCell(j, k).getFormat().getFill().setColor("Yellow");

errSRCount = errSRCount + 1

}

/* end SR Check*/

}

if(errInSR)

console.log("Incorrect SR Records " + errSRCount+"");

Categories:

Using flows

Hi @Kalimuthu ,

I know that it has been a while since you posted this issue and probably already solved it. For the Run Script action, Power Automate has a limitation that requires your action to run in a maximum of 120 seconds. The alternative recommended by Microsoft is to split your tasks in more than one script, which individually run in less than 120 seconds.

I had a problem similar to yours in the past (need to run a script through thousands of rows), as the solution that I found was to limit the flow to a 'N' number of rows, and store the index of the last accessed row by the script (in my case, I stored it in a specific cell from my Spreadsheet). Then in the next script run, you restart the process from this stored index for more 'N' rows.