Error when running Youtube Data Service in App Scripts (js) – Daily Limit for Unauthenticated Use Exceeded

Question

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: API call to youtube.videos.list failed with error: Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup. (line 15).

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

Update: Here's what my code looks like:

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
}

Answer 1

I believe your goal as follows.

• 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?

Issue and workaround:

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?

Workaround 1:

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.

Sample script:

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

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.

1. Prepare script.

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

Sample script:

// 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. Deploy Web Apps.

1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "Me" for "Execute the app as:".
   • By this, the script is run as the owner.
3. Select "Anyone, even anonymous" for "Who has access to the app:".
   • In this case, no access token is required to be request. I think that I recommend this setting for testing this workaround.
   • Of course, you can also use the access token. But, in this case, when the access token is used, this sample script cannot be directly used as the custom function.
4. Click "Deploy" button as new "Project version".
5. Automatically open a dialog box of "Authorization required".
   1. Click "Review Permissions".
   2. Select own account.
   3. Click "Advanced" at "This app isn't verified".
   4. Click "Go to ### project name ###(unsafe)"
   5. Click "Allow" button.
6. Click "OK".

7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.

   • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

8. Please set the URL of https://script.google.com/macros/s/###/exec to url of above script. And please redeploy Web Apps. By this, the latest script is reflected to the Web Apps. So please be careful this.

4. Test this workaround.

Please put =getYoutubeData("###youtubeId###") to a cell. By this, the youtube ID is sent to the Web Apps and the Web Apps returns the values of vidData.statistics.

Note:

• These are the simple sample scripts for explaining the workarounds. So when you use this, please modify it for your actual situation.

References:

• Custom Functions in Google Sheets
• Web Apps
• Taking advantage of Web Apps with Google Apps Script