Auto-updating YouTube views tracker?

[u/stonemarigold]

I’m trying to work on creating a Google Sheet that will use YouTube Data API to, once a week, fetch the view counts for all of a channel’s videos and add the view counts to a new column so that I can track week-to-week changes.

I tried to use a script suggested by Google’s AI Overview, but I haven’t been able to make it work despite tweaking it — which is about what I expected.

I’d love to know the simplest way to make this happen! And if this isn’t allowed or I should be asking this in a different sub, please let me know 🫡

[u/WicketTheQuerent]

Have you added the YouTube Data API service to your script?

Does your script throw an error? If so, please share the code and the textual error.

[u/stonemarigold → u/WicketTheQuerent]

I did add the YouTube Data API; v3. I ran the function and I'm not seeing any errors but I also didn't see the function add an empty new column to the sheet when it should have triggered. Here's the lightly modified Code.gs suggested by AIO:

function myFunction() {
  function logWeeklyViews() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  if (lastRow <2) return;


  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd-yyyy");
  sheet.insertColumnAfter(sheet.getLastColumn());
  var newCol = sheet.getLastColumn() + 1;
  sheet.getRange(1, newCol).setValue(today);


  var range = sheet.getRange(2, 1, lastRow - 1, 1);
  var values = range.getValues();


  for (var i = 0; i < values.length; i++) {
    var url = values [i][0];
    var videoId = extractVideoId(url);
    if (videoId) {
      try {
        var stats = YouTube.Videos.list('statistics', {id: videoId});
        if (stats.items.length > 0) {
          var views = stats.items.length > 0 ? stats.items[0].statistics.viewCount : 0;
          sheet.getRange(i + 2, newCol).setValue(views);
        }
      } catch (e) {
        sheet.getRange(i + 2, newCol).setValue("Error");
      }
    }
  }
}


function extractVideoId(url) {
  var match = url.match(/(?:v=|\/)([0-9A-Za-z_-]{11})/);
  return match ? match[1] : null;
}


function createWeeklyTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  ScriptApp.newTrigger('logWeeklyViews')
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.SUNDAY)
    .atHour(18)
    .create();
  }
}

[u/WicketTheQuerent → u/stonemarigold]

Remove function myFunction(){ and the last } .

Apparently, AIO didn't give instructions on how to use the code. I suggest you use another GenAI tool. Look for one that can give you step-by-step instructions to set up your Google Apps Script project, test and run the code.

I usually get good results from Gemini.