Getting YouTube Analytics with Google Apps Script when you get authentication loops

In my previous post I provided a tutorial on using the YouTube Data API with Google Apps Script for situations where you were using branded channels (channels not directly owned by a Google account). In this post I’m going to revisit this topic looking at the YouTube Analytics API.

If you are only interested in data from a YouTube channel owned by a Google account you are better off looking at this example from the Google Apps Script documentation which uses the built-in Google Apps Script advanced services for YouTube Data and YouTube Analytics. (Another example ‘How to get stats about youtube videos in your channel with apps script’ by Bruce Mcpherson)

As the procedure is almost identical I’m going to hopefully keep your interest by demonstrating a different OAuth library, using Goa by Bruce Mcpherson, and show how you can setup your project to interact with multiple Google APIs, in this case the YouTube Data and Analytics API. As with the previous post if you just want to grab the source you can copy the finished project below:


Note: You still need to complete Part 2: Creating client credentials for your copy to work

Tutorial – Accessing YouTube Analytics for branded accounts

This tutorial is based on the code example in the YouTube Analytics and Reporting API documentation but has been slightly modified for use within a single Google Sheet to avoid app verification warning. The tutorial is divided into the following sections:

Part 1: Setting up the Google Sheet and Script Editor

To start with we are going to create a Google Sheet and setup the Script Editor with the libraries for this project. For this project we are going to use three libraries:

  • cGoa – library developed by Bruce Mcpherson to handle OAuth
  • YouTube – a library I’ve created of YouTube Data API calls
  • YouTubeAnalytics – a library I’ve created for the YouTube Analytics API

To get started:

  1. In Google Drive create a new Google Sheet
  2. Open the Script Editor by clicking on the Tools menu, then select Script editor….
  3. In the Script Editor click on Resources > Libraries… and in the Add a library field add the following libraries selecting the latest version:
    • MZx5DzNPsYjVyZaR67xXJQai_d-phDA33 – cGoa
    • 13FP5EWK7x2DASsiBXETcr0TQ07OCLEVWOoY1jbVR-bqVpFmsydUSXWdR – YouTube Data API
    • 1Tou3otKvYPaiubK-yydlaMIF1cPEAQkAaL88Gx14bh4qRxN3SMxzpDGL – YouTube Analytics API
      Libraries setup

Part 2: Creating client credentials

To use the authentication service we’ve created we need to provide client credentials. To do this we need to setup a Google Cloud Platform project and create credentials. I’ve included some screenshots in these steps but if you prefer more Bruce has prepared this Google Slides presentation:

  1. In the Script Editor select Resources > Cloud platform project… and click the link to the currently associated project (this should begin with your script file name followed by a project id and a random string)
  2. In the Google Cloud Platform window click Go to APIs overview
  3. In APIs & services click Enable APIs and Services
  4. In the Library search/click on YouTube Data API and click Enable doing the same for the YouTube Analytics API
    Enabling APIs
  5. Still in the APIs & services screen click on Credentials from the side menu
  6. Click the Create credentials, select OAuth Client ID then Web application
  7. Enter a name as required and in the Authorised JavaScript origins enter https://script.google.com
  8. In the Script Editor copy/paste the following code inserting your own client ID and secret where required (Goa also provides an option to read credentials from a downloaded file):
function oneOffScript() {
  var options =  {
    packageName: 'youtube-analytics', // user defined
    clientId: 'YOUR_CLIENT_ID_HERE',
    clientSecret:'YOUR_CLIENT_SECRET_HERE',
    // following method to add Google scopes. Here we are mixing YouTube Data and Analytics (prefixed yt-)
    // in the same service allowing calls to both APIs with the same token (we can do this because both are Google APIs)
    // More on mixing services http://ramblings.mcpher.com/Home/excelquirks/goa/mutipleconsent
    scopes : cGoa.GoaApp.scopesGoogleExpand (['youtube',
                                              'youtube.force-ssl',
                                              "youtubepartner",
                                              "youtubepartner-channel-audit",
                                              "yt-analytics-monetary.readonly",
                                              "yt-analytics.readonly"]),
    service:'google' // always this for Google APIs. Ref: http://ramblings.mcpher.com/Home/excelquirks/goa/services
  };
  // store one off in user properties
  cGoa.GoaApp.setPackage(PropertiesService.getUserProperties(), options);
}
function doGet(e) {
  var goa = cGoa.GoaApp.createGoa ('youtube-analytics',
       PropertiesService.getUserProperties()).execute (e);
  if (goa.needsConsent()) {
    return goa.getConsent();
  }
  return HtmlService.createHtmlOutput (goa.hasToken() ?
        'your token has been stored' : 'failed to get token')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
  1. In the Script Editor click Run > Run function > oneOffScript
  2. Next from the Script Editor select Publish > Deploy as web app and open the current web app URL
    Publish as web app [click to enlarge]Get Redirect URL [click to enlarge]
  3. Goa will detect if authentication is required and if so provides a Redirect Url. To add this url return to the Google Cloud Platform console window and edit the web client you just created saving the new Redirect URL.
    Edit credentials [click to enlarge]Adding redirect URL [click to enlarge]
  4. When you have done this return to the published web app and click start which will allow you to select the YouTube account you would like get an analytics report for. If successful you should see a ‘your token has been stored’ message
    Selecting the YouTube account to get data for
  5. Finally, delete the oneOffScript() and doGet() functions and unpublish the web app as these are no longer required and it’s better to not have client credentials left in your script project

Once Goa has been set up there is no need for any more interaction and your access token can be fetched using:

var goa = cGoa.GoaApp.createGoa ('youtube-analytics',
               PropertiesService.getUserProperties()).execute ();
if (goa.hasToken()) {
   var token = goa.getToken();
}

Note: Including cGoa.GoaApp.createGoa () in a script before you’ve gone through the steps above will throw an error as the package hasn’t been fully setup.

The procedure above has is based on the assumption of only the script owner requires authenticated access. Bruce has provided other examples to cover scenarios where you would like users to authenticate access, for example, from the sidebar or dialogs. Here are some links taken from Bruce’s site:

Part 3: Interacting with the YouTube Analytics API

Now that we have a connection to your YouTube channel we need to make some API calls to get our channel id and fetch an analytics report. The YouTube Analytics API documentation has more details about this if you want to read more, but to complete the project you can follow these steps:

  1. In the Script Editor copy/paste the following code and Save (double click in the code window to select all):
/**
 * @OnlyCurrentDoc
 */
function spreadsheetAnalytics() {
  var goa = cGoa.GoaApp.createGoa('youtube-analytics',
                                  PropertiesService.getUserProperties())
                                  .execute();
  YouTube.setTokenService(function(){ return goa.getToken(); } );
  YouTubeAnalytics.setTokenService(function(){ return goa.getToken(); });
  // Get the channel ID
  var myChannels = YouTube.channelsList('id', {mine: true});
  var channel = myChannels[0];
  var channelId = channel.id;
  // Set the dates for our report
  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');
  // For the YouTubeAnalytics.Reports.query() options see
  // https://developers.google.com/youtube/analytics/reference/reports/query
  var analyticsResponse = YouTubeAnalytics.reportsQuery({ids:'channel==' + channelId,
                                                         startDate: oneMonthAgoFormatted,
                                                         endDate: todayFormatted,
                                                         metrics: 'views,likes,dislikes,shares',
                                                         dimensions: 'day',
                                                         sort: '-day'
                                                        });
  // Create a new sheet with rows and columns corresponding to our dates
  var sheetName = 'Report ' + oneMonthAgoFormatted + ' - ' + todayFormatted;
  var numRows = analyticsResponse.rows.length;
  var numCols = analyticsResponse.columnHeaders.length;
  // Get the bound spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // remove any existing sheet for this report
  if (ss.getSheetByName(sheetName)){
    ss.deleteSheet(ss.getSheetByName(sheetName));
  }
  // insert and set rows/cols
  var sheet = ss.insertSheet(sheetName, {template: ss.getSheetByName('TMP')});
  sheet.insertColumnsAfter(1, numCols-1).insertRowsAfter(1, numRows-1);
  // Get the range for the title columns
  // Remember, spreadsheets are 1-indexed, whereas arrays are 0-indexed
  var headersRange = sheet.getRange(1, 1, 1, numCols);
  var headers = [];
  // These column headers will correspond with the metrics requested
  // in the initial call: views, likes, dislikes, shares
  for(var i in analyticsResponse.columnHeaders) {
    var columnHeader = analyticsResponse.columnHeaders[i];
    var columnName = columnHeader.name;
    headers[i] = columnName;
  }
  // This takes a 2 dimensional array
  headersRange.setValues([headers]);
  // Bold and freeze the column names
  headersRange.setFontWeight('bold');
  sheet.setFrozenRows(1);
  // Get the data range and set the values
  var dataRange = sheet.getRange(2, 1, numRows, numCols);
  dataRange.setValues(analyticsResponse.rows);
  // Bold and freeze the dates
  var dateHeaders = sheet.getRange(2, 1, numRows, 1);
  dateHeaders.setFontWeight('bold');
  sheet.setFrozenColumns(1);
  // Include the headers in our range. The headers are used
  // to label the axes
  var range = sheet.getRange(1, 1, numRows, numCols);
  var chart = sheet.newChart()
                   .asColumnChart()
                   .setStacked()
                   .addRange(range)
                   .setPosition(4, 2, 10, 10)
                   .build();
  sheet.insertChart(chart);
}
  1. Run > Run function > schedule_events to schedule events on your YouTube channel

If the function runs successfully viewing the sheet should now include some data from YouTube Analytics similar to the following:

Summary

Hopefully this tutorial has shown you how to get YouTube Analytics data with Google Apps Script when working with branded accounts. It has also hopefully shown you how easy it is to combine multiple Google APIs when using OAuth libraries like Goa, the key being adding the required scopes and enabling the required APIs in the Google Cloud Platform console project. One final note from Bruce said in this scenario you could probably be able to use a service account which would mean there would be no need to bother with publishing as a web app to get a redirect URL … I’ll let you try that one 😉

chevron_left
chevron_right
css.php