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.
Open a new or existing Google Sheet.
In your Google sheet, in the menu at the top, click Extensions > Apps Script
If there is any placeholder code typed into the script editor, you can remove it.
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.
There’s also other free importjson scripts available online if you do a search for them.
After you copy and paste all of the script into the script editor, click the save button and name your project.
You can name the project anything you want, but naming it something like “ImportJSON” will make it easy to remember.
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.
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:
Enter your URL like this:
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.
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.
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.
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:
Make a GET request
Make a POST request
PUT, PATCH, & DELETE Requests
Save requests for later
Schedule requests for automatic updates
Reference cell values in requests
=APIPHENY custom function
Stack multiple URLs in a single request
Run all saved requests at once
Modify your request settings
Import & export saved API settings
Alpha Vantage API
Constant Contact API
Facebook Graph API
Facebook Ads API
Google SERP API
Linkedin Ads API
Product Hunt API
Reddit Ads API
Riot Games API