Can Google Sheets Import JSON? Easy 2023 Guide | SSP (2024)

Importing JSON data is a powerful way to get data from a public data source. This can help you get large data sets in seconds, which you can use for storage, analysis, or even build upon.

JSON is one of the most commonly used API formats, which means there are abundant resources available that can help you pull data from these sources into Google Sheets.

Follow along with this Google Sheets Import JSON guide to learn what JSON files are and ways you can move them into Sheets with scripts and no-code tools.

SPECIAL OFFER: Revolutionize your workflow with Stackby, the all-in-one tool that combines the ease of spreadsheets, power of databases, and connectivity to thousands of apps, ready from day one. Get a discount on a lifetime subscription here.

How to Import JSON to Google Sheets

1. Go to Extensions >App Script
2. In the script window, clear everything so you can add your code.
3. Paste the script.
4. Write a name for the function.
5. Click on the Save icon.

What Is a JSON File?

JSON is an acronym that stands for JavaScript Object Notation, and it is a text format used to store and transport data. People mainly use it to send data in web apps, such as sending server data to the client to be displayed on a web page. The files are in a human-readable format, so you can just open the file and read the data.

Ways to Import JSON Into Google Sheets

There are essentially two ways to import JSON files to Google Sheets. The first method includes coding a JSON importer on your own, while the second involves importing JSON files using a pre-built tool.

Create a JSON Importer Using Google Apps Script

The most popular method used by many is to utilize the Google Apps Scriptfunctionality built into Google Sheets. This simply involves getting the code online or writing your own if you have the appropriate skills. Here are the steps you need to follow to use the Apps Script for a Google Sheets JSON Import:

1. Open a spreadsheet on Google Sheets.
2. Click on Extensions.
3. In the dropdown menu, click on Apps Script.

4. This will open a new page with some placeholder content. Clear everything so you can add your code.

5. Search for an open source script on Google and copy it. You can use thisas an example. However, feel free to find one that suits your needs.
6. Write a name for the function.
7. Click on the Savebutton.

1. To run the function in your Google Sheets, click on the Select functionbutton and select the ImportJSONfunction you created.
2. Open the tab containing your spreadsheet.
3. Start typing the ImportJSONinto your spreadsheet just like a formula. For example, you could type =ImportJSON(into cell A1.
4. After the opening bracket, type in the URL inside quotation marks. For example, the formula could look something like this:

=ImportJSON(“https://api.coinbase.com/v2/prices/ETH-USD/buy”)

• Press Enterto execute the formula.

You may need to wait for a few seconds depending on the size of the data and your internet connection speed. The script used in this example is completely free. However, you may find yourself limited by the functionality. You can add your own code as well because the script is open-source.

Import JSON to Google Sheets Using No-Code Tools

Using a no-code app is a great way to have Google spreadsheet import JSON data, which doesn’t require any coding knowledge and has a minimal setup. There are countless tools that allow you to do this, including Zapier, Supermetrics, Funnel.io, and Apipheny. To demonstrate, we will be using the Apipheny tool. Note that most of these no-code tools are paid and require a monthly or a yearly subscription.

Here are the steps you need to follow to set up Apipheny to import JSON to a spreadsheet:

1. Head over to the Google Workspace Marketplace page and type in the tool name you want to install for your spreadsheets (Apipheny in this case).
2. Click on the blue Installbutton.
3. In the window that shows up, click on Continue.
4. Another browser window will open up, prompting you to connect your Google account to the tool.
5. Click on Allowto grant the permissions.

Now that you’ve installed theadd-on in the Google Sheetsdirectory, here are the steps you need to follow to use a tool for Google Sheet import JSON to the spreadsheet:

1. Head over to the spreadsheet tab

1. Click on Extensionsin the top bar.
2. Click on Apipheny – API connectorand then on Enable Apipheny.
3. This will open the tool in a sidebar on the right side of the screen.

• Type in the URL in the API URL Path.
• Click on Runat the bottom of the sidebar.

This can take a few seconds, depending on the amount of data. Once the JSON data has been imported, feel free to close the add-on.

Difference Between Scripts and No-Code Tools

You might be wondering, what’s the difference between a no-code tool and getting open-source code from Github? Although both methods achieve the same results, no-code tools allow you to do this in a user-friendly way. It’s just software that does everything with a user interface while code doesn’t have an interface.

However, there are advantages to using code, especially if you can build your own, as you can customize the code to fit your specific needs. If you’re working in a large organization, instead of buying a tool, we recommend coding so you can learn more about building using Google Apps Script.

You can hire a developer or a freelancer to do this for you if you don’t feel comfortable creating the import JSON script.

Frequently Asked Questions

Can Google Sheets Import JSON Files?

Yes, Google Sheets can import JSON files, you can do it with Google Apps Script or third-party no-code apps like Zapier.

How Do I Export JSON Data to Google Sheets?

Unlike searching multiple criteria with INDEX MATCH, you may need to install a script into the Google Sheets using the Google Apps Script functionality. Once you install the script, you can use the script-specific formula to import the data from a JSON api URL into your spreadsheet.

How Do I Get JSON Data Into a Spreadsheet?

First, you may need to install the tool needed for importing JSON data into your Google spreadsheet. Click on Extensionsin the top bar and select the tool’s name. This will open a new window where you can enter the URL of the JSON data. Once done, click on the Runbutton to start the process. This will import the data into the spreadsheet within a few seconds.

Does Google Sheets Support JSON?

You can create a script or code one that allows you to import a JSON format file into Google Sheets. You can also opt to install a tool that provides you with a user interface and several other features, but it does come at a cost.

Wrapping Up the Import JSON Google Sheets Guide

So can Google Sheets Import JSON files easily? Although it might seem confusing at first, you can import JSON into Google Sheets by following the methods in the above guide.

The right way to open the files for you solely depends on your needs, like the size of the spreadsheet, your technical expertise, and how much time you wish to spend on the process. The most cost-effective way is to just use an open-source code or even code the script yourself.

However, this does take some time to set up. On the other hand, you can download a pre-built tool that saves you time. However, it does require you to have a monthly or a yearly subscription.

No matter which option you choose, the task performed is essentially the same. We hope this article helped you understand what a JSON file is and how you can import them into your spreadsheet. Similarly, we have a guide to importhtmlin Google Sheets that you’d find useful.

Related:

• How to Import CSV to Google Sheets
• How to Use the IMPORTRANGE Google Sheets Function
• How To IMPORTHTML in Google Sheets