Google Sheets, Import JSON

JSON To Google Sheets: Free & Open-Source Method [Tutorial]

  1. By Meelad Mashaw

How to import JSON into Google Sheets

JSON is a standard protocol for sending data between apps.

In this tutorial, I’m going to show you how to import JSON data into a Google Sheet using a free, open source script available on Github.

Then, at the end, I’ll show you an even easier method of importing JSON data into a Google Sheet with more advanced features.

Part I.) Use The Import JSON Script

1. Open a Google Sheet

Open a new or existing Google Sheet.

  • Tip: You can go to sheets.new in your browser if you want to create a new Google Sheet.

2. Open Script Editor

In your Google sheet, in the menu at the top, click “Add-ons” > “Script Editor”.

Open Script Editor

3. In the script editor, remove the placeholder content

If there is any placeholder code typed into the script editor, you can remove it.

Remove placeholder code

4. Copy and paste an open-source script

Go to: https://gist.github.com/paulgambill/cacd19da95a1421d3164

Copy all of the code for “import_json_appsscript.js” and paste the code into the script editor of your Google Sheet.

This is a free, open-source script someone created to import JSON into a Google Sheet.

Copy the import json script
Paste the import json script

5. Save and name the script

After you copy and paste all of the script into the script editor, click the save button and name your project.

Click the save button
Click the save button
Name your project
Name your project

You can name the project anything you want, but naming it something like “ImportJSON” will make it easy to remember.

6. Go back to your Google Sheet

After saving your project in the script editor, you can now close the script editor tab in your browser and go back to your Google Sheet.

Now, in any cell of your Google Sheet, start typing “=import” (without the quotation marks) and you will see “ImportJSON” pop-up as an option. This is the script you saved in the script editor.

Click on the “ImportJSON” option.

Import JSON formula
Import JSON formula

7. Add your JSON API URL

After opening your Import JSON formula, you’ll need to add your JSON API URL.

If you want to use a free JSON API URL for testing, you can use one of these:

  • Chuck Norris Jokes API: https://api.chucknorris.io/jokes/random
  • Cat Facts API: https://cat-fact.herokuapp.com/facts
  • Star Wars API: https://swapi.co/api/people/1/

Enter your URL like this:

=ImportJSON("https://api.example.com")

There should be “quotes” around the URL and parentheses around the quotes.

In the URL is where you would specify your endpoint and add any other parameters to fetch the data you need.

8. Import your JSON data

Your last and final step to import JSON, after entering your JSON URL, is to your Google Sheet is to hit the enter button on your keyboard and wait for the JSON data to populate your spreadsheet.

Enter your JSON URL and hit Enter
Enter your JSON URL and hit Enter
JSON data imported into Google Sheets
JSON data imported into Google Sheets
Import JSON to Google Sheets demo

Part II.) Use the Apipheny Add-on

If you want to try an alternative method to import JSON data into your Google Sheet, you can use the add-on we created to do just that.

Apipheny is an API integrator for Google Sheets. It supports GET and POST requests to JSON and CSV APIs.

Apipheny add-on for Google Sheets
Apipheny add-on for Google Sheets

The difference between using the public script described in part 1 versus using the Apipheny add-on is that Apipheny comes with more functions and features than the script from Github does.

For example if you want to save your JSON data request, or you want to scheduling your request to refresh the data in your Google Sheet on a recurring basis, then you’ll need to modify the code in the script.

But with Apipheny, all of those features are included. With Apipheny, you can:

  • Call JSON and CSV APIs in your Google Sheet
  • Make GET and POST requests
  • Run multiple requests at the same time
  • Include the date/timestamp
  • Choose between overwriting existing data or appending the data
  • Choose which Sheet the API request is tied to
  • and much more!

Click here to read about all of Apipheny’s features, or check out my demo video:

Apipheny is free to try for 30 days and then it starts at $19/month.

Click here to install Apipheny.

If you want to check out some tutorials showing you how to connect various APIs to Google Sheets using the Apipheny add-on, we’ve created the following guides:

No Comments
TAGS :
Meelad Mashaw

Meelad Mashaw

Meelad is a data analyst living in the San Francisco Bay Area and Co-founder of the Apipheny API integrator for Google Sheets.

Related Posts

03 Apr

SurveyMonkey API to Google Sheets
SurveyMonkey API To Google Sheets: Import SurveyMo

01 May

Wikipedia API to Google Sheets: How To Import Wiki

14 Apr

Hubspot API to Google Sheets: How To Import Hubspo

Leave a Comment