Many of you know I use a Synology NAS because I like how I can attribute a track date to all tracks in my all-local collection (now just shy of 50,000 tracks.)
For a long time, I have had an “80s Greatest” playlist of 1,675 tracks (120 hours) that was manually curated over about 15-20 years of manual work.
I’ve wanted to create an equivalent “70s Greatest” playlist, but it’s been daunting.
Recently, I decided to use some generative AI magic to assist, and I got the whole thing done in just under a week, in my spare time. I used two paid offerings for this; the first is a $20/month subscription to ChatGPT, which enables me to take advantage of GPT 4, and the other is a Google Sheets extension that I describe below.
Here’s what I did:
-
I created 10 smart playlists in Audio Station…one for each decade of the 1970s. The parameters:
Match ALL of the following rules:
- Year is {year for each, from 1970-1979}
- File path does not contain “/Classical”
- Genre is not Christmas
- Genre is not Sound Effects
I created non-smart .m3u-based playlists for each of these, then brought them together into one .m3u playlist, in year order.
-
Then I did a force rescan in Roon, which imported the .m3u playlist into Roon.
-
I then turned right around and exported it to Excel in the usual way so that I had all of Roon’s extra metadata columns that comes from an export.
-
In Excel, I added a column for “Year” and added the year for each track (the tracks were all still in year order, so I was able to refer to the individual playlists created in step 1 to see where the year boundaries were…took a little bit of work but not too much. We’re only talking about 10 copy/shift-select/paste operations, here…
-
Then, wherever the “Album Artist” was “Various Artists”, I rewrote that column to refer to the “Track Artist” field.
-
Then the magic part. I imported the Excel spreadsheet into Google Sheets, where I use an extension called “GPT for Sheets and Docs” (https://workspace.google.com/marketplace/app/gpt_for_sheets_and_docs/677318054654).
I added a few columns there, with the following formulas:
a) A column called “Prompt” with the following formula:
=CONCATENATE("Yes or no: Was the ",{YEAR CELL REF}," song '",{SONG CELL REF},"' by ",{ALBUM ARTIST CELL REF}," a hit on the charts?")This creates a result that looks like:
Yes or no: Was the 1978 song 'Come Together' by Aerosmith a hit on the charts?b) A column called “GPT” with the following formula:
=GPT({PROMPT CELL REF})This is the most essential function that is provided by “GPT for Sheets” - and it, in almost all cases, produces a straightforward “Yes.” or “No.” answer in each cell.
c) A column called “Text” with the following formula:
=TO_TEXT({GPT CELL REF})This converts the Yes/No result from b) to text, which ensures that the textual result remains when you download it to your local computer to use in Excel, which doesn’t recognize the “GPT” function.
-
Then, let GPT for Sheets to do its stuff.
In my configuration, I did not use Safe Mode (it’s too slow), but I did enable the cache. I chose the “OpenAI gpt-4-turbo option” and I connected the extension to my OpenAI account through an API key (the instructions for the GPT for Sheets extension guide you through this quite nicely.) I left Creativity and Max Tokens at their default settings, and didn’t employ any “Advanced” settings.
After it works for a while, you may need to use the “Retry Errors” function until you get a solid Yes or No answer to each of your prompts.
I started with 8,604 songs and the total bill for the GPT for Sheets usage was about $17. My Open AI bill was only 11¢!
-
When it’s done, download it to your computer, and then use Excel to browse through the content and make adjustments as you see fit, because the results are not perfect. You can even ask ChatGPT or Copilot one-off questions about artists and their hits to make your reasearch easier.
I wound up moving my “Yes/No” column over between the Title and Track Artist columns, and then I used Excel’s conditional formatting to colorize the song titles with red (No) or green (Yes) to help me visualize things.
-
After I was done, I wound up with 1,938 tracks that qualified as “70s Greatest,” totaling 130 hours.
To make your .m3u playlist, you simply filter your spreadsheet by the “Yes” answers and copy the “Path” column to a text file. Remove the unnecessary prefixes with a search & replace.
For example, in my case, I replace this:
/var/packages/RoonServer/target/roonmnt/music/with:
../…because my playlists folder is at the root of my music collection, and all paths follow from there.
FYI - The first line of your file should always be “#EXTM3U”
-
From there, re-scan your Roon library, and the playlists is all ready for use in Roon!
I am so happy with this taking multiple orders of magnitude less time than it did for my 80s Greatest playlist.
I hope you can explore some generative AI magic to make playlists in Roon too!
