Is there a way based on the URL to see if a video link would play?

Question

i have a thousand youtube videos to review, some of which do not work. i'm trying to find a way to check these faster. I came across this script, which I thought worked perfectly. however, I'm encountering video links that are BROKEN but still return a 302.

function getStatusCode(url) {
  var url_trimmed = url.trim();
  // Check if script cache has a cached status code for the given url
  var cache = CacheService.getScriptCache();
  var result = cache.get(url_trimmed);
  
  // If value is not in cache/or cache is expired fetch a new request to the url
  if (!result) {

    var options = {
      'muteHttpExceptions': true,
      'followRedirects': false
    };
    var response = UrlFetchApp.fetch(url_trimmed, options);
    var responseCode = response.getResponseCode();

    // Store the response code for the url in script cache for subsequent retrievals
    cache.put(url_trimmed, responseCode, 21600); // cache maximum storage duration is 6 hours
    result = responseCode;
  }

  return result;
}

Here are some samples of not-playable videos https://www.youtube.com/watch?v=yovjv3nL4D8 https://www.youtube.com/watch?v=zBhtOnFURPY

Is there a way to identify if the video is playable based on the URL?

Answer 1

You can use the IMPORTDATA function.

=LET(url,A1,
     data,TOCOL(IMPORTDATA(url)),
     IFERROR(REGEXREPLACE(FILTER(data,REGEXMATCH(data,"reason:")),"reason:|""",)))

This formula returns a blank cell if the video is playable and the error message if the video is not playable. The error message takes 1 or 2 seconds to load.