Automate Excel Reporting: Populate Templates from JSON Data

Power Automate’s Excel Online connector is powerful, but it has one notable limitation: it cannot write a value to an arbitrary single cell. Instead, it can only update rows inside an Excel table. Fortunately, a clever workaround using a helper table and cell references lets you push individual values into any cell. Combined with the ability to add rows to a table from an array, you can fully populate a pre‑built Excel template—ready to be converted to PDF, emailed, or stored.

This article walks through a real‑world scenario where a company generates work orders from a SharePoint‑hosted Excel template. You’ll learn the two core techniques—updating a single cell through a lookup table and bulk‑adding rows from JSON—and see how to avoid common pitfalls.

Scenario: Adventure Works Equipment Rental

The rental company uses a standardised Excel workbook to create work orders. Each order contains:

• A Work Order Number (single value)
• Rental Date and Return Date (single values)
• Customer information (including name, street, city – displayed on multiple lines)
• A Table of rented equipment with product, daily rate, number of days, and total cost.

The template is stored in a SharePoint document library. A Power Automate flow receives the order data (as trigger inputs) and fills the template automatically.

Step 1: Prepare the Excel Template

Create a new Excel file in a SharePoint document library (for example, a library named “Rental Work Orders”). Rename the default sheet to WorkOrder and add a second sheet named Data.

Build the Helper Table

On the Data sheet, insert a table named tblWorkOrderInfo with two columns: Field and Value. Populate the Field column with the rows you need to fill individually:

Field            | Value (leave empty)
-----------------|-------------------
Work Order #     |
Rental Date      |
Return Date      |
Customer         |

On the WorkOrder sheet, create cell references that pull the values from the Data sheet. For example:

• Cell B5 (Work Order #): =Data!B2
• Cell B7 (Rental Date): =Data!B3
• Cell B9 (Return Date): =Data!B4
• Cell B11 (Customer): =Data!B5

These cells will automatically update when the corresponding rows in tblWorkOrderInfo are modified.

Prepare the Equipment Table

On the WorkOrder sheet, add an Excel table named tblEquipment with columns:

• Product
• Daily Rate
• Days
• Total

This table will receive the rows from the flow.

Format the Customer Cell for Multi‑Line Display

Select the cell that will hold the customer details (B11 in our example), then:

1. Merge & Center across a suitable range (e.g., B11:D11) so the content can span multiple lines.
2. Right‑click the merged cell, choose Format Cells, and check Wrap Text.

Without these settings, a string with newline characters will appear as a single line.

Step 2: Create the Power Automate Flow

Add an Instant cloud flow with a manual trigger. Include the following inputs:

WorkOrderNumber     (number)
RentalDate          (text – expects date string)
ReturnDate          (text)
Customer            (text – can include \n for line breaks)
EquipmentItems      (text – JSON array)

Step 3: Update the Header Fields

Because the Excel connector cannot write to arbitrary cells, we target the tblWorkOrderInfo table. Use the Update a row action for each header field.

Update Work Order Number

• Location: Choose your SharePoint site and the template file.
• Table: tblWorkOrderInfo
• Key Column: Field
• Key Value: Work Order #
• Value: @{triggerBody()?['WorkOrderNumber']} (dynamic content)

Repeat the same action for Rental Date and Return Date, changing the Key Value and the supplied value accordingly.

Step 4: Insert the Customer Text (Multi‑Line)

For the Customer field, use the same Update a row action with Key Value set to Customer and the Value taken from the trigger input. The string may contain \n characters (or actual newlines) to separate lines. Because the target cell is merged and has wrap text enabled, the lines will appear correctly.

Step 5: Add Equipment Rows from JSON

The EquipmentItems input is a text string containing a JSON array. To work with it as an array, use a Parse JSON action.

Parse JSON Action

• Content: @{triggerBody()?['EquipmentItems']}
• Schema: Generate from a sample payload.

Use the following sample to create the schema:

[
{
  "Product": "Concrete Mixer",
  "Daily Rate": 120,
  "Days": 3,
  "Total": 360
},
{
  "Product": "Jackhammer",
  "Daily Rate": 80,
  "Days": 2,
  "Total": 160
},
{
  "Product": "Safety Harness",
  "Daily Rate": 15,
  "Days": 5,
  "Total": 75
}
]

Apply to Each and Add a Row

After the Parse JSON action, add an Apply to each loop. Set the output from the Parse JSON action as the list to iterate over. Inside the loop, place an Add a row into a table action.

Configure the Add a row action:

• Location: SharePoint (same file).
• Table: tblEquipment
• Product: @{items('Apply_to_each')?['Product']}
• Daily Rate: @{items('Apply_to_each')?['Daily_Rate']} (note: the schema may use a space in the name, which becomes Daily_Rate in the expression).
• Days: @{items('Apply_to_each')?['Days']}
• Total: @{items('Apply_to_each')?['Total']}

}

Step 6: Test the Flow

Manually trigger the flow and supply realistic test data:

WorkOrderNumber: 2049
RentalDate:      2026-06-15
ReturnDate:      2026-06-18
Customer:        ACME Builders\n742 Evergreen Terrace\nSpringfield, IL 62701
EquipmentItems:  [{"Product":"Concrete Mixer","Daily Rate":120,"Days":3,"Total":360},{"Product":"Jackhammer","Daily Rate":80,"Days":2,"Total":160},{"Product":"Safety Harness","Daily Rate":15,"Days":5,"Total":75}]

After the flow runs, open the Excel file. The header fields should be populated, the customer text displayed on separate lines, and the tblEquipment table filled with the three rows.

Common Mistakes and Troubleshooting

• Key Value Mismatch: If the Update a row action cannot find the row, the flow fails silently. Double‑check that the Field values in the table exactly match the Key Value strings (including trailing spaces).
• Missing Cell References: The single cells on the WorkOrder sheet will remain blank if the references to the Data sheet are incorrect. Verify that =Data!B2, etc., are present and point to the Value column of the helper table.
• No Wrap Text / Merge: Without these formatting steps, the customer string appears on one line, clipped or overflowing. Always format the target cell in advance.
• Parse JSON Errors: The input text must be valid JSON. Common issues include missing double quotes or extra commas. Use a linter before pasting the array.
• Table Column Names: Ensure the column names in your Excel table match the property names in your JSON exactly. Spaces in column names are allowed, but the dynamic content expressions will use _x0020_ (or an underscore) representing the space.

Performance and Security Considerations

• Each Update a row action performs an API call. For many header fields, this might be slower than a batch operation. Keep the helper table lean.
• The flow uses the file stored in SharePoint. Assign appropriate permissions so only authorised users can trigger modifications.
• For large equipment lists, the Add a row action inside a loop can cause throttling. If you anticipate hundreds of rows, consider using the Add multiple rows action (if you can pre‑build a table array). For most template use cases, the loop is acceptable.

Final Recommendation

The combination of a helper table with cell references and table row insertion is a reliable pattern for populating Excel templates from Power Automate. It works for invoices, work orders, certificates, and any structured report. Once the template is filled, you can easily convert it to PDF or attach it to an email.

Experiment with your own templates—just remember to keep the formatting consistent and verify that your flow inputs match the expected structure.

References

• Original technique described by Matthew Devaney: Fill-In An Excel Template With Power Automate
• Microsoft Learn – Introduction to the Excel connector (placeholder): https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/
• Working with tables in Power Automate: https://learn.microsoft.com/en-us/power-automate/create-excel-tables (placeholder)