• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in touch
  • About Me
    • Privacy Policy

Create PDF Invoice using Microsoft Power Automate with JSON

December 2, 2019 by Paulie Leave a Comment

This post demonstrates how to create a PDF invoices from JSON data using Microsoft Power Automate (Microsoft Flow). The five step flow follows this sequence:

  1. Receive data from a HTTP request containing a JSON payload.
  2. Populate a Microsoft Word Template.
  3. Create File (Word Document).
  4. Convert previously created Word document to PDF.
  5. Save the PDF File.
Image of a Microsoft Flow that generates a PDF Invoice from JSON data.

The source data is line printer data from a legacy version of a Kerridge Autoline system based on KCML. Microsoft Flow is an easy way to add functionality to old or legacy systems without spending a lot of money.

Here is a sample of the original line printer text:

                                                                         Page No:       1
                                                                        Inv no.  00102707


                                                                          Acc no.   0000024
                                                                          Date   28/11/2019

  Invoice to:                                              Deliver to:
  Sandy Lane Garage                                        Sandy Lane Garage               
  12 Sandy Lane                                            12 Sandy Lane                   
  Walton-On-Thames                                         Walton-On-Thames                
                                                                                           
  Surrey                                                   Surrey                          
  KT12 2EQ                                                 KT12 4EQ                        

                                                           VAT NO:  FR86329130660          

    Order no.                  Order Date
     088135                    28/11/2019

  Part No.           Description                         Qty    Price     Disc      Value

  02/200073           HOSE*                               1    12.00      0.00      12.00
  02/200147           THERMOSTAT GASKET                   1     0.66      0.00       0.66
  02/202278           GASKET - ROCKER COVER P12           1     4.50      0.00       4.50
  1450/0002           GREASE NIPPLE                      10     0.25      0.00       2.50

You can see the complete listing here on Pastebin. Here is the final PDF produced by flow from the data above (click here to see full PDF):

Image preview of a PDF produced by Microsoft Flow (Power Automate) from a JSON file

I wrote a shell script to convert the line printer data to JSON using grep, sed and awk. You can see the JSON code here on Pastebin. Next I built a Microsoft Word template that would be the destination for the data.

Create a Word Template for the PDF Invoice

I used the Simple Sales Invoice in the Word template gallery as my starting point and modified the appearance to my liking.

Next, plain text control controls were added to the Invoice for each of the fields in the JSON file that will appear only once (i.e. everything that is not part of an Invoice line). Do the following:

  • Enable the Developer Tab in Word.
  • Put the cursor into a table cell where your data needs to go.
  • Enter some sample data into that cell.
  • Highlight the sample data and click on “Plain Text Context Control” in the developer toolbar.
  • Click on Properties in the control toolbar ribbon.
  • Give the content control a meaningful name(preferably match it to your JSON field names).
Animated Gif showing how to add plain text content controls to a Microsoft Word Template.

Add Content Controls for repeating data

For the repeating data, follow the same procedure as above for each column of data in your invoice line table, but this time make sure the field names match the JSON field names. Then specify the row as repeating content like this:

Animated Gif showing how to add repeating data content to a Word Table.
  1. Select the entire Invoice line item row. I find it is easiest to go to the Layout tab and click “Select” and then choose “Select Row”.
  2. Go back to the developer tab and click on the repeating section content control.
  3. Name the repeating section with a sensible name.

In this example, the JSON for a single invoice line is like this:

{
	"PartNo": "813/00419",
	"Description": "GASKET-EXHAUST FLANGE",
	"ToInvoice": "1",
	"SellingPrice": "0.50",
	"DiscountPercentage": "0.00",
	"NetLineValue": "0.50"
}

Set Repeating Column Headers

My example data has quite a lot of invoice lines which do not all fit on a single page. So it is important to make sure that “Repeat Header Rows” is enabled. This will ensure the subsequent pages have column headings.

Select the top row of your Invoice lines table and go to the Layout tab and select “Repeat Header Rows”.

Image of "Repeat Header Rows" button in Microsoft Word.

Now that you have finished editing the Word document. Save it somewhere you will be able to access it from Flow, such as OneDrive or SharePoint.

Create the Flow

The flow is really easy to build and in this example there are only five steps:

  • Step 1 – Create a new flow
    • Choose “Instant – from blank”
    • Trigger the flow “When a HTTP request is received”
    • When the flow editor appears click the “Generate from Sample” button within the HTTP Request flow part and paste in a sample of your JSON data and then flow will automatically generate the JSON schema which you will be able to use in the rest of the flow.
  • Step 2 – Add the “Populate a Microsoft Word Template” action.
    • Select the previously saved template file.
    • Flow will read the document and present a list of fields to be added to the document.
    • Go through each field and add them to the document, apart from the repeating fields.
    • When you reach the line items block it will first appear like this:
    • Click on the little box I have highlighted in red which switches the input to accept an entire array. Then you can select the aeeay from your JSON file.
  • Step 3 – Save the File
    • Now your Word document has been populated you can save it somewhere that is appropriate for your environment (I used “Create file – OneDrive for Business).
    • For the Filename I used “INV_” + Invoice Number from JSON File + “.docx”:
  • Step 4 – Convert the Word Invoice to PDF using the path from the previous step.
  • Step 5 – Save the new PDF. I named the file as per the above step but changed the extension to .PDF

Executing the Flow

This flow is triggered via a HTTP request with a JSON payload. In this example it was fired using cURL from Linux like this:

#!/bin/bash
host="https://prod-84.westeurope.logic.azure.com:443/workflows/..."
curl -v -d @INV_00102707.json -H 'Content-Type: application/json' $host

But it could just as easily be executed from PowerShell like this:

$FlowUri = "https://prod-84.westeurope.logic.azure.com:443/workflows/..."
$PostBody = Get-Content ".\INV_00102707.json"
Invoke-RestMethod -Method Post -Uri $FlowUri -Body $PostBody

Conclusion

Microsoft Flow can provide a good solution for automatically generating invoices or other business documents. In it’s live implementation this flow will also:

  • Save the document to a SharePoint list in a custom content type:
    • Metadata saved with the item.
    • Easy lookups.
    • Will create a simple document management system within 365.
  • Use a Sendgrid template to email the invoice to the recipient.
  • Will transmit relevant data to a Power BI Streaming dataset to allow for easy analysis of invoicing.

I was frustrated that I couldn’t find an easy way to format numbers in flow so that there are thousands separators etc. But it is a minor complaint.

Filed Under: Office 365 Tagged With: Microsoft Flow, Power Automate

Log RDS Sessions with a Power BI Streaming Dataset

November 14, 2019 by Paulie 1 Comment

Image showing Power-BI Dashboard displaying Terminal Server User Authentications

After a conversation with a customer today, I needed to create an easy way to monitor RDP authentications. This turned out to be more difficult than I expected. We suspected that some accounts had been compromised. I decided to combine a few … [Continue reading] about Log RDS Sessions with a Power BI Streaming Dataset

Filed Under: How To Tagged With: Power BI, Remote Desktop

Emailing from Turbosoft TTwin 4 Terminal Emulator with VBA Script

November 12, 2019 by Paulie Leave a Comment

Image of PowerSoft TTWin4 Emulator creating an Outlook Email

I've got a lot of customers with legacy systems, by chance most of them use Century TinyTerm. One, who uses TTwin 4 by Turbosoft asked if it would be possible to initiate an email from the contents of the emulation screen. Take this screen for … [Continue reading] about Emailing from Turbosoft TTwin 4 Terminal Emulator with VBA Script

Filed Under: Scripts & Utilities Tagged With: Legacy, VBA

Monitor Disk Usage on Linux and Unix with Microsoft Flow

November 5, 2019 by Paulie Leave a Comment

I look after quite a number of servers, many of which are different flavours of Unix and Linux. Checking disk usage is easy with the df command but I wanted to setup something automatic, where I could get a view of all the systems at once. I wanted … [Continue reading] about Monitor Disk Usage on Linux and Unix with Microsoft Flow

Filed Under: How To Tagged With: Linux, Microsoft Flow, Shell Scripting

Fixing Headset overload with the Jabra Evolve 75 wireless

October 30, 2019 by Paulie Leave a Comment

Image of Jabra Evolve 75 in Charging Dock

I spend a lot of time on the phone, and I cannot stand to hold the phone to my head, unless I can talk on a headset, I really don't want to talk. So I bought myself a Jabra Evolve 75 to set about clearing the cables off my desk. The problem is I … [Continue reading] about Fixing Headset overload with the Jabra Evolve 75 wireless

Filed Under: Product Review Tagged With: VOIP

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 26
  • Go to Next Page »

Primary Sidebar

Recent Posts

  • Create PDF Invoice using Microsoft Power Automate with JSON
  • Log RDS Sessions with a Power BI Streaming Dataset
  • Emailing from Turbosoft TTwin 4 Terminal Emulator with VBA Script
  • Monitor Disk Usage on Linux and Unix with Microsoft Flow
  • Fixing Headset overload with the Jabra Evolve 75 wireless

Recent Comments

  • Manfred Kipp on How to enable SMB1 on Windows 10
  • Dermot on How to find the startup folder on Windows Server 2012 or 2016
  • Nwanda on How to run a VBA macro when new mail is received in Outlook
  • Paulie on How to run a VBA macro when new mail is received in Outlook
  • Nwanda on How to run a VBA macro when new mail is received in Outlook