Parse JSON response from an api in Google Apps Script

Question

I'm using Google Apps Script to parse this json response.

{"reports":[{"columnHeader":{"dimensions":["ga:country"],"metricHeader":{"metricHeaderEntries":[{"name":"ga:sessions","type":"INTEGER"}]}},"data":{"rows":[{"dimensions":["(not set)"],"metrics":[{"values":["73"]}]},{"dimensions":["Albania"],"metrics":[{"values":["1"]}]},{"dimensions":["Algeria"],"metrics":[{"values":["3"]}]},{"dimensions":["Angola"],"metrics":[{"values":["2"]}]},{"dimensions":["Antigua & Barbuda"],"metrics":[{"values":["3"]}]},{"dimensions":["Argentina"],"metrics":[{"values":["3"]}]},{"dimensions":["Armenia"],"metrics":[{"values":["1"]}]},{"dimensions":["Aruba"],"metrics":[{"values":["2"]}]},{"dimensions":["Australia"],"metrics":[{"values":["344"]}]},{"dimensions":["Austria"],"metrics":[{"values":["11"]}]},{"dimensions":["Azerbaijan"],"metrics":[{"values":["2"]}]},{"dimensions":["Bahamas"],"metrics":[{"values":["6"]}]},{"dimensions":["Bahrain"],"metrics":[{"values":["8"]}]},{"dimensions":["Bangladesh"],"metrics":[{"values":["1"]}]},{"dimensions":["Barbados"],"metrics":[{"values":["1"]}]},{"dimensions":["Belarus"],"metrics":[{"values":["1"]}]},{"dimensions":["Belgium"],"metrics":[{"values":["29"]}]},{"dimensions":["Bermuda"],"metrics":[{"values":["3"]}]},{"dimensions":["Bosnia & Herzegovina"],"metrics":[{"values":["4"]}]},{"dimensions":["Botswana"],"metrics":[{"values":["3"]}]},{"dimensions":["Brazil"],"metrics":[{"values":["12"]}]},{"dimensions":["Brunei"],"metrics":[{"values":["1"]}]},{"dimensions":["Bulgaria"],"metrics":[{"values":["24"]}]},{"dimensions":["Cameroon"],"metrics":[{"values":["1"]}]},{"dimensions":["Canada"],"metrics":[{"values":["260"]}]},{"dimensions":["Cayman Islands"],"metrics":[{"values":["1"]}]},{"dimensions":["Chile"],"metrics":[{"values":["5"]}]},{"dimensions":["China"],"metrics":[{"values":["101"]}]},{"dimensions":["Colombia"],"metrics":[{"values":["2"]}]},{"dimensions":["Costa Rica"],"metrics":[{"values":["2"]}]},{"dimensions":["Croatia"],"metrics":[{"values":["18"]}]},{"dimensions":["Curaçao"],"metrics":[{"values":["3"]}]},{"dimensions":["Cyprus"],"metrics":[{"values":["21"]}]},{"dimensions":["Czechia"],"metrics":[{"values":["99"]}]},{"dimensions":["Denmark"],"metrics":[{"values":["25"]}]},{"dimensions":["Egypt"],"metrics":[{"values":["14"]}]},{"dimensions":["El Salvador"],"metrics":[{"values":["1"]}]},{"dimensions":["Estonia"],"metrics":[{"values":["25"]}]},{"dimensions":["Eswatini"],"metrics":[{"values":["1"]}]},{"dimensions":["Falkland Islands (Islas Malvinas)"],"metrics":[{"values":["3"]}]},{"dimensions":["Faroe Islands"],"metrics":[{"values":["1"]}]},{"dimensions":["Fiji"],"metrics":[{"values":["1"]}]},{"dimensions":["Finland"],"metrics":[{"values":["22"]}]},{"dimensions":["France"],"metrics":[{"values":["147"]}]},{"dimensions":["Georgia"],"metrics":[{"values":["4"]}]},{"dimensions":["Germany"],"metrics":[{"values":["79"]}]},{"dimensions":["Ghana"],"metrics":[{"values":["9"]}]},{"dimensions":["Gibraltar"],"metrics":[{"values":["1"]}]},{"dimensions":["Greece"],"metrics":[{"values":["22"]}]},{"dimensions":["Guatemala"],"metrics":[{"values":["1"]}]},{"dimensions":["Guernsey"],"metrics":[{"values":["27"]}]},{"dimensions":["Guinea"],"metrics":[{"values":["1"]}]},{"dimensions":["Guyana"],"metrics":[{"values":["2"]}]},{"dimensions":["Haiti"],"metrics":[{"values":["1"]}]},{"dimensions":["Hong Kong"],"metrics":[{"values":["16"]}]},{"dimensions":["Hungary"],"metrics":[{"values":["26"]}]},{"dimensions":["Iceland"],"metrics":[{"values":["3"]}]},{"dimensions":["India"],"metrics":[{"values":["165"]}]},{"dimensions":["Indonesia"],"metrics":[{"values":["14"]}]},{"dimensions":["Iran"],"metrics":[{"values":["9"]}]},{"dimensions":["Iraq"],"metrics":[{"values":["6"]}]},{"dimensions":["Ireland"],"metrics":[{"values":["721"]}]},{"dimensions":["Israel"],"metrics":[{"values":["18"]}]},{"dimensions":["Italy"],"metrics":[{"values":["47"]}]},{"dimensions":["Jamaica"],"metrics":[{"values":["3"]}]},{"dimensions":["Japan"],"metrics":[{"values":["18"]}]},{"dimensions":["Jersey"],"metrics":[{"values":["82"]}]},{"dimensions":["Jordan"],"metrics":[{"values":["4"]}]},{"dimensions":["Kenya"],"metrics":[{"values":["5"]}]},{"dimensions":["Kosovo"],"metrics":[{"values":["2"]}]},{"dimensions":["Kuwait"],"metrics":[{"values":["3"]}]},{"dimensions":["Latvia"],"metrics":[{"values":["3"]}]},{"dimensions":["Lebanon"],"metrics":[{"values":["4"]}]},{"dimensions":["Lesotho"],"metrics":[{"values":["3"]}]},{"dimensions":["Libya"],"metrics":[{"values":["1"]}]},{"dimensions":["Lithuania"],"metrics":[{"values":["5"]}]},{"dimensions":["Luxembourg"],"metrics":[{"values":["4"]}]},{"dimensions":["Malaysia"],"metrics":[{"values":["35"]}]},{"dimensions":["Malta"],"metrics":[{"values":["25"]}]},{"dimensions":["Mauritius"],"metrics":[{"values":["1"]}]},{"dimensions":["Mexico"],"metrics":[{"values":["17"]}]},{"dimensions":["Moldova"],"metrics":[{"values":["3"]}]},{"dimensions":["Monaco"],"metrics":[{"values":["1"]}]},{"dimensions":["Montenegro"],"metrics":[{"values":["2"]}]},{"dimensions":["Morocco"],"metrics":[{"values":["4"]}]},{"dimensions":["Myanmar (Burma)"],"metrics":[{"values":["1"]}]},{"dimensions":["Namibia"],"metrics":[{"values":["2"]}]},{"dimensions":["Netherlands"],"metrics":[{"values":["105"]}]},{"dimensions":["New Zealand"],"metrics":[{"values":["53"]}]},{"dimensions":["Nigeria"],"metrics":[{"values":["32"]}]},{"dimensions":["North Macedonia"],"metrics":[{"values":["7"]}]},{"dimensions":["Norway"],"metrics":[{"values":["26"]}]},{"dimensions":["Oman"],"metrics":[{"values":["7"]}]},{"dimensions":["Pakistan"],"metrics":[{"values":["27"]}]},{"dimensions":["Philippines"],"metrics":[{"values":["23"]}]},{"dimensions":["Poland"],"metrics":[{"values":["30"]}]},{"dimensions":["Portugal"],"metrics":[{"values":["27"]}]},{"dimensions":["Puerto Rico"],"metrics":[{"values":["3"]}]},{"dimensions":["Qatar"],"metrics":[{"values":["13"]}]},{"dimensions":["Réunion"],"metrics":[{"values":["1"]}]},{"dimensions":["Romania"],"metrics":[{"values":["30"]}]},{"dimensions":["Russia"],"metrics":[{"values":["8"]}]},{"dimensions":["Saudi Arabia"],"metrics":[{"values":["12"]}]},{"dimensions":["Senegal"],"metrics":[{"values":["3"]}]},{"dimensions":["Serbia"],"metrics":[{"values":["16"]}]},{"dimensions":["Singapore"],"metrics":[{"values":["40"]}]},{"dimensions":["Sint Maarten"],"metrics":[{"values":["1"]}]},{"dimensions":["Slovakia"],"metrics":[{"values":["19"]}]},{"dimensions":["Slovenia"],"metrics":[{"values":["13"]}]},{"dimensions":["South Africa"],"metrics":[{"values":["162"]}]},{"dimensions":["South Korea"],"metrics":[{"values":["10"]}]},{"dimensions":["South Sudan"],"metrics":[{"values":["1"]}]},{"dimensions":["Spain"],"metrics":[{"values":["90"]}]},{"dimensions":["Sri Lanka"],"metrics":[{"values":["3"]}]},{"dimensions":["St. Vincent & Grenadines"],"metrics":[{"values":["2"]}]},{"dimensions":["Suriname"],"metrics":[{"values":["1"]}]},{"dimensions":["Sweden"],"metrics":[{"values":["43"]}]},{"dimensions":["Switzerland"],"metrics":[{"values":["32"]}]},{"dimensions":["Taiwan"],"metrics":[{"values":["3"]}]},{"dimensions":["Tajikistan"],"metrics":[{"values":["1"]}]},{"dimensions":["Tanzania"],"metrics":[{"values":["3"]}]},{"dimensions":["Thailand"],"metrics":[{"values":["17"]}]},{"dimensions":["Trinidad & Tobago"],"metrics":[{"values":["7"]}]},{"dimensions":["Tunisia"],"metrics":[{"values":["8"]}]},{"dimensions":["Turkey"],"metrics":[{"values":["24"]}]},{"dimensions":["Turkmenistan"],"metrics":[{"values":["1"]}]},{"dimensions":["Turks & Caicos Islands"],"metrics":[{"values":["1"]}]},{"dimensions":["Ukraine"],"metrics":[{"values":["22"]}]},{"dimensions":["United Arab Emirates"],"metrics":[{"values":["65"]}]},{"dimensions":["United Kingdom"],"metrics":[{"values":["104807"]}]},{"dimensions":["United States"],"metrics":[{"values":["4281"]}]},{"dimensions":["Vietnam"],"metrics":[{"values":["8"]}]},{"dimensions":["Zambia"],"metrics":[{"values":["2"]}]},{"dimensions":["Zimbabwe"],"metrics":[{"values":["7"]}]}],"totals":[{"values":["112788"]}],"rowCount":134,"minimums":[{"values":["1"]}],"maximums":[{"values":["104807"]}],"isDataGolden":true}}]}

I need to get the ".colimnHeader"dimensions and metricHeader values and the ".data.rows" dimensions and metrics values. My Code doesn't work:

    var json = JSON.parse(response.getContentText()); 
    
        var dataSet = json.reports[0].data.rows;
        var rows2 = [],
              json;
        
        for (var i in json.reports[0].data.rows) {
        data = dataSet[i];
  
            rows2.push(json.reports[0].columnHeader.dimensions[i], json.reports[0].columnHeader.metricHeader[0].name[i], json.reports[0].data.rows[0].dimensions[0], json.reports[0].data.rows[0].metrics[0].values[i]);
        }

I think that your sample value is not valid as the JSON object. Can you confirm it again? — Tanaike
– Tanaike, Commented Jun 28, 2020 at 0:59
@Tanaike I edited the code. This is what I get in the Logger.log(json); — Mauro Bros
– Mauro Bros, Commented Jun 28, 2020 at 1:11
Thank you for replying and adding more information. In that case, I would like to recommend to provide the value retrieved with Logger.log(JSON.stringify(json)). — Tanaike
– Tanaike, Commented Jun 28, 2020 at 1:13
Thanks for your help @Tanaike . Please find the code Logger.log(JSON.stringify(json)) in my question. — Mauro Bros
– Mauro Bros, Commented Jun 28, 2020 at 1:18
Thank you for replying and updating it. I proposed a modified script as an answer. Could you please confirm it? — Tanaike
– Tanaike, Commented Jun 28, 2020 at 1:37

Tanaike · Accepted Answer · 2020-06-28 01:37:28Z

How about this answer?

Modification points:

json.reports[0].columnHeader.dimensions[i] might be json.reports[0].columnHeader.dimensions[0].
json.reports[0].columnHeader.metricHeader[0].name[i] might be json.reports[0].columnHeader.metricHeader.metricHeaderEntries[0].name.
json.reports[0].data.rows[0].dimensions[0] might be json.reports[0].data.rows[i].dimensions[0].
json.reports[0].data.rows[0].metrics[0].values[i] might be json.reports[0].data.rows[i].metrics[0].values[0].
In your case, I think that the value in push to rows2 as an array might be suitable.
var dataSet = json.reports[0].data.rows; is not used in your script.

When above points are reflected to your script, it becomes as follows.

Modified script:

From:

var rows2 = [],
              json;
for (var i in json.reports[0].data.rows) {
  data = dataSet[i];
  rows2.push(json.reports[0].columnHeader.dimensions[i], json.reports[0].columnHeader.metricHeader[0].name[i], json.reports[0].data.rows[0].dimensions[0], json.reports[0].data.rows[0].metrics[0].values[i]);
}

To:

var rows2 = [];
for (var i = 0; i < dataSet.length; i++) {
  data = dataSet[i];
  rows2.push([
    json.reports[0].columnHeader.dimensions[0],
    json.reports[0].columnHeader.metricHeader.metricHeaderEntries[0].name,
    data.dimensions[0],
    data.metrics[0].values[0]
  ]);
}

Yasin Hassanien · Accepted Answer · 2022-01-19 00:53:00Z

You can also filter the data output literally one of the most important factors in speed.

function doGet(e) {
  
  //thew below is to get the taskid from a paramater that can be added at the end of the url to use it here to pass as a filter paramaeter
  var taskid = e.parameter.taskid;



  // Sheet Name, Chnage Sheet1 to Users in Spread Sheet. Or any other name as you wish the sheet name below as an example is the task history 
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Task History");
  var data = sheet.getDataRange().getValues();
  var newData = data.filter(function (item) {

    return item[1] == taskid;
  });

/*for (var i = 0; i < newData.length; i++) {
  if (newData[i][2]) {   // index 2 = 3rd column = C
    break;
  }
}
Logger.log("First nonempty row is " + (i+1))  // i is 0-based but row numbers are 1-based

  console.log(data);
  console.log(newData);

    */
    return getUsers(newData);

}


function getUsers(newData) {
  var jo = {};
  var dataArray = [];

  // collecting data from 2nd Row , 1st column to last row and last column
  var rows = newData;
  //.getRange(1, 1, newData.getLastRow()-1, newData.getLastColumn()).getValues();
//var rows = 4;
  for (var i = 0; i < rows.length; i++) {
    var dataRow = rows[i];
    var record = {};
    //the below are the headers preparation as you can see an example of headers are the 3 headers
record['Timestamp'] = dataRow[0];
    record['Task ID'] = dataRow[1];
    record['Action'] = dataRow[2];

    dataArray.push(record);

  }

  jo.result = dataArray;

  var result = JSON.stringify(jo);

  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

}

Collectives™ on Stack Overflow

Parse JSON response from an api in Google Apps Script

2 Answers 2

Modification points:

Modified script:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Modification points:

Modified script:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related