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.

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:

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.

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.