在 App Scripts (js) 中运行 Youtube 数据服务时出错 - 已超出未经验证使用的每日限制

问题描述

我正在使用 Youtube(YouTube 数据 API v3)高级服务的 App Scripts 中运行自定义函数.运行时出现以下错误:

I'm running a custom function in App Scripts which utilizes the Youtube (YouTube Data API v3) advanced service. When running, I get the following error:

GoogleJsonResponseException:对 youtube.videos.list 的 API 调用失败并出现错误:已超出未验证使用的每日限制.继续使用需要注册.(第 15 行).

GoogleJsonResponseException: API call to youtube.videos.list failed with error: Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup. (line 15).

我不确定如何验证我的应用程序.我已将其添加到云项目并启用了 API.

I'm not sure how to authenticate my application. I've added it to a cloud project and enabled the API's.

更新:这是我的代码的样子:

function getYoutubeData(youtubeId) {

  // Don't run on empty
  if(!youtubeId){return null}

  // Make the request
  var vidData = YouTube.Videos.list("statistics, snippet", {id: youtubeId}).items;
  if (!vidData|vidData.length<1){return null}

  // Get the first item
  vidData = vidData[0];

  return vidData.statistics
}

推荐答案

我相信你的目标如下.

• 您希望将脚本中 vidData.statistics 的值放入单元格中.
• 您希望使用 =getYoutubeData(youtubeId) 等自定义函数来实现此目的.

• You want to put the value of vidData.statistics in your script to the cell.
• You want to achieve this using custom function like =getYoutubeData(youtubeId).

对于这个，这个答案怎么样?

For this, how about this answer?

很遗憾，在自定义函数中使用 Advanced Google services 的 YouTube Data API 时，没有使用访问令牌.从您的脚本来看，我认为您的问题的原因是这个.例如，当const sample = () =>的函数时；ScriptApp.getOAuthToken(); 与 =sample() 一样用作自定义函数，不返回值.出于安全考虑，我认为这是谷歌方面目前的规范.

Unfortunately, when YouTube Data API of Advanced Google services is used in the custom function, the access token is not used. From your script, I think that the reason of your issue is this. For example, when the function of const sample = () => ScriptApp.getOAuthToken(); is used as the custom function like =sample(), no value is returned. I think that this is the current specification of Google side because of the security.

为了在上述情况下实现您的目标，以下变通方法如何?

In order to achieve your goal under above situation, how about the following workarounds?

在此解决方法中，首先将 youtube ID 设置为 Google 电子表格中的单元格.vidData.statistics 的值由不是自定义函数的 Google Apps 脚本检索，并将 youtube ID 替换为结果值.

In this workaround, at first, the youtube ID is set to the cells in Google Spreadsheet. And the value of vidData.statistics are retrieved by the Google Apps Script which is not the custom function and replace the youtube ID with the result values.

请将 youtube ID 的单元格范围设置为 sourceRange 和工作表名称.在示例中，它假设 youtube ID 被放置到单元格A1:A10"中.请在脚本编辑器中运行 getYoutubeData().当然，您也可以将其设置为自定义菜单.

Please set the range of cells of youtube IDs to sourceRange and the sheet name. At the sample, it supposes that the youtube IDs are put to the cells "A1:A10". And please run getYoutubeData() at the script editor. Of course, you can also set this to the custom menu.

function getYoutubeData() {
  const sourceRange = "A1:A10"; // Please set the range of cells of youtube IDs.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  // Please set the sheet name.

  const range = sheet.getRange(sourceRange);
  const youtubeIds = range.getValues();
  const values = youtubeIds.map(([youtubeId]) => {

    // This is your script.
    if(!youtubeId){return [null]}
    var vidData = YouTube.Videos.list("statistics, snippet", {id: youtubeId}).items;
    if (!vidData|vidData.length<1){return [null]}
    vidData = vidData[0];
    return [JSON.stringify(vidData.statistics)];

  });
  range.setValues(values);
}

解决方法 2:

在此解决方法中，使用了自定义函数.但是，在这种情况下，Web 应用程序被用作包装器.这样，授权过程在 Web 应用程序中完成.所以自定义函数可以在没有授权的情况下运行.请执行以下流程.

Workaround 2:

In this workaround, the custom function is used. But, in this case, the Web Apps is used as the wrapper. By this, the authorization process is done at the Web Apps. So the custom function can be run without the authorization. Please do the following flow.

当你的脚本被使用时，它变成如下.请将以下脚本复制并粘贴到脚本编辑器中.

When your script is used, it becomes as follows. Please copy and paste the following script to the script editor.

// This is your script.
function getYoutubeData_forWebApps(youtubeId) {
  // Don't run on empty
  if(!youtubeId){return null}

  // Make the request
  var vidData = YouTube.Videos.list("statistics, snippet", {id: youtubeId}).items;
  if (!vidData|vidData.length<1){return null}

  // Get the first item
  vidData = vidData[0];

  return vidData.statistics
}

// Web Apps using as the wrapper.
function doGet(e) {
  const res = getYoutubeData_forWebApps(e.parameter.youtubeId)
  return ContentService.createTextOutput(JSON.stringify(res));
}

// This is used as the custom function.
function getYoutubeData(youtubeId) {
  const url = "https://script.google.com/macros/s/###/exec?youtubeId=" + youtubeId;  // Please set the URL of Web Apps after you set the Web Apps.
  return UrlFetchApp.fetch(url).getContentText();
}

2.部署 Web 应用程序.

1. 在脚本编辑器上，通过发布"->部署为网络应用"打开一个对话框.
2. 为执行应用程序为:"选择我".
   • 由此，脚本以所有者身份运行.

• 在这种情况下，请求不需要访问令牌.我认为我建议使用此设置来测试此解决方法.
• 当然，您也可以使用访问令牌.但是，在这种情况下，当使用访问令牌时，此示例脚本不能直接用作自定义函数.

1. 点击查看权限".
2. 选择自己的帐户.
3. 点击此应用未验证"中的高级".
4. 点击转到###项目名称###(不安全)"
5. 点击允许"按钮.