Parse JSON using Regex in Google Sheet

Question

I try to parse JSON data using Regex. I want to get values of "name", "street", "lat" and "lon". This formula to get their values:

get name :

=REGEXEXTRACT(REGEXREPLACE(JSON!$A$1,char(10)&"|"&char(34)&"|\s{2,}",""),"name:(.*?),street")
get street :

=REGEXEXTRACT(REGEXREPLACE(JSON!$A$1,char(10)&"|"&char(34)&"|\s{2,}",""),"street:(.*?)},location")
get lan and lon:

=REGEXEXTRACT(REGEXREPLACE(JSON!$A$1,char(10)&"|"&char(34)&"|\s{2,}",""),"lat:(.*?),lon")

=REGEXEXTRACT(REGEXREPLACE(JSON!$A$1,char(10)&"|"&char(34)&"|\s{2,}",""),"lon:(.*?)},city")

My problem: i don't know to get the next data in column E until column L.

My sheet: https://docs.google.com/spreadsheets/d/1QaCT0X8iw6OjfzrPXPTc-r_HNscHIOQl5N9BBJjaaaY/edit#gid=1163290419

When the values are retrieved from JSON object, I thought that to use the custom function created by Google Apps Script might be suitable. So I proposed it. But if that was not the direction you expect, I apologize. — Tanaike
– Tanaike, Commented Dec 16, 2020 at 0:10

Tanaike · Accepted Answer · 2020-12-16 00:06:41Z

1

In your situation, I thought that to use a custom function created by Google Apps Script might be suitable. So in this answer, I would like to propose to achieve your goal using the custom function.

Sample script:

Please copy and paste the following script to the script editor of the Google Spreadsheet, and please put =SAMPLE((JSON!$A1)) to a cell. By this, each value are parsed and put to each cell of a row.

const SAMPLE = value => [JSON.parse(value).result.flatMap(({place_info: {name, street}, location: {lat, lon}}) => [name, street, lat, lon])];

Result:

Note:

In your sample Spreadsheet, there are 2 name2,street2,lat2,lon2. I thought that 2nd one might be name3,street3,lat3,lon3.

References:

answered Dec 16, 2020 at 0:06

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d'natsu Over a year ago

awesome, thanks a lot. i think that is so complicated if i use regex to parse the json data.

Tanaike Over a year ago

@d'natsu Thank you for replying. I'm glad your issue was resolved. Thank you, too.

Collectives™ on Stack Overflow

Parse JSON using Regex in Google Sheet

1 Answer 1

Sample script:

Result:

Note:

References:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Result:

Note:

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related