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 and there's an abundance of resources available to help you pull into . This article serves as a summary of the available resources and a good jumping off point. most popular API formats JSON Google Sheets 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 1a.) Use Google Apps Script The most popular method to import JSON data into Google Sheets is by using the editor and copying and pasting a publicly available from , or writing your own code. Most of the top results on Google Search for are a variation of these steps: Google Apps Script Import JSON script Github this topic 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 or this one 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: Here's a link to the script used in the video. 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 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. by Ben Collins. Google Apps Script Here's a beginner's guide to apps script 1b.) Use 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. Python 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 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”. request library Once the request library is installed, use the command “ ” 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.). import request 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 to see why your request was not successful, and adjust your code accordingly. this dictionary 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 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 Zapier has . Check that page to see if they have an integration available for your specific data source(s). thousands of integrations available for Google Sheets 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 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. Cost: Zapier is free to use 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 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() Apipheny could be right for you if you want the ability to query 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. any Apipheny is free to use with affordable paid plans. Cost: Try Apipheny free for 30 days Apipheny Website 2c.) 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 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. 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. Cost: Supermetrics . Click here to read a Supermetrics review . Click here to visit Supermetrics' website 2d.) Funnel 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.io Funnel has a lot of different features, and one of those features is the ability to . So what you would do is connect your data source to the Funnel software, and then export the data to Google Sheets. export your Funnel 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. , 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. In this tutorial and their pricing is based on your ad spend. Cost: Funnel starts at $499 usd/month 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 , 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. Upwork 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. and then just create an account and start a job post. Choose data analyst as the category. Click here to see data analysts on Upwork 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. Toptal 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. (if the popup shows, you can click the "No Thanks" button at the bottom). Click here to see data analysts on Toptal 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: Nate's Upwork Profile Nate's Website/Portfolio 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. . Click here to learn more about hiring a developer 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 and even embed it on a website. Google Data Studio If you have any comments or questions, just let me know in the comments.
