“I’d like to store complex data without creating too many columns, can I use JSON to store and load it within PowerApps?”
When you build a SharePoint based solutions, there’re two basic ways how users can interact with it. You can let them use SharePoint with all it’s functionality: search, new/display forms, sorting, views, etc., or you can build a PowerApp. A modern application that’ll contain the necessary functionality, completely hiding the SharePoint lists on the background. And since the list is on the background, you can be much more creative with the data structure. Instead of having a separate column for everything, you can group the information – especially helpful if you work with dynamic number of values.
As this blog is mostly about Power Automate and Power Automate loves JSON, this post will show you how to store and read JSON data from a SharePoint list (or any other source).
SharePoint column settings
The SharePoint column should be a multiple lines of text column, set to plain text only. You don’t want to be limited to 255 characters of the single line of text and you don’t want the special characters added by enabling rich text.
Storing the JSON
To store a JSON into this column, start from PowerApps collection. You can take any collection and convert into JSON with the JSON(…) function. In my example I’m creating a collection manually.
Once I have a collection I can convert it into JSON, e.g.
JSON(colJSONdata)And store it to a SharePoint list into the multiple lines of text column with the Patch(…) function.
The result is visible in the SharePoint list, a valid JSON array in the text field.
Reading the JSON
But this JSON is not something you want to show to the users. The users want to see the data in the PowerApps application in an easy to read way. Meaning that at some point you’ll have to load the data and show it in the app.
As PowerApps work with collections, not JSON objects, you’ll have to convert it into a collection.
Firstly, parse the JSON with ParseJSON(…) function and turn it into a Table(…).
Table(ParseJSON(varItem.'JSON column'))Secondly, loop through the table and recreate a collection with the JSON data.
ForAll(
Table(ParseJSON(varItem.'JSON column')),
{
Name: Text(Value.Name),
Surname: Text(Value.Surname)
}
)Finally, store the data in a new collection.
ClearCollect(
JSONdata,
ForAll(
Table(ParseJSON(varItem.'JSON column')),
{
Name: Text(Value.Name),
Surname: Text(Value.Surname)
}
)
)
Now, with the data back in a collection you can use it in the PowerApp.
Summary
As you can see, using PowerApps can give more freedom over the data on the background that’ll allow you to use even complex data objects – store and load JSON in this example. You can turn a collection into a JSON, store it in a text column, and load it back when needed.
But it doesn’t mean that you should do it for all of your solutions now! It’s just an alternative solution when the database would get too complicated, or when you’re working with dynamic number of inputs. E.g. if a request can contain information about 1-5 people, or if you need to define a weekly schedule with day + time combinations, having one column with JSON sounds like a good solution.