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 Report
• Setup The SharePoint List
• Get All Items From The SharePoint List
• Create An Array Of Multiple Excel Rows
• Make A New Excel Workbook With A Table
• Get The Excel Workbook Drive ID And Item ID
• Configure A Connection To The Microsoft Graph
• Add Multiple Rows To An Excel Table Using HTTP With Azure AD
• Run 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.

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.