Loading public JSON data into Google Sheets in 67 lines of code

Code snippet from repo shared below

I realized lately that I want to mess around with visualizations in Google Data Studio, but I want it to be with data that I understand. That way, I can more easily tell if there’s something wrong or needing adjustment.

Number of Universities per country, via public api data

So…i figured i’d write up a super quick method of getting data into Google Sheets from one of the public APIs available. I looked around on https://github.com/public-apis/public-apis and noticed there was University information available via https://github.com/Hipo/university-domains-list, and even better, the actual JSON file was available too:

https://raw.githubusercontent.com/Hipo/university-domains-list/master/world_universities_and_domains.json

Of course, this method would work with any JSON data, but having it already available in a public file definitely simplified things.

Here’s how I did it in just a few lines of code:

1. Grab the JSON data from the URL
2. Parse it using the built-in appscript helpers
3. Loop through the data, creating a “row” to be added to a Google Sheet for each domain
4. After a certain number of rows, write the results to the sheet to help avoid any memory limit issues.
5. Write any remaining rows to the sheet
6. That’s it!

Once the data is in there, it’s pretty straight forward to throw a quick datastudio dashboard together to do some digging & experimenting.

Example dashboard created using the sample university data

Repo code shared here:

usaussie/appscript-json-google-sheets

Hopefully this helps people easily get some sample data together without having to write a lot of code.