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 ruclips.net/video/4ZiMjjV1c3Q/видео.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 👍
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 RUclips. 🎉
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 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
Thanks, Damien. I will try this. Will be very handy for a project I am working on. Best wishes.
Love the vids keep it up❤
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/
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 ruclips.net/video/4ZiMjjV1c3Q/видео.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 👍
Thanks
❤ Thank you Adi, appreciated 👍
@@DamoBird365not a problem, thank you for another great video!
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
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 RUclips. 🎉
Tak!
Thank you very very much Anders 😍
Thanks Damien. Do you have a github link to download this?
My aim will be to make this available via a blog post.
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.
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 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();
}
}