Excel Invoice Template in Power Automate: Fill It from a SharePoint List and Archive Every Invoice as PDF/A with PDF4me

PDF4me Excel - Populate is a Power Automate action that fills an Excel invoice template with JSON data. Here a new SharePoint list item supplies the data, Populate writes it into the template's smart markers, and three more PDF4me actions turn the workbook into a compressed PDF/A invoice that SharePoint stores automatically.

Search for an excel invoice template and page one is a gallery. Microsoft, Vertex42, QuickBooks and Square all hand you a well-designed blank workbook, and every one of them stops at the download. None of them says how the template gets filled, which in practice means someone copying a customer's details into cells, exporting to PDF, naming the file and dropping it in a folder, one invoice at a time. This flow keeps the part those pages get right, a template anyone can design in Excel, and removes every step that comes after the download.

Why-Based Q&A​

What You'll Get​

Input: a SharePoint list row holding the invoice header, the customer's details and two line items. Output: a compressed PDF/A-2b invoice, ExcelArchived.pdf, in the /Shared Documents/ArchivedExcel folder of the same site.

The data source: one list row per invoice, twenty-two columns wide. The last row, Morris - INV, is the one that produced the sample output.

ExcelArchived.pdf in the ArchivedExcel folder, written by the flow account a few seconds after the row was added.

What You Need​

The Flow at a Glance​

1. When an item is created (SharePoint trigger) watches the list named Excel Template.
2. Compose builds the RootData JSON from the new row.
3. Get file content using path reads the template from /Shared Documents/Excel Template.
4. Excel - Populate (PDF4me Excel) merges the JSON into worksheet 1.
5. Convert to PDF (PDF4me Convert) renders the workbook as Invoice.pdf.
6. PDF - Compress (PDF4me PDF) with Optimize Profile Web.
7. PDF - Create PdfA (PDF4me PDF) with Compliance PdfA2b.
8. Create file (SharePoint) into /Shared Documents/ArchivedExcel.

Complete flow overview​

The run that produced the sample invoice. Convert, Compress and Create PdfA take ten to eleven seconds each, and everything else finishes in under a second apart from the two-second SharePoint write. Budget about thirty-six seconds per invoice.

Step 1: How do you trigger a flow when a SharePoint list item is created?​

Flow so far: nothing yet, this is the trigger.

Every new row in the list is one invoice, so the trigger is the list itself.

1. Create an Automated cloud flow and pick SharePoint > When an item is created.
2. Configure:
   • Site Address: PDF4me Sharepoints - https://ynoox1.sharepoint.com/sites/PDF4meSharepoints
   • List Name: Excel Template

SharePoint trigger configuration​

One list, one trigger. The list's built-in Title column carries the invoice number, which is why the next step maps Number to Title.

Step 2: How do you turn a flat SharePoint row into JSON for an Excel template?​

Flow so far: SharePoint trigger.

The template expects a nested structure: an invoice header, a company block, a customer block and a list of items. The list row is flat. Compose is the translation layer between the two.

1. Add Data Operation > Compose.
2. Paste this into Inputs. Every value is a triggerBody()?['<column>'] expression that points at one list column.

{
  "RootData": {
    "Invoice": [
      {
        "Number": "@{triggerBody()?['Title']}",
        "Date": "@{triggerBody()?['Dateandtime']}",
        "DueDate": "@{triggerBody()?['DueDate']}",
        "PONumber": "@{triggerBody()?['PONumber']}",
        "PaymentTerms": "@{triggerBody()?['PaymentTerms']}"
      }
    ],
    "Company": [
      {
        "Email": "@{triggerBody()?['CompanyEmail']}",
        "Address": "@{triggerBody()?['CompanyAddress']}"
      }
    ],
    "Customer": [
      {
        "Name": "@{triggerBody()?['CustomerName']}",
        "Email": "@{triggerBody()?['CustomerEmail']}",
        "Company": "@{triggerBody()?['CustomerCompany']}",
        "Address": "@{triggerBody()?['CustomerAddress']}",
        "CityStateZIP": "@{triggerBody()?['CustomerCityStateZIP']}"
      }
    ],
    "Items": [
      {
        "Description": "@{triggerBody()?['Item1Description']}",
        "Qty": "@{triggerBody()?['Item1Qty']}",
        "UnitPrice": "@{triggerBody()?['Item1UnitPrice']}",
        "Discount": "@{triggerBody()?['Item1Discount']}",
        "TaxRate": "@{triggerBody()?['Item1TaxRate']}"
      },
      {
        "Description": "@{triggerBody()?['Item2Description']}",
        "Qty": "@{triggerBody()?['Item2Qty']}",
        "UnitPrice": "@{triggerBody()?['Item2UnitPrice']}",
        "Discount": "@{triggerBody()?['Item2Discount']}",
        "TaxRate": "@{triggerBody()?['Item2TaxRate']}"
      }
    ]
  }
}

Compose code view​

Invoice, Company and Customer are one-element arrays. Items has two elements, one for each set of item columns in the list.

How each template cell gets its value​

The From block (B3) is plain text, PDF4me, so it is the same on every invoice. Replace it with your own company name in the template.

Step 3: Where does the flow read the Excel template from?​

Flow so far: SharePoint trigger plus Compose.

The template is an ordinary file in a document library. The flow reads it on every run, so whatever is in SharePoint at that moment is the layout that gets used.

1. Add SharePoint > Get file content using path.
2. Configure:
   • Site Address: PDF4me Sharepoints - https://ynoox1.sharepoint.com/sites/PDF4meSharepoints
   • File Path: /Shared Documents/Excel Template/PDF4me_Excel_SharePoint_Template_.xlsx
   • Infer content type (advanced): Yes

Get file content using path configuration​

The list and the template folder are both called Excel Template. They are different things: one is a list, the other is a folder in Shared Documents.

Step 4: How does Excel - Populate fill an Excel invoice template?​

Flow so far: SharePoint trigger plus Compose plus Get file content using path.

This is the step that does the work the template galleries leave to you. Excel - Populate takes the workbook and the JSON, finds every smart marker, and writes the matching value into its cell.

1. Add Excel - Populate from the PDF4me Excel connection.
2. Configure:
   • File Content: the Body token from Get file content using path
   • JSON Data: the output of Compose (the token shows as Body)
   • File Name: Populate.xlsx
   • Worksheet Indexes: 1
3. Open Advanced parameters and set three of the four:
   • Strict JSON Strings: Yes
   • Culture & Language Settings: en-US
   • Calculate Formulas: Yes

Excel - Populate configuration​

Two inputs carry the work: the template bytes from SharePoint and the JSON from Compose. Everything else is formatting and scope.

The template uses two kinds of marker, and the difference between them is the whole trick:

• Header cells such as Invoice No. hold &=RootData.Invoice.Number(noadd). The (noadd) modifier writes the value in place and never inserts a row.
• The item row holds &=RootData.Items.Description, &=RootData.Items.Qty and so on, with no modifier. That row is written once for every element of the Items array, so two items produce two rows, and a third item would produce a third.

The full grammar, including modifiers, nested paths and formula handling, is in the Excel Populate syntax overview. For the Populate action on its own, with a Dropbox template and an inline JSON payload, see populating an Excel template from JSON in Power Automate.

Step 5: How do you convert the populated workbook to PDF?​

Flow so far: SharePoint trigger plus Compose plus Get file content using path plus Excel - Populate.

The populated workbook only exists inside the run. It is never saved as an .xlsx; it goes straight into the converter.

1. Add Convert to PDF from the PDF4me Convert connection.
2. Configure:
   • File Content: the Output File Content token from Excel - Populate
   • File Name: Invoice.pdf

Convert to PDF configuration​

Output File Content is the populated workbook. It never touches SharePoint as an .xlsx.

Step 6: How do you compress the invoice and convert it to PDF/A?​

Flow so far: everything through Convert to PDF.

Two actions from the PDF4me PDF connection, compress first and certify second. Reversing them would let compression rewrite a file that had already been made compliant.

1. Add PDF - Compress.
   • File Content: the Body token from Convert to PDF
   • File Name: the headers/FileName token carried through from the previous action
   • Optimize Profile: Web
2. Add PDF - Create PdfA.
   • File Content: the Body token from PDF - Compress
   • File Name: the headers/FileName token
   • Compliance: PdfA2b
   • Allow Upgrade: Yes
   • Allow Downgrade: Yes

Compress and Create PdfA parameters​

Compress works on the converted invoice, before anything is certified.

Create PdfA is the last action to touch the document, which is exactly where it belongs.

The same pair does the same job on files that are already in SharePoint in PDF archiving in Power Automate with an unattended SharePoint folder.

Step 7: Where does the finished invoice get saved?​

Flow so far: everything through Create PdfA.

The last action writes the certified invoice back into the same SharePoint site.

1. Add SharePoint > Create file.
2. Configure:
   • Site Address: PDF4me Sharepoints - https://ynoox1.sharepoint.com/sites/PDF4meSharepoints
   • Folder Path: /Shared Documents/ArchivedExcel
   • File Name: ExcelArchived.pdf
   • File Content: the Body token from PDF - Create PdfA

SharePoint Create file configuration​

Every PDF4me action outputs a token called Body, so the label alone does not tell you which one you picked. Hover it, or check the code view for body('PDF_-_Create_PdfA').

Run the Flow and Verify​

1. Save the flow at the top right.
2. Add a new item to the list named Excel Template and fill in every column the template uses.
3. Open the run history. All eight actions should be green, with Convert, Compress and Create PdfA taking most of the time.
4. Open /Shared Documents/ArchivedExcel and check that the PDF is there, with Modified By showing the flow's account.
5. Open the file itself and compare it with the list row, then check its document properties for the PDF/A declaration.

The invoice from the captured run is 37,876 bytes. It renders the row Morris - INV: customer Morris Luma at Invoicing.co, dated 2026-09-01 and due 2026-09-20, with two line items, PDF to Excel (quantity 60) and PDFA (quantity 45). Its XMP metadata declares pdfaid:part as 2 and pdfaid:conformance as B, which is PDF/A-2b seen from the inside. It is two pages rather than one, for the page-setup reason covered in step 5.

Common Variations You Can Add Without Rebuilding​

How this compares to other ways of producing invoices​

Each row describes the alternative. The last row is this flow.

The distinction that matters: the first two keep the layout in Excel but keep a person in the loop, and the third removes the person but moves the layout into code. Only this flow does both.

Common questions​

Is there an invoice template in Excel?​

Yes. Excel ships with invoice templates: in the desktop app, choose File > New and search for invoice, and Microsoft publishes more in its online template gallery. They are designed to be filled in by hand.

The template used in this post is built for a different job. Its value cells hold smart markers instead of sample text, so a flow can fill it. You can convert any invoice template you already like the same way: keep the design, and replace each value with a marker such as &=RootData.Invoice.Number(noadd).

How to generate invoice from Excel data?​

Keep the data in rows, keep the layout in a template, and let something merge the two. Inside Excel alone that something is a Word mail merge or a VBA macro, and both run on someone's desktop.

In this flow the rows live in a SharePoint list, Compose turns one row into JSON, and Excel - Populate merges it into the template. If your data is already an Excel table in OneDrive or SharePoint, the Excel Online (Business) connector's List rows present in a table action can feed the same Compose inside an Apply to each loop, with items('Apply_to_each') in place of triggerBody(). Everything from step 3 onward stays as it is.

How to automate invoices in Excel?​

Automating an invoice means removing every step a person does per invoice: typing the values in, exporting to PDF, naming the file and filing it. The template itself stays a normal Excel file.

This flow removes all four. The list row is the only input. The template is filled by Excel - Populate, exported by Convert to PDF, made archival by PDF - Create PdfA, and filed by Create file. Once the file name is built from the row, as step 7 describes, there is nothing left to do by hand.

Can Power Automate convert Excel to PDF?​

Yes. In a cloud flow, the OneDrive for Business connector's Convert file action turns an .xlsx into a PDF, but only for a file stored in OneDrive, so SharePoint flows often copy the workbook there first and delete it afterwards.

PDF4me Convert to PDF takes the file content directly from any previous action. That is why the populated workbook in this flow is never saved anywhere: it goes from Excel - Populate straight into the converter, and the only file that ever lands in SharePoint is the finished PDF/A invoice.

Troubleshooting​

Next Steps​

The same eight-step pattern (list row, reshape to JSON, fetch the template, populate, convert, compress, certify, save) handles any document that starts life as a spreadsheet: quotes, delivery notes, statements and certificates.