What is JSON?
JSON stands for “JavaScript Object Notation”. It is a way to represent data in a simple, easy-to-read format that can be easily used by many programming languages.
Imagine you have a big bag of toys and you want to organize them. You could use different bags to put each kind of toy in a specific bag, like dolls in one bag, cars in another bag, and so on. In the same way, JSON helps organize data by putting similar information together.
JSON files are like little bags of data that contain information about things like people, animals, or objects. The information inside a JSON file is organized into “key-value” pairs, which means that each piece of information has a name (the “key”) and a value.
For example, if we wanted to store information about a webshop, we might have a JSON file that looks like this:
{
“shoes”: {
“sneakers”: {
“colour”: “blue”,
“brand”: “Nike”,
“price”: 125
},
“trainers”: {
“colour”: “white”,
“brand”: “Puma”,
“price”: 115
},
“running_shoes”: {
“colour”: “yellow”,
“brand”: “Asics”,
“price”: 145
}
}}
This JSON file contains information about the shoe section of a web shop. The first “key” is the category “shoes.” After that, the second “keys” store the information on the types of shoes.
JSON files are useful because they are easy to read and write, and they can be used by many different programming languages. They are often used to store and share data between different applications or websites. When you want to import or export data from an API, it’s often done in JSON format.
How to import a JSON into Google Sheets
Creating the result that you’re looking for can be quite challenging. If you prefer to have the help of one of our expert consultants to complete your project, click here.
To start, create a new Google Sheets by typing sheets.new in your browser and give it a name. Open the Google Apps Script Integrated development environment (IDE) by clicking on “Extensions” and “Apps Script”. A new tab will open that looks like this.
Remove the three lines of code that are there, give the project a name, and copy and paste the following code into this space.
/**
———————————————————————————–
———————————————————————————–
— Prj.Name : Import json to google sheets
— Created By : [email protected]
— Date : 2023-04-23
— Description: A script to import data from a JSON file hosted on Google Drive
— : into a Google Sheets.
— Version # : 1.0
— Last Update: Null
———————————————————————————–
———————————————————————————–
* @param {string} fileId – ID of the json file in google drive.
* @param {string} sheetName – Sheet name to import json.
*/
function getJson(
fileId = “139GrQu0oEu1tWAMz5vHMjzmcClxA0FQ9”, // Get the ID of the JSON file.
sheetName = “Sheet1” // Name of the sheet to import json
) {
var files = DriveApp.getFileById(fileId); // Specify which JSON to get.
var json = JSON.parse(files.getBlob().getDataAsString()); //Get the JSON information which now is a huge piece of text, and parse it to JSON format.
var headers = [“category”, “item”, “colour”, “brand”, “price”]; // Define the headers for each column. This will be inserted in row 1.
let values = [] // This is an empty Javascript array that you’re going to fill in line 27.
//You’re nesting the for loops because the json file is cascading down.
for (let category in json) {
for (let item in json[category]) {
values.push([category, item, json[category][item][‘colour’], json[category][item][‘brand’], json[category][item][‘price’]])
}
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var newSheet = ss.getSheetByName(sheetName);
var dataToInsert = [headers].concat(values)// Here you push the headers into the first row and create an array.
var range = newSheet.getRange(1, 1, dataToInsert.length, dataToInsert[0].length) // get a range of cells (row, column, numRows, numColumns)
range.setValues(dataToInsert); // set the values to the range of cells
}
Running a test
In order to test out the script for the first run, click here to make a copy of the JSON file used for this example.
Getting the file ID
Now we have to get the file ID of your JSON file. Upload it to Google Drive, right-click on it, select “share” and click on “copy link.” Paste this link somewhere and copy the ID that comes between “https://drive.google.com/file/d/” and “/view?usp=sharing” Paste this file ID between the double quotes of line 18.
Running the script
Now that the correct file ID is in place and the script knows which file to grab, let’s run this script for the first time. Click on “Save project” and afterwards click on “Run”.
Now Google will ask you for permission to run the script.
Click on “Review permissions” and afterwards on “Allow”.
You might need to click one more time on “Run”. Otherwise, you should see that the execution has started and ended. And if we go to the spreadsheet we should now see this result. Having imported a JSON file is a nice result to see. You have arrived here because you want to import your JSON file. So now that the script is in place and working, let’s modify it for your JSON file.
Modifying the “key-values” for your file
Finding the positions of your key values.
For this example we have used five keys, “category”, “item”, “color”, “brand” and “price”. In order to modify the script to work for your JSON file, double-click on the JSON file inside Google Drive to see the preview, and compare the keys in the example with your JSON file. Take note of which key in the example matches the position of the key in your file.
What if your JSON file has more than two cascades?
A JSON file has “cascades”, which are the indentations the values are written in. This is important to know because the script is written for a JSON file with only two cascades, but perhaps yours will have more than two.
In line 28 and 29 of the script, you can modify how many cascades you’d like to look into. Because this JSON file has one main cascade with the second cascade being “items” we only use two “for loops”. If your JSON file has more than two cascading down, you will have to change this:
for (let category in json) {
for (let item in json[category]) {
values.push(… )
}
}
To for example:
for (let cascade1 in json) {
for (let cascade2 in json[cascade1]) {
for (let cascade3 in json[cascade1][cascade2]) {
values.push(… )
}
}} //Note the extra curly bracket for the added for loop.
What if your JSON file has more than four categories?
Another thing to highlight about the script is that in line 30 you see this:
values.push([category, item, json[category][item][‘colour’], json[category][item][‘brand’], json[category][item][‘price’]])
We’re pushing these values as rows into an empty array and each value that is being pushed into this array is separated with a comma. As you can see, this JSON file holds one category, “shoes” and there are three items, “sneakers”, “trainers” and “running_shoes”. For each of these items there are three values, “color”, “brand” and “price”.
This is the reason why the notation of the values in this script is this way. Let’s break it down quickly so you can modify it better for your JSON file.
- The first value is plain and simple.
‘category’. - As is the second value.
‘item’. - But with the third value we have to let the script know it has to look inside the Javascript object of ‘item’ and return the value of ‘color’. For this reason the third value is written like this:
json[category][item][‘colour’]. - Same with the fourth item. We look into the Javascript object of ‘item’ and return ‘brand’.
json[category][item][‘brand’] - Lastly, with the fifth item, we stay in the object of ‘item’ and return ‘price’.
json[category][item][‘price’]
Changing the values in the script.
To change the values in the script, go back to it and hit CTRL+F and Click on the drop down arrow to expand the search menu.
Type in your key name you want to replace in the bar below it and hit this icon to replace all instances in the script. Do this for each key name you’d like to import into the Google Sheets.
Import JSON to Google Sheets
Lastly, take your JSON file and hold it next to line 30 of the script. Are all the value notations in the values.push( … ) method telling the script to look in the right places? If you think so, go for it. Click on “Run” and see which result is created. Do you get an error message? No problem, that is part of the process. Check back in line 28 to 32 if you’ll have to tweak something. Programming is all about making slight changes, testing, changing again till the script does what you want.
If you’ve made it this far in the article, congratulations! You’re doing some highly technical work or at least working it out by yourself. Regardless of the results you’re creating, that should be respected. If you’re just not able to get it right, no worries. Reach out to us, let us know the result you’re trying to create for your project and our expert consultant can help you out.
