parsing youtube-api google-sheets-formula html-parsing google-sheets-api

Scraping data from view-source:https://www.youtube.com/embed/

I have a big list of embedded Youtube videos. Some of them are not available.

I am looking for a solution how to identify them in a big batch of URLs.

Manually it is possible to check in the following way:

E.g., embedded Youtube video - https://www.youtube.com/embed/GdGB0cv6i8I

view-source:https://www.youtube.com/embed/GdGB0cv6i8I

previewPlayabilityStatus - "Video unavailable"

I am trying to incorporate this with Google spreadsheets with the formula

Here is the link to the file: https://docs.google.com/spreadsheets/d/1fHB2UvCVxq4tgNrn_VWlmGyfpXyS-vhbWu57EjUaKsw/edit#gid=0

For some reasons it does not work.

Solution

Continuing your previous question, you can solve your problem by using my no-key service as follows with a Google Apps Script (see my modified Google Sheet example):

function fills_embeddable_associated_to_given_youtube_videos() {
  const A = 1, B = 2;
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var row = 2; row <= sheet.getLastRow(); row++)
  {
    const videoId = sheet.getRange(row, A).getValue().toString().replace("https://www.youtube.com/embed/", "").replace("view-source:", "");
    const responseStr = UrlFetchApp.fetch(`https://yt.lemnoslife.com/videos?part=status&id=${videoId}`).getContentText();
    const response = JSON.parse(responseStr);
    sheet.getRange(row, B).setValue(response["items"][0]["status"]["embeddable"]);
  }
}