The Power Platform provides immense flexibility in connecting apps and automating workflows with APIs. A common scenario is using a Canvas App to trigger a Power Automate flow, which in turn, uses the HTTP connector to interact with external APIs.
However, when working with JSON in Power Apps, especially when the JSON is created by using formulas or concatenating strings, you may occasionally encounter unexpected behavior, such as the introduction of line breaks.
In this post, I will walk through a practical solution for handling this issue using the Power FX Substitute function to generate clean JSON string and prevent line breaks from causing API call failures.
Triggering a Power Automate Flow Containing JSON
In this example, I created a Canvas App that triggers a Power Automate flow. The app constructs a JSON object, assigns it to a variable and then passes the variable to Power Automate to use in the HTTP action.
Here is the formula used to construct the JSON variable, which in this particular case is used to add an asset to a register. The formula detail isn’t relevant other than to demonstrate that the Set function creates a string variable in JSON format by combining text and the values from multiple controls.
Set(
varNewAsset,
"
{
""name"":""" & TextInput_DisplayName.Text & """,
""asset_type_id"":" & ComboBox_AssetType.Selected.ID & ",
""asset_tag"":""" & TextInput_AssetTag.Text & """,
""user_id"":" & Label_User.Text & ",
""type_fields"":
{
""product"":" & ComboBox_Product.Selected.ID & ",
""acquisition_date"":""" & varNow & """,
""warranty"":" & Dropdown_Warranty.Selected.Value & ",
""warranty_expiry_date"":""" & varWarrantyExpiry & """,
""serial_number"":""" & varSerialNumber & """,
""asset_state"":""In Use""
}
}
"
);
The Problem? Sometimes the JSON string, when processed in Power Apps, would unexpectedly include line breaks (carriage returns or new line characters).
Frustratingly, this can happen intermittently, resulting in unpredictable behavior. Line breaks in JSON can cause the API call to fail with errors that are very difficult to troubleshoot and track down.
Removing Line Breaks with Substitute()
To address the issue of line breaks in the JSON, I use the Substitute function to remove any occurrences of the ASCII characters, carriage return Char(13) and new line Char(10) from the string.
Here’s the formula to generate clean JSON. The formula is run in the Canvas app before passing the variable to Power Automate.
Set(
varNewAssetString,
Substitute
(
Substitute
(
varNewAssetString,
Char(13),
""
),
Char(10),
""
)
);
Let’s break this formula down:
- Set the Variable: Set(varNewAssetString, …) assigns the cleaned-up JSON string to the variable varNewAssetString.
- Remove Carriage Returns (Char(13)): The first Substitute replaces any carriage return characters (Char(13)) with an empty string (“”).
- Remove New Line Characters (Char(10)): The second Substitute removes any new line characters (Char(10)), ensuring the JSON is formatted correctly.
This ensures that no line breaks are present.
The string variable can now be passed from the Canvas app to the ‘Post New Asset’ Power Automate flow containing the HTTP action that calls the API using the formula:
PostNewAsset.Run(varNewAsset)
Why Clean JSON Is Important
When you pass a JSON object to the HTTP connector, formatting is crucial. Line breaks in JSON can make it invalid or cause the HTTP request to be misinterpreted. By using Substitute, you ensure that the string remains clean and predictable. This simple approach prevents line breaks and makes the API interaction reliable and smooth.
Cleaning the JSON in this way can save hours tracking down why the API call is failing, even though the JSON appears to be formatted exactly as required.
Conclusion
When working with APIs in the Power Platform, especially when building Power Apps that trigger Power Automate flows, ensuring clean and valid JSON formatting is key to avoiding errors. Using the Power Apps’ Substitute function to remove line breaks from JSON strings is a quick and effective solution to prevent unexpected failures in API calls.
Use this solution in your own Power Platform projects and streamline your API integrations without the worry of line breaks!
Thank you very much, I’ve needed this solution for a long time. Additional Quotation Marks added by users in Text Fields were causing issues when bringing the object into an adaptive card. Now I’ve managed to resolve this.
Hey Matthew, it’s great to hear you’ve solved the issue.