AN ALTERNATIVE TO FETCH JSON PLUGIN
For those of us not too familiar with all the technical mumbo jumbo surrounding Glide’s fetchJSON plugin, here is a real no code solution for importing JSON to your Google Sheet and presenting the fetched results in your Glide apps/Pages.
This will not work with Glide Tables. There are a couple of workarounds but we are not interested in those for this exercise. We are keeping things simple.
What I am sharing here with you has already been covered other threads with different use cases in mind). The script I am sharing with you will fetch JSON data from any API (paid/unpaid, public/private).
Our use case
I have a Pages site, and I want:
- curated headlines from the US.
- list of some of the members of House of Commons in the UK.
- 50 daily random inspirational quotes
I don’t have the time to search for these things and add them to my Google Sheet. I need to import a lot of data from several APIs that return data in JSON format. Try doing all of this with Glide’s Fetch JSON Plugin. Not in any way undermining it. Very grateful for it, but the task is fraught with difficulties if you are a no-coder. However, there is an amazing alternative.
What is importJSON?
Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in the JSON feed. The remaining rows contain the data.
Installing ImportJSON script
- visit https://gist.github.com/paulgambill/cacd19da95a1421d3164
- Copy the code on the screen.
- Open the new sheet, click Tools > Script editor.
- Paste the script in the code.gs editor
- Rename the script from code.gs to ImportJSON.gs. Save the file and move back to the sheet. No need to set triggers (the best bit about it!)
- Once the script is saved, we are ready to import some data from any API that returns JSON data. Complete doc on this script is maintained at https://blog.fastfedora.com/projects/import-json , and ImportJSON/ImportJSON.gs at master · bradjasper/ImportJSON · GitHub
Things You Need To Know:
“ImportJSON takes 3 parameters:
url
The URL to a JSON feed.
query
A comma-separated list of paths to import. Any path starting with one of these paths gets imported.
parseOptions
A comma-separated list of options that alter processing of the data. By default, data gets transformed so it looks more like a normal data import. Specifically: Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values of the rows representing their parent elements. Values longer than 256 characters get truncated. Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case. To change this behaviour, pass in one of these values in the parseOptions parameter:
noInherit
Don’t inherit values from parent elements
noTruncate
Don’t truncate values
rawHeaders
Don’t prettify headers
noHeaders
Don’t include headers, only the data
debugLocation
Prepend each value with the row & column it belongs in
For example, to return all the number of shares and comments for the URL http://www.yahoo.com/ from the Facebook Graph API, you could use:
=ImportJSON(CONCATENATE(“http://graph.facebook.com/”, URLEncode(“http://www.yahoo.com/”)), “”, “”)
If you wanted to get rid of the headers, you would add a “noHeaders” to the last parameter.
=ImportJSON(CONCATENATE(“http://graph.facebook.com/”, URLEncode(“http://www.yahoo.com/”)), “”, “noHeaders”)
As an advanced example, if you wanted to query YouTube for the most popular videos, but only see the data returned relating to the ‘title’ and the ‘content’, you could use:
=ImportJSON(“http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json”, “/feed/entry/title,/feed/entry/content”, “noInherit,noTruncate,rawHeaders”)
The “rawHeaders” allows us to see the full path to each column of data in the spreadsheet.”
NO TRUNCATE
This I believe is the one that we will ever need. Since values longer than 256 are truncated, I need to change behaviour to “noTruncate”.
List of some of the members of House of Commons in the UK
For this I will be using a public API. I am grabbing only a few as I am limited to 100 rows on the free tier of Pages.
- Visit GitHub - public-apis/public-apis: A collective list of free APIs
- Look for “Government” > Data.parliament.uk (Contains live datasets including information about petitions, bills, MP votes, attendance and more)
- The link in 2 above is to Dataset Explorer
- Scroll down and find “Data formats”. We want this > JSON e.g. https://lda.data.parliament.uk/members.json
- Replace “members” in the URL with any of the following search paths:
API
commonsmembers All Commons Members
lordsmembers All Lords Members
members All Members
members/{id} Member by ID
commonsregisteredinterests Returns Commons Financial Interests
lordsregisteredinterests Returns Lords Financial Interests
lordsregisteredinterests?member={id} Returns Lords Financial Interests By MemberId
- I just want members, so I am not replacing anything (you can though). NOTE: Data is too old and not all links are correct. But that’s not the fault of the Script, but API.
- In cell A1 of Members Sheet insert following
=importjson(“https://lda.data.parliament.uk/members.json","","noTruncate”)
Inspiration Quotes
For the 50 daily inspirational quotes, again:
- Visit GitHub - public-apis/public-apis: A collective list of free APIs
- Look for “Personality” > Zen Quotes (Large collection of Zen quotes for inspiration)
- The link in 2 above is https://zenquotes.io/.
- Scroll down to “Sample Requests” > https://zenquotes.io/api/quotes (generates 50 random quotes on each request)
- In cell A1 of sheet ZenQuotes enter
=importjson(“https://zenquotes.io/api/quotes","","noTruncate”)
Curated USA Headlines
- I will import top headlines from USA from NewsAPI (https://newsapi.org/) . Just sign up to get your API (free for developers but you will need to pay once you switch from developer/staging mode to production mode).
- I am leaving the ‘query’ parameter empty to import all the paths.
- Insert this into cell A1 of sheet named, say, “US Headlines”.
=importJSON(“https://newsapi.org/v2/top-headlines?country=us&apiKey=mykeyhere","","noTruncate”)
Magic result:
What else did I learn – next time I am struggling with Jquery paths, all I need to do is an importJSON, check the headers in the sheet, replace uppercase with lowercase (but not always the case) in my fetchJSON plugin.
Thank you
