Get YouTube Analytics Data With Google Apps Script

This article explain here steps to configure Google Sheet and Apps Script to get Youtube Analytics data like views, subscribersGained, estimatedRevenue, grossRevenue, cpm etc for all videos uploaded for the linked channel account.

1. Create a New Google Spreadsheet And Add Script

var CLIENT_ID     = 'clientid';
var CLIENT_SECRET = 'clientsecret';

function putInSheet(result) {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getActiveSheet();

  sh.getDataRange().clearContent();
  sh.appendRow(["Video ID","views","subscribersGained","estimatedRevenue","grossRevenue","estimatedAdRevenue","adImpressions","cpm"]);
  
  sh.getRange(2,1,result.length, result[0].length).setValues(result);
}

//Authorizes and makes a request to the Youtube API.
function runScript() {
  var myChannels = YouTube.Channels.list('id', {mine: true});
  var channel = myChannels.items[0];
  var channelId = channel.id;
   
  var today = new Date();
  var oneMonthAgo = new Date();
  oneMonthAgo.setMonth(today.getMonth() - 1);
  var todayFormatted = Utilities.formatDate(today, 'UTC', 'yyyy-MM-dd')
  var oneMonthAgoFormatted = Utilities.formatDate(oneMonthAgo, 'UTC', 'yyyy-MM-dd');
  
  var service = getService();
  if (service.hasAccess()) {
    var videoIDs = getVideoIDs();
    var result = [];
    videoIDs.forEach(function(videoID){
      var url = "https://youtubeanalytics.googleapis.com/v2/reports" +
        "?ids=channel=="+ channelId +
          "&startDate="+ oneMonthAgoFormatted +"&endDate="+ todayFormatted +
            "&metrics=views,subscribersGained,estimatedRevenue,grossRevenue,estimatedAdRevenue,adImpressions,cpm" + 
            //"&metrics=views,subscribersGained" + 
               "&filters=video==" + videoID;
      
      var response = UrlFetchApp.fetch(url, {
        headers: {
          Authorization: 'Bearer ' + service.getAccessToken()
        }
      });
      var row = [videoID];
      row = row.concat(JSON.parse(response.getContentText()).rows[0]);
      result.push(row);
    });
    
    putInSheet(result);
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    
    Browser.msgBox(authorizationUrl)
  }
}

//Get playlist of the uploaded videos of the channel
function getVideoIDs() {
  var playlist = JSON.parse(YouTube.Channels.list("contentDetails", {mine: true}))
     .items[0].contentDetails.relatedPlaylists.uploads;
  
  var vids = [];
  var nextPage;
  do {
    var list = JSON.parse(YouTube.PlaylistItems.list("snippet", {playlistId : playlist, maxResults:50, pageToken:nextPage}));
    nextPage = list.nextPageToken;
    
    list.items.forEach(function(item) {
      vids.push(item.snippet.resourceId.videoId);
    });
  } while(nextPage);
  
  return vids;
}

//Reset the authorization state, so that it can be re-tested.
function reset() {
  getService().reset();
}

//Configures the service.
function getService() {
  return OAuth2.createService('Youtube')
      //Set the endpoint URLs.
      .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth')
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')

      //Set the client ID and secret.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)

      //Set the name of the callback function that should be invoked to complete the OAuth flow.
      .setCallbackFunction('authCallback')

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getUserProperties())

      //Set the scope and additional Google-specific parameters.
      .setScope('https://www.googleapis.com/auth/yt-analytics.readonly https://www.googleapis.com/auth/yt-analytics-monetary.readonly https://www.googleapis.com/auth/youtube https://www.googleapis.com/auth/youtubepartner')
      .setParam('access_type', 'offline')
      .setParam('approval_prompt', 'force')
      .setParam('login_hint', Session.getActiveUser().getEmail());
}

//Handles the OAuth callback.
function authCallback(request) {
  var service = getService();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success!');
  } else {
    return HtmlService.createHtmlOutput('Denied.');
  }
}

//Logs the redict URI to register in the Google Developers Console.
function logRedirectUri() {
  Logger.log(getService().getRedirectUri());
}

2. Get Script ID

Click on File->Project Properties, copy script id which we will use in later steps.

3. Adding OAuth2 Library

OAuth2 for Apps Script is a library for Google Apps Script that provides the ability to create and authorize OAuth2 tokens as well as refresh them when they expire.
This library is already published as an Apps Script, making it easy to include in your project. 

To add OAuth2 library to your script, do the following in the Apps Script code editor:

1. Click on the menu item “Resources > Libraries…”
2. In the “Find a Library” text box, enter the script ID

1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF 

and click the “Select” button.

3. Choose a version in the dropdown box (usually best to pick the latest version).
4. Click the “Save” button.

4. Enable Youtube Data API and YouTube Analytics API

In the Apps Script editor, click Resources > Advanced Google Services.

  1. Locate YouTube Data API and YouTube Analytics in the dialog and click the corresponding toggle, setting it to on.
  2. Click the Google Cloud Platform API Dashboard link in the box below the list of services:
  3. Enter “YouTube Data API v3” into the search box and click on the corresponding entry in the results, Click the Enable button.
  4. Similarly search YouTube Analytics API and Enable.
  5. Return to the Apps Script code editor and click the OK button in the Advanced Google Services dialog. 

5. Create OAuth2 Credentials

On the Credentials page, select Create credentials, then select OAuth client ID.

Select Web Application and set a name, In Authorized redirect URIs Enter below URL
https://script.google.com/macros/d/{Script ID}/usercallback

Replace {Script ID} with script ID in found in Step 2 which is 1SWqweb6L8TLFR0tikpFmgr2ndogU7s05SEqGppE_jgJ7gNY2wIj1W4us

Click on Create button, below screen will appear with Client ID and Client Secret, Copy and paste Client Id and Client Secret in the script and save.

Run the function runScript, it will open message box with a URL.

Copy this url and open this url in new tab.

This app isn’t verified screen will appear, here you need to authorize it.

It should display a Success after authorization.

Run the same function runScript again which will populate analytics data on sheet.

3 Responses

Add a Comment

Your email address will not be published. Required fields are marked *