Discussion on: Using Sheets and the YouTube API to track video analytics

[Marco Scarpa]

Hi Rick, I'm getting this error:

TypeError: Cannot call method "setActiveCell" of null.

This is all the script. I've just changed the column names. Name range "IDs" start from row 7 to 200 and on N7 I've my first YT ID...

// This is "Sheet1" by default. Keep it in sync after any renames.
var SHEET_NAME = 'YT Stats 3.2';

// This is the named range containing all video IDs.
var VIDEO_ID_RANGE_NAME = 'IDs';

// Update these values after adding/removing columns.
var Column = {
VIEWS: 'V',
LIKES: 'Y',
DISLIKES: 'Z',
COMMENTS: 'X',
DURATION: 'O'
};

// Adds a "YouTube" context menu to manually update stats.
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var entries = [{name: "Update Stats", functionName: "updateStats"}];

spreadsheet.addMenu("YouTube", entries);
};

function updateStats() {
var spreadsheet = SpreadsheetApp.getActive();
var videoIds = getVideoIds();
var stats = getStats(videoIds.join(','));
writeStats(stats);
}

// Gets all video IDs from the range and ignores empty values.
function getVideoIds() {
var spreadsheet = SpreadsheetApp.getActive();
var range = spreadsheet.getRangeByName(VIDEO_ID_RANGE_NAME);
var values = range.getValues();
var videoIds = [];
for (var i = 0; i < values.length; i++) {
var value = values[i][0];
if (!value) {
return videoIds;
}
videoIds.push(value);
}
return videoIds;
}

// Queries the YouTube API to get stats for all videos.
function getStats(videoIds) {
return YouTube.Videos.list('contentDetails,statistics', {'id': videoIds}).items;
}

// Converts the API results to cells in the sheet.
function writeStats(stats) {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName(SHEET_NAME);
var durationPattern = new RegExp(/PT((\d+)M)?(\d+)S/);
for (var i = 0; i < stats.length; i++) {
var cell = sheet.setActiveCell(Column.VIEWS + (2+i));
cell.setValue(stats[i].statistics.viewCount);
cell = sheet.setActiveCell(Column.LIKES + (2+i));
cell.setValue(stats[i].statistics.likeCount);
cell = sheet.setActiveCell(Column.DISLIKES + (2+i));
cell.setValue(stats[i].statistics.dislikeCount);
cell = sheet.setActiveCell(Column.COMMENTS + (2+i));
cell.setValue(stats[i].statistics.commentCount);
cell = sheet.setActiveCell(Column.DURATION + (2+i));
var duration = stats[i].contentDetails.duration;
var result = durationPattern.exec(duration);
var min = result && result[2] || '00';
var sec = result && result[3] || '00';
cell.setValue('00:' + min + ':' + sec);
}
}

[Rick Viscomi]

The error is saying that getSheetByName("YT Stats 3.2") is coming up empty. Did you rename it without updating the SHEET_NAME variable?