YouTube Playlist to Linked List In Excel? Why Not!

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

Don Tomoff

Follow

Published in

Let’s Excel

4 min readMar 24, 2018

--

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

Copy Playlist into Excel Worksheet

Search YouTube for a playlist (any topic that is of interest to you). For this example, I searched for “Computer Science”.

**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).

Extract Web URL’s Using ASAP Utilities

Let’s extract the web URL’s embedded in the pasted information. Fortunately, ASAP Utilities gives me this option. :-)

**Access ASAP Utilities to extract hyperlinks**

Select options and click “OK”

**Extract hyperlinks from text and place in cell to right of current cell**

**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

Transform Data Into Our List with Power Query

Via the Excel Power Query add-in, import the CSV file created above. Then enter “Edit query” mode.

**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!).

**Power Query steps to get clean dataset**

Now, “Load” the data into Excel.

**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)

**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!