How to Create an Array From JSON and Import the Values to Multiple Rows in a Google Sheet Using Google Apps Scripts

Question

I'm trying to import data from the Google Search Console API to a Google Sheet. I was able to retrieve the data I need in a JSON format but cannot figure out how to transfer it to the sheet so it fills multiple rows.

I have the following function that is able to import the data, but only to one row:

 function dataFillTest(sheet, json, regex) {
  sheet.clear();
  sheet.appendRow(["Date", "Link", "Clicks", "Category"]);

  var data = new Array();

  for (var i = 0; i < 5; i++) {
      data.push(json.rows[i].keys[0], json.rows[i].keys[1], json.rows[i].clicks);
  }
  Logger.log("Contents of data after for loop");
  Logger.log(data);
  
  var lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1, 1, 15).setValues([data]);

  var cell = sheet.getRange("D2:D");
  cell.setFormula('=REGEXEXTRACT(B2; "https://'+ regex+ '(\\w+)")');
}

This is what the sheet looks like when I run the code:

And this is what I'm trying to achieve:

This is how the JSON I have is structured if it is any help:

{responseAggregationType=byPage, rows=[{ctr=0.2625019822003836, keys=[2021-08-26, https://mondo.rs/Magazin/Zdravlje/a1522777/Letovanje-u-Crnoj-Gori-i-stomacni-problemi.html], clicks=120842, impressions=460347}, {ctr=0.18834311984734511, keys=[2021-09-13, https://mondo.rs/Magazin/Stil/a1528560/Hrana-za-jaci-imunitet-posle-50.html], clicks=102256, impressions=542924}, {ctr=0.23844989249500642, keys=[2021-09-07, https://mondo.rs/Magazin/Zdravlje/a1527110/Novi-organ-u-ljudskom-telu.html], clicks=93712, impressions=393005}, {ctr=0.27251045096802823, keys=[2021-09-10, https://mondo.rs/Sport/Tenis/a1528706/Novak-Djokovic-zamalo-diskvalifikovan-sa-US-opena.html], clicks=93349, impressions=342552}, {ctr=0.22042795846910482, keys=[2021-11-27, https://mondo.rs/Info/Drustvo/a1561695/Za-4-godine-ustedeo-7000-evra-na-grejanju.html], clicks=87129, impressions=395272}]}

I think most of us will want to see the JSON. Because generally we will think that parsing it and reading the data out of a object will result in a much easier solution. BTW the json that you have provided is not JSON. Please provide valid JSON because we won't want to have to edit it to make it valid JSON. — Cooper
– Cooper, Commented Dec 1, 2021 at 15:42
I'm really new to all of this so I'm not quite sure what to add. The code I have to get the thing I called JSON goes like this: I define var response_mondo = UrlFetchApp.fetch(apiURL_mondo, options); and then do this var json_mondo = JSON.parse(response_mondo.getContentText()); The json_mondo is what I send to the function. — Gilbert
– Gilbert, Commented Dec 1, 2021 at 15:48
I think I understand, do you mean this? { "rows": [ { "keys": [ "2021-08-26", "https://mondo.rs/Magazin/Zdravlje/a1522777/Letovanje-u-Crnoj-Gori-i-stomacni-problemi.html" ], "clicks": 120842, "impressions": 460347, "ctr": 0.26250198220038362 },... — Gilbert
– Gilbert, Commented Dec 1, 2021 at 15:59

Kristkun · Accepted Answer · 2021-12-01 22:05:39Z

1

The reason why your data was written on a single row is because you are pushing a list of elements in your array data, then you made your data array into 2-d when writing it in your sheet (Here is an example of what you are actually doing). Your array values should be in array[row][column] format when using setValues(), hence you should push an array to your data to make it a 2-d array.

Sample Code:

var data = new Array();

  for (var i = 0; i < 5; i++) {
      data.push([json.rows[i].keys[0], json.rows[i].keys[1], json.rows[i].clicks]); 
  }
  Logger.log("Contents of data after for loop");
  Logger.log(data);
  
  var lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1, data.length, data[0].length).setValues(data);

Modifications:

I enclosed the json data with [] then push it to the array data.
Modified the getRange() based on the data array dimension

Output:

edited Dec 1, 2021 at 22:05

answered Dec 1, 2021 at 17:02

Kristkun

6,0031 gold badge7 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gilbert Over a year ago

It works perfectly, thanks a lot. On a side note, could you please explain the difference between a list and an array? If I understood correctly my problem was that I was pushing a list to data instead of an array, but I don't understand how they differ?

Kristkun Over a year ago

example your array = [1,2] then array.push(3,4) it will add the elements in the push parameter to the end of the array hence array = [1,2,3,4]. But when you push an array, array.push([5,6]) then your array will be array = [1,2,3,4,[5,6]]. What you did is to push all json data and created a 1-d array = [1,2,3,4,5 .... 15] then you convert them to 2d during setValue([array]).

Kristkun Over a year ago

But the format in writing values to your sheet should be array[row][column]. Hence you should create an array = [[1,2,3],[4,5,6],....[13,14,15]]

Gilbert Over a year ago

I think I get it now. Thanks again for the help.

Collectives™ on Stack Overflow

How to Create an Array From JSON and Import the Values to Multiple Rows in a Google Sheet Using Google Apps Scripts

1 Answer 1

Sample Code:

Modifications:

Output:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample Code:

Modifications:

Output:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related