Search API Connector Documentation

Print

Import YouTube Analytics Data to Google Sheets

YouTube provides several different APIs for extracting data from the platform:

  • The YouTube Public Data API allows you to retrieve lists of content based on search parameters. It also provides some basic statistics like views, likes, comment counts, and subscriber counts, for all public videos.
  • The YouTube Reporting API is focused on reporting, and provides bulk reports of YouTube analytics data for your own channel, including detailed metrics and ad performance reports. These bulk reports are generated daily and are designed to be downloaded and stored in a data warehouse, where you can then query the data yourself.
  • The YouTube Analytics API is very similar to the Reporting API, but instead of daily bulk reports, it lets you target your queries through filtering and sorting parameters. For example, you can specify a date range in your request, which is not possible in the Reporting API. (This table provides a summary of the differences between YouTube’s Reporting and Analytics APIs). Unlike the YouTube Public Data API, this API allows you to query private statistics about your channel, like video watch times and demographic breakdowns.

This guide will focus on the YouTube Analytics API. We will pull YouTube video data directly into Google Sheets, using the API Connector add-on for Sheets. The YouTube Analytics API only supports authentication via OAuth2, so we’ll be connecting using API Connector’s built-in OAuth2 integration for YouTube Analytics (this is a paid feature, please install API Connector for a free trial or upgrade to access).

YouTube’s Public Data API is discussed in this article.

CONTENTS

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: CONNECT TO THE YOUTUBE ANALYTICS API

If you haven’t connected API Connector to YouTube Analytics before, you’ll first need to initiate the connection as follows:

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find YouTube and click Connect.
    youtube-img2
  3. You will see a couple of screens asking you to log in and approve the connection. Click Allow.
    youtube-img3
  4. You’ll now be returned to your Google Sheet, and can verify that your YouTube connection is active in the Connections screen.
    youtube-img4

PART 2: CREATE A YOUTUBE ANALYTICS API REQUEST URL

For our first request, we’ll get some basic information about your YouTube account. Again, the YouTube Analytics API only lets you query your own data. If you’re looking for public YouTube data, you’ll need the YouTube Public Data API.

  • API root: https://youtubeanalytics.googleapis.com
  • Endpoint: /v2/reports
  • Parameters: ?dimensions=video&sort=-views&metrics=estimatedMinutesWatched,views,likes,subscribersGained&ids=channel==MINE&startDate=2021-01-01&endDate=2021-12-31&maxResults=200

Putting it together, we get the full API Request URL.

https://youtubeanalytics.googleapis.com/v2/reports?dimensions=video&sort=-views&metrics=estimatedMinutesWatched,views,likes,subscribersGained&ids=channel==MINE&startDate=2021-01-01&endDate=2021-12-31&maxResults=200

PART 3: PULL YOUTUBE ANALYTICS API DATA INTO SHEETS

Now let’s enter our URL into API Connector and import YouTube data into Google Sheets.

  1. In the Create Request interface, enter the Request URL we just created.
    youtube-analytics-api-img6
  2. We don’t need any headers for this API, so just leave that section blank.
  3. Under Authentication, choose YouTube from the Connections dropdown.
    youtube-img5
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Under Output Options, choose Compact style. For our purposes, the YouTube Analytics API outputs data in a pretty inconvenient format (it returns all the field headers in their own array, then all the data separately), and this style provides a clearer view of the data.
  6. Name your request and click Run. A moment later you’ll see some information about your account populate your sheet.
    youtube-analytics-api-img7
  7. Tip: for an alternative view of the data, try “default (single row)” report style instead and enter the following JMESPath: {Keys:columnHeaders[].name,rows:rows}. This will split your data into separate columns. It’s not ideal either, but probably better than the above.
    youtube-analytics-api-img8

PART 4: MORE EXAMPLE API URLS

You can access the full YouTube Analytics API documentation here, but if you just want to get started, you can try the following URLs (one at a time).

  • Aggregated metrics for a channel
    https://youtubeanalytics.googleapis.com/v2/reports?metrics=views,comments,likes,dislikes,estimatedMinutesWatched,averageViewDuration&ids=channel==MINE&startDate=2021-01-01&endDate=2021-12-31&maxResults=200
  • Most watched videos for your channel
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=video&metrics=estimatedMinutesWatched,views,likes,subscribersGained&maxResults=10&sort=-estimatedMinutesWatched&startDate=2021-01-01&endDate=2021-12-31&maxResults=200&ids=channel==MINE
  • Metrics summarized by month
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=month&metrics=views,estimatedMinutesWatched,averageViewDuration,averageViewPercentage,subscribersGained&sort=month&startDate=2021-01-01&endDate=2021-12-31&ids=channel==MINE
  • Demographic breakdown
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=ageGroup,gender&metrics=viewerPercentage&startDate=2021-01-01&endDate=2021-12-31&ids=channel==MINE
  • View counts and watch time by traffic source
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=day,insightTrafficSourceType&metrics=views,estimatedMinutesWatched&sort=day&startDate=2021-01-01&endDate=2021-12-31&ids=channel==MINE

Many additional sample queries are provided in YouTube’s list of sample requests. In addition, they provide an API Explorer tool that simplifies the process of testing and constructing queries.

PART 5: HANDLE PAGINATION

Let’s get it out of the way immediately, this section doesn’t contain a real way to handle pagination. You can add maxResults=200 to the end of your API request URL, but after testing and searching through documentation, it seems that this API doesn’t support paginating through results to retrieve more than 200 records at a time. If you have more than 200 videos, you can try using the filters parameter to break your data into chunks such that it can be retrieved in groups of less than 200 at a time, and then run those request URLs separately.

For example, a request filtered by video ID would include filters=video==<list of video IDs>, like this:

https://youtubeanalytics.googleapis.com/v2/reports?dimensions=video&metrics=views,estimatedMinutesWatched,likes,dislikes,averageViewPercentage,averageViewDuration&sort=-views&maxResults=200&ids=channel==MINE&startDate=2020-01-01&endDate=2021-12-31&filters=video==fbY6r12Kyyo,n84bsqaer-g,ik2uk5pHyEA,XxYv3LIJtmo

Beyond that, you would need to get clever with sorting, or just use the YouTube Public Data API instead, which does support pagination. Of course, if you find any solutions I’ve missed, please leave a comment below.

PART 6: YOUTUBE COMPLIANCE

To comply with the terms of YouTube’s API, requests are subject to the following conditions:

  1. All saved requests to YouTube will refresh every 30 days, even if you have not set up a scheduled refresh.
  2. All YouTube requests will show only the metric name rather than the full path in the header row.

Relevant Terms:

API Clients may store all other types of Authorized Data not identified in section (III.E.4.b) for as long as is necessary for the purposes of the specific consent granted by an active user and for no longer than 30 calendar days. After 30 calendar days, the API Client must either delete or refresh the stored data”. (link)

“Your API Clients must not (i) replace API Data with similar, independently calculated data, or (ii) access or use API Data to create new or derived data or metrics.” (link)

Previous Import Xero Data to Google Sheets
Next Import YouTube Data to Google Sheets

8 thoughts on “Import YouTube Analytics Data to Google Sheets”

  1. First of all, thank you so much for this amazing tool!

    I am, however, running into some trouble with pagination for the Youtube analytics API using the “video” dimension (whole request is below). I simply want to retrieve data for all videos on my channel. Do you know what settings I should use in order to get pagination working?

    “https://youtubeanalytics.googleapis.com/v2/reports?dimensions=video&metrics=views,estimatedMinutesWatched,likes,dislikes,averageViewPercentage,averageViewDuration&sort=-views&startDate=2014-01-01&endDate=2061-12-31&maxResults=200&ids=channel==MINE”

    Kind regards.

    Reply
    • The docs show the startIndex / maxResults parameters, but it seems like these still don’t let you paginate beyond 200 total results. I’ll update the article to reflect that.

      Reply
  2. Absolutely amazing article! hi there thanks for sharing this piece of information with us, I was looking for something like this as importing the data is an important part of analyzing a video’s performance. Thanks again for sharing it with us. you really eased my task.

    Reply
  3. Hi – this is the tool I’ve been looking for! But I can’t seem to get more than 1 dimension at a time to work. I would like to get view counts by video by day (actually, I’d like by Country as well, but let’s just get two working for now!)

    This is what I have:

    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=day&metrics=estimatedMinutesWatched,views,likes,subscribersGained&ids=channel==MINE&startDate=2020-01-01&endDate=2021-12-31

    The above works fine.

    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=day,video&metrics=estimatedMinutesWatched,views,likes,subscribersGained&ids=channel==MINE&startDate=2020-01-01&endDate=2021-12-31

    But if I add in video it errors. in the docs ‘video’ seems to be a valid dimension, but it doesn’t work when I use it in the API. Please help! 😉

    Reply
    • Good question. Unfortunately the YouTube API is really outdated and doesn’t provide a lot of data points that people would obviously be interested in, for example video views per day. As far as I can tell YouTube doesn’t permit this combination. It’s not clearly written in their docs, but their examples don’t show this combination and I see other people asking this same question online (e.g. here, here)

      So I think you can only get this by pulling dates one by one, like this:
      https://youtubeanalytics.googleapis.com/v2/reports?dimensions=video&sort=-views&metrics=estimatedMinutesWatched,views,likes,subscribersGained&ids=channel==MINE&startDate=2021-05-23&endDate=2021-05-23&maxResults=200

      Then you would need to repeat that for each day. Obviously that’s not ideal, but you could list these one after the other or reference cells in the sheet to make this a bit more convenient (info).

      Reply
    • Hi Mark,
      I found a workarround for the problem. You need to create a second table with a list of all video IDs (Maybe use a more general request with the API Connector) and then add a filter parameter to your first request like so: &filters=video==+++!videoIDsA2:A+++
      The information between the +++ is the column with the Video IDs so the Connector makes new requests for each video in the list.
      Hope this helps a bit.

      Reply
  4. Thanks so much for your reply!

    Well, at least it’s not me doing something wrong…but it is surprising that (what I consider to be) the primary Use Case isn’t supported by the API…

    Maybe I can get something like Postman to run it multiple times with changing dates?

    Reply
    • Yeah it’s pretty weird, their API doesn’t support key use cases and has strange limits. You can definitely run it in Postman by manually changing the dates and re-running, but I’m not sure if Postman has an automatic feature to cycle through requests like API Connector does.

      Reply

Leave a Comment

Table of Contents