Maker @ Apipheny.io, an API Integrator for Google Sheets. Based in SF Bay. Data Analytics Nerd.
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 crafted this guide after countless hours of research and have divided it into three sections:
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:
Okay, let's dive in...
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:
and you'll see the function you created pop-up as an option. Click it.
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:
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):
”. 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.).
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:
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.
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:
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.
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) $8/month, $59/year, or $119 for the lifetime deal.
Here's a Youtube demo I made showing you how to use Apipheny to get JSON data into your Google Sheet:
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.
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.
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.
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 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).
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.
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.