We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

16

Currently there is no any Add-on which offer this functionality, so you have to create or import custom functions by Script Editor available in Tools menu (for Spreadsheet). Then re-paste the content of ImportJSON.gs from bradjasper's GitHub page and save it. After that, you can use ImportJSON function, for example: =ImportJSON("http://gdata.youtube.com/...


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

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

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),""),",",""))


1

If you want to paste a JSON object you can use http://www.convertcsv.com/json-to-csv.htm to convert it to a CSV and then import that into Google Sheets. If you want to rotate the data from columns to rows you can use http://www.convertcsv.com/transpose-csv.htm I found this easier than messing with an addon script.


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.


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