Creating JSON data string using Google Sheet formula

If you want to create an adaptive JSON data string for a table, use the following formula

="{" & char(34) & JOIN(char(34) & “,” & char(34),
JOIN(char(34) & “:” & char(34), {$A$1, INDIRECT(“A” & ROW())}),
JOIN(char(34) & “:” & char(34), {$B$1, INDIRECT(“B” & ROW())})
) & char(34) & “}” & “}”

You can add more columns as you needed. Usually put the JSON column at the end of a table.

It is a less buggy way to generate JSON strings for physical columns since it does not rely on the column names.

Note - somehow AppSheet does not convert A2,B2 references to RC format when the formula has multiple lines. So updated the formula to use INDIRECT