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:
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):
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.).
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:
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.
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:
Note: I'm the co-founder of Apipheny.
Apipheny.io is an API integrator add-on 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 parse in your Google Sheet.
Apipheny Google Sheets add-on
You can make unlimited requests (within Google's limits) and also save and schedule your API requests, make GET and POST requests, and stack multiple API URLs in the same request. There's a custom
function and the ability to reference the value of a cell in your API URL, headers, or POST body. All without leaving your Google Sheet.
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 try for 30 days and then usd $29/month afterwards (or $228 if you pay for a year which comes out to $19/month.)
Here's a Youtube demo I made showing you how to use Apipheny to get JSON data into your Google Sheet:
In the video I show you how to enter an API URL with your parameters into Apipheny, as well as headers if you have any, and then just click "Run" and JSON data will automatically import in to your Google Sheet. Use it to connect to unlimited data sources.
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 $99 usd/month. There is a free plan but it only includes a Google Analytics integration.
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.
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.
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.
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.
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.