-1

This Google Apps Script code Search YouTube results by keywords. I want to add View Count and Subscribes Count too.

Output Data

function youTubeSearchResults() {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();
  const modifyResults = values.flatMap(([keywords]) => {
    const searchResults = YouTube.Search.list("id, snippet", { q: keywords, maxResults: 10, type: "video", order: "viewCount", videoDuration: "short", order: "date" });
    const fSearchResults = searchResults.items.filter(function (sr) { return sr.id.kind === "youtube#video" });
    return fSearchResults.map(function (sr) { return [keywords, sr.id.videoId, `https://www.youtube.com/watch?v=${sr.id.videoId}`, sr.snippet.title, sr.snippet.publishedAt, sr.snippet.channelTitle, sr.snippet.channelId,`https://www.youtube.com/channel/${sr.snippet.channelId}`, sr.snippet.thumbnails.high.url] });
  }); 
  sheet.getRange(2, 2, modifyResults.length, modifyResults[0].length).setValues(modifyResults);
}
tamey
  • 3
  • 3

1 Answers1

0

When your showing script is modified, how about the following modification?

Modified script:

function youTubeSearchResults() {
  // 1. Retrieve values from column "A".
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().filter(([a]) => a);

  // 2. Retrieve your current values.
  const modifyResults = values.flatMap(([keywords]) => {
    const searchResults = YouTube.Search.list("id, snippet", { q: keywords, maxResults: 10, type: "video", order: "viewCount", videoDuration: "short", order: "date" });
    const fSearchResults = searchResults.items.filter(function (sr) { return sr.id.kind === "youtube#video" });
    return fSearchResults.map(function (sr) { return [keywords, sr.id.videoId, `https://www.youtube.com/watch?v=${sr.id.videoId}`, sr.snippet.title, sr.snippet.publishedAt, sr.snippet.channelTitle, sr.snippet.channelId, `https://www.youtube.com/channel/${sr.snippet.channelId}`, sr.snippet.thumbnails.high.url] });
  });

  // 3. Retrieve viewCounts and subscriberCounts.
  const { videoIds, channelIds } = modifyResults.reduce((o, r) => {
    o.videoIds.push(r[1]);
    o.channelIds.push(r[6]);
    return o;
  }, { videoIds: [], channelIds: [] });
  const limit = 50;
  const { viewCounts, subscriberCounts } = [...Array(Math.ceil(videoIds.length / limit))].reduce((obj, _) => {
    const vIds = videoIds.splice(0, limit);
    const cIds = channelIds.splice(0, limit);
    const res1 = YouTube.Videos.list(["statistics"], { id: vIds, maxResults: limit }).items.map(({ statistics: { viewCount } }) => viewCount);
    const obj2 = YouTube.Channels.list(["statistics"], { id: cIds, maxResults: limit }).items.reduce((o, { id, statistics: { subscriberCount } }) => (o[id] = subscriberCount, o), {});
    const res2 = cIds.map(e => obj2[e] || null);
    obj.viewCounts = [...obj.viewCounts, ...res1];
    obj.subscriberCounts = [...obj.subscriberCounts, ...res2];
    return obj;
  }, { viewCounts: [], subscriberCounts: [] });
  const ar = [viewCounts, subscriberCounts];
  const rr = ar[0].map((_, c) => ar.map(r => r[c]));

  // 4. Merge data.
  const res = modifyResults.map((r, i) => [...r, ...rr[i]]);

  // 5. Put values on Spreadsheet.
  sheet.getRange(2, 2, res.length, res[0].length).setValues(res);
}

  • When this script is run, the following flow is run.

    1. Retrieve values from column "A".
    2. Retrieve your current values.
    3. Retrieve "viewCounts" and "subscriberCounts".
    4. Merge data.
    5. Put values on Spreadsheet.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Again it's work like a charm... wish I can code like you, I don't know programing can you please guide me from where to start. Also can you help me to fix it more, like fixing date format and it stops after 100 keywords giving me error API daily quota reached. – tamey Sep 25 '22 at 01:42
  • @tamey Thank you for replying. I'm glad your issue was resolved. About your following 3 questions. Q1. `I don't know programing can you please guide me from where to start.`. A1. I think that the links at https://stackoverflow.com/tags/google-apps-script/info might be useful. – Tanaike Sep 25 '22 at 03:30
  • @tamey Q2. `Also can you help me to fix it more, like fixing date format`. A2. How about using [setNumberFormat(numberFormat)](https://developers.google.com/apps-script/reference/spreadsheet/range#setnumberformatnumberformat) and [formatDate(date, timeZone, format)](https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format). – Tanaike Sep 25 '22 at 03:31
  • @tamey Q3. `and it stops after 100 keywords giving me error API daily quota reached.`. A2. I cannot understand your 3rd question. But, I would like to support you. So, can you post it as a new question by including more information? By posting it as a new question, users including me can think of it. If you can cooperate to resolve your new issue, I'm glad. Can you cooperate to resolve your new question? – Tanaike Sep 25 '22 at 03:31
  • First of all Thank you again for all the help you provided, wish I can give you the Title of Sir Tanaike..... As far as regarding my First Question I mean to say I don't know programing at all except a little bit html. That's why I asked from where to start so can I code like you. For the Second Question I will create a separate post and For the Third question I already created a post [link](https://stackoverflow.com/questions/73846826/api-call-to-youtube-videos-list-failed-with-error). I know I am making it complicated but after reading your Bio I will take the liberty to do so. – tamey Sep 25 '22 at 18:26
  • @tamey Thank you for replying. When I saw your new question, I noticed that an answer has already been posted. In this case, I would like to respect the existing answer. – Tanaike Sep 26 '22 at 00:08
  • unfortunately with my limited knowledge I am unable to understand that answer so it seems like right now it's dead end for me. Thank you again for all the support. – tamey Sep 26 '22 at 05:35
  • @tamey Thank you for replying. About `unfortunately with my limited knowledge I am unable to understand that answer so it seems like right now it's dead end for me. Thank you again for all the support.`, I deeply apologize for this. In this case, I would like to recommend asking your current issue to the existing answer. I deeply apologize that I respect the existing answer. – Tanaike Sep 26 '22 at 05:45
  • it's ok, I believe these hurdles will help me to learn more. You already solved 90% of my issue as I used your old code [link](https://stackoverflow.com/questions/72317511/scraping-subscriber-count-from-youtube-link-in-google-sheets/73719721#73719721) to clean the data. only issue was that as you easily bypass 50 id's limit by using JavaScript limit function. I though might be there is something like that in search option too. – tamey Sep 26 '22 at 05:55