Convert Raw JSON (in Multi Text Field) to Table in Calculation

Hey everyone,

First off, a HUGE thanks to @jacquelynmay for her help identifying the regex required to parse out the tags in a multi text field and just leave raw JSON behind. As a result, I have an API integration that can just dump raw JSON into this field, and rather than muck around with it in the Automations tool, I can now have a calculation field that reads the raw JSON and converts it into a table. I wanted to share this example for a few reasons (mostly as an aid for those who are coming from Podio and Globiflow):

  1. If you have JSON, parsing it using JSON.parse (the equivalent of json_decode) gives you so many more abilities. I recommend to everyone that getting yourself to a JSON object will allow you to take advantage of other tools for data manipulation.

  2. Iโ€™m using the Lodash library (its tools are accessible using _. at the beginning of the statement) to do a for each and the code required looks so much more cleaner and readable.

  3. If you put your string in backticks rather than single or double quotes (something thatโ€™s impossible in GF) you can take advantage of the shorthand way of showing variables in the statement. Youโ€™ll notice Iโ€™m using ${key} and ${value} rather than having to break up the string and concatenate it with a bunch of plus symbols. Makes it MUCH easier to read.

This exact same code would work well in the Automations tool as well for converting JSON to a table for PDF purposes.

Screenshot 2023-11-22 at 8.14.45 AM

4 Likes

Thanks for sharing this advanced use case @andrew.cranston. We added it to the new calculation cheat sheet. :partying_face:

1 Like