Power Automate - Run Script

[MasaYR]

Hi, 

I am trying to create a flow with "Run Script" to create table in excel. 

I watched DamoBird365's YouTube video (which was so helpful) to start. 

https://www.youtube.com/watch?v=4g8Lh0gzEnc

He provided the office script there too. So, I used that as a base and modified to my needs. 

The flow run perfectly with test with 1000 items (I am getting items from D365FO.) but I actually have 15000 items or so, and when I delete my top count (1000) to run with actual number of items (rows), flow stuck at "Run script" stage (with many retries). I basically have to cancel it. 

The script I wrote in excel is basically copy of what DamoBird365 provided which is below.

function main(workbook: ExcelScript.Workbook,
arrayfromflow: arrayofdata[],
sheetname: string
) {
let selectedSheet = workbook.getActiveWorksheet();

// Set range A1:F1 on selectedSheet
selectedSheet.getRange("A1:F1").setValues([["ID", "FirstName", "LastName", "Address", "PostCode", "Email"]]);

// Add a new table at range A1:F1 on selectedSheet
// Optional if you want to use ADD Row Into Table
let newTable = workbook.addTable(selectedSheet.getRange("A1:F1"), true);
newTable.setName('MyTableName')

selectedSheet.setName(sheetname);

//Populate rows below Header Row with Array Variable
const starterrow = 2; //starting row for "table" data

for (let i = 0; i < arrayfromflow.length; i++) {
const currentObject = arrayfromflow[i];

const formattedrow = [[currentObject.ID, currentObject.FirstName, currentObject.LastName, currentObject.Address, currentObject.PostCode, currentObject.Email]];

const rowRange = `A${starterrow + i}:F${starterrow + i}`;
selectedSheet.getRange(rowRange).setValues(formattedrow);
}
}

//Defining Interfaces for MultiVar and Array

interface arrayofdata {
ID: string,
FirstName: string,
LastName: string,
Address: string,
PostCode: string,
Email: string
}

I am not sure why the flow runs perfectly with 1000 data (It took 30sec to finish) but it has problem with 15000 data...

I am hoping someone knows how to solve this problem...

Thank you!

[v-yueyun-msft]

Hi , @MasaYR 

As searched in your video , Someone is having the same problem as you.

So it seems this office script may encounter the error code  when process the large data.

So can you try to use other method to add the rows to you excel table , and i find the similar video about this:
Create Excel File and Add Rows Fast | Graph API | Office Scripts | Power BI | Power Automate (youtub...

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[MasaYR]

Thank you, @v-yueyun-msft!

The other script you pointed me to worked! It is creating table beautifully but I am encountering another issue... 
Not sure anyone here can help but now, "Lists items present in table" from Fin&Op is only grabbing partial data.. 😕

I do have about 15000 data but it is only grabbing about 10000....

I did set "Pagination" to 100000. So, that should not be a cause...

Not sure if it is a D365 side of setting. 

Does anyone know about this issue? 

Thank you!