如何在Apps Script中正确读取Snapchat API JSON响应的曝光量数据

我想用Apps Script把Snapchat API的数据导入Google Sheets，写了如下代码：

function readData() {

var accesstoken = "TOKEN"    
var sheet = SpreadsheetApp.getActiveSheet() 
 var url = "https://adsapi.snapchat.com/v1/campaigns/e431cbcd-2281-49fe-8d05-b26c87660eb5/stats"

 var response = UrlFetchApp.fetch(url, {
       headers: {
           "Authorization": "Bearer " + accesstoken
       }
   });
 var data = JSON.parse(response.getContentText());
 var campaigndata = []
 campaigndata.push(data.total_stats)

 var campaign = []
 campaign.push(campaigndata)

 var targetrange = sheet.getRange('B2:B')

 targetrange.setValue(campaign)

}

API返回的JSON响应体如下：

{
  "request_status": "success",
  "request_id": "57ad1ad600076e58fa35e192",
  "total_stats": [
    {
      "sub_request_status": "success",
      "total_stat": {
        "id": "7057e31f-b908-4bc7-85dd-88169f53e08d",
        "type": "CAMPAIGN",
        "granularity": "TOTAL",
        "stats": {
          "impressions": 0,
          "swipes": 0,
          "spend": 0,
          "quartile_1": 0,
          "quartile_2": 0,
          "quartile_3": 0,
          "view_completion": 0,
          "screen_time_millis": 0
        }
      }
    }
  ]
}

尝试用campaigndata.push(data.total_stats.total_stat.stats.impressions)提取曝光量时，出现错误：

TypeError: Cannot read properties of undefined (reading 'stats')

错误根源是total_stats是数组（JSON中用[]包裹），不是单个对象，不能直接通过data.total_stats.total_stat访问，必须先通过索引获取数组元素，再逐层读取属性。

单条数据提取代码 #

function readData() {
  var accesstoken = "TOKEN";    
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var url = "https://adsapi.snapchat.com/v1/campaigns/e431cbcd-2281-49fe-8d05-b26c87660eb5/stats";

  var response = UrlFetchApp.fetch(url, {
    headers: {
      "Authorization": "Bearer " + accesstoken
    }
  });
  
  var data = JSON.parse(response.getContentText());
  
  // 从数组取第一个元素，逐层获取曝光量
  var impressions = data.total_stats[0].total_stat.stats.impressions;
  
  // 写入B2单元格
  sheet.getRange('B2').setValue(impressions);
}

多元素场景处理（如果total_stats返回多条数据） #

function readData() {
  var accesstoken = "TOKEN";    
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var url = "https://adsapi.snapchat.com/v1/campaigns/e431cbcd-2281-49fe-8d05-b26c87660eb5/stats";

  var response = UrlFetchApp.fetch(url, {
    headers: {
      "Authorization": "Bearer " + accesstoken
    }
  });
  
  var data = JSON.parse(response.getContentText());
  
  // 遍历数组收集所有曝光量
  var impressionsList = [];
  data.total_stats.forEach(item => {
    impressionsList.push([item.total_stat.stats.impressions]);
  });
  
  // 从B2开始批量写入数据
  sheet.getRange(2, 2, impressionsList.length, 1).setValues(impressionsList);
}

关键说明 #

• data.total_stats[0]：获取数组的第一个元素（当前响应中仅一条数据）
• 逐层访问total_stat→stats→impressions即可拿到目标数值
• 原代码中getRange('B2:B').setValue(campaign)会把数组结构写入单元格，改用getRange('B2').setValue(impressions)更贴合需求

内容的提问来源于stack exchange，提问作者Karim