New Bug Encounterd: Quotes in JSON Templates used by workflows are escaped too many times

[gebrema]

Hi all,

I want to use the appsheet API for adding a row in a table for each entry in a enumlist in another table. I made and tested a expression, which I validated by running that expression in a virtual collumn and manualy sending it to the API via postman. When I use the same expression within a JSON templates there are issues with the way quotes in strings used by appsheet formula’s are escaped. Resulting in the API call to fail. Below a example of what I want to achieve.

What the data looks like:

ID	PARTS CHANGED
jhDJpwAB	Location 1 ― Drill , Location 2 ― Saw , Location 3 ― Custom Tool

How I want the transformed data to look:

ID	Tool	Location	Recycled
jhDJpwAB	Drill	Location 1	false
jhDJpwAB	Saw	Location 2	false
jhDJpwAB	Custom Tool	Location 3	false

JSON Template:

{
    "Action": "Add",
    "Properties": {},
    "Rows": [
        <<
            CONCATENATE(
                "{ ""ID"": """, [ID],
                """, ""Location"": """,
                SUBSTITUTE(
                    SUBSTITUTE([PARTS CHANGED], " ― ", """, ""Tool"": """),
                    " , ",
                    CONCATENATE(""", ""Recycled"": false }, { ""ID"": """, [ID], """, ""Location"": """)
                ),
                """, ""Recycled"": false }"
            )
        >>
    ]
}

Output when testing template:

"Payload": "{
    \"Action\": \"Add\",
    \"Properties\": {},
    \"Rows\": [
        {
            \\\"ID\\\": \\\"jhDJpwAB\\\",
            \\\"Location\\\": \\\"Location 1\\\",
            \\\"Tool\\\": \\\"Drill\\\",
            \\\"Recycled\\\": false
        },
        {
            \\\"ID\\\": \\\"jhDJpwAB\\\",
            \\\"Location\\\": \\\"Location 2\\\",
            \\\"Tool\\\": \\\"Saw\\\",
            \\\"Recycled\\\": false },
        {
            \\\"ID\\\": \\\"jhDJpwAB\\\",
            \\\"Location\\\": \\\"Location 3\\\",
            \\\"Tool\\\": \\\"Custom Tool\\\",
            \\\"Recycled\\\": false
        }
    ]
}",

(Probably) desired output and the output resulting from running the expression in a virtual column:

"{
    \"Action\": \"Add\",
    \"Properties\": {},
    \"Rows\": [
        {
            \"ID\": \"jhDJpwAB\",
            \"Location\": \"Location 1\",
            \"Tool\": \"Drill\",
            \"Recycled\": false
        },
        {
            \"ID\": \"jhDJpwAB\",
            \"Location\": \"Location 2\",
            \"Tool\": \"Saw\",
            \"Recycled\": false },
        {
            \"ID\": \"jhDJpwAB\",
            \"Location\": \"Location 3\",
            \"Tool\": \"Custom Tool\",
            \"Recycled\": false
        }
    ]
}"

What I suspect is happening here is that appsheets expects the result of a template expression to be a string used as a value within a json template and therefore escapes the already escaped string. So \" becomes \\\". Could it be possible to let appsheet generate the json body itself?