Re: Help splitting JSON to separate fields

374 0
Showing results for 
Search instead for 
Did you mean: 
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.

Community Manager
Community Manager

No Scripting required:

Longitude formula:

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


Latitude Formula:


{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