Liked YouTube Videos Catalog

Populating my liked YouTube videos in an Excel spreadsheet

YouTube has largely become a part of everyone’s life during the COVID 19 pandemic. ‘Liking’ these videos not only provides a means of giving our feedback but also provides us a means to create our own personalized curated playlist. Although this list can be accessed through the website and the app, there is not really a friendly way to fetch older liked videos — not without painful scrolling especially when the list gets too large.

…I was compelled to stay up all night to complete a script that would fetch me my liked YouTube videos.

One fine night I thought that me being a programmer, why don’t I put my skills at work to fetch a detailed list of the same. Although I identify myself as a night person, pulling all nighters usually isn’t my thing. This was one of those occasions, however, where I was compelled to stay up all night to complete a script that would fetch me my liked YouTube videos.

Without wasting much time I looked up Google’s documentation for YouTube’s API, which would enable me to programmatically pull the data from their servers. I would be using their Data API v3. I then created a small .NET Core console app and tried out a simple query; and realized that I had to use OAuth to fetch my personal data as opposed to using an API key, which would only have fetched publicly available data.

Now that OAuth credentials were set up, I had to store the results somewhere. For that I decided to use an Excel spreadsheet, since text file would be too rudimentary and relatively clunky to navigate. So I created a small data model and implemented an abstraction using the EPPlus library, to fetch the last populated entry in the Excel file and append results from the API query response, thus creating a continuous list.

This is where the problems started. The people at Google had imposed some funny restrictions over the YouTube Data API. No query can fetch more than 50 results. To top that, there was a daily quota of 10,000 units for a project. That would seem huge, but a single upload request would cost 1,600 quota units. Fortunately for me, the APIs I was using surprisingly consumed just 1 unit per request. So that was not the main problem.

…managed to get my entire list of 2.5k videos

The API’s video endpoint that I was using only allowed for 1000 cumulative rows for paged requests; whereas my likes comprised of around 2.5k videos. A web search on the same led me to a solution where one suggested to use the playlist items endpoint instead; and sure enough, I managed to get my entire list of 2.5k videos, with a minor shortcoming — it didn’t fetch other useful details such as channel info, category ID and tags.

So I went through the documentation again and found one plausible solution — fetch video list based on their IDs. Thus, a few Excel gymnastics later with SheetKraft’s spreadsheet automation, I was able to construct an array of the remaining comma separated video IDs obtained via the playlist items endpoint. Needless to say, I did integrate the same in my code later, so one does not have to dabble with Excel, and the whole thing gets done in a single click.

Now all that was left was fetching the video categories, which was a one-time task — basically to fetch a list of categories mapped to their respective IDs — which I lazily scrapped off within the debugger itself! Here’s an interesting code snippet for the programming geeks :)