Import JSON file to Google Sheets (Easy Step-by-Step Guide)

This article aims to give you a detailed guide on importing JSON-type files to Google Sheets.

Below are four methods that will help you with importing data from JSON.

It’s important to note, however, that which method is suitable for your use case will depend on what it is exactly you need to be done.

You should select different ones when trying to import local JSON files into Google Sheets than if trying to fetch external API data.

How to Import JSON to Google Sheets – 4 Solutions That Work For Me

Solution 1: Use Import JSON data Google Workspace Extension

Screenshot showing the Import JSON extension homepage

I’m listing it as the number one option for a few reasons.

It takes no time to install the extension and it’s super easy to use (just type =IMPORTJSON(your_json_url) ). After you do that your data will be fetched immediately into a 2-dimensional array.

I’m pretty sure that it will be more than enough for most people who just want to fetch the JSON data quickly and focus on processing it rather than creating a solution on their own.

The only downside to this solution is that it’s a freemium product. The free version offers you 5 requests per day, if you want more you probably need to buy a Basic plan.

Download the extension here

Learn more about the product here

Solution 2: Write Custom App Scripts (or Use a Code From GitHub)

The screenshot shows the GitHub repository page for import JSON code snippet written in javascript

If the previously mentioned solution does not exactly fit your needs for some reason (i.e. you need more than 5 requests per day or/and you don’t want to pay for the solution) you have other options.

Fortunately, you don’t need to be a coding expert in order to take advantage of this solution. You can look for a code on GitHub (where developers share their code with others) and just implement it into your project.

The advantage of this solution is that it offers a customizable, no-cost option.

The drawback? It may take some effort to implement it correctly.

Let me give you a hand and make the implementation process easier! I’ve crafted an easy-to-follow guide on how to use GitHub snippets in your spreadsheets.

Step 1: From the Options Menu pick Extensions and then click on the “Apps script”

The screenshot shows how to access apps script item from the options menu

Step 2: Pick a JSON parser snippet on Github

The screenshot shows the GitHub repository page for import JSON code snippet written in javascript

I found these 3 to be the best for me so you can pick one of them:

Step 3: Copy-paste the code, name your function, and SAVE the code

The screenshot shows where to paste the code snippet, how to change the file name, and where to save the code for future use

Step 4: Head back to your Spreadsheet and use the function exactly as you named it.

The screenshot shows how to use newly created custom function in google sheets

Done!

This is the final output where I fetch some data from Rick and Morty API (https://rickandmortyapi.com/api/character/108)

The gif shows the import JSON function in action. User types in the URL where the data will be fetched and the data is present in our spreadsheets a few seconds after

Solution 3: Use a No-Code Tool

The two solutions listed above should be enough to deal with the problem however I just want sure that we went through all the options.

Moreover, you may already have a no-code tool implemented for different purposes so adding an extra pipeline will be just a matter of seconds.

If you don’t then you may check the most popular ones such as Zapier, Tray.io, Funnel.io or Supermetrics

The downside of utilizing no-code tools is that they are usually paid services from the start, so I would not advise them as the sole option for importing JSON files.

Solution 4: Convert JSON to CSV and import it from the Options Menu

Last but not least – if you need to import local JSON files to your Google Spreadsheets I have a solution for you too.

Since you can’t import JSON directly you will need to change the output to CSV. The easiest way is to do it through an online tool. You can try this or this (doesn’t really matter as they all are similar).

Once you have the CSV file you are ready to go. Make sure to check out this article if you are wondering how to import CSV data in Google Sheets but in a most basic scenario it shouldn’t be more than just uploading the CSV into your working sheet.

Comparison Table

Let’s take a step back and assess all four methods from an eagle-eye view. This is likely the most effortless way to determine which solution best suits you if you are still undecided at this point.

SolutionTime requiredProsConsType
Import JSON plugin< 5 minEasyFreemiumExternal API
GitHub snippet /
Apps Script
10 – 30 minsAlways freeTime-consumingExternal API
No Code Tools5 – 20 minsEasyPaid featureExternal API
JSON to CSV
conversion
< 5 minutesEasy, QuickNoneLocal JSON file

Further Reading

If you’d like to learn more about working with data in Google Sheets, here are some additional articles to explore:

  1. DetectLanguage Google Sheet Function Learn how to use the DetectLanguage function in Google Sheets to identify the language of text data, which is helpful when importing JSON files with text in various languages.
  2. Filter Data in Google Sheets Dive into filtering data in Google Sheets, a useful skill when organizing and analyzing the data imported from JSON files.
  3. Share and Collaborate in Google Sheets Find out how to share and collaborate on Google Sheets, a valuable feature for users who import JSON files and want to work together with others on the imported data.
  4. Translate Languages in Google Sheets Discover how to translate languages in Google Sheets, which can be especially useful when working with JSON files containing data in multiple languages.
  5. Insert Current Date in Google Sheets Learn how to insert the current date in your Google Sheet, a helpful feature for users who want to track when JSON data was imported or updated.

My Final Thoughts

So is it possible to import JSON data into Google Sheets? Yes, it is! There are many different ways to do so and it all depends on what you need, what type of data you need to fetch, and how often the data will be updated in your sheet.

Hopefully, after reading this article you won’t have any issues dealing with JSON files again. But if you do then feel free to drop me a note and I’ll do my best to help out!

Leave a Reply

Your email address will not be published. Required fields are marked *