I would like to write the formula to create the JSON format .

Options
rama
rama
edited 12/30/21 in Formulas and Functions

I am unable to find any documentation for creating the formula to generate the JSON format ,Can you please help me if there is any documentation available.

Actually the requirement is we have 10 column values and one JSON generator column ,Smartsheet should convert those 10 column values into JSON format after entering the respective column attributes.


Can you please give me some clue to sort-out those issue using Smartsheet.


Thanks

Best Answer

  • Heath Hilton
    Heath Hilton ✭✭
    Answer ✓
    Options

    rama,


    So this one may take a bit of work to get set up but once it is functional it may not be too bad. I created a list of six columns and then showed what the JSON would look like when it was done (hopefully I didn't mess up the formatting.



    The important thing the remember is that your quotes ( " ) are going to require you to put in a backslash to get it to show up in the cell properly. Here is the formula that I used to get this all to output what is shown.

    ="'{\"Animal\":\"" + Animal@row + "\", \"Book\":\"" + Book@row + "\", \"Car\":\"" + Car@row + "\", \"Dog\":\"" + Dog@row + "\", \"Earring\":" + Earring@row + ", \"Fish\":\"" + Fish@row + "\"}'"

    Resulting in ...

    '{"Animal":"Pig", "Book":"The West Wing", "Car":"Mustang", "Dog":"Doodle", "Earring":false, "Fish":"Snapper"}'

    Notice that I had to manually type out what would be the column names to get them to show up.

    Let me know if this works for you.

    Hope this helps!

    Heath Hilton

Answers

  • Heath Hilton
    Heath Hilton ✭✭
    Answer ✓
    Options

    rama,


    So this one may take a bit of work to get set up but once it is functional it may not be too bad. I created a list of six columns and then showed what the JSON would look like when it was done (hopefully I didn't mess up the formatting.



    The important thing the remember is that your quotes ( " ) are going to require you to put in a backslash to get it to show up in the cell properly. Here is the formula that I used to get this all to output what is shown.

    ="'{\"Animal\":\"" + Animal@row + "\", \"Book\":\"" + Book@row + "\", \"Car\":\"" + Car@row + "\", \"Dog\":\"" + Dog@row + "\", \"Earring\":" + Earring@row + ", \"Fish\":\"" + Fish@row + "\"}'"

    Resulting in ...

    '{"Animal":"Pig", "Book":"The West Wing", "Car":"Mustang", "Dog":"Doodle", "Earring":false, "Fish":"Snapper"}'

    Notice that I had to manually type out what would be the column names to get them to show up.

    Let me know if this works for you.

    Hope this helps!

    Heath Hilton

  • rama
    Options

    Thanks its helped me a lot

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!