How to use Youtube API from Power Query – Part 2

This post follows on from my previous post regarding the use of the Youtube API from Power Query.

You will need to follow the steps from the previous post to ensure that you have a browser API key to the YouTube Data API v3.

In this post, we’ll be exploring how to get the number of views, likes, dislikes and comments for a video.

To achieve this we will use a videos query rather than a search query.

I’ve picked a video at random:

Its video id can be taken from the URL and I have highlighted it in bold below:

https://www.youtube.com/watch?v=MtN1YnoL46Q

You can easily get this value for any video on YouTube.

Now, we can get into Excel and Power Query.

Start with a Blank Query and paste the following into the Advanced Editor:

Expected result:
2016-05-01 11_00_08-Query3 - Query Editor

This query requests the statistics for the Duck Song video (which has the id of MtN1YnoL46Q).

You can review the Youtube API documentation for more detailed information.

You could actually enter this directly into your browser if you wanted to. It is equivalent to:

https://www.googleapis.com/youtube/v3/videos?key=<your api key here>&id=MtN1YnoL46Q&part=id,statistics

Now, if you click on the items ListPower Query will return a list of the results as records.

2016-05-01 11_06_35-Query3 - Query Editor

They will be easier to work with as a Table so next we’ll convert to a Table.

2016-01-24 11_42_07-Youtube Search List Merge - Query Editor

Returning this:

2016-05-01 11_10_13-Query3 - Query Editor

If you then expand out the column, you can start to see all of our data:

2016-05-01 11_11_05-Query3 - Query EditorThen expand the statistics column and here we go – all the information we wanted regarding views, likes, dislikes and comments:


2016-05-01 11_12_12-Query3 - Query Editor

So at time of writing, this video has been viewed by 212 million people! 731,916 liked it, 95,318 disliked it and it has 197,897 comments.

Here’s all the code:

It is relatively easily to request statistics for multiple videos/channels at once. Can cover that in a future post if there is interest.

Please let me know if you encounter any issues or need further clarification.

Enjoy!

8 thoughts on “How to use Youtube API from Power Query – Part 2”

  1. great post – have read through and seems very easy to follow even for a beginner like myself.

    if you could add the last bit I would def appreciate it. cheers

    “It is relatively easily to request statistics for multiple videos/channels at once. Can cover that in a future post if there is interest.”

  2. Hi, I want to use power query to get search results from youtube movie.
    For example, in excel cell sheet1 A1, I input “American car” and
    I want to get search list for American car from youtube API V3.
    How can I do that using excel power query?

  3. Hi Kevin,

    That’s essentially what Part 1 in this series shows you how to do:

    http://www.excelandpowerbi.com/?p=86

    If you follow that post and replace “power query” with “American car” you will get the results that you are after. Then if you follow the other posts in the series you can get more statistics etc. on those videos returned by the search.

    If the issue is more around how you can dynamically change what you are searching for i.e. by entering in A1 and then refreshing – then you can make A1 a named range and refer to it in the PowerQuery code.

    Let me know if you need me to walk you through this process.

    Regards

    Ken

  4. Hi Ken, excellent blog.

    I looked through both your blog posts and made it work perfectly. But now I want to combine the two – and request statistics based on the top searches.
    E.g. I want to have the viewCounts, likeCounts etc. for all the videos.

    How can this be solved?

    I tried naming a range, but I get an error.

    Best,
    Pelle

    1. Hi Pelle, it’s been a while since I looked at this. Glad you’ve found it helpful.

      I’m pretty sure that I did progress to combining the two in my own experiments. I’ll need to check my library. Otherwise I’ll see if I can knock something together.

      Regards

      Ken

Leave a Reply

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