1
\$\begingroup\$

I have the following code below that takes a new row added to my Google Spreadsheet (has a header row) and extracts the video ID. If the ID does not exist in the playlist, it adds the video to the playlist. If it doesn't, it skips it. Not really comfortable with Google Apps Script, so I would like some constructive feedback.

function addVideoToYouTubePlaylist() {
  // retrieve video ID list from the playlist.
  var playlistId = "PL6bCFcS8yqQxSPjwZ9IXFMfVm6kaNGLfi";
  var list = [];
  var pageToken = "";
  do {
    var res = YouTube.PlaylistItems.list(["snippet"], {playlistId: playlistId, maxResults: 50, pageToken: pageToken});
    if (res.items.length > 0) list = list.concat(res.items);
    pageToken = res.nextPageToken || "";
  } while (pageToken);
  var obj = list.reduce((o, e) => Object.assign(o, {[e.snippet.resourceId.videoId]: e.snippet.resourceId.videoId}), {});

  // read the source videos from Google Sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();

  // iterate through all rows in the sheet
  for (var d=1,l=data.length; d<l; d++) {
    var videoId = extractVideoID(data[d][0]);
    if (!obj[videoId]) {
    YouTube.PlaylistItems.insert({
      snippet: {
        playlistId: playlistId,
        resourceId: {
          kind: "youtube#video",
          videoId: extractVideoID(videoId)
          }
        }
      }, "snippet");
    }
    else{
      console.log("Already in playlist");
    }
    sheet.deleteRow(d+1); // delete row after video is checked
  }
}

function extractVideoID(url){
    var regExp = /^.*((youtu.be\/)|(v\/)|(\/u\/\w\/)|(embed\/)|(watch\?))\??v?=?([^#\&\?]*).*/;
    var match = url.match(regExp);
    if ( match && match[7].length == 11 ){
        return match[7];
    }
    else if (!( match && match[7].length == 11 )){
        console.log("Could not extract video ID.");
        var trimmedVideoID = url.replace("https://youtu.be/", "");
        trimmedVideoID = trimmedVideoID.replace('https://www.youtube.com/watch?v=', "");
        trimmedVideoID = trimmedVideoID.replace('https://youtube.com/watch?v=', "");
        trimmedVideoID = trimmedVideoID.replace("&feature=youtu.be", "");
        trimmedVideoID = trimmedVideoID.replace("&feature=share", "");
        console.log(trimmedVideoID);
        return trimmedVideoID;
    }
}
\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

Let's start with extractVideoID().

Your regex /^.*((youtu.be\/)|(v\/)|(\/u\/\w\/)|(embed\/)|(watch\?))\??v?=?([^#\&\?]*).*/ doesn't guarantee the URL has a YouTube format. For example, the URL www.google.com/watch?v=01$@T$89h does match the regex and has the following groups: ["www.google.com/watch?v=0123456", "watch?", undefined, undefined, undefined, undefined, "watch?", "01$@T$89h"], where the 7th group 01$@T$89h isn't a valid YouTube video ID.

I suggest you replace your regex with /^((?:https?:)?\/\/)?((?:www|m)\.)?((?:youtube\.com|youtu.be))(\/(?:[\w\-]+\?v=|embed\/|v\/)?)([\w\-]+)(\S+)?$/ (source). This regex only matches valid YouTube URLs format, and the 5th group is the ID of the video. Notice that a valid YouTube URL along with a video ID doesn't guarantee that the video exist. You can use YouTube.Videos.list() to test if the video for the given ID exists.

  if ( match && match[7].length == 11 ){
    return match[7];
  }
  else if (!( match && match[7].length == 11 )){ // equivalent of else {}
    // multiple attempts to replace
    return trimmedVideoID;
  }

The if..else if block is bad. If you find a match, you return the ID. Done. So extractVideoID() has only one purpose: if the URL has a valid format, return the ID. Otherwise, i.e., if it doesn't find a match, then extractVideoID() should return null.

if (match && match[5])
  return match[5];

In your code, the else if block is opposite of the first if, so it is actually an else block. This also means that, altought you couldn't find any match, you are returning anything anyway, even if url is garbage like Lorem ipsum dolor sit amet, consectetur adipiscing elit..

Now, with addVideoToYouTubePlaylist().

pageToken = res.nextPageToken || "";

The nextPageToken is a string for the next page, so unless YouTube.PlaylistItems.list() fails - and thow an error, stopping everything - it won't evaluate to false. And if it does, you are restarting all over again, possibly heading to the same falsy value thus creating an infinite loop.

for (var d=1,l=data.length; d<l; d++) {

You can test d < data.length directly for improved reading.

  var videoId = extractVideoID(data[d][0]);
  if (!obj[videoId]) {
  YouTube.PlaylistItems.insert({

You may want to test if the video with the given ID exists before of inserting it in the playlist.

  resourceId: {
    kind: "youtube#video",
    videoId: extractVideoID(videoId)
  }

Without chaning extractVideoID() and passing a valid ID to it returns null.

sheet.deleteRow(d+1); // delete row after video is checked

When you delete row number d+1, what was in row (d+1)+1 now is in row d+1. Because of that, you need a counter of deleted rows, say r, and delete the row number d+1-r to compensate the upward "shift".

\$\endgroup\$
2
  • \$\begingroup\$ Thank you for the in-depth explanation of everything. However, I'm having a hard time putting all this together. Do you think you could point me in the right direction? \$\endgroup\$
    – Frank Doe
    Feb 11, 2021 at 16:45
  • \$\begingroup\$ You should start with extractVideoID() for it to test the url with the regex from the answer to test if there is a match and the match also has an id, i.e., the 5th group is not empty. Otherwise, don't return anything and move forward to the next url. \$\endgroup\$
    – user235971
    Feb 11, 2021 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.