CODEWITHSUNDEEP
arraysjsongoogle-apps-scriptgoogle-sheets
Gilbert
Gilbert

Reputation: 83

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

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:

enter image description here

And this is what I'm trying to achieve:

enter image description here

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}]}

Upvotes: 0

Views: 3137

Answers (1)

Kristkun
Kristkun

Reputation: 5953

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:

enter image description here

Upvotes: 1

Related Questions