I am facing an issue with the http patch approach. For 5k records it works fine. But the moment is pass 70k records, it throws 302 error with message as the response is not in a json format and with internal error as cannot read server response. Had anybody faced similar issue. I am trying to figure out the reason it works for 5k records and not for huge list of records makes me wonder if it's the api limitation.
Hi, Damien, I have got a question Please. what would the highest numbers of rows can be populated on excel sheet using office script and Graph API. I have a use case where rows can go beyond 20K.
There are some published limits for Office Scripts, more about request limits - 5MB learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits?tabs=business#excel. Excel and Graph I am not sure learn.microsoft.com/en-us/graph/throttling-limits#excel-service-limits but the aim would be to batch requests. See learn.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset
Highly appreciate your prompt response ❤thank you once again sharing great knowledge about power automate. I'm great admirer of your video's on TH-cam. 🎉
You are the king og flows! Impressive. Do you know if it is poaaible to build an Office script that gets only the filtered data from an Excel file and sned that to Flow instead of the whole file file content? As I know it is not possible to do that kind og filtering in Power Automate. It just gets it all.
Yes, it would be possible as you define in the script what you want to return. Take a look at th-cam.com/video/4ZiMjjV1c3Q/w-d-xo.html where I return the sheet names in one script. You define the output and it could be a filter on data. What's your use case? Maybe drop me a dm and I can take a look for a future video.
Goodnight! First of all, I would like to thank you for your videos, they help me a lot! (I speak directly from Brazil) Please, can you tell me if this method could work with an extraction of 500k+ rows? Because the databases that we need to analyze in our company are gigantic due to many SKUs and customers
Wow, 500,000 is a huge amount. Where are you extracting the data from? This will likely be pushing limits, you might want to consider batching. Both methods have limits and timeouts of 120 secs I believe.
@@DamoBird365 oops demo! all good? I extract this data from a power bi dashboard published in the sales workspace, and we need to use the complete database for analysis in our logistics planning dashboards. Really, I tried many ways to do this and unfortunately I couldn't :( With the "execute a query on a dataset" connector, I built a DAX that returns JSON, then I convert it to CSV. But I also don't know if that would be the appropriate way...
Amazing video, thanks for sharing. You mention at 20:17 that the graph api created excel is not formatted as a table, albeit the graph api can do that. Would it be possible to explain in the comments how to do that or provide a link or another video? Thanks again
This should get you up and running. I may do a video in due course though if you think that is valuable. learn.microsoft.com/en-us/graph/api/worksheet-post-tables?view=graph-rest-1.0 👍
I'm encountering an issue where my script calls a URL using fetch when integrated into Power Automate. It throws the error 'fetch is not defined'. Do you have a solution?
Using either the "Send an HTTP request V2" (Office 365 Groups) or "Send an HTTP request" (Office 365 Groups) --- Which has the same icon you are using is unable to make the call to graph api to get the Site Id. Do we have to use the premium adapter now?
Hello Sir, you referred me here from another video. I could use this but only problem is my data is not formatted the way you have it from Power Bi. I a have the CSV and I can only make it an array by the row. It looks pretty much like this: [ ""ID."\t"Div"\t"First By"\t"First Date"\t"Ct" ", ""123"\t"1"\t"DT"\t"02/05/2024"\t"1" ", ""124"\t"1"\t"DA"\t"02/05/2024"\t"1" ", ""125"\t"1"\t"DT"\t"02/05/2024"\t"2" ", "" ] Is there any process in power automate to format it like you have? Otherwise, I have to use for loop in the script maybe
@smarttaus1f This post might also help. You want to convert the CSV to JSON array and then the JSON array to the nested [[].[].[]] array using the technique in this video. pnp.github.io/blog/post/excel-file-tricks-with-powerautomate/
I’m not sure my video even has an apply to each? It sounds like you are tryi by to add row by row, so f which there are 27567. You would be better to use office scripts of graph.
First off fantastic video, i think i see the issue. I have just tried this and after the compose when you are doing the select action i cant add the parse json body into the start so i do the text part and paste the json body in thr top part and as soon as i do [[my first column name from the parsejson body ]] it converts to apply to each. I have deleted this step and tried again but still cant see what im doing wrong?
I get error at runtime at line workbook.addTable(sheet.getUsedRange(),includesheader).setPredefinedTableStyle("TableStyleMedium6"); The error is "Workbook addTable: The argument is invalid or missing or has an incorrect format." I have taken code exactly like in your video. Can you help? function main( workbook: ExcelScript.Workbook, includesheader: boolean= false, formatastable: boolean= false, sheetname: string = "Sheet1", worksheetdata: string[][]) { let sheet = workbook.getWorksheet(sheetname); if(worksheetdata.length > 0 && worksheetdata[0].length > 0) { let rowCount = worksheetdata.length; let colCount = worksheetdata[0].length; let range = sheet.getRangeByIndexes(0,0,rowCount,colCount); range.setValues(worksheetdata); } if(formatastable) { workbook.addTable(sheet.getUsedRange(),includesheader).setPredefinedTableStyle("TableStyleMedium6"); sheet.getUsedRange().getFormat().autofitColumns(); } }
Blog Post 👉 www.damobird365.com/export-power-bi-to-excel-with-power-automate/
Download here 👉 damobird365.gumroad.com/l/BulkLoadDataExcelOfficeScript
I am facing an issue with the http patch approach. For 5k records it works fine. But the moment is pass 70k records, it throws 302 error with message as the response is not in a json format and with internal error as cannot read server response. Had anybody faced similar issue. I am trying to figure out the reason it works for 5k records and not for huge list of records makes me wonder if it's the api limitation.
Thanks, Damien. I will try this. Will be very handy for a project I am working on. Best wishes.
Thanks!
Thank you thank you 🙏
Hi Damien, can you share the blogpost version of this please? Sorry, I can't seem to follow the script code properly from the video.
www.damobird365.com/export-power-bi-to-excel-with-power-automate/
Love the vids keep it up❤
Hi, Damien, I have got a question Please. what would the highest numbers of rows can be populated on excel sheet using office script and Graph API. I have a use case where rows can go beyond 20K.
There are some published limits for Office Scripts, more about request limits - 5MB learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits?tabs=business#excel. Excel and Graph I am not sure learn.microsoft.com/en-us/graph/throttling-limits#excel-service-limits but the aim would be to batch requests. See learn.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset
Highly appreciate your prompt response ❤thank you once again sharing great knowledge about power automate. I'm great admirer of your video's on TH-cam. 🎉
You are the king og flows! Impressive. Do you know if it is poaaible to build an Office script that gets only the filtered data from an Excel file and sned that to Flow instead of the whole file file content? As I know it is not possible to do that kind og filtering in Power Automate. It just gets it all.
Yes, it would be possible as you define in the script what you want to return. Take a look at th-cam.com/video/4ZiMjjV1c3Q/w-d-xo.html where I return the sheet names in one script. You define the output and it could be a filter on data. What's your use case? Maybe drop me a dm and I can take a look for a future video.
@@DamoBird365 I have filed out your Forms form, hope it is clear, otherwise I can send it again :) THX again for your great job
Goodnight! First of all, I would like to thank you for your videos, they help me a lot! (I speak directly from Brazil)
Please, can you tell me if this method could work with an extraction of 500k+ rows? Because the databases that we need to analyze in our company are gigantic due to many SKUs and customers
Wow, 500,000 is a huge amount. Where are you extracting the data from? This will likely be pushing limits, you might want to consider batching. Both methods have limits and timeouts of 120 secs I believe.
@@DamoBird365
oops demo! all good?
I extract this data from a power bi dashboard published in the sales workspace, and we need to use the complete database for analysis in our logistics planning dashboards.
Really, I tried many ways to do this and unfortunately I couldn't :(
With the "execute a query on a dataset" connector, I built a DAX that returns JSON, then I convert it to CSV. But I also don't know if that would be the appropriate way...
Amazing video, thanks for sharing.
You mention at 20:17 that the graph api created excel is not formatted as a table, albeit the graph api can do that. Would it be possible to explain in the comments how to do that or provide a link or another video?
Thanks again
This should get you up and running. I may do a video in due course though if you think that is valuable. learn.microsoft.com/en-us/graph/api/worksheet-post-tables?view=graph-rest-1.0 👍
Love this!
Great video. Have you ever tried doing bulk updates to Dataverse using the Dataverse API? I would love to see a video on this.❤
I was thinking the same. I haven’t but I was looking earlier in the week. Have you tried this?
@@DamoBird365 No, I haven’t. I was hoping you as the guru would show us how.😀
@@kimsalas8197 I’ll try and work something out then 😂 every day is a learning day
Thanks Damien. Do you have a github link to download this?
My aim will be to make this available via a blog post.
I'm encountering an issue where my script calls a URL using fetch when integrated into Power Automate. It throws the error 'fetch is not defined'. Do you have a solution?
It’s not supported and in the docs learn.microsoft.com/en-us/office/dev/scripts/develop/external-calls#external-calls-from-power-automate
Using either the "Send an HTTP request V2" (Office 365 Groups) or "Send an HTTP request" (Office 365 Groups) --- Which has the same icon you are using is unable to make the call to graph api to get the Site Id. Do we have to use the premium adapter now?
I believe I used the entra id http action. There are 4 other graph http connectors but they are limited by what they can do.
Thanks
❤ Thank you Adi, appreciated 👍
@@DamoBird365not a problem, thank you for another great video!
Hello Sir, you referred me here from another video. I could use this but only problem is my data is not formatted the way you have it from Power Bi. I a have the CSV and I can only make it an array by the row. It looks pretty much like this:
[
""ID."\t"Div"\t"First By"\t"First Date"\t"Ct"
",
""123"\t"1"\t"DT"\t"02/05/2024"\t"1"
",
""124"\t"1"\t"DA"\t"02/05/2024"\t"1"
",
""125"\t"1"\t"DT"\t"02/05/2024"\t"2"
",
""
]
Is there any process in power automate to format it like you have? Otherwise, I have to use for loop in the script maybe
@smarttaus1f This post might also help. You want to convert the CSV to JSON array and then the JSON array to the nested [[].[].[]] array using the technique in this video. pnp.github.io/blog/post/excel-file-tricks-with-powerautomate/
Tak!
Thank you very very much Anders 😍
im hitting a wall 'The number of foreach items limit exceeded for action 'Apply_to_each_PBI': maximum '5000' and actual '27567'.'.
I’m not sure my video even has an apply to each? It sounds like you are tryi by to add row by row, so f which there are 27567. You would be better to use office scripts of graph.
First off fantastic video, i think i see the issue. I have just tried this and after the compose when you are doing the select action i cant add the parse json body into the start so i do the text part and paste the json body in thr top part and as soon as i do [[my first column name from the parsejson body ]] it converts to apply to each. I have deleted this step and tried again but still cant see what im doing wrong?
I get error at runtime at line workbook.addTable(sheet.getUsedRange(),includesheader).setPredefinedTableStyle("TableStyleMedium6");
The error is "Workbook addTable: The argument is invalid or missing or has an incorrect format."
I have taken code exactly like in your video. Can you help?
function main(
workbook: ExcelScript.Workbook,
includesheader: boolean= false,
formatastable: boolean= false,
sheetname: string = "Sheet1",
worksheetdata: string[][])
{
let sheet = workbook.getWorksheet(sheetname);
if(worksheetdata.length > 0 && worksheetdata[0].length > 0)
{
let rowCount = worksheetdata.length;
let colCount = worksheetdata[0].length;
let range = sheet.getRangeByIndexes(0,0,rowCount,colCount);
range.setValues(worksheetdata);
}
if(formatastable)
{
workbook.addTable(sheet.getUsedRange(),includesheader).setPredefinedTableStyle("TableStyleMedium6");
sheet.getUsedRange().getFormat().autofitColumns();
}
}