JSON Template Generator: Google Sheet for Creating Webhook Templates

AppSheet Tips & Tricks
, ,
1

Sometimes you have to map a LOT of columns from one table to another and create a really big JSON template.

I had to do a table with over 100 columns, so I built a template generator to provide a dropdown menu of columns and add all the extra characters.

3X_5_5_55994b969482086627db4a42f5bc0d80d869c11c.png
3X_5_5_55994b969482086627db4a42f5bc0d80d869c11c.png846×273 27.8 KB

3X_e_6_e6da5864cff91aeb408ab66b2b300fd484185575.png
3X_e_6_e6da5864cff91aeb408ab66b2b300fd484185575.png836×415 44.3 KB

Just paste in your two rows of column headers, and use the dropdown to finish generating your template.

The sheet is public, but view-only. Copy the template to your GDrive to use.

15 Likes
2

This is very handy. I usually do these in Notepad++ but will switch to this method. Thanks @GreenFlux

1 Like
3

@Bellave_Jayaram
I have created a copy of this sheet under gDrive > able3ventures. FYI.

2 Likes
4

@GreenFlux
In your gSheet, there is a TEMPLATE sheet. Provided you place below sheet formula in [C1] cell, it will eliminate the last comma (,) in the generated payload and also eliminate the empty payload generation in blank cells:

={"Template Row";ARRAYFORMULA(IF(LEN(A2:A),IF(LEN(A3:A),""""&A2:A&""""&": "&""""&"<<["&B2:B&"]>>"&""""&",",""""&A2:A&""""&": "&""""&"<<["&B2:B&"]>>"&""""),""))}

3X_6_8_68b80e98f8ecfa3f11372a4f890f2f9cc27e9fb8.png
3X_6_8_68b80e98f8ecfa3f11372a4f890f2f9cc27e9fb8.png1475×423 44.9 KB

3 Likes
5

@GreenFlux
It can even be re-arranged like this:

Sheet expression in [C1]

={"Template Row";ARRAYFORMULA(IF(LEN(A2:A),""""&A2:A&""""&": "&""""&"<<["&B2:B&"]>>"&"""",""))}

Sheet expression in [D1]

="{"&CHAR(10)&JOIN(","&CHAR(10),INDIRECT("C2:"&ADDRESS(ARRAYFORMULA(IFNA(MATCH(2,1/(C:C<>"")))),3,4,TRUE)))&CHAR(10)&"}"

which will produce a concatenated payload with the correct syntax

3X_5_8_583d54640964c3c0f38003f3b4602d35ec899c71.png
3X_5_8_583d54640964c3c0f38003f3b4602d35ec899c71.png1475×326 37.5 KB

6 Likes
6

Nice touch! Thanks, @LeventK I updated the public version.

2 Likes
7

Here’s a related tip for generating a list of all columns in your app. It works well with the template in this post because you need a list to feed the dropdowns anyway.

[Auto-Fill a Sheet with All Table/Column Names from Your App](https://community.appsheet.com/t/auto-fill-a-sheet-with-all-table-column-names-from-your-app/29169) Tips & Tricks ?

Have you ever wanted a list of all Tables and Column Names in your app, displayed IN your app (not just in the editor)? [Screen Shot 2020-08-11 at 7.46.38 AM] This can be useful for feeding dropdowns in other tables, especially when using another table to control security, user-permissions, etc. This method uses Google Apps Script to write a custom function that can be used as a sheet formula. [2020-08-11 07.31.28] Just open the script editor, and paste in the following code: [Screen Shot 20…

1 Like
8

I also created a quick way to generate a JSON string for physical columns

[Creating JSON data string using Google Sheet formula](https://community.appsheet.com/t/creating-json-data-string-using-google-sheet-formula/47634) Tips & Tricks ?

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 na…

Might be helpful for few use cases

1 Like