YouTube Slurper

This is a repo intended to help us fetch video metadata from our YouTube channel and dump the metadata into a Google Sheet.

Local setup

This currently must be run locally.

To run this locally, you need various pieces of config you can fetch from our password manager. There's a file called "env.txt" in the password manager entry — download this, rename it to .env, and put it in the same directory as this project.

Run npm install to set up dependencies, then parcel dev to start a local server. It should be live at http://localhost:1234/index.html.

Using the tool

This whole flow could be improved; it is what it is.

Go to the URL in Chrome. On load, it immediately generates a login popup. You will likely need to tell Chrome to always allow popups, then refresh the page. It will warn you the application isn't authorized, approve it anyway.

Running this tool currently results in taking each of our public YouTube videos and appending a new row to our spreadsheet containing its metadata. It currently does no merging; running it again will result in duplicate data.

Do NOT rename the header rows in the spreadsheet. They are currently used as exact matches for data structure keys.

Future improvements

• Confirm before running
• Check if a given videoId exists, only add it if it's not there (primarily useful for future years)
• The ability to push updated metadata back to YouTube
• Do ??? with tags
• How does this integrate with transcripts, if at all?

Where we get our ENV vars from

• The API keys were generated by going to the Google Cloud Console and generating an API key restricted to accessing the YouTube Data API v3 and Google Sheets API, and creating a 'web application' OAuth client.
• Our YouTube playlist ID comes from going to the YouTube API explorer for the Channels/list endpoint as the Roguelike Celebration account and running the provided sample query for "list my channel". The result payload contains a "related playlists" property somewhere that includes the "uploads" playlist reflecting all items uploaded to our channel
• The Google Sheets keys were fetched from the URL of the correct sheet. A Sheets URL is of the form https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit#gid=[SHEET_ID].

License

MIT License. See license.txt in this repo.