{"id":42359,"date":"2023-05-16T21:26:35","date_gmt":"2023-05-16T19:26:35","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=42359"},"modified":"2025-12-17T18:45:24","modified_gmt":"2025-12-17T17:45:24","slug":"importar-json-a-google-sheets","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/google-sheets-features\/import-json-to-google-sheets\/","title":{"rendered":"Importar JSON a Google Sheets"},"content":{"rendered":"

[et_pb_section fb_built=”1″ _builder_version=”4.20.2″ _module_preset=”default” da_disable_devices=”off|off|off” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on”][et_pb_row _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text module_id=”what-is-json” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

What is JSON?<\/b><\/h1>\n

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.<\/span><\/p>\n

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.<\/span><\/p>\n

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.<\/span><\/p>\n

For example, if we wanted to store information about a webshop, we might have a JSON file that looks like this:<\/span><\/p>\n

[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

{<\/span><\/p>\n

“shoes”<\/span>:<\/span> {<\/span><\/p>\n

\u00a0\u00a0<\/span>“sneakers”<\/span>:<\/span> {<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0<\/span>“colour”<\/span>:<\/span> “blue”<\/span>,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>“brand”<\/span>:<\/span> “Nike”<\/span>,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>“price”<\/span>:<\/span> 125<\/span><\/p>\n

\u00a0\u00a0<\/span>},<\/span><\/p>\n

\u00a0\u00a0<\/span>“trainers”<\/span>:<\/span> {<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0<\/span>“colour”<\/span>:<\/span> “white”<\/span>,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>“brand”<\/span>:<\/span> “Puma”<\/span>,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>“price”<\/span>:<\/span> 115<\/span><\/p>\n

\u00a0\u00a0<\/span>},<\/span><\/p>\n

\u00a0\u00a0<\/span>“running_shoes”<\/span>:<\/span> {<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0<\/span>“colour”<\/span>:<\/span> “yellow”<\/span>,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>“brand”<\/span>:<\/span> “Asics”<\/span>,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/span>“price”<\/span>:<\/span> 145<\/span><\/p>\n

\u00a0\u00a0<\/span>}<\/span><\/p>\n

}}<\/span><\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

This JSON file contains information about the shoe section of a web shop. The first \u201ckey\u201d is the category \u201cshoes.\u201d <\/span>After that,<\/span> the second \u201ckeys\u201d store the information on the types of shoes.<\/span><\/p>\n

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 <\/span>API,<\/span> it\u2019s often done in JSON format.<\/span><\/p>\n

[\/et_pb_text][et_pb_text module_id=”how-to-import-json” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

How to import a JSON into Google Sheets<\/b><\/h1>\n

Creating the result that you\u2019re looking for can be quite challenging. <\/span><\/i>If you prefer to have the help of one of our expert consultants to complete your project, <\/span><\/i>click here<\/span><\/i><\/a>.\u00a0<\/span><\/i><\/p>\n

To start,<\/span> create a new Google Sheets by typing <\/span>sheets.new<\/span><\/a> in your browser and give it a name. Open the Google Apps Script Integrated development environment (IDE) by clicking on \u201cExtensions\u201d and \u201cApps Script”. A new tab will open that looks like this.<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-10.png” alt=”import json to gs 10″ title_text=”import json to gs-10″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Remove the three lines of code that are there, give the project a name, and copy and paste the following code into this space.<\/span><\/p>\n

[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.27.4″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]function getJson(<\/p>\n

fileId = “139GrQu0oEu1tWAMz5vHMjzmcClxA0FQ9”, \/\/ Get the ID of the JSON file.<\/p>\n

sheetName = “Sheet1” \/\/ Name of the sheet to import json<\/p>\n

) {<\/p>\n

var files = DriveApp.getFileById(fileId); \/\/ Specify which JSON to get.<\/p>\n

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.<\/p>\n

var headers = [“category”, “item”, “colour”, “brand”, “price”]; \/\/ Define the headers for each column. This will be inserted in row 1.<\/p>\n

let values = [] \/\/ This is an empty Javascript array that you’re going to fill in line 27.<\/p>\n

 <\/p>\n

\/\/You’re nesting the for loops because the json file is cascading down.<\/p>\n

for (let category in json) {<\/p>\n

for (let item in json[category]) {<\/p>\n

values.push([category, item, json[category][item][‘colour’], json[category][item][‘brand’], json[category][item][‘price’]])<\/p>\n

}<\/p>\n

}<\/p>\n

 <\/p>\n

var ss = SpreadsheetApp.getActiveSpreadsheet();<\/p>\n

var newSheet = ss.getSheetByName(sheetName);<\/p>\n

var dataToInsert = [headers].concat(values)\/\/ Here you push the headers into the first row and create an array.<\/p>\n

var range = newSheet.getRange(1, 1, dataToInsert.length, dataToInsert[0].length) \u00a0 \/\/ get a range of cells (row, column, numRows, numColumns)<\/p>\n

range.setValues(dataToInsert); \/\/ set the values to the range of cells<\/p>\n

}[\/et_pb_text][et_pb_text module_id=”running-a-test” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Running a test<\/b><\/h2>\n

In order to test out the script for the first run<\/span>, click here<\/a> to make a copy of the JSON file used for this example.<\/span><\/p>\n

Getting the file ID<\/span><\/h3>\n

Now we have to get the file ID of your JSON file. Upload it to Google Drive, <\/span>right-click on it, <\/span>select \u201cshare\u201d and click on \u201ccopy link.\u201d Paste this link somewhere and copy the ID that comes between \u201chttps:\/\/drive.google.com\/file\/d\/\u201d and \u201c\/view?usp=sharing\u201d Paste this file ID between the double quotes of line 18.<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-3.png” alt=”import json to gs 3″ title_text=”import json to gs-3″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Running the script<\/span><\/h3>\n

Now that the correct file ID is in place and the script knows which file to <\/span>grab,<\/span> let\u2019s run this script for the first time. Click on \u201cSave project\u201d and afterwards click on \u201cRun\u201d.<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-8.png” alt=”import json to gs 8″ title_text=”import json to gs-8″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Now Google will ask you for permission to run the script.<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-9.png” alt=”import json to gs 9″ title_text=”import json to gs-9″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Click on \u201cReview permissions\u201d and afterwards on \u201cAllow\u201d.\u00a0<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-6.png” alt=”import json to gs 6″ title_text=”import json to gs-6″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

You might need to click one more time on \u201cRun\u201d. <\/span>Otherwise, <\/span>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\u2019s modify it for your JSON file.<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-5.png” alt=”import json to gs 5″ title_text=”import json to gs-5″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”modifying-key-values” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Modifying the \u201ckey-values\u201d for your file<\/b><\/h2>\n

Finding the positions of your key values.<\/span><\/h3>\n

For this example we have used five keys, “category”, “item”, “color”, \u201cbrand\u201d and \u201cprice\u201d. 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.<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-11.png” alt=”import json to gs 11″ title_text=”import json to gs-11″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

What if your JSON file has more than two cascades?<\/span><\/h3>\n

A JSON file has \u201ccascades\u201d, 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.<\/span><\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-1.png” title_text=”import json to gs-1″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

In line 28 and 29 of the script, you can modify how many cascades you\u2019d like to look into. Because this JSON file has one main cascade with the second cascade being \u201citems\u201d we only use two \u201cfor loops\u201d. If your JSON file has more than two cascading down, you will have to change this:<\/span><\/p>\n

[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

for<\/span> (<\/span>let<\/span> category<\/span> in<\/span> json)<\/span> {<\/span><\/p>\n

\u00a0\u00a0<\/span>for<\/span> (<\/span>let<\/span> item<\/span> in<\/span> json[category])<\/span> {<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0<\/span>values.push(…<\/span> )<\/span><\/p>\n

\u00a0\u00a0<\/span>}<\/span><\/p>\n

}<\/span><\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

To for example:<\/span><\/p>\n

[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

for<\/span> (<\/span>let<\/span> cascade1<\/span> in<\/span> json)<\/span> {<\/span><\/p>\n

\u00a0\u00a0<\/span>for<\/span> (<\/span>let<\/span> cascade2<\/span> in<\/span> json[cascade1])<\/span> {<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0<\/span>for<\/span> (<\/span>let<\/span> cascade3<\/span> in<\/span> json[cascade1][cascade2])<\/span> {<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0<\/span>values.push(…<\/span> )<\/span><\/p>\n

\u00a0\u00a0<\/span>}<\/span><\/p>\n

}}<\/span> \/\/Note the extra curly bracket for the added for loop<\/span>.<\/span><\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

What if your JSON file has more than four categories?<\/span><\/h3>\n

Another thing to highlight about the script is that in line 30 you see this:<\/span><\/p>\n

[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.20.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

values.push([category,<\/span> item,<\/span> json[category][item][<\/span>‘colour’<\/span>],<\/span> json[category][item][<\/span>‘brand’<\/span>],<\/span> json[category][item][<\/span>‘price’<\/span>]])<\/span><\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

We\u2019re 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, \u201cshoes\u201d and there are three items, \u201csneakers\u201d, \u201ctrainers\u201d and \u201crunning_shoes\u201d. For each of these items there are three values, \u201ccolor\u201d, \u201cbrand\u201d and \u201cprice\u201d.<\/span><\/p>\n

This is the reason why the notation of the values in this script is this way. Let\u2019s break it down quickly so you can modify it better for your JSON file.<\/span><\/p>\n

    \n
  1. The first value is plain and simple. <\/span>
    <\/span>\u2018category\u2019.<\/span><\/li>\n
  2. As is the second value. <\/span>
    <\/span>\u2018item\u2019.<\/span><\/li>\n
  3. But with the third value we have to let the script know it has to look inside the Javascript object of \u2018item\u2019 and return the value of \u2018color\u2019. For this reason the third value is written like this:<\/span>
    <\/span>json[category][item][<\/span>‘colour’<\/span>].<\/span><\/li>\n
  4. Same with the fourth item. We look into the Javascript object of \u2018item\u2019 and return \u2018brand\u2019.<\/span>
    <\/span>json[category][item][<\/span>‘brand’<\/span>]<\/span><\/li>\n
  5. Lastly, with the fifth item, we stay in the object of ‘item’ and return \u2018price\u2019.<\/span>
    <\/span>json[category][item][<\/span>‘price’<\/span>]<\/span><\/li>\n<\/ol>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Changing the values in the script.<\/span><\/h3>\n

    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.<\/span><\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/import-json-to-gs-7.png” alt=”import json to gs 7″ title_text=”import json to gs-7″ _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    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\u2019d like to import into the Google Sheets.\u00a0<\/span><\/p>\n

    [\/et_pb_text][et_pb_text module_id=”import-json” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Import JSON to Google Sheets<\/p>\n[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Lastly, take your JSON file and hold it next to line 30 of the script. Are all the value notations in the values.push( \u2026 ) method telling the script to look in the right places? If you think so, go for it. Click on \u201cRun\u201d 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\u2019ll have to tweak something. Programming is all about making slight changes, testing, changing again till the script does what you want.<\/span><\/p>\n

    If you\u2019ve made it this far in the article, congratulations! You\u2019re doing some highly technical work or at least working it out by yourself. Regardless of the results you\u2019re creating, that should be respected. If you\u2019re just not able to get it right, no worries. Reach out to us<\/a>, let us know the result you\u2019re trying to create for your project and our expert consultant can help you out. <\/span><\/p>\n

    [\/et_pb_text][et_pb_code _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_code][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":42,"featured_media":42182,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[37],"tags":[39,28],"class_list":["post-42359","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-features","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/42359","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/42"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=42359"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/42359\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/42182"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=42359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=42359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=42359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}