Fill-In An Excel Template With Power Automate

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 TemplateCreate An Excel Invoice TemplateInsert An Invoice Headers Table Into The Excel FileUpdate A Single Cell In An Excel SpreadsheetDisplay Multiple Lines Of Text In A CellAdd Multiple Rows To An Excel TableRun 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.

FieldValue
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 Date1/1/2023
Due Date1/6/2023
Bill ToSarah 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.







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.

Matthew Devaney

Subscribe
Notify of
guest

8 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Daniel
Daniel
1 month ago

Great stuff. THX for shareing!

Nic
Nic
1 month ago

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!)

George Winters
1 month ago

Wonderful, Thanks

Leonardo Marques
1 month ago

Please , apply to be the responsible for the documentation of the power platform !!!

Mark
Mark
1 month ago

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