从Google脚本中的api解析JSON响应

Question

我正在使用Google脚本来解析这个json响应。

/*
* 提示：该行代码过长，系统自动注释不进行高亮。一键复制会移除系统注释 
* {"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}}]}
*/

我需要得到".colimnHeader"dimensions和metricHeader值以及".data.rows“dimensions和metrics值。我的密码不管用：

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

Answer 1

这个答案怎么样？

修改要点：

• json.reports[0].columnHeader.dimensions[i]可能是json.reports[0].columnHeader.dimensions[0].
• json.reports[0].columnHeader.metricHeader[0].name[i]可能是json.reports[0].columnHeader.metricHeader.metricHeaderEntries[0].name.
• json.reports[0].data.rows[0].dimensions[0]可能是json.reports[0].data.rows[i].dimensions[0].
• json.reports[0].data.rows[0].metrics[0].values[i]可能是json.reports[0].data.rows[i].metrics[0].values[0].
• In您的情况下，我认为push到rows2中作为数组的值可能是suitable.
• 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]);
}

至：

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

Answer 2

您还可以过滤数据输出字面上是最重要的因素之一的速度。

​

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);

}

​