Google Apps Script：如何将嵌套JSON数据推入数组后写入谷歌表格

8月18日编辑：我已更新下方脚本，新增了可抓取JSON响应中嵌套数组的相关逻辑。

我目前无法从嵌套JSON数组中提取非空格式数据：脚本可正常执行并拉取顶层数据，但只要访问嵌套数组，日志就会输出null，push和追加操作均无有效内容返回。

我的整体目标是遍历谷歌表格中的URL列表，发送API请求后将返回结果记录到另一张表格中，目前除无法获取JSON响应内嵌套数组的数据外，其余功能均正常实现。

我已尝试解决该问题多日，但我对JSON结果解析转数组的操作经验不足，暂未找到可行方案，恳请大家提供相关建议，万分感谢！

统一格式JSON示例 #

{
  "kind": "youtube#channelListResponse",
  "etag": "MjhfUO2Z_x1Njr9Rw7uDjA1-bvM",
  "pageInfo": {
    "totalResults": 1,
    "resultsPerPage": 5
  },
  "items": [
    {
      "kind": "youtube#channel",
      "etag": "uHgaADZBzVjfmmqUFEHIy5RFmIk",
      "id": "UCuKkFu9WVxCRoj2EbWzIj3Q",
      "snippet": {
        "title": "AhnaldT101",
        "description": "Just a guy who loves to play anything Star Wars while having fun making other sorts of content that is sure to make you laugh and put a smile on your face!",
        "customUrl": "ahnaldt101",
        "publishedAt": "2012-12-09T04:34:18Z",
        "thumbnails": {
          "default": {
            "url": "https://yt3.ggpht.com/ytc/AKedOLTk3v99OfR76ONLecJpy80h4qaDQ2m9RGYRFPdgww=s88-c-k-c0x00ffffff-no-rj",
            "width": 88,
            "height": 88
          },
          "medium": {
            "url": "https://yt3.ggpht.com/ytc/AKedOLTk3v99OfR76ONLecJpy80h4qaDQ2m9RGYRFPdgww=s240-c-k-c0x00ffffff-no-rj",
            "width": 240,
            "height": 240
          },
          "high": {
            "url": "https://yt3.ggpht.com/ytc/AKedOLTk3v99OfR76ONLecJpy80h4qaDQ2m9RGYRFPdgww=s800-c-k-c0x00ffffff-no-rj",
            "width": 800,
            "height": 800
          }
        },
        "localized": {
          "title": "AhnaldT101",
          "description": "Just a guy who loves to play anything Star Wars while having fun making other sorts of content that is sure to make you laugh and put a smile on your face!"
        },
        "country": "US"
      },
      "contentDetails": {
        "relatedPlaylists": {
          "likes": "",
          "favorites": "",
          "uploads": "UUuKkFu9WVxCRoj2EbWzIj3Q"
        }
      },
      "brandingSettings": {
        "channel": {
          "title": "AhnaldT101",
          "description": "Just a guy who loves to play anything Star Wars while having fun making other sorts of content that is sure to make you laugh and put a smile on your face!",
          "showRelatedChannels": true,
          "showBrowseView": true,
          "unsubscribedTrailer": "9hXIxPXngCo",
          "country": "US"
        },
        "image": {
          "bannerExternalUrl": "https://lh3.googleusercontent.com/I9Ffei-ZhVZ116pR61k_kP40J2OqlUx6LmToadolqzZ9vaPs7j9a-y0Jdr2LMOyKUCjQgV-cJw"
        }
      }
    }
  ]
}

现有脚本代码 #

function listYTChannels() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("YTChannel");
  const otherSheet = SpreadsheetApp.getActive().getSheetByName('YTChannelResults')
  var sheetLR = sheet.getLastRow();
  var data = sheet.getRange(1,2,sheetLR).getValues();


  data.forEach(function (row,index) {
    Logger.log(row, index);
    var response = UrlFetchApp.fetch(row);
    Logger.log(response.getContentText());
    var responseParse = JSON.parse(response.getContentText());
    Logger.log(responseParse);

    var responseData=[];  // this is an empty array to hold the data from responseParse
    var date = new Date(); // create new date for timestamp
    responseData.push(date); // this will use the timestamp created above
    responseData.push(responseParse.items[0].id);  // this works, follow this format
    responseData.push(responseParse.items[0].snippet.title);
    responseData.push(responseParse.items[0].snippet.description); 
    responseData.push(responseParse.items[0].snippet.customUrl);
    responseData.push(responseParse.items[0].snippet.publishedAt);
    responseData.push(responseParse.items[0].snippet.thumbnails.high.url);
    responseData.push(responseParse.items[0].snippet.thumbnails.high.width);
    responseData.push(responseParse.items[0].snippet.thumbnails.high.height);
    responseData.push(responseParse.items[0].contentDetails.relatedPlaylists.uploads);
    responseData.push(responseParse.items[0].brandingSettings.channel.title);

    Logger.log(responseData);
    otherSheet.appendRow(responseData);  // no issues here

  });
}

你当前脚本返回null的核心原因是没有做字段存在性校验，部分接口返回结果中缺少对应字段、或者items数组为空时直接取值就会触发空值报错，打断后续写入流程。

修改后的可用脚本如下：

function listYTChannels() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("YTChannel");
  const otherSheet = SpreadsheetApp.getActive().getSheetByName('YTChannelResults')
  var sheetLR = sheet.getLastRow();
  var data = sheet.getRange(1,2,sheetLR).getValues();

  // 安全取值工具，字段不存在时返回默认占位符，避免空值报错
  function getSafeValue(obj, path, defaultValue = '-') {
    return path.split('.').reduce((current, key) => {
      return current && current[key] !== undefined ? current[key] : defaultValue;
    }, obj);
  }

  data.forEach(function (row,index) {
    // 跳过空URL行
    if(!row[0]) return;
    try {
      var response = UrlFetchApp.fetch(row[0]);
      var responseParse = JSON.parse(response.getContentText());
      var date = new Date();
      
      // 遍历所有返回的items项，如果你确定每次接口仅返回1条，可直接取items[0]
      (responseParse.items || []).forEach(item => {
        var responseData=[];
        responseData.push(date);
        responseData.push(getSafeValue(item, 'id'));
        responseData.push(getSafeValue(item, 'snippet.title'));
        responseData.push(getSafeValue(item, 'snippet.description'));
        responseData.push(getSafeValue(item, 'snippet.customUrl'));
        responseData.push(getSafeValue(item, 'snippet.publishedAt'));
        responseData.push(getSafeValue(item, 'snippet.thumbnails.high.url'));
        responseData.push(getSafeValue(item, 'snippet.thumbnails.high.width'));
        responseData.push(getSafeValue(item, 'snippet.thumbnails.high.height'));
        responseData.push(getSafeValue(item, 'contentDetails.relatedPlaylists.uploads'));
        responseData.push(getSafeValue(item, 'brandingSettings.channel.title'));
        Logger.log(responseData);
        otherSheet.appendRow(responseData);
      })
    } catch (e) {
      // 单条URL处理失败不中断整体流程，打印错误日志方便排查
      Logger.log(`第${index+1}行URL处理失败：${e.toString()}`)
    }
  });
}

本次修改点：

• 新增空行跳过逻辑，避免表格空单元格触发异常
• 新增全局异常捕获，单条URL请求/解析失败不会中断整个脚本运行
• 封装安全取值方法，字段不存在时自动填充占位符，不会返回null打断数据写入
• 支持完整遍历items数组，所有接口返回的频道数据都会写入表格，不需要多数据遍历可直接改为仅取items[0]即可

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