如何在Google Sheets脚本中过滤TMDB嵌套JSON数组并返回指定值

需求说明 #

我正在用TMDB API往Google Sheet抓取电影数据，表格基于Reddit用户6745408的「MediaSheet 3.0」修改，用JavaScript脚本实现。目前已经能参照原有脚本提取未包含的键值，现在需要实现：过滤TMDB返回JSON的results数组中，site为YouTube且type为Trailer或Teaser的视频项，返回第一个符合条件项的key值。

待解析的TMDB JSON数据 #

{
  "id": 290098,
  "results": [
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "In the Projection Booth - Park Chan-wook, director of The Handmaiden (contains spoilers)",
      "key": "P8g8QJk96M4",
      "site": "YouTube",
      "size": 1080,
      "type": "Featurette",
      "official": true,
      "published_at": "2017-04-14T08:30:01.000Z",
      "id": "65be6283902012012fc9a5a7"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "UK Promo",
      "key": "xX7HsdfIYGw",
      "site": "YouTube",
      "size": 1080,
      "type": "Teaser",
      "official": true,
      "published_at": "2017-03-31T16:51:17.000Z",
      "id": "65be628efc6538017cea4ee3"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "UK Trailer",
      "key": "wYsdzNIcJNc",
      "site": "YouTube",
      "size": 1080,
      "type": "Trailer",
      "official": true,
      "published_at": "2017-03-09T12:54:54.000Z",
      "id": "65be625ea7e363018454330d"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "Extended Preview",
      "key": "5bOWrDriBno",
      "site": "YouTube",
      "size": 1080,
      "type": "Clip",
      "official": true,
      "published_at": "2017-01-17T17:49:04.000Z",
      "id": "65be613143999b0184c5d86e"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "Dress Up (Movie Clip)",
      "key": "y43Y20jdctw",
      "site": "YouTube",
      "size": 1080,
      "type": "Clip",
      "official": true,
      "published_at": "2016-10-24T21:00:04.000Z",
      "id": "65be60b512c604017c0096e5"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "The House (Movie Clip)",
      "key": "jUP3cPWQBh4",
      "site": "YouTube",
      "size": 1080,
      "type": "Clip",
      "official": true,
      "published_at": "2016-10-24T17:44:18.000Z",
      "id": "65be60bca7e36301b7547dcd"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "The Library (Movie Clip)",
      "key": "gvijQuKBz88",
      "site": "YouTube",
      "size": 1080,
      "type": "Clip",
      "official": true,
      "published_at": "2016-10-24T01:44:31.000Z",
      "id": "65be60c4031deb0162ee63e3"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "The Kiss (Movie Clip)",
      "key": "6ir3_NoTJOw",
      "site": "YouTube",
      "size": 1080,
      "type": "Clip",
      "official": true,
      "published_at": "2016-10-22T17:00:18.000Z",
      "id": "65be60cca7e363013653c57c"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "The Bath (Movie Clip)",
      "key": "O4p9o3aJGj8",
      "site": "YouTube",
      "size": 1080,
      "type": "Clip",
      "official": true,
      "published_at": "2016-10-21T17:00:08.000Z",
      "id": "65be60d443999b0163c50b39"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "Official US Trailer",
      "key": "whldChqCsYk",
      "published_at": "2016-07-29T17:02:31.000Z",
      "site": "YouTube",
      "size": 1080,
      "type": "Trailer",
      "official": true,
      "id": "57dbf0eb925141684c008ae7"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "Official International Trailer",
      "key": "NoyWKl0e8FI",
      "published_at": "2016-05-03T06:15:07.000Z",
      "site": "YouTube",
      "size": 1080,
      "type": "Trailer",
      "official": true,
      "id": "57dbf22b9251416905008c72"
    },
    {
      "iso_639_1": "en",
      "iso_3166_1": "US",
      "name": "Official Int'l Special Trailer",
      "key": "6sVYumzrKvs",
      "published_at": "2016-04-25T12:39:44.000Z",
      "site": "YouTube",
      "size": 1080,
      "type": "Trailer",
      "official": true,
      "id": "571e5a939251416f2c0003f0"
    }
  ]
}

当前Google Sheets脚本代码 #

function TMDBmovietrailer123(rows) {
  var tmdbKey = PropertiesService.getScriptProperties().getProperty('tmdbkey');
  const requests = rows.map(id => {
    return {
      url: `https://api.themoviedb.org/3/movie/${id}/videos?api_key=${tmdbKey}`,
      muteHttpExceptions: true
    }
  })
  const responses = UrlFetchApp.fetchAll(requests)
  return responses.map(request => {
    try {
      const data = JSON.parse(request.getContentText());
      const id = data.id

      return [id]
    } catch (err) {
      return ['']
    }
  })
}

修改后的脚本代码 #

function TMDBmovietrailer123(rows) {
  var tmdbKey = PropertiesService.getScriptProperties().getProperty('tmdbkey');
  const requests = rows.map(id => {
    return {
      url: `https://api.themoviedb.org/3/movie/${id}/videos?api_key=${tmdbKey}`,
      muteHttpExceptions: true
    }
  })
  const responses = UrlFetchApp.fetchAll(requests)
  return responses.map(request => {
    try {
      const data = JSON.parse(request.getContentText());
      // 过滤符合条件的视频项：site为YouTube，type是Trailer或Teaser
      const targetVideo = data.results.find(video => 
        video.site === 'YouTube' && (video.type === 'Trailer' || video.type === 'Teaser')
      );
      // 返回第一个符合条件的key，没有则返回空字符串
      return [targetVideo ? targetVideo.key : ''];
    } catch (err) {
      return ['']
    }
  })
}

代码说明 #

• 使用数组的find方法遍历results数组，找到第一个满足条件的视频对象：site严格等于YouTube，且type是Trailer或Teaser。
• 如果找到目标视频，返回其key值；如果没有符合条件的项，返回空字符串。
• 保留了原脚本的批量请求和异常处理逻辑，确保在API请求失败或解析出错时返回空值，避免表格报错。

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