Fill-In An Excel Template With Power Automate

Power Automate can be used to populate a reusable Excel template with values. To do this, you’ll need to know two techniques: how to write a value to a single cell and how to add multiple rows to a table. In this article I’ll show you how to do both. Then you can convert the Excel file into a PDF or send it as an email attachment in Outlook
Table of Contents
⢠Introduction: The Purrfect Pet Shop Invoice Template
⢠Create An Excel Invoice Template
⢠Insert An Invoice Headers Table Into The Excel File
⢠Update A Single Cell In An Excel Spreadsheet
⢠Display Multiple Lines Of Text In A Cell
⢠Add Multiple Rows To An Excel Table
⢠Run The Flow To Fill-In The Excel Invoice Template
Introduction: The Purrfect Pet Shop Invoice Template
The owners of a pet shop use Power Automate to create invoices for their customers. An Excel template gets filled-in with the following information: Bill To, Invoice #, Invoice Date, Due Date and Invoice Lines.

Create An Excel Invoice Template
Setup a new SharePoint Document Library named Purrfect Pet Stores and insert a blank Excel file named Purrfect Invoice Template.xlsx.

Rename Sheet1 to Invoice and make the spreadsheet look like the image below. Insert a new Excel table named tblInvoiceLines. Add the columns: Description, Unit Price, Quantity & Amount.

Insert An Invoice Headers Table Into The Excel File
The Power Automate Excel connector cannot write a value to a single cell. But it can update a specific row in an Excel table. We will do a workaround to make Power Automate update the Excel table and have the desired cell reference it.
Create a new sheet in the Excel workbook named Data. Insert a new table named tblInvoiceHeaders with the columns Field and Value.

Populate tblInvoiceHeaders with the following values in the field column. Leave the Value column blank.
Field | Value |
Invoice # | |
Invoice Date | |
Due Date | |
Bill To |
Then go to the Invoice sheet and create cell references to the Value column of Excel table.
- Invoice # – Data!B2
- Invoice Date – Data!B3
- Due Date – Data!B4

Update A Single Cell In An Excel Spreadsheet
Now we will create the Power Automate flow actions to update a single cell in Excel for Invoice #, Invoice Date and Due Date. Create a new flow with an instant trigger named Populate Excel Invoice Template. Then add the following inputs:
- Invoice # – number
- Invoice Date – text
- Due Date – text
- Bill To – text
- Invoice Lines – text

Then add an Excel – Update A Row action to the flow. Select the tblInvoiceHeader table. Designate the table column Field as the Key Column and Invoice # as the Key Value. This tells the flow to update the matching row in Excel. Insert the Invoice # input into the value field.

Insert a 2nd Excel – Update A Row action for the Invoice Date…

And a 3rd Excel – Update A Row action for the Due Date. When these actions are run it will update the tblInvoiceHeader table but the cell references will make them also appear in the a single cell located on the Invoice sheet.

Display Multiple Lines Of Text In A Cell
The Bill To field on the invoice will show three lines of information
- Company Name
- Street Address
- City, State, ZIP
To properly display multiple lines of text we need to format the spreadsheet. Select the cells in the Bill To range and perform a Merge & Center action. Doing this makes 3 cells become 1cell.

Then right-click the cell and choose Format Cells. Check Wrap Text to make the text show on multiple lines. If we do not do this, the text will display as a single line.

Back in Power Automate add another Excel – Update A Row action as use the Bill To input for the Value field.

Add Multiple Rows To An Excel Table
The final section of the invoice we must create are the Invoice Lines. Just like all of the other invoice fields we will get the Invoice Lines from the flow trigger. But since the Invoice Lines have a data type of text we must convert it to an array using the Parse JSON Action
Insert a Data Operations – Parse JSON action into the flow. Add the Invoice Lines trigger input to the Content field.

Generate the JSON schema from a sample.

Use this JSON as the sample. Notice that the column names for tblInvoiceLines in the Excel invoice and field names of this JSON are the same.
[
{
"Description": "Fancy Cat Food",
"Unit Price": 5,
"Quantity": 10,
"Amount": 50
},
{
"Description": "Fishing Pole Cat Toy",
"Unit Price": 15,
"Quantity": 1,
"Amount": 15
},
{
"Description": "Cat Treats",
"Unit Price": 2,
"Quantity": 5,
"Amount": 10
}
]
To insert the array of rows into the Excel invoice add an Apply To Each action to the flow and place an Excel – Add A Row Into A Table action inside of it. Loop over the body of the Parse JSON action.
Target the tblInvoiceLines table in the add a row action. And supply the corresponding dynamic values for Description, Unit Price, Quantity and Amount from the Parse JSON action.

Run The Flow To Fill-In The Excel Invoice Template
We are done building the Power Automate flow. Test run the flow and supply the following values to the flow trigger:
Invoice # | 12345 |
Invoice Date | 1/1/2023 |
Due Date | 1/6/2023 |
Bill To | Sarah Green 123 Fake Street City, State, ZIP |
Invoice Lines | [ { “Description”: “Fancy Cat Food”, “Unit Price”: 5, “Quantity”: 10, “Amount”: 50 }, { “Description”: “Fishing Pole Cat Toy”, “Unit Price”: 15, “Quantity”: 1, “Amount”: 15 }, { “Description”: “Cat Treats”, “Unit Price”: 2, “Quantity”: 5, “Amount”: 10 } ] |

After the flow run completes the Excel invoice template looks like this. The data tab can be hidden so the customer will not see it.

Did You Enjoy This Article? šŗ
Subscribe to get new Power Apps & Power Automate articles sent to your inbox each week for FREE
Questions?
If you have any questions or feedback about Fill-In A Excel Template With 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.
Great stuff. THX for shareing!
Daniel,
You are welcome. Iām glad you liked it š
So with this guide you’ve so kindly given us (thank you!), someone would set up their “parent” list to collect the invoice #/dates/bill to information, and set up a “child” list collecting the Invoice Lines data that is tied to the “parent” list. Then a flow could get the data from both lists and merge it together using the method you have explained above…right? (Because the employees creating these invoices aren’t going to understand how to enter invoice lines as an array!)
Wonderful, Thanks
Please , apply to be the responsible for the documentation of the power platform !!!
Leonardo,
Hahaha. I love it. Thank you for the message š
Really interesting article and really clearl to follow.
Can you please elaborate on how this can reusable like you refer to in the beginning of the article.
I guess the header data of the invoice is fine as it will simply update those lines but what about the invoice lines which uses the add a row action rather than update?
Thanks do another really useful article
Mark,
It is reusable because you start with a blank invoice template and fill-in these fields:
– Bill To
– Invoice Number
– Invoice Date
– Due Date
– Invoice Lines
In the article I teach both how to add rows to a table and update a single cell