Apple Podcast Transcript Viewer

Apple recently added transcripts to their Podcasts app, quickly becoming one of my favorite new features. ¹ Cause it sure as heck ain't Genmoji. I wanted to copy a paragraph out of the transcript though ² For this post on Possession (1981). , and ran into the 200 word cap on their selection screen. Luckily the MacOS Podcasts app locally caches the transcripts, and so I built a simple web app that allows you to browse the transcripts and easily select parts of them.

The transcripts are locally stored within ~/Library/Group Containers/243LU875E5.groups.com.apple.podcasts/Library/Cache/Assets/TTML ³ Shoutout to Matt Murphy on Reddit who I first saw reference this. , but it only has the text breakdowns. To improve the user experience of finding the right podcast I wanted to include information about which podcast each one went to. Browsing around the folders of the high-level 243LU875E5.groups.com.apple.podcasts revealed a /Documents/MTLibrary.sqlite file which seemed promising for this information. ⁴ A lot of MacOS system apps store metadata in a local SQLite DB (which I also leveraged for the Books Annotation app and Markov Messages). The only information we have about the transcript is the file path (which looks like a UUID) and the filename, which looks like transcript_1000686664163.ttml-1000686664163.ttml.

Most of these DBs are pretty small, so we can quickly iterate over every column in every table to see if 1000686664163 is used as an identifier anywhere (Python is great for this kind of stuff). This found a hit in the ZMTEPISODE table under the ZSTORETRACKID column. That table has the author, episode title, duration, release date, and a brief description, which is all we need to render the episodes.

Rather than hardcode a specific export behavior I just made it so when you click any podcast it opens a popup with the full transcript. From here you can Cmd ⌘ + A or select a portion and copy it to whatever tool you want. If you want to tweak this further the code is all public here, and Matt has a CLI repo using Node.

One fun thing about this solution is that it does all of the processing locally in your browser without uploading any files. This has the security of a local script, but with better UX than running a script or downloading an Electron app. To do this though, I needed to read the .sqlite file locally in JS. Luckily SQLite has been compiled to Javascript through the beauty of WebAssembly, so this was pretty plug-and-play. ⁵ I'm still shocked by WASM. Running this kind of code in-browser would have been unthinkable ten years ago.

Once again this is available at https://alexbeals.com/projects/podcasts/ (though not on mobile) to play with. This was a one-day project, but here's the requisite "it works" screenshot before I put the lipstick on the pig: