Help

Help splitting JSON to separate fields

Topic Labels: Automations
147 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Jbro40
4 - Data Explorer
4 - Data Explorer

Hello and thanks in advance.  I have form submissions coming into Airtable via a Zap from Gravity Forms.  The problematic field is Location which contains lat, lng, and address. I need lat and lng in separate fields in airtable.  This is an example of the imported data: 

{"location":{"lng":-122.38418565202375,"lat":47.653449368341484},"address":"Magnolia Connector Trail, Seattle, WA 98199, United States"}

I need to wind up with 122.38418565202375 and 47.653449368341484 in separate fields. I've created 2 new columns in my table with the names lng and lat and installed the scripting extension. What do I do next?  Also I would like to process all the existing records at once and then process all new entries one by one manually when they come in.

Thank you so much!

 

2 Replies 2

I don't know Javascript, but I would highly recommend using Make's automations instead of Zapier for integrating external apps (such as Gravity Forms) with Airtable. Make has a Parse JSON module that will automatically parse the JSON for you, and then you can automatically put the right data into the right fields in Airtable.

Dan_Montoya
Community Manager
Community Manager

No Scripting required:

Longitude formula:

MID(
{Location Data},
FIND("\"lng\":", {Location Data}) + 6,
FIND(",", {Location Data}, FIND("\"lng\":", {Location Data})) - FIND("\"lng\":", {Location Data}) - 6
)

 

Latitude Formula:

 

MID(
{Location Data},
FIND("\"lat\":", {Location Data}) + 6,
FIND(",", {Location Data}, FIND("\"lat\":", {Location Data})) - FIND("\"lat\":", {Location Data}) - 6
)Screenshot 2024-01-24 at 7.59.54 PM.png