# importJSON **URL:** \ **Category:** Ask for Help\ **Created:** [November 20, 2021, 6:16pm UTC](https://community.glideapps.com/t/importjson/34819 "2021-11-20T18:16:52Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [November 20, 2021, 6:16pm UTC](https://community.glideapps.com/t/importjson/34819/1 "2021-11-20T18:16:52Z")
**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 1. visit [https://gist.github.com/paulgambill/cacd19da95a1421d3164](https://gist.github.com/paulgambill/cacd19da95a1421d3164) 2. Copy the code on the screen. 3. Open the new sheet, click Tools \> Script editor. 4. Paste the script in the code.gs editor 5. 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!) 6. 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](https://blog.fastfedora.com/projects/import-json) , and [ImportJSON/ImportJSON.gs at master · bradjasper/ImportJSON · GitHub](https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs) 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/](http://www.yahoo.com/) from the Facebook Graph API, you could use: =ImportJSON(CONCATENATE(“[http://graph.facebook.com/](http://graph.facebook.com/)”, URLEncode(“[http://www.yahoo.com/](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/](http://graph.facebook.com/)”, URLEncode(“[http://www.yahoo.com/](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](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. 1. Visit [GitHub - public-apis/public-apis: A collective list of free APIs](https://github.com/public-apis/public-apis#index) 2. Look for “Government” \> [Data.parliament.uk](http://Data.parliament.uk) (Contains live datasets including information about petitions, bills, MP votes, attendance and more) 3. The link in 2 above is to [Dataset Explorer](https://explore.data.parliament.uk/?learnmore=Members) 4. Scroll down and find “Data formats”. We want this \> JSON e.g. [https://lda.data.parliament.uk/members.json](https://lda.data.parliament.uk/members.json) 5. 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 1. 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. 2. In cell A1 of Members Sheet insert following _=importjson(“[https://lda.data.parliament.uk/members.json","","noTruncate](https://lda.data.parliament.uk/members.json%22,%22%22,%22noTruncate)”)_ **Inspiration Quotes** For the 50 daily inspirational quotes, again: 1. Visit [GitHub - public-apis/public-apis: A collective list of free APIs](https://github.com/public-apis/public-apis#index) 2. Look for “Personality” \> Zen Quotes (Large collection of Zen quotes for inspiration) 3. The link in 2 above is [https://zenquotes.io/](https://zenquotes.io/). 4. Scroll down to “Sample Requests” \> [https://zenquotes.io/api/quotes](https://zenquotes.io/api/quotes) (generates 50 random quotes on each request) 5. In cell A1 of sheet ZenQuotes enter _=importjson(“[https://zenquotes.io/api/quotes","","noTruncate](https://zenquotes.io/api/quotes%22,%22%22,%22noTruncate)”)_ **Curated USA Headlines** 1. I will import top headlines from USA from NewsAPI ([https://newsapi.org/](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). 2. I am leaving the ‘query’ parameter empty to import all the paths. 3. Insert this into cell A1 of sheet named, say, “US Headlines”. _=importJSON(“[https://newsapi.org/v2/top-headlines?country=us&apiKey=mykeyhere","","noTruncate](https://newsapi.org/v2/top-headlines?country=us&apiKey=mykeyhere%22,%22%22,%22noTruncate)”)_ Magic result: [https://randomq.glide.page/](https://randomq.glide.page/) 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 ---
**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\ **Post date:** [November 20, 2021, 7:37pm UTC](https://community.glideapps.com/t/importjson/34819/2 "2021-11-20T19:37:26Z")
Hola @Wiz.Wazeer Good tip. There are other GS Add-Ons available able to do what you show, I have tested some them before and I’d recommend 2: - Data Connector: [https://dataconnector.app/](https://dataconnector.app/) - Apipheny: [https://apipheny.io/](https://apipheny.io/) They support a kind of custom formula (e.g. =Apipheny()) able to see a cell’s value and fire an API call if it has changed. Also, they can save and schedule API requests, great tool! Saludos ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [November 20, 2021, 8:32pm UTC](https://community.glideapps.com/t/importjson/34819/3 "2021-11-20T20:32:54Z")
Thank you for this 👍👍👍 ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [November 28, 2021, 1:42am UTC](https://community.glideapps.com/t/importjson/34819/4 "2021-11-28T01:42:40Z")
Could someone please help test the IP address plugin on this pages site. I can see mine, but just wanted to confirm it’s working for others. > **[importJSON](https://randomq.glide.page/)** Thank you ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [November 28, 2021, 2:37am UTC](https://community.glideapps.com/t/importjson/34819/5 "2021-11-28T02:37:41Z")
It gives the correct result for me. ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [November 28, 2021, 2:41am UTC](https://community.glideapps.com/t/importjson/34819/6 "2021-11-28T02:41:48Z")
@Darren_Murphy Really appreciate it1 ---
**Author:** ![SantiagoPerez](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/santiagoperez/32/16807_2.png) [@SantiagoPerez](https://community.glideapps.com/u/SantiagoPerez)\ **Post date:** [November 28, 2021, 2:43am UTC](https://community.glideapps.com/t/importjson/34819/7 "2021-11-28T02:43:13Z")
Correct for me too. ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [November 28, 2021, 2:44am UTC](https://community.glideapps.com/t/importjson/34819/8 "2021-11-28T02:44:29Z")
@SantiagoPerez Again, really appreciate it…so it is working…! ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [November 29, 2021, 7:10pm UTC](https://community.glideapps.com/t/importjson/34819/9 "2021-11-29T19:10:48Z")
Wiz, can u message me please.