YouTube Playlist to Linked List In Excel? Why Not!

This Could Be Very Useful — Here’s How To Do It

Image for post
Image for post
YouTube Playlist →Excel Dashboard

Overview

Last week, I had a conversation with Olivia Tomoff and Bill Tomoff regarding extracting data from the internet into Excel.

Olivia asked if there was an easy way to “create a linked list in Excel of any YouTube playlist?”

That’s a great question, and one that I decided to solve.

Using two of my favorite Excel add-ins — ASAP Utilities and Power Query, I was able to do this very quickly (yes, even I was surprised!).

Here’s how:

The Process

  • Search YouTube for a playlist (any topic that is of interest to you). For this example, I searched for “Computer Science”.
Image for post
Image for post
Udacity “Introduction to Computer Science” Playlist
  • This playlist contains 649 videos. Highlight the selection and press “CTRL +C” to copy it. Open Microsoft Excel and paste it into a blank worksheet (CTRL + V).
Image for post
Image for post
YouTube Playlist Pasted into Excel
  • Let’s extract the web URL’s embedded in the pasted information. Fortunately, ASAP Utilities gives me this option. :-)
Image for post
Image for post
Access ASAP Utilities to extract hyperlinks
  • Select options and click “OK”
Image for post
Image for post
Extract hyperlinks from text and place in cell to right of current cell
Image for post
Image for post
YouTube playlist with extracted hyperlinks
  • Save this Excel file as a CSV format file and open up a new Excel file — time to get to work!

Now we have the data we need — the next challenge is to “transform” it

  • Via the Excel Power Query add-in, import the CSV file created above. Then enter “Edit query” mode.
Image for post
Image for post
Data Import in Edit mode for Power Query
  • Work through the process of cleaning the data. I don’t review the detail here, but if you are comfortable with Power Query, it’s a relatively straight forward process (and I expect there are easier ways to do it than what I did!).
Image for post
Image for post
Power Query steps to get clean dataset
  • Now, “Load” the data into Excel.
Image for post
Image for post
Playlist query loaded into Excel as Table
  • Finally, clean up the presentation and functionality in Excel and you have a basic user dashboard.

Steps performed:

  • Add hyperlink column for easy “Click to Open”
  • Hide existing URL column — we no longer need it
  • Add “Date Cmpltd” column to track progress
  • Add “Notes” column if desired (I didn’t)
Image for post
Image for post
Final user dashboard / resource in Excel!

This is basic, but that is it. And it is done QUICK!

Basic knowledge of the functionality provided by two Excel add-ins enables a tedious, time-consuming task (which you probably wouldn’t undertake…) to be accomplished in minutes!

Thanks to Olivia and Bill for the thought provoking discussion last week — and last, a shout out to Olivia for throwing out the specific question. That was a fun one!

Image for post
Image for post

“It’s time for DIFFERENT”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

LinkedIn, Flipboard, Twitter, Snapchat

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store