Search API Connector Documentation

Print

Import YouTube Public Data to Google Sheets

In this guide, we'll walk through how to pull public video data like views, comments, and likes from the YouTube Public Data API directly into Google Sheets using the API Connector add-on for Sheets.

There are 2 ways to connect to the YouTube Public Data API:

  • Preset "Connect" button (OAuth) premium
  • Personal API key. Please check the appendix for detailed instructions to retrieve your key.

Contents

Before You Begin

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

Part 1: Connect to the YouTube Data API

The easiest way to get started with the YouTube Public Data API is through API Connector’s built-in integration.

  1. Select YouTube Public Data from the drop-down list of applications
    youtube-application
  2. Under Authorization, click Connect to YouTube Public Data
    youtube-authorization
  3. You will see a couple of screens asking you to log in and approve the connection. Click Allow.
    youtube-img3
  4. You'll then be returned to your Google Sheet, and can verify that your YouTube Data connection is active.

Part 2: Pull Public Data from YouTube to Sheets

Now that we’re connected, let’s pull some data into Sheets.

  1. Under Endpoint, choose the data you’d like to retrieve. We'll start with /channels.
    youtube-endpoints
  2. Fill in any required parameters. For the /channels endpoint we need to populate the id parameter with at least one channel ID.
  3. To get a channel ID, visit the channel page on YouTube, right-click > View Page Source, and search for https://www.youtube.com/channel/ in the page source. The channel ID will appear directly after the /channel/ text.
    youtube-channelId
  4. Optionally set any optional parameters, such as maxResults to get more records, or part to select which data points should be included in the response.
  5. Select a destination sheet, name your request, and click Run.
    youtube-response

Part 3: Create a Custom API Request

Alternatively, you can create a custom request instead of using API Connector’s built-in integration. To create a custom request, add the complete URL into the API URL field and select YouTube from the OAuth menu.
youtube-custom

To easily convert your preset request to a custom request, click Output Options > More Options > Add Request URL. This will print your URL into your sheet where you can copy/paste it.

Part 4: Handle Filtering

When creating custom requests, you can filter your results using the "fields" parameter as described here. For example, this filtered URL will produce a list of metrics like video titles, view counts, like counts, dislike counts, etc. for all the video IDs listed in the id parameter:

https://www.googleapis.com/youtube/v3/videos?part=snippet,contentDetails,statistics&id=qhJOSOBtAlc,4JJd7gmlobY,YVMtL6rexKQ,pJpApDVCXPg,MaeRXppkzdA&fields=items(id),items(snippet(publishedAt,title)),items(contentDetails(duration)),items(statistics(viewCount,likeCount,dislikeCount,commentCount))

If you're using pagination handling, please make sure your field list includes nextPageToken.

You can also filter data through API Connector's visual field editor (just click Edit Fields to enter).

Part 5: Handle Pagination

By default YouTube limits search results and lists of channel/playlist videos to 5 records, which can be increased to by setting the maxResults parameter to 50. If you want to retrieve more than 50 results at a time, you'll need to traverse the paginated results as described here.

This can be handled automatically by API Connector through the pagination handling feature, like this:

Even after applying pagination, YouTube limits total video results to about 500 (info)
  • Pagination type: cursor
  • Next token parameter: pageToken
  • Next token path: nextPageToken
  • Run until: choose when to stop fetching data
    youtube-pagination-cursor

Part 6: YouTube Compliance

To comply with the terms of YouTube's API, requests using API Connector's YouTube OAuth connection 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)

Part 7: API Documentation

Official API documentation: https://developers.google.com/youtube/v3/docs/search/list

Appendix: Connect with a YouTube Data API Key

YouTube requires that all API requests are authenticated. This is the detailed, step-by-step process to authenticate with a YouTube API key:

This section is provided as a totally free alternative to the method described above. Instead of clicking Connect you will retrieve your key yourself.
  1. Navigate to https://console.developers.google.com/. If this is your first time accessing this page, you'll be prompted to accept Google Cloud Platform's terms of service. Click agree and continue.
    youtube-api-img11
  2. Now in the top left, click Select a Project, which will open up a modal. Click New Project
    youtube-api-img12
  3. This will open up the New Project modal. You can name your project anything, but let's call it YouTube API. You'll also be prompted to browse for a Location (aka folder). For our purposes here, it doesn't matter which one you pick.
    youtube-api-img13
  4. Once you create your project, click on Credentials in the left-hand menu. Next, click the Create credentials dropdown and click 'API key'.
    youtube-api-img14
  5. Your YouTube API key is now ready. You'll see a screen containing your API key; copy this to your clipboard and click Close.
    youtube-api-img15
  6. There's still one last step: you need to enable the YouTube API in your project. To do this, click Library and search for 'YouTube Data API'. Alternately, you can navigate there directly with this link: https://console.developers.google.com/apis/library/youtube.googleapis.com. Click Enable as shown:
    youtube-api-img16
  7. Congrats, you're done! You can use this token by appending key=YOUR_API_KEY to any custom API URL, e.g. https://www.googleapis.com/youtube/v3/search?part=snippet&q=cats&key=YOUR_API_KEY. Since you're manually including an API key, leave OAuth2 authentication set to None.
    youtube-key

49 thoughts on “Import YouTube Public Data to Google Sheets”

  1. Hi,

    Thanks for detailed documentation. I am new to APIs. So before start implementing this I would like to know if creating project is free or paid service of google? Does it required GSuit?

    Regards,
    Sai Krishna.

    Reply
    • This is free, you don't need a paid G Suite account to use Google's APIs. API Connector is free, too (with option for paid upgrade).

      Reply
      • Hi,
        I'm doing a personal project that exceed daily quota.
        Could you please give more information about YouTube API paid option? The only thing I could find on the net is you need to request for additional units.
        Thanks

      • Sorry, I'm not aware of any paid option for the YouTube API. If you haven't seen it already, this page describes how to check your quota usage and contains a link to the form where you can request a quota increase. This article also contains info on requesting an increase.

  2. Hi,

    For a noob like me this page is very obscure, I don't really understand what the end result is, but it looks like it's close to what I'm looking to achieve.

    I found this page because I want a Google sheet to update the subscriber count of my YouTube channels daily (I am the owner of those channels).

    The thing is I want that Google Sheet to update daily without me manually opening it (so I can analyze the data with graphs)

    Right now, I have a clunky solution that uses IMPORTXML in a cell and targets socialblade.com to gather different metrics and a daily google script creates a line every day with the information on that sheet.
    Problems:
    - I'd rather get that data directly from YouTube and not a 3rd party site
    - IMPORTXML doesn't update if I don't manually open the Google sheet...

    Any help is appreciated!

    Reply
    • Hi Tristan, thank you for the message and sorry if the article was confusing. Please see below.

      I don't really understand what the end result is
      There are a few screenshots in the article, for example the final screenshot of part 3 shows the result of a query for a list of videos, and the screenshot at the very end shows the end result of a query for likes, views, and comments. Does that help or are you looking for a screenshot of something in particular?

      I want that Google Sheet to update daily without me manually opening it
      The method in the article is free to run manually, but automatic daily updates requires the paid version of API Connector. Not sure if you were looking for a totally free solution or not.

      I'd rather get that data directly from YouTube and not a 3rd party site
      Yep, this method pulls directly from YouTube's official API, so it's all above board and reliable.

      Happy to answer any further questions so just let me know what you're looking for.

      Reply
      • Thanks for your answer, I guess the confusing thing is that you seem to be focused on pulling data for videos, when the first need - at least in head - would be to pull data for the channel (things like subscriber count, watch time in different period of time, number of uploads, etc).
        After having those channel metrics covered, I understand that to get more granular some would want to go on the video level.

        My current need is to have a daily automatic scrape of my YouTube channels and reporting in google sheet automatically without having to open the sheet itself.
        So when you go on that sheet every month or so, it's all populated and graphs are telling you the evolution of those channel metrics...

        I can't find a simple solution anywhere...

      • You can get channel data as well as video data through the YouTube Data API, you just need to select the /channels endpoint. Though if you want detailed private data (data that you can only see while logged in) about your account like watch time and number of uploads, you would need to use the YouTube Analytics API instead. I think you can manage the solutions in either of these guides if you try, since ultimately you just need to follow the steps and select an endpoint or paste in a URL at the end. It doesn't require writing code or any particular tech skills. But if you aren't comfortable with that, I suggest using a different tool like Supermetrics. They simplify the connection process even further so you can just select your metrics from a drop down menu instead of working with the API directly.

  3. OK, I'm not a coder, half the things you mentioned are foreign and not understood, but I am a marketer and I know there is a need.

    In this time of let-me-write-a-blog-to-bring-value-when-the-only-thing-I-really-want-is-organic-exposure I am stunned that my simple need (which is every serious YouTuber need) isn't answered.

    I went through many many sites, and everything is wordy and confusing.

    YouTubers need to see the history of how they're doing to decide on what to do next.
    We need to see the subscriber count growth, we need to see the watch time evolution and many other things but that's a start.

    Anybody reading those lines: there is an obvious need for YouTubers to have an automatic report on google sheet. It looks like it should be a piece of google script. So if you explain how to do it, you would have great traffic on your page.

    Just my 2 cents

    Reply
    • Hi Tristan, thanks for the feedback. Please check my last response to you as it includes the name of a tool that imports YouTube data without directly accessing the API like the method in this article.

      Reply
  4. How do I return the first result for say 50 different youtube searches in one go? E.g query one would be "manchester united football", query two would be "chelsea football club" and so on.

    Reply
  5. This seems like a very convenient add-on, and I very much appreciate the opportunity to play around with the various searches (still getting to grips with the script); I was interested in retrieving all of the videos in a playlist, which you say can be done with:

    https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=YOUR_PLAYLIST_ID

    For me, it just produces a list of playlists (but not videos), is that what you meant? If I add the maxResults parameter, it will list the videos, but only up to 50 (acceptable values 0-50, with 5 as default):

    https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&playlistId=YOUR_PLAYLIST_ID

    Do you have any suggestions as to how I can retrieve the details for more than 50 videos?

    Reply
    • Hi Ken, thank you for the nice message, I'm glad you're having fun checking out the YouTube API with API Connector. For your first question, I'm actually not sure why you're receiving a list of playlists. I just tested again and for me it returned a list of the videos within my playlist. Is it possible to share your request URL with me so I can check it?

      As for retrieving more than 50 videos, you'll need to set up pagination handling, I just added a section on that.

      Reply
      • Hi Ana,

        Thanks for that - the pagination handling worked; I didn't realise that API Connector had this functionality, so that saves a lot of effort. And very good of you to add the guide!

        As for my original query, I realised that I had made an error there (sigh), so everything is good. I'm now going to try and filter the query so that it returns the number of views and for each video in a playlist using your tips in Part 5: Handle Filtering, hoping that I can combine filters with the Playlist query.

      • Great, I’m glad that worked! If you want to get stats for each video in a playlist, I think you need to do a 2-step process:
        1) get a list of all the IDs in a playlist using this URL:
        https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=YOUR_PLAYLIST_ID

        2) Create a comma-separated list of those video IDs and plug that into the id parameter, since the YouTube API allows you to query multiple video IDs at once.

  6. Thanks Ana, this is a useful start for me.

    What I'm trying to achieve is being able to import all 'Held For Review' comments for my YT channel into a Google sheet.

    Appreciate any tips or pointers you can give me!

    Cheers

    Reply
    • Hi there, this article shows how to work with comments: https://developers.google.com/youtube/v3/docs/comments
      Based on that, you'd set up a request URL like this:
      https://www.googleapis.com/youtube/v3/commentThreads?part=snippet,replies&videoId=Ecm5TZPyAI8&moderationStatus=heldforReview

      I haven't tested it but I don't think this will work with an API key, I think it requires a custom OAuth2 connection (YouTube slices up permissions quite finely, and API Connector's pre-built YouTube connections only include scopes for video statistics, not comments).

      To create a custom OAuth2 connection you'd need to set up an app at https://console.cloud.google.com, and get your client ID and secret. Then you'd enter these values into API Connector along with an an authorization base URL of https://accounts.google.com/o/oauth2/v2/auth?scope=https://www.googleapis.com/auth/yt-analytics.readonly https://www.googleapis.com/auth/youtube.force-ssl, and a token URL of https://oauth2.googleapis.com/token.

      Reply
  7. I ran the playlist API and it returned the items in the playlist. However, I also need the duration of each video in the playlist. How do I add that parameter for the duration in the get request?

    Reply
    • contentDetails doesn't return duration in the playlist API. For that I need to use the Video id and that requires a different API. My question is how to use your API connector to call 2 different APIs and pass value from one API to another.
      For the time I have to do manually by copying all the video ids from a playlist and then making another request. But this is really not scalable, specially if the playlist has 100s of videos.

      Reply
      • Hey Rajinder, you need to make multiple requests to achieve this, there's no way around that as that is the way their API is designed. API Connector provides a few features to assist with this, like referencing cells in requests and automatically cycling through a list of request URLs. Plus you can combine it with Sheets functions to automate or semi-automate the whole thing. In this case I'd do something like this:
        1) Make a request to the /playlistItems endpoint to get a list of videos and their IDs
        2) Make a comma separated list of all those IDs with a Sheets function like =JOIN(",", A2:A50)
        3) Now make a second API request that references your comma-separated list of video IDs using the +++ syntax, like https://www.googleapis.com/youtube/v3/videos?part=snippet,contentDetails&id=+++Sheet!A1+++&key=xyz
        Please try that and let me know how it goes.

  8. Hi Ana,
    Thanks for the work around. I tried something different (though a similar approach). I first used playlist API with content details to get Video Id in a column. Then I created another Google Sheet and copied this column using IMPORTRANGE and then ran the connector making API request on the video content details like this -
    https://www.googleapis.com/youtube/v3/videos?part=contentDetails&id=+++SheetwithVideoIDs!A2:A1000+++

    ( I got this idea from your another reply to a comment, where you give the example of referencing the column)

    Once I have the two sheets, I created another Google Sheet using IMPORTRANGE which pulled content from both the sheets.

    The problem with the method you gave (when I reference a single cell with all the values) was when I tried it, I got the error - too many parameters in the request. It was because when I joined the Video ID, there was tool of around 200 ids and when I passed them to the API in a single request, there was an error.

    With the above method of referencing the columns that I have mentioned, I didn't get the error.

    The problem is solved now, albeit a better work around would be do this programmatically in one sheet only. As this can solve many other use cases.

    Thanks again for your help.

    Reply
    • One problem I noted is that when referencing a column, the request takes easily 4 mins to 5 mins to complete. I tried referencing around 200 rows of the column. Is there any reason for this beside that it makes lot of API calls and that is the reason for the time delay?

      Reply
      • Yep, that's exactly it. Your method uses API Connector's multi-query functionality to cycle through a list of URLs. It makes 1 request at a time, with each request returning data for a single ID. The advantage is you won't run into any URL limits, but it's not super efficient.

        My method batches all the IDs together so can retrieve data for dozens of IDs in a single request. So the advantage is it's faster and more efficient, and also free (multi-query requests are a paid feature, though you're probably still in your free trial). However as you found, if you include too many IDs in a single URL you can go over Google's URL limits, so you would need to split them up into separate requests.

        Basically there are a few pros and cons of either method. Fun fun 😀

  9. One observation, when using filters pagination is not working. Because the column nextpage token is not available for the pagination functionality to work. Am I correct?

    Reply
      • Thanks Ana, this helps. I too noticed when filtering a search query for just the field "items(id(videoId)", that it would not apply pagination. But when including "nextPageToken" in the URL it fixed it right up.

  10. Hello. I've spent hours trying to figure out a way to track youtube views per hour using google sheets and as someone who's not anywhere near IT or progamming and all that, this is all so confusing.

    But I get that I'd need API and scripts

    However, I'd just like to confirm, coz it seems that I have to pay for a premium service, if I'd need to get the premium if I want to track views per hour? Thanks

    Reply
    • Hi there, I'm not sure of any way to get YouTube views per hour via the API. It's not related to paying or not paying, I just haven't seen it as an available metric in their API documentation. I don't even see it available in the YouTube Studio Dashboard when I log in and check channel metrics. Is this something you've seen before?

      Reply
  11. Thanks this definitely seems interesting. Being a non-coder, initially, it took a bit of trial and error to understand. I want to actually try to link channel and video data. For example - If I have the channel ID, I want to pull the latest 3 videos of the channel along with the individual video statistics. Is it possible?

    Reply
    • Sure, you can see instructions here. Basically you use a URL like this: https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=UUuNKF6uPXIwBHzW9rdMWzkQ&maxResults=3, where the playlistID is the same as your channel ID, just change the initial "UC" to "UU". That will give you a list of the latest 3 videos in the channel. Then you'd run API requests for those 3 videos as shown in the example in this article titled "Key statistics (views, likes, dislikes, comments) for a list of videos".

      Reply
      • Thanks Ana. That worked like a charm. Now I actually want to take a notch higher. Can we execute several API calls in a systematic way through one push? I want to first procure top 50 ranking videos for a keyword -> Pull their channel IDs -> Pull the stats for their last 3 videos -> Sum it up -> Assign it back to the channel ID to represent them as channel stats.
        So this will require 3 API calls over array of channel IDs and video IDs. Let me know what you think.

  12. This is an amazing tool!

    One quick question. Why won't this execute? I keep getting an error "Failed to run request"

    https://www.googleapis.com/youtube/v3/videos?part=snippet,contentDetails,statistics&id=statistics&id=+++Sheet1!J2:J500+++&key=1111111111111111&fields=items(id),items(snippet(publishedAt,title)),items(contentDetails(duration)),items(statistics(viewCount,likeCount,dislikeCount,commentCount))

    With OAUTH2 AUTHENTICATION set to None

    But this will run fine:

    https://www.googleapis.com/youtube/v3/videos?part=snippet,contentDetails,statistics&id=statistics&id=+++Sheet1!J2:J500+++&fields=items(id),items(snippet(publishedAt,title)),items(contentDetails(duration)),items(statistics(viewCount,likeCount,dislikeCount,commentCount))

    With OAUTH2 AUTHENTICATION set to YouTube Data with it connected.

    I've tried moving the key to various places in the query but to no good effect.

    Reply
    • By the way, this query also runs fine:

      https://www.googleapis.com/youtube/v3/search?part=snippet&key=11111111111111111111&relevanceLanguage=en&q=+++Sheet3!A1+++&order=rating&type=video&videoDuration=long&publishedAfter=2016-01-01T00%3A00%3A00Z&maxResults=1000

      Reply
    • Hey Stephen, "Failed to run request" means that the query timed out (i.e. went over the Sheets 6 minute limit). There is no difference between running queries via API key or OAuth2, and I just tested your queries in my own account and confirmed that they worked exactly the same. It also doesn't matter where exactly you place the API key parameter in your query. Therefore I think the fact that it timed out on one but not the other is probably a fluke, or perhaps indicates that some other setting is different. Can you please check if the two queries are using a different report style or anything like that?

      Either way, I think you're hitting timeout issues because you're running through nearly 500 URL requests at once. Instead of running 500 separate requests, I suggest batching your video IDs together and running them together. You can see the basic process and some functions to support this here. You'll still need to split it into a couple different requests to avoid exceeding the maximum URL length limit, but you should be able to reduce that 500 to about 3 instead. Hope that helps point you in the right direction, let me know!

      Reply
  13. Hi Ana
    I am looking to have a Realtime API integration with Youtube API to Google Sheets. Is there any info you can provide for the same?

    Reply
    • Google only allows Workspace addons to automatically update once an hour so we can't get continuously updating realtime data through API Connector. So you would probably need to use a custom script for your use case, or you can look into some scraping tools that can update more frequently.

      Reply
  14. curious question, does the youtube analytics api results (dimensions and metrics) are different from the YouTube connector with Data Studio?

    Is there a difference that metrics that I couldn't pull from Data Studio is can be pulled in Google Sheets using this strategy?

    Like impressions or thumbnail impressions?

    Thanks

    Reply
    • Sorry I don't know about the YouTube connector with Data Studio. This method provides access to the full set of metrics provided through YouTube's API, but I don't know which metrics that connector provides.

      Reply
  15. Thank you for the time you put into this! Any chance you could tell me what the URL string would be to pull all the public videos from a given channel? There are a few hundred... Just need the title and URL.

    Thanks!!

    Reply
  16. Hi there,

    I'm learning to use your API and trying to automate a few KPIs around media my team and I have produced.

    The code below provides a lot of data (and am thankful):
    https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=PL_YvoQ-KM3YGH3ixsYjynMyDxwvEmTVez&maxResults=50
    I'd like to see the view counts for each video and sum them up. I found a solution with filters -- Skip down to Part 5: Handling Filters -- Importing YouTube Public Data to Google Sheets (https://mixedanalytics.com/knowledge-base/api-connector-integrate-youtube-api-with-google-sheets/)

    I read through this thread and found Rajinder's comment helpful. I wasn't sure if I would need the exact same steps with except changing one parameter for view counts.

    Am I overcomplicating this?

    Thanks again!

    Reply
    • Yeah, it should be the same, or maybe even simpler 🙂 Just merge your list of video IDs into a comma-separated list with a function like =JOIN(",", AC2:AC37)

      Then plug the cell containing that comma-separated list into a second request like this:
      https://youtube.googleapis.com/youtube/v3/videos?part=statistics&id=+++Joined!A1+++ (substitute in your own cell location where it says Joined!A1)

      That should do it, just let me know if you have any questions.

      Reply

Leave a Reply to James Cancel reply

Jump To...