Google Apps Script- Loop Through Nested JSON Response and Set Values to Spreadsheet

Question

In Google Apps Script, I am connecting to a service via OAuth2. I get a JSON response and parse it with JSON.parse(response.getContentText()); so it logs something like so:

{Results=[{Id=45364743, Description=null, Name=Math I , IsActive=true}, 
          {Id=45364768, Description=null, Name=Math II, IsActive=true}]}

Using the following code, I have previously taken JSON responses and written them successfully to Google Sheets. However, this time the FOR loop does not run because dataSet does not have a length. I am guessing that is because everything is nested under "Results".

function getService2() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('Data');
var key = 'kjeu7hjf7873alkjhehjhfayuluoojsds'
var options = {
    method: "get",
    headers: {
        Authorization: "Bearer " + key,
        api_key:                   key,
    }
}
var response = UrlFetchApp.fetch(URL, options);  
var dataAll = JSON.parse(response.getContentText()); 
var dataSet = dataAll;
Logger.log(dataSet)
var rows = [],
  data;
for (i = 0; i < dataSet.length; i++) 
if (  dataSet.length > 0.0){
  data = dataSet[i];
rows.push([data.Id, data.Name, data.IsActive ]); //your JSON entities here

dataRange = sheet.getRange(lastRow + 1, 1, rows.length , 2);
dataRange.setValues(rows);
}}

How would I determine the length of the parsed JSON response (i.e. "dataSet") to run the loop and write to the sheet?

Replace var dataSet = dataAll; with var dataSet = dataAll.Results; — ra89fi
– ra89fi, Commented Mar 21, 2019 at 15:12
That was it! Any idea why the "rows.length" grows by 1 for each loop? — Jerry Williamson
– Jerry Williamson, Commented Mar 21, 2019 at 16:17

ra89fi · Accepted Answer · 2019-03-21 17:17:04Z

I noticed some bugs and edited to fix them. Your for loop had issues. You were writing into sheet on every iteration which is not performant.

function getService2() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Data');
  var key = 'kjeu7hjf7873alkjhehjhfayuluoojsds';
  var options = {
    method: 'get',
    headers: {
      Authorization: 'Bearer ' + key,
      api_key: key
    }
  };
  var response = UrlFetchApp.fetch(URL, options);
  var dataAll = JSON.parse(response.getContentText());
  var dataSet = dataAll.Results;
  Logger.log(dataSet);
  var rows = [],
    data;
  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.Id, data.Name, data.IsActive]); //your JSON entities here
  }
  sheet.getRange(lastRow + 1, 1, rows.length, 3).setValues(rows);
}

Collectives™ on Stack Overflow

Google Apps Script- Loop Through Nested JSON Response and Set Values to Spreadsheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related