Google Sheet to Youtube Playlist

 I'm always wanting to build my own playlists on YouTube, so I hunted down the way to do it, and got it running and working well.

Before you start this, please note:
For really long lists you might make, you need to take into account the number of songs you will be loading to your playlist. 

Using this method requires the use of Google Script App and a quota that is allocated to the process that you will be using, to fetch YouTube addresses and add them to a playlist. 

From what I can tell, 150-200 songs can be loaded to a YouTube playlist, then it will tell you, you have maxed out your quota limit. To add the rest you will have to wait till the next day to do more.

Creating a playlist to load to on YouTube

You will need to create  a playlist so we can load the songs into. The easiest way to create this, would be to find any song on YouTube and save it to a playlist:

At the bottom right of the window, click on the "+ Save" option (A). This will display a list of all your YouTube playlists, and click on the "+ Create a playlist" option. (B)

New entry fields will display, so type in a playlist name and choose whether you would like the list to be Private or Public. Then click "Create".

The new playlist should appear on the right side of your window, like below. Click on the new playlist to open it.

This will display the list and the songs currently in it. The important thing we need from this window is the address or ID of playlist allocated by YouTube. In the address bar at the top click into it and copy the last section of the address. In my case, the playlist ID is everything after the "=" in that address. Which for this list is: "PLBMszOMJcaozp7Jv8Y1C4p9YX-DbPHdch"

Keep this playlist ID copied somewhere, as we will need it for the coding part of this project.

Building a Google Sheet playlist

For me, I had taken a list of the songs that from a local radio station Radio Hauraki, here in New Zealand.

The station and the public had done the hard work, with making up the list (Radio Hauraki GOAT 2022), so I simply copied and pasted the list from their website into a google sheet.

For us to make up a list to upload, we need to fill in details of the videos we are looking for, from YouTube. 

(I used the instructions from Amit Agarwal website on how to make up the list. https://www.labnol.org/internet/youtube-playlist-spreadsheet/29183/ )

We need to create 3 extra columns:

1. YouTube Video URL

This is where you will need to be individually pasted in to the first column, from YouTube. You can do this later, but just have this column available to fill in. There might be a way to do this automatically, but that's above my paygrade on sorting that just now.  I've just searched for the individual songs, found the version of the song I wanted. I right clicked on the video and copied the url (See below) and pasted it into the sheet.

2. YouTube ID

In column 2 we add this formula to get the YouTube ID:

=REGEXEXTRACT(A2, "youtu(?:.*\/v\/|.*v\=|\.be\/|.*?embed\/)([A-Za-z0-9_\-]{11})")

Copy/fill this formula down the column. 

3. Thumbnail Image of the Video

In the 3rd column we need to add the formula to extract the thumbnail image for the video. This is so, that when you add the url of the song, the thumbnail will match. 

=IMAGE("https://i3.ytimg.com/vi/"&B2&"/hqdefault.jpg", 4, 80, 120)

Copy/fill this formula down the column.

It took me a while to collect all 500 songs, but this is what the list looks like now. 

Before you start the next part for coding, I would suggest that you make a backup copy of the finished list. So make a second copy of this Google Sheet and call it a Backup.

Uploading the Google Sheet to YouTube

With the created google sheet, we need to use Google App Script to complete this part of the process. We need to click on menu option "Extensions" and then down to "Apps Scripts".

Please note: In my case, I have multiple google accounts for work and personal, and it doesn't allow you to open the Apps Script page. I usually get the message "Sorry, unable to open the file at present. Please check the address and try again."

You will either have to login with just the single account you are working in, or open another browser and login with that account only. In my case, I used Microsoft Edge, logged in with my google account on there and opened the google sheet I'm am working on and opened "Apps Script" there.

I've also noticed you have to click on the "Apps Script" menu option at least twice for it to open.... this could be a me issue... not sure.

Google Apps Script

For this part of the instructions, I followed the details from Aryan Irani YouTube video - "Add Videos to Playlist using Google Apps Script and the YouTube Data API"

When Apps Script new window opens, it creates a new project for the spreadsheet that you will be working with. At the top of the window, change the name of the script to whatever you want. I changed mine to "Youtube Playlist".

The next part, that took me a while to find out about.... as I think I found an older version of his video that didn't include this part. We need to add the YouTube API service.

So in the window, click on the "+" next to the title "Services" on the left side, below the code window and the "libraries" category.

The services window should open, so you will need to scroll down to the bottom of the list, till you find "YouTube Data API v3". Click on it and add it to your project.

With this service added, we can use the YouTube API to call in the google sheet and add to the playlist.

We switch back to the coding window, click on the "code.gs" on the left side of the window. Once there we need to paste the following code:

function addvideos() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getActiveSheet();

const START_ROW = 2;

const START_COLUMN = 2;

const video_ID = sheet.getRange(START_ROW,START_COLUMN,sheet.getLastRow()).getValues().flat([1]);

const playlistId = "PLBMszOMJcaozp7Jv8Y1C4p9YX-DbPHdch";

video_ID.forEach( vid =>

     YouTube.PlaylistItems.insert({

snippet: {

     playlistId: playlistId,

      resourceId: {

          kind: "youtube#video",

          video_Id: vid

          }

     }

}, "snippet"));

}

Now I could explain all this code... but I don't want to.... 

The only bits of the code we need to worry about is:

const START_ROW = 2;

const START_COLUMN = 2;

This is pointing the code to part of the google sheet that we want it to pull the youtube ID from for loading to the playlist. Starting at row 2 and column 2 which would be B2 on the sheet.

and

const playlistId = "PLBMszOMJcaozp7Jv8Y1C4p9YX-DbPHdch";

Which is the ID of the playlist that we setup earlier in this post. Change this playlist ID to your playlist that you have created.

And your ready to go. Click the save button, just above the code window, and then click on the Play or "Run" button to start the process of loading the Youtube list.

Please Note Again: Like I mentioned If you have a list that is really long, you will only be able to load 150 to 200 songs per day using this method. 

Also note, You will need to make sure that if you do load and run out on the quota for the day, you must delete the songs, from your google sheet, that do get uploaded to the playlist, before you run the code again. Otherwise the same songs will be loaded again into the playlist again. 

Ok, you should be all good to go, on making your own lists from Google Sheets to a YouTube playlist.

If you want to play my 500 song playlist, click here: 
https://youtube.com/playlist?list=PLBMszOMJcaoyLuzhwLv7lfkplnfsxvu2X