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?
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 >>
]
}
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 }]}"
)>>
@ gebrema : Thank you very much, you saved me!
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |