for Microsoft Power Automate and Azure logic apps

Escape double quotes in JSON using Power Automate

By Jay Goodison

12th September 2021

Escape double quotes in JSON using Power Automate

We have a number of actions which expect to receive data formatted as JSON:

This isn’t specific to Encodian and many actions within Power Automate utilise the JSON format to exchange data, click here to understand more about the JSON standard

Lets consider the following basic example using our Populate Word Document action to add data to a Microsoft word document.

There is very little opportunity for the JSON data I have provided to the ‘Document Data‘ property to become invalid, however lets consider the following example where we are aiming to create a new document populated from data contained with a new SharePoint list item:

The validity of the JSON data passed to the Encodian action is dependent on the data coming from the SharePoint list item, and therefore the data entered by the end user into SharePoint. This is where things can go wrong! Recently we’ve had lots of support tickets reporting invalid JSON values which have been caused by speech marks contained within the dynamic data item. For example, considering the previous example lets assume the ‘PO Number‘ column contained this value: P12456

{
   "poNumber":"P123456", 
   "address":"",
   "due":"" 
}

This JSON data is valid, however what happens if the ‘PO Number‘ column contains this value: P”12456″

The following JSON is not valid due to the speech marks contained within the ‘PO Number‘ columns value: P”12456″

So how do you ensure dynamic data (and speech marks specifically) do not corrupt JSON payloads in Power Automate?

FIX: Escape double quotes in Power Automate

We’ll use the previous example to illustrate the resolution which is simply to use the Power Automate replace() expression to escape any speech marks contained within the dynamic data.

Expression Reference: replace(<dynamicDataHere>,'”‘,’\”‘)

The resultant JSON is now valid.

You may find the following website helpful for validating JSON: https://jsonlint.com/

If you’re unsure how to locate JSON data provided to actions, you need to review the INPUT value in your flow’s run history. This is further explained within this article: Power Automate Run History – Inputs and Outputs

If you’d like to review the complete guide to populating a word document using SharePoint data, check out: Add SharePoint List Item Data to a Document