New Bug Encounterd: Quotes in JSON Templates used ...

gebrema · 07-09-2019 02:06 AM

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?

Phil

Your body template should take the form:

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”
},
“Rows”: [
{
“Id”: “<<[ID]>>”,
“Location”: “<<[Location]>>”,
“Tool”: “<<[Tool]>>”,
“Recycled”: false
},
]
}

You can use a Start expression to add multiple rows where the row values are taken from another table.

{
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”
},
“Rows”: [
<<Start: Select( …) >>
{
“Id”: “<<[ID]>>”,
“Location”: “<<[Location]>>”,
“Tool”: “<<[Tool]>>”,
“Recycled”: false
},
<< End >>
]
}

gebrema

My rows are not taken from another table, I want a single cell containing a list of entries to be split up into multiple rows. Start expressions only work with using row refrences and not with lists of values. This is why I have the formula that substitutes list seperators for JSON syntax.

I fixed my issue though , by generating all the JSON inside the expression appsheet doesn’t escape it and it works properly, so this is my final expression.

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

ericjoye

@ gebrema : Thank you very much, you saved me!

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