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
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.
Learn more about the product here
Solution 2: Write Custom App Scripts (or Use a Code From GitHub)
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”
Step 2: Pick a JSON parser snippet on Github
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
Step 4: Head back to your Spreadsheet and use the function exactly as you named it.
Done!
This is the final output where I fetch some data from Rick and Morty API (https://rickandmortyapi.com/api/character/108)
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.
| Solution | Time required | Pros | Cons | Type |
| Import JSON plugin | < 5 min | Easy | Freemium | External API |
| GitHub snippet / Apps Script | 10 – 30 mins | Always free | Time-consuming | External API |
| No Code Tools | 5 – 20 mins | Easy | Paid feature | External API |
| JSON to CSV conversion | < 5 minutes | Easy, Quick | None | Local 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:
- 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.
- 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.
- 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.
- 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.
- 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!