3 ways to pull JSON data into a Google Spreadsheet
In my last article, I showed you 3 ways to import external data into Google Sheets.
In this article, we'll take a look at how to use a JSON API in Google Sheets and how to convert JSON data to Google Sheets.
JSON is one of the most popular API formats and there's an abundance of resources available to help you pull JSON into Google Sheets. This article serves as a summary of the available resources and a good jumping off point.
I've divided this guide into three sections:
- Code it yourself (using Google Apps Script or Python)
- Use a no-code tool (I cover 4 different tools)
- Hire a freelancer (like a data analyst or a developer)
Free JSON APIs for Testing
Some of the tools and recourses described below may require the use of an API URL. If you want to use a test JSON URL that doesn't require authorization, you can try one of these:
- Chuck Norris Jokes API:
https://api.chucknorris.io/jokes/random - Pokemon API:
https://pokeapi.co/api/v2/pokemon/1 - Click here to see a list of free public APIs you can use
Okay, let's dive in...
1.) Code a JSON Importer Yourself
The most popular method to import JSON data into Google Sheets is by using the Google Apps Script editor and copying and pasting a publicly available Import JSON script from Github, or writing your own code. Most of the top results on Google Search for this topic are a variation of these steps:
- Open a Google Sheet
- In the menu at the top of the Google Sheets, click Tools > Script Editor
- In the script editor, remove all the placeholder content so you can paste your own code in
- Copy and paste an open source script from Github.com such as this one or this one
- Click the save icon (or click File > Save)
- Enter any name for your project such as "
"ImportJSON - Go back to your Google Sheet
- In cell A1, start typing
and you'll see the function you created pop-up as an option. Click it.=IMPORTJSON - Enter your JSON API URL between the parentheses in the function, between a pair of quotes. eg.
=IMPORTJSON("https://cat-fact.herokuapp.com/facts") - Add any other parameters you want
- Hit enter and your JSON API data should populate your sheet
This script is completely free to use. The downside is that you're limited to what the script can do, unless you modify the code.
In it's current form, the Github script is best for one-off data pulls. If you'd like something with a user interface that can automate data imports and exports, keep reading.
Here's a video someone made showing the steps to use Google Apps Script:
An example of someone taking the public script and modifying it so that the data refreshes automatically can be seen here:
If you're interested in automating tasks at work, and dipping your toes into coding, then Google Apps Script is a great place to start. It has a great community around it; you can find an active Google Apps Script group on your social media of choice. And it's relatively easy to learn how to use it. Here's a beginner's guide to apps script by Ben Collins.
1b.) Use Python
Python is one of the world’s fastest and most popular programming languages used by a diverse range of professionals like data analysts, software engineers, and even accountants.
The difference between Python and Google Apps Script is that Google Apps Script is Google's own native programming language. Some people even supplement Google Apps Script with Python. There are several reasons you might choose to use Python:
- Python is a multi-purpose language which makes it usable across many industries
- You can solve problems without writing a lot of code
- It’s also a multi-platform tool, which means that you can use it on Mac, Windows, or Linux
- The syntax is user friendly
One of the most common uses for Python is creating code for a repetitive task to save you time. In this example, I'm going to show you how to use Python to import JSON data into Google Sheets. Here's the steps that you need to take (for Windows):
- Download the python application here:
https://www.python.org/downloads/ - Follow the instructions for installation (you can leave the defaults settings as-is)
- Once it's downloaded, you want to verify that the Python terminal was downloaded correctly. To do this, open your python application and find the version in the first line. It’s the number right after the word “Python” at the top-left corner of the window. If your interpreter matches the version you downloaded, you're verified.
- In your account of the software you are looking to pull API data from, find your API key and token. This is usually found in the developer section. Once you have your API key and token, move on to the next step
- Next you're going to install the request library to work with the requests in Python. Think of modules as a code template that will do part of the job for you. To do this use the command “install requests”.
- Once the request library is installed, use the command “import request” to send a request using the API URL and the command “
”. The API URL usually consists of the domain’s API URL, you API token, and parameters that you want to pull (ex. First_Name, Last_Name, Email, etc.).requests.get - Now you want to get the status code to see if the request was a success using the command “result.status_code”. If there is an error, use this dictionary to see why your request was not successful, and adjust your code accordingly.
- Lastly, you will use the command “result.json” to get the results of your API request in JSON format. Or if you would like to receive the results of your API data in text form, use the command “results.text”.
And then you’re done. You've successfully pulled JSON data from an API into Google Sheets using Python. As you can see, Python is relatively easy to use, but like with any programming language, there's a learning curve.
Here's a video I found on youtube of someone using Python with Google Sheets:
2.) Import JSON Using a No-Code Tool
A "no-code" tool is a great option for those who want to connect and import JSON data sources in a user-friendly way. A no-code tool is just software that takes something technical like connecting data sources and presents it in an interface.
Here's the 4 best no-code tools to connect a JSON API data sources to Google Sheets, in order of features and affordability:
2a.) Zapier
Zapier might be the most popular tool for connecting JSON data sources between different platforms. Zapier's an automation tool for connecting different apps together.
Zapier has thousands of integrations available for Google Sheets. Check that page to see if they have an integration available for your specific data source(s).
The downside to using Zapier is that it won't work if they don't currently have an integration with your data source, or if their integration is limited and doesn't do what you need it to do. Unless, you want to build your own Zapier integration.
Cost: Zapier is free to use as long as you only need to do 100 "tasks" per month or less. Any more tasks than 100/month and you'll need to move to a paid plan, which start at $19.99 usd/month.
For example, if you’re an e-commerce store that has more than 100 orders a month, and you want to connect your Shopify orders to Google Sheets, you will need to move to a paid plan.
Here's an example video someone made showing you how to connect a data source to Google Sheets using Zapier:
2b.) Apipheny
Apipheny.io is an API connector for Google Sheets. Apipheny is popular with data analysts and with people who have experience working with APIs.
With Apipheny, you can import JSON into Google Sheets in just a few clicks. All you have to do is open the add-on, then enter your API URL and headers/key into the add-on and then click "Run". Your JSON API data will automatically pull in your Google Sheet.
You can also save and schedule your API requests so the data refreshes on a recurring basis. There's a custom
=APIPHENY()Apipheny could be right for you if you want the ability to query any endpoint of an API instead of having to rely on pre-built integrations, and the ability to connect to almost any JSON or CSV data source, making it possible to connect to an unlimited number of data sources and make custom blends of data.
Cost: Apipheny is free to use with affordable paid plans.
2c.) Supermetrics
Supermetrics is a popular marketing reporting and automation tool for PPC, SEO, social and web analytics. Supermetrics has integrations with more than 70 different platforms. It's one of the go-to choices for agencies.
Supermetrics has a few different tools, and one of them is a Google Sheets specific add-on that you can install in the G Suite Marketplace.
Using Supermetrics, you can segment different marketing channels in multiple ways in Google Sheets then use your sheets skills to make reports that really stand out. They also have a Google Data Studio connector that allows you to connect major marketing platforms directly for reporting.
Cost: Supermetrics has a free 14-day trial and then goes to a paid plan which starts at $69 usd/month. There is a free plan but it only includes a Google Analytics integration.
2d.) Funnel
Funnel.io is an enterprise software that allows for automated data collection and marketing reports. Funnel has many integrations available - they have integrations with over 500 data sources.
Funnel has a lot of different features, and one of those features is the ability to export your Funnel data to Google Sheets. So what you would do is connect your data source to the Funnel software, and then export the data to Google Sheets.
With the Funnel software, you would be working outside of Google Sheets, like Zapier, instead of directly in Google Sheets as with Supermetrics or Apipheny.
In this tutorial, the Funnel team shows you step-by-step how to connect your Funnel data to your Google Sheet and then schedule it as a task to refresh daily.
Cost: Funnel starts at $499 usd/month and their pricing is based on your ad spend.
If you only need a JSON connector, and want to keep costs down, then Funnel and Supermetrics may not be the right choice unless you have a need for more of their features.
There's many other tools and alternatives to the tools mentioned above available depending on your needs.
3.) Hire A Freelancer
The third best option to connect JSON data sources to Sheets is simply to hire a data analyst to do it for you, or a developer to create a custom solution for you.
3a.) Hire a data analyst
A data analyst will either use a no-code tool such as the ones I covered in section 2 above, or they'll create a custom solution.
You can hire a freelance data analyst on a website such as Upwork or Toptal.
Upwork
On Upwork, you can post a job for free and start getting applicants almost immediately. It's free to post a job on Upwork but making it a featured job for $29.99 helps get more applicants.
I've been using Upwork for years now for a wide variety of projects and have been satisfied with the results. You have to interview and hire a freelancer yourself. It's a good option for small business owners.
Click here to see data analysts on Upwork and then just create an account and start a job post. Choose data analyst as the category.
Toptal
Toptal is another freelancer website but it's more exclusive. Toptal puts its applicants through a difficult hiring process and they claim to only hire the top 3% of freelance talent.
So if you want a true expert to help you out, Toptal might be the place to get it, but with that expertise comes a bigger price tag.
Click here to see data analysts on Toptal (if the popup shows, you can click the "No Thanks" button at the bottom).
Other
At Apipheny we have Google Sheets experts and developers for hire. Click here to learn more.
My friend and colleague, Nate Page, is a freelance data analyst who has experience working with JSON:
You could also look for a freelance data analyst on a website like Craigslist, Linkedin, etc.
3b.) Hire a developer
If you think the scope of your project calls for a custom solution with features that other software doesn't provide, or you just want to be in control of the product, you could always hire a developer to help you out.
You'll have the best luck if you specifically look for a Google Apps Script developer on a freelance site like Upwork or Fiverr.
The right developer for you will depend on the scope of your project. You'll have to send your project brief/details to freelancers and interview them to see who's the best fit.
Final Words
Importing JSON is easy with all the available resources these days.
The right option for you will depend on certain factors such as the scope of your project, your technical experience (whether you can/want to do it yourself), and your budget.
The most cost effective way to pull JSON into Google Sheets is to code it yourself or use a free, publicly available script, as we covered in section 1.
In section 2, we covered a range of products that have been purpose built to help people connect JSON API data sources with Google Sheets.
And in section 3, I gave you some resources for finding someone to help get it done for you.
If you want to use a free data visualizer to present your JSON data in pretty charts, you can connect your Google Sheets to Google Data Studio and even embed it on a website.
If you have any comments or questions, just let me know in the comments.