Quickly Add Multiple Rows to Excel Table In Power Automate

Quickly Add Multiple Rows to Excel Table In Power Automate

Multiple rows can be added to an Excel table using a single Power Automate action. And the best part is its blazing fast. Using the HTTP for Azure AD connector I added 10 rows to a table in 1 second vs. doing it individually in 14 seconds with the Excel connector. The best part is this technique scales up to 100,000 rows added at once. You’ve got to check out this article on how to add rows to an Excel table super fast!!!


Table of Contents
• Introduction: The Car Sales ReportSetup The SharePoint ListGet All Items From The SharePoint ListCreate An Array Of Multiple Excel RowsMake A New Excel Workbook With A TableGet The Excel Workbook Drive ID And Item IDConfigure A Connection To The Microsoft GraphAdd Multiple Rows To An Excel Table Using HTTP With Azure ADRun The Flow To Perform A Batch Update To The Excel File




Introduction: The Car Sales Report

Each day a car manufacturing company uses an automation to create a Car Sales Report. The automation adds multiple rows to an Excel table to be displayed in the report.



The automation uses the HTTP With Azure AD – Invoke An HTTP Request action combined with the Microsoft Graph API to POST all rows to an Excel table in a single batch.



It only takes 1s to execute as opposed to the 14s it would that to add each row one by one using the built in Excel For Business (Online) – Add A Row Into A Table action.




Setup The SharePoint List

Create a new SharePoint List named Car Sales Inventory with the following columns and data types:

  • Year – number
  • Make – single line text
  • Model – single line text
  • Cost – number



Include these rows in the SharePoint List. Our flow will add all of the rows to an Excel table.

YearMakeModelCost
2000HondaAccord15,242.74
1998OldsmobileAurora14,691.91
1984MaseratiQuattroporte7,568.83
2011ChevroletMalibu22,711.39
2006Mercedes-BenzG-Class13,949.48
1985FordLaser34,159.31
2009KiaSpectra29,023.15
1996DodgeRam Van 350024,883
1985BuickCentury33,778.10
2009Mercedes-BenzS-Class27,039.57



Once the SharePoint list is setup it will look like this.




Get All Items From The SharePoint List

Open Power Automate and create a new flow with an instant trigger. Add the SharePoint – Get Items action and choose the Car Sales Inventory list we just created.



The SharePoint – Get Items action only returns up to 100 items by default. Our example does not return more than 100 items but the maximum rows can be increased up to 100,000 if needed. Go to the action’s settings, enable Pagination and input the new maximum number of rows.




Create An Array Of Multiple Excel Rows

We will use the HTTP With Azure AD connector and the Microsoft Graph API to batch insert rows into an Excel table. The Microsoft Graph API call requires us to load an array of rows in a specific format. Insert the Data Operations – Select action into the flow. Switch the Map field into text mode and create an array in the format shown below.

Note: in this examples the Map field uses square brackets.




Make A New Excel Workbook With A Table

The rows in our array will be added to the end of an Excel table. Open SharePoint and create a new spreadsheet called Car Sales Report.xlsx inside of a document library.



Insert an Excel table into Sheet1 of the spreadsheet with the columns: Year, Make, Model and Cost. Rename the table tblCarSales. Then save & exit the file.




Get The Excel Workbook Drive ID And Item ID

The Microsoft Graph API call to add rows to a table requires the Excel workbook’s Drive ID and Item ID. To obtain these IDs go back to Power Automate and add the Excel for Business (Online) – List Rows Present In A Table action to the flow. Select the Excel table named tblCarSales from the Car Sales Report.xlsx spreadsheet.

Set the Top Count field to 1 to make the action run faster. We aren’t actually trying to retrieve the table rows in this action. We only want to get the information about where the table is located.



Once the flow is run the Drive ID (“drive”) and the Item ID (“file”) can be found in the action’s inputs.



Then create a set of two Data Operations – Compose actions named Compose: Drive ID and Compose: Item ID.



Get the Drive ID using this Power Automate expression.

actions('List_rows_present_in_a_table:_Car_Sales_Report')?['inputs']?['parameters']?['drive']



And get the Item ID using this Power Automate expression.

actions('List_rows_present_in_a_table:_Car_Sales_Report')?['inputs']?['parameters']?['file']




Configure A Connection To The Microsoft Graph

The Microsoft Graph API provides access to Microsoft 365 data and services, such as allowing developers to programmatically interact with Excel files stored in SharePoint. We can access it using the HTTP With Azure AD connector. Add the HTTP With Azure AD – Invoke An HTTP Request action to the flow.



Use this URL for both the Base Resource URL and Azure AD Resource URI fields when configuring the connection.

https://graph.microsoft.com




Add Multiple Rows To An Excel Table Using HTTP With Azure AD

Now that we are connected to the MS Graph we can use the Create Table Row endpoint for Excel workbooks endpoint to add multiple rows to a table at once. Choose the POST HTTP method to indicate a resource is being updated.



Then add this code to the Url of the request.

v1.0/drives/@{outputs('Compose:_Drive_ID')}/items/@{outputs('Compose:_Item_ID')}/workbook/worksheets/Sheet1/tables/tblCarSales/rows/



And use this code in the Body of the request.

{
    "values":  @{body('Select:_Create_Car_Sales_Rows')}
}




Run The Flow To Perform A Batch Update To The Excel File

We have now completed building the flow. Save the flow and give it a test run. The example below used a single action to write 10 rows to an Excel table in 2s.



The Excel file now shows all the rows from the SharePoint list.





Questions?

If you have any questions or feedback about Quickly Add Multiple Rows to Excel Table In Power Automate please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Matthew Devaney

Subscribe
Notify of
guest

35 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Joe Kramer
Joe Kramer
6 months ago

Hey Matthew! Thanks for another great post! Just an FYI, the URL in the copy/paste block is missing a “:” and may throw some folks of the more copy/paste variety 🙂

Haroldbk
Haroldbk
6 months ago

This is a cool solution!
Another option to consider and/or explore is to use Office Script to add the rows.
This doesn’t require any additional licensing.

Kumar Anubhav
Kumar Anubhav
6 months ago

Thank you Mathew for sharing this. This is very helpful. Just one query here, do we need premium or any specific license to use this method?

AFU
AFU
6 months ago

Very nice solution!

Is there also a way to batch delete all the items with “Invoke an HTTP request” in the table that we just created?

I want my template to be rid of content, but right now I’m using the “Delete a Row” action so I’m basically at the same point again with a very slow flow.

Unfortunately I don’t get this ID of the dynamic file that I create when I copy the excel sheet before adding the content via flow. So I see no other way than to delete the content after making a copy of the original file.

Matt
Matt
6 months ago

If you use the Office365 Groups connector – there’s an HTTP request action that can do this same thing without needing a premium license. The V2 HTTP request can only do this by going through the /groups/ endpoint, but if you have a way to get a copy of the original HTTP request, you can do this through /sites/ or /drive/ or /me/ – for free.

Jose conde
Jose conde
5 months ago

Matthew Thanks for that post, it´s interesting. how can i get “Item ID” of an action “Create file in Sharepoint”?

Jose conde
Jose conde
5 months ago

sorry if I didn’t make myself understood, you get the “compose: Item ID” after the “List rows present” action which I would like to know instead of the excel action to get the rows, how could I get the “compose:Item ID” of an action that creates an excel file dynamically.

Max
Max
4 months ago
Reply to  Jose conde

I am also interested in a solution for this, as i’ve gotten stuck on this part of the solution. I can’t retrieve the Item ID (“file”) from a newly created file, only the path. Is there a solution for this?

Ramesh Mukka
Ramesh Mukka
5 months ago

Hey Matt,
What configuration is required prior to using the action “Invoke an HTTP Request”. I get below error. Can you please throw some references?

The resource principal named https//graph.microsoft.com was not found in the tenant named xxxxx. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant.

Ramesh Mukka
Ramesh Mukka
5 months ago

My bad. Yes, thank you

Jason Templeton
Jason Templeton
5 months ago

Hi Matthew, looking to implement this but with rows coming from a dataverse table instead of sharepoint. I can’t seem to get the array to work with the dataverse data any guidance?

Jason Templeton
Jason Templeton
5 months ago

Hi Matthew, I redid the flow and it is working as expected now! I must have done something wrong when I first tried! Thanks for your great content!

Gez
Gez
5 months ago

It did work! But then when I was trying add more than 30k rows, I get an error: The response is not in a JSON format.

Error.PNG
Gez
Gez
5 months ago

Hi Matthew,

It was on a different file but with less rows (around 18k). I was able to add more than 30k rows by incorporating: https://curbal.com/curbal-learning-portal/export-more-than-100k-rows-of-power-bi-data-to-csv-using-power-automate-remove-headers. This is how my flow looks like now:

Flow.PNG
Jake
Jake
5 months ago

Hi Matthew,

Just curious on how would you tackle a dynamic file ID as the input in the list rows in a table but same excel format? Rationale is I want to keep a blank excel template and create a duplicate of the file template (using create file of sharepoint) and feed the ID to the list rows in a table. There is a difference between the ID as it shows an encoded file path compared to your example that starts at 0173.

Ramesh Mukka
Ramesh Mukka
4 months ago

This doesn’t work for items around 7000 which I am exporting from Dataverse to Excel. It simply throws error “The response is not in a JSON format.”

Josh T
Josh T
4 months ago

I am getting AADSTS65002: Consent between first party application ‘###’ and first party resource ‘###’ must be configured via preauthorization – applications owned and operated by Microsoft must get approval from the API owner before requesting tokens for that API. Trace ID: ### Correlation ID: ### Timestamp: ### ‘. Please sign in again.

What do I need to ask of my admin?

Omar
Omar
1 month ago
Reply to  Josh T

Did you find a solution to the problem? I have the same problem and haven’t found a solution for it

Ajay Dholi
Ajay Dholi
2 months ago

Hey Matthew! Thanks for this, it works perfectly, but I am not able to execute more than 1,10,000 records. My use case is I have a Do-Unit loop where I am writing records into an excel file from the API, in the batches of 10,000. This works fine till 11th loop that is 1,10,000 records after which it gives bad gateway issues. Any workaround for this? It will be great help!

Error details – {
  “error”: {
    “code”504,
    “message”“{\r\n  \”error\”: {\r\n    \”code\”: 504,\r\n    \”source\”: \”uk-001.azure-apim.net\”,\r\n    \”clientRequestId\”: \”4ec097dc-82d5-41a1-9d45-7dd826c02b00\”,\r\n    \”message\”: \”BadGateway\”,\r\n    \”innerError\”: {\r\n      \”error\”: {\r\n        \”code\”: \”MaxRequestDurationExceeded\”,\r\n        \”message\”: \”We’re sorry. We couldn’t finish what you asked us to do because it was taking too long.\”,\r\n        \”innerError\”: {\r\n          \”code\”: \”gatewayTimeoutUncategorized\”,\r\n          \”message\”: \”The service wasn’t able to complete the request within the time limit.\”,\r\n          \”innerError\”: {\r\n            \”code\”: \”MaxRequestDurationExceeded\”,\r\n            \”message\”: \”We’re sorry. We couldn’t finish what you asked us to do because it was taking too long.\”\r\n          },\r\n          \”date\”: \”2024-02-05T17:13:27\”,\r\n          \”request-id\”: \”65e1e33e-cd22-4f10-b9c7-6f224fd9ab7d\”,\r\n          \”client-request-id\”: \”65e1e33e-cd22-4f10-b9c7-6f224fd9ab7d\”\r\n        }\r\n      }\r\n    }\r\n  }\r\n}”
  }
}

Screenshot 2024-02-07 110221.png
Rob Gallini
Rob Gallini
2 months ago

I found one exception to this, in that the file ID is only returned if you specifically select the file in the action ‘List_rows_present_in_a_table:_Car_Sales_Report’. When using a variable or output from a previous action like Sharepoint – Create File, the output for ‘file’ is the filename not the ID. I suspect this is due to a lookup function when the object is not selected manually.

Ajay Dholi
Ajay Dholi
1 month ago
Reply to  Rob Gallini

There is a way around to get the fileid if your filepath is dynamic. Use the below API to get the fileid –

Invoke an HTTP request
URL for the request –
v1.0/drives/<driveid>/root/children?$filter=name eq ‘<filename>’

Headers-
{
“accept”:”application/json”
}

Set the path in the URL of the request.

Salil
Salil
17 days ago
Reply to  Ajay Dholi

Can you provide an example of the ‘Url of the request’ and show where to enter the path?