Hot answers tagged

3

You can build a custom parser for any fixed JSON string using regular expressions. For example, if you want to extract the child items into columns you can do the following (assuming your JSON string is in A1): =SPLIT(REGEXREPLACE(INDEX(REGEXEXTRACT(A1,"(\[(\n|\s|""|\w|,)+\])"),1),"(\[|\]|\n|\s|"")",""),",") You can adapt this formula to suit whatever ...


1

https://spreadsheets.google.com/feeds/list/[[your key here]]/od6/public/values?alt=json Source: http://blog.pamelafox.org/2013/06/exporting-google-spreadsheet-as-json.html The author doesn't explain it super well, but if you just swap out your key for hers in the example provided in the comments, that works.


1

Assuming that the value of rev2_1 always will have 5 characters use =mid(A1,find("rev2_1",A1)+10,5) Explanation find finds the position of the first character of rev2_1 mid gets a text (substring) of certain length (5) from a starting position, in this case the result of find plus 10 characters.


1

short formulas: =REGEXEXTRACT(A1;"_1""\: ""(.{5})") =REGEXEXTRACT(A1;"_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})") =REGEXEXTRACT(A1;"rev2_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.+)\"", ""rev2_2") =REGEXEXTRACT(A1;"rev2_1""\: (.+)\, ""rev2_2") =REGEXEXTRACT(A1;"_1""\: ""(.+)\"", ""rev2_2") =MID(A1;32;5) this will search for what is ...


1

There might be a shorter way, such as with string manipulation (e.g., MID) or regular expressions (REGEXEXTRACT), but this works: =1*trim(substitute(substitute(substitute(index(SPLIT(A1,":"),3),"rev2_2",""),CHAR(34),""),",",""))


Only top voted, non community-wiki answers of a minimum length are eligible