Identity crisis: Using the YouTube Data API with Google Apps Script and Scheduling Live Broadcasts from Google Sheets

One of my faourite Google Apps Script talks I give highlights the ease in which you can connect to other Google Services. As well as the long list of built-in services for the likes of Document, Forms and Spreadsheets by toggling a couple of switches to can quickly start using advanced services like Analytics, Google+ and YouTube allowing you to easily mashup connections to different Google services in the same project.

One use case I often see is using Google Sheets as a database to read/write data to other Google services, the spreadsheet providing an interface recognised by most users. One of the advance services where this can become problematic is YouTube. In YouTube identity isn’t limited to a person as you can also have a YouTube channel owned by a Google+ page. This means you hit authentication issues when you have a script project that mixes a Google+ channel owner and other Google services.

This problem was something I encountered a couple of years ago suggesting a workaround by Using Google Apps Script to proxy YouTube Analytics Channel Reports. This used a separate script project authenticated as a Google+ page to proxy calls to the YouTube reporting API. I recently discovered a new issue when I was trying to interact with our YouTube Live Events data. The problem is outlined in the ‘YouTube API Will Not Authorize a Google+ Page’s YouTube Channel’ issue ticket:

If you are trying to use the YouTube API to get data about a YouTube Channel that you manage using the Google+ association system, it will get you stuck in an infinite loop. Google+ pages can “own” a YouTube channel, and the authorization will not work when trying to use one of those channels

The ticket records that it’s a known issue and the status changed to Won’t Fix (Infeasible), but with the suggestion to:

use the OAuth2 library to authorize access to the G+ page, and then use UrlFetchApp instead of the YouTube Advanced service to make requests to the API

So in this post I’m going to give provide details of how you can use a custom YouTube Data API library to interact with any YouTube account even if it’s owned by a Google+ page. To demonstrate what is possible we’ll create a Google Sheet which can be used to schedule and record access details of YouTube Live Broadcasts. As part of this I’ll also introduce you to how you can easily read and write data to a Google sheet using the Fiddler class which is part of Bruce Mcphersons cUseful library. If you’d rather skip the tutorial you can make a copy of the finished project with the following link:

*** Copy Schedule Youtube Live Broadcasts Sheet ***
[Important: follow the instructions on the auth.gs script file for setting up OAuth access or follow Part 3: Creating client credentials ]

Tutorial – Scheduling Youtube Live Broadcasts from a Google Sheet

This 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 with the required headings and setup the Script Editor with the libraries for this project. For this project we are going to use three libraries:

  • cUseful – a helper library developed by Bruce Mcpherson which contains lots of useful functions. For this project we are going to use the Fiddler class which is used to easily read, manipulate and write data
  • OAuth2 for Apps Script – a library developed by Google for Google Apps Script that provides the ability to create and authorize OAuth2 tokens
  • YouTube – a library I’ve created of YouTube Data API calls

To get started:

  1. Take a copy of this Google Sheet (this includes the required headings, cell data validation and number formats)
  2. Enter some test data on row 2 columns A to F (columns G to I will be automatically filled when the script runs)
    Example data
  3. Open the Script Editor by clicking on the ‘Tools’ menu, then select ‘Script editor…’.
  4. In the Script Editor click on Resources > Libraries… and in the ‘Add a library’ field add the following libraries slecting the latest version:
    1. 1EbLSESpiGkI3PYmJqWh3-rmLkYKAtCNPi1L2YCtMgo2Ut8xMThfJ41Ex – cUseful
    2. 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF – OAuth2 for Apps Script
    3. 13FP5EWK7x2DASsiBXETcr0TQ07OCLEVWOoY1jbVR-bqVpFmsydUSXWdR – YouTube Data API
      Added libraries

Part 2: Creating a YouTube authentication service

Now that we have the Script Editor setup the next steps are to create an authentication service with the OAuth2 library. To do this:

  1. Add a new script file in the Script Editor by clicking File > New > Script file. For the name use ‘auth’
  2. In the create script file copy/paste in the following code and then File > Save. More details about what this does and how it can be customised is available in the OAuth2 library Usage information (double click in the code window to select all):
/**
 * Authorizes and makes a request to the YouTube Data API.
 */
function setup() {
  var service = getYouTubeService();
  YouTube.setTokenService(function(){ return service.getAccessToken(); });
  if (service.hasAccess()) {
    var result = YouTube.channelsList("snippet", {mine:true});
    Logger.log(JSON.stringify(result, null, 2));
    throw "Open View > Logs to see result";
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',
        authorizationUrl);
    throw "Open View > Logs to get authentication url";
  }
}


/**
 * Configures the service.
 */
function getYouTubeService() {
  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(getStaticScriptProperty_('client_id'))
      .setClientSecret(getStaticScriptProperty_('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
      // you might want to switch to Script Properties if sharing access
      .setPropertyStore(PropertiesService.getUserProperties())

      // Set the scope and additional Google-specific parameters.
      .setScope(["https://www.googleapis.com/auth/youtube",
      "https://www.googleapis.com/auth/youtube.force-ssl",
      "https://www.googleapis.com/auth/youtube.readonly",
      "https://www.googleapis.com/auth/youtubepartner",
      "https://www.googleapis.com/auth/youtubepartner-channel-audit"])
      .setParam('access_type', 'offline');
}

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

/**
 * Logs the redirect URI to register in the Google Developers Console.
 */
function logRedirectUri() {
  var service = getYouTubeService();
  Logger.log(service.getRedirectUri());
  throw "Open View > Logs to get redirect url";
}

/**
 * Reset the authorization state, so that it can be re-tested.
 */
function reset() {
  var service = getYouTubeService();
  service.reset();
}

/**
 * Gets a static script property, using long term caching.
 * @param {string} key The property key.
 * @returns {string} The property value.
 */
function getStaticScriptProperty_(key) {
  var value = CacheService.getScriptCache().get(key);
  if (!value) {
    value = PropertiesService.getScriptProperties().getProperty(key);
    CacheService.getScriptCache().put(key, value, 21600);
  }
  return value;
}

Part 3: 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:

  1. In the Script Editor select Resources > Cloud platform project… and click the link to the currently associated project (this should begin ‘Youtube Schedule Live Broadcas’ followed by a project id)
  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’
    Enabling YouTube Data API
  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 Script Editor click Run > Run function > logRedirectUri. From the View > Logs copy the url into the ‘Authorised redirect URIs’ field API console, click ‘Create’
    Create credentials
  9. In the Script Editor open File > Project properties and in the Script properties tab and new rows for client_id and client_secret copying the values from the API console
    Project properties
  10. In the Script Editor click Run > Run function > setup and then from the View > Logs copy the url in a new browser tab.
  11. Finally select the YouTube account you would like to schedule broadcasts for
    select the YouTube account

You can test if you have successfully setup and connect with the YouTube Data API by running the setup function again and checking the Log and to see if channel data is returned

Example log output

Part 4: Interacting with the YouTube Data API and scheduling live events

Now that we have a connection to your YouTube channel we need to make some API calls to create our YouTube Live Events. The YouTube Streaming API documentation has more details about this but the basic process is to schedule events (broadcasts) create a live stream and bind these together.

  1. In the Script Editor switch to the Code.gs script file and copy/paste the following code and Save (double click in the code window to select all):
/**
 * @OnlyCurrentDoc
 */

YouTube.setTokenService(function(){ return getYouTubeService().getAccessToken(); });

// Read data from current sheet and create live events returning details back to sheet
function schedule_events(){
  var sheet = SpreadsheetApp.getActiveSheet();
  new cUseful.Fiddler(sheet)
  .mapRows (function (row) {
    if (!row.youtube_url && !row.stream_key){ // if not already scheduled
      // format datetime to ISO 8601 format as per docs https://developers.google.com/youtube/v3/live/docs/liveBroadcasts#snippet.scheduledStartTime
      row.start_time = Utilities.formatDate(row.start_time, Session.getScriptTimeZone(), "YYYY-MM-dd'T'HH:mm:ssZZZZZ");
      row.end_time = Utilities.formatDate(row.end_time, Session.getScriptTimeZone(), "YYYY-MM-dd'T'HH:mm:ssZZZZZ");
      // step 1 - create a broadcast
      var broadcast_resource = insert_broadcast(row);
      // step 2 - create a stream
      var stream_resource = insert_stream(row);
      // step 3 - bind broadcast to stream
      var bind_result = bind_broadcast(broadcast_resource.id, stream_resource.id);
      
      // step 4 - prep data to add back to the sheet
      row.youtube_url = "https://youtu.be/"+broadcast_resource.id;
      
      var ingestionInfo = stream_resource.cdn.ingestionInfo;
      row.stream_key = ingestionInfo.streamName;
      row.ingestion_address = ingestionInfo.ingestionAddress;
      
      row.start_time = new Date(row.start_time);
      row.end_time = new Date(row.end_time);
    }
    return row;
  }).dumpValues();
}


// The following is based on 
// https://developers.google.com/youtube/v3/live/code_samples/python#create_a_broadcast_and_stream
// LICENCE Copyright Google http://www.apache.org/licenses/LICENSE-2.0

// Create a liveBroadcast resource and set its title, scheduled start time,
// scheduled end time, and privacy status.
function insert_broadcast(options){
  var insert_broadcast_response = YouTube.liveBroadcastsInsert("snippet,status",
                                                               {"snippet": {
                                                                 "title": options.title,
                                                                 "description": options.desc,
                                                                 "scheduledStartTime": options.start_time,
                                                                 "scheduledEndTime": options.end_time,
                                                               },
                                                                "status": {
                                                                  "privacyStatus": options.privacy_status,
                                                                }
                                                               },{});

  Logger.log(insert_broadcast_response);
  return insert_broadcast_response;
}

// Create a liveStream resource and set its title, format, and ingestion type.
// This resource describes the content that you are transmitting to YouTube.
function insert_stream(options){
  var insert_stream_response = YouTube.liveStreamsInsert("snippet,cdn",
                                                         {"snippet": {
                                                           "title":options.title,
                                                           "description": options.description,
                                                         },
                                                          "cdn": {
                                                            "format": options.format,
                                                            "ingestionType":"rtmp"
                                                          }
                                                         },{});

  Logger.log(insert_stream_response);
  return insert_stream_response;
}

// Bind the broadcast to the video stream. By doing so, you link the video that
// you will transmit to YouTube to the broadcast that the video is for.
function bind_broadcast(broadcast_id, stream_id){
  var bind_broadcast_response = YouTube.liveBroadcastsBind(broadcast_id, "id,contentDetails", {"streamId":stream_id});
  Logger.log(bind_broadcast_response);
  return bind_broadcast_response;
}
  1. Run > Run function > schedule_events to schedule events on your YouTube channel

If the function runs successfully viewing the data entry sheet should now include details of the created event.

Summary

While the initial setup for this is perhaps a little daunting it hopefully provides a useful starting point for developing this further. For example, within schedule_events() you could include calls to CalendarApp to include details of events in a Google Calendar. You may wish to extend even further by creating functions that return data about the live streamed event along the lines of ‘How to get stats about youtube videos in your channel with apps script’. If you do anything interesting with this please share :)