Last week, I was working on a Power Automate flow and came across a requirement to add data to an Excel table named ‘DataLog‘, which already had some previous data.
Instead of entering the information manually every time, I thought, “Why not automate this?” The goal was to simplify the process: I wanted to set up a flow that would run every day at 10 PM and automatically add whatever’s in my SharePoint list called ‘Client Requests’ to the existing data in the Excel table.
In this tutorial, I will explain how to add row to Excel table using Power Automate and how to add a row to an Excel table from an array using Power Automate.
Add Row to Excel Table Using Power Automate
For this example, I created a SharePoint list called Client Requests with columns like:
| Columns | Data Types |
|---|---|
| Request ID | Single line of text |
| Client Name | Single line of text |
| Client Contact | Single line of text |
| Request Description | Multiple lines of text |
| Submission Date | Date and Time |
I have a SharePoint document library that contains an Excel file named Client Requests, where all client requests are stored.
Now follow the below steps:
1. Navigate to the Power Automate Home page, click + Create, and select the Scheduled Cloud Flow. Then provide the following information:
- Starting: Provide on which date you want to run your flow.
- at: Provide the time you want to run the flow, in my case, at 10:00 PM.
- Repeat every: We want to run the flow daily.
2. Then add a Get items action to retrieve all items present in the SharePoint list and provide the following parameters:
- Site Address: Select the SharePoint site address.
- List Name: Select the name of the SharePoint list from which you want to add items to the Excel table.
3. Now, let’s add a row to the table.so “Add a row into a table” action (under the Excel Online (Business) connector) and provide the required parameters:
- Location: Choose SharePoint Site.
- Document Library: Choose the Documents folder.
- File: Provide the Excel file we created above.
- Table: Provide the table name.
- Row: Put below the table value
| Column Name | Dynamic Content |
|---|---|
| Request ID | items(‘For_each’)?[‘RequestID’] |
| Last Name | item(‘For_each’)?[‘ClientName’] |
| item(‘For_each’)?[‘ClientContact’] | |
| Phone Number | item(‘For_each’)?[‘RequestDescription’] |
| Company | item(‘For_each’)?[‘SubmissionDate’] |
4. Add delete item action and provide the below parameters:
- Site Address: Select the SharePoint site address.
- List Name: Select the name of the SharePoint list.
- Id: Provide id from the dynamic content for the get items.
Now save the flow, and below run the flow make sure you have some items in the sharePoint list:
Then, run the flow manually. After it runs successfully, open the Excel file, and you’ll see that new rows have been added to the Excel table.
Add a Row to an Excel Table From an Array Using Power Automate
Suppose you have a survey collecting customer feedback on recent services. Each response includes details like Customer ID, Satisfaction Score, and Feedback Comment. The feedback is currently stored in an array:
[
{ "Customer ID": "C123", "Satisfaction Score": 4, "Feedback Comment": "Great service!" },
{ "Customer ID": "C456", "Satisfaction Score": 3, "Feedback Comment": "Good, but room for improvement." }
]Now, you want to store these responses in your Excel table.
For this example, I have a SharePoint document library that contains an Excel file named Customer Feedback Survey.
Now follow the below steps:
1. Go to Power Automate, click the Instant Cloud flow, enter the Flow name, and choose the trigger flow (i.e., manually trigger a flow).
2. Then, Add the Initialize Variable action, enter the name and type of the variable as an array, and values in the below:
[
{ "Customer ID": "C123", "Satisfaction Score": 4, "Feedback Comment": "Great service!" },
{ "Customer ID": "C456", "Satisfaction Score": 3, "Feedback Comment": "Good, but room for improvement." }
]
3. Select Parse JSON action and provide the below parameter:
- Content: Provide the dynamic content for the Initialize Variable action.
- Schema: Click Use sample payload to generate schema, pass the array value, and choose Done. It will automatically generate the JSON schema as below:
4. Add a row to the table action (under the Excel Online (Business) connector) and provide the required parameters:
- Location: Choose SharePoint Site.
- Document Library: Choose the Documents folder.
- File: Provide the Excel file we created above.
- Table: Provide the table name.
- Row: Put below the table value
| Column Name | Dynamic Content |
|---|---|
| Customer ID | items(‘For_each’)[‘Customer ID’] |
| Satisfaction Score | item(‘For_each’)[‘Satisfaction Score’] |
| Feedback Comment | item(‘For_each’)?[‘Feedback Comment’] |
Now, click Save and run the flow manually. After it runs successfully, go to the SharePoint document library and open the Excel library to see that the array values have been added successfully.
In this tutorial, I covered how to automate adding rows to an Excel table using Power Automate. We started with a SharePoint list, creating a scheduled flow to add daily updates to an Excel file. Then, we explored how to add rows from an array using an example of survey feedback data.
- Send a Text Message SMS Using Power Automate
- Save Microsoft Forms Responses to Excel Online using Power Automate
- Rename Subfolders in a SharePoint Document Library Using Power Automate
- Power Automate indexOf() Function
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
