How to store JSON in SharePoint and read JSON in PowerApps

[mgreiner79]

I'm making a powerapp, where I have a collection of tasks. The tasks data comes from a Sharepoint list. I want to include a event log for each task. I dont want to do this using look-ups because it will be slow, and doesnt really need to be a relationship, because the logs wont be referenced anywhere outside of the context of a task.

I am trying to do it by storing JSON as text into a Sharepoint List text field.

It looks something like this:

[{"Date":"31-08-2023", "EventType":"MovedToInProgress", "Comments":"Working on it"}]

In Powerapps, I can load this data into a collection using ParseJSON.

ClearCollect(
    TaskLogs,
    ForAll(
        Table(ParseJSON(ThisItem.JSON)),
        {
            Date: Text(Value.Date),
            EventType: Text(Value.EventType),
            Comment: Text(Value.Comment)
        }
    )
);

Then I can add a new event to the TaskLogs collection like so:

Patch(
    TaskLogs,
    Defaults(TaskLogs),
    {
        Date: Text(
            Now(),
            "dd-md-yyyy hh:mm:ss"
        ),
        EventType: "MovedToDone",
        Comment: TextInput.Text
    }
);

 Then I try to update the data in the sharepoint site, so I convert the object back to a JSON string, but in doing so, PowerApps inserts a bunch of escape characters, making the string look like this:

"[{\"Date\":\"31-08-2023\", \"EventType\":\"MovedToInProgress\", \"Comments\":\"Working on it\"},{\"Date\":\"31-08-2023\", \"EventType\":\"MovedToDone\", \"Comments\":\"All done!\"}]"

 This is understandable, since it need to be a string. However, If I store this string in SharePoint, then try to read it again using ParseJSON, I get an parsing error:

JSON parsing error: Expected property name or '}' in JSON at position 2.

Any idea how I can make this work?

[LaurensM · Accepted solution]

Hi @mgreiner79,

Patching the following value to your Text field did not result in those characters on my end:

Text(JSON(TaskLogs))

//EDIT: only using JSON() on the collection also resulted in no additional characters
JSON(TaskLogs)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[mgreiner79]

@LaurensM  Yeah,  it looks like you are right.
What I was doing was first storing the value of the JSON string to a global variable, and saw that it had the escape characters in it. If I just directly patch the item in the sharepoint list (without storing it as a global variable in powerapps), then it just works.

Thanks for the answer!