There is IMPORTXML function to import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
Is there any similar solution to import data in JSON format?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.Sign up to join this community
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/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content", "noInherit,noTruncate,rawHeaders")
Here is another example:
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.
You can use this add-on to import JSON API data into Google Sheets: https://gsuite.google.com/marketplace/app/api_connector/95804724197