How To Automate Podcast Downloads To Google Drive Using Google Sheets

How To Automate Podcast Downloads To Google Drive Using Google Sheets

You’ll learn how to make your very own podcast manager using Google Sheets and Google Drive in this simple guide. You may easily arrange and download your preferred podcast episodes to your Google Drive using our custom manager.

How the Drive Podcast Manager Works:

You must add the URLs to your preferred podcasts to Google Sheet’s column A.

The program automatically downloads the newest episodes of every podcast you’ve defined and stores them to the Google Drive you’ve specified. Either immediately from the Google Sheet or your Google Drive, you have easy access to the MP3 files.

Within your Google Drive, a special folder called “Podcasts” will be created. For every podcast, sub-folders will be created and neatly labeled with the program’s name.

Download Podcasts to Google Drive:

Let’s begin by taking a few quick steps to create your unique podcast manager:

on order to create a copy of the Google Sheet on your own Google account, visit here first.

  • After copying the spreadsheet, go to the “Subscriptions” page and fill out column A with the links to your preferred podcasts’ RSS feeds.
  • Using our Apple Podcasts Lookup tool, you can find the RSS feed for any podcast on Apple Podcasts if you need help.
  • To open the underlying Google Apps Script file, go to the “Extensions” menu and choose “Script Editor”.
  • Select “Install” from the list of functions, then click “Run” to set up the application. As the app needs your permission to store files to
  • your Google Drive on your behalf, you might only need to allow it once.

I’m done now! The software will now set up a cron job to automatically download the most recent episodes of your favorite podcasts to your Google Drive every few hours in the background. Additionally, Google Sheets now has an integrated MP3 player that enables you to quickly play the most recent episode of any podcast with a single click of the “Play” button.

The Technical Details:

Here is a quick summary of the technical components in case you’re interested in learning more about the app’s inner workings:

The app reads the list of podcasts from Google Sheets using the Spreadsheet API. Then, it uses Apps Script’s XML service to analyze the RSS feeds and retrieve any fresh podcast episodes that have been released since the last check.

There must be an item> tag with an enclosure> tag within for every podcast RSS feed. The MP3 file’s URL may be found in the enclose> tag, which the program utilizes to find the episode’s download URL.

const parseRSS = (xmlUrl, lastUpdatedTime) => {
  const feed = UrlFetchApp.fetch(xmlUrl).getContentText();
  const doc = XmlService.parse(feed);
  const root = doc.getRootElement();
  const channel = root.getChild('channel');
  const episodes = channel
    .getChildren('item')
    .map((item) => ({
      date: new Date(item.getChildText('pubDate')),
      title: item.getChildText('title'),
      enclosure: item.getChild('enclosure')?.getAttribute('url')?.getValue(),
    }))
    .filter(({ date }) => date > lastUpdatedTime)
    .filter(({ enclosure }) => enclosure);
  return { title: channel.getChildText('title'), episodes };
};

After compiling a list of new episodes, the software uses the UrlFetch service to download the episodes and saves them in specialized Google Drive folders for each podcast show.

The link to the Google Drive file and the timestamp of when the episode was downloaded are both provided in a new row that is added to the Google Sheet by the app.

const getPodcastFolder = (folderName) => {
  const parentFolder = DriveApp.getFoldersByName('Podcasts').next();
  const folders = parentFolder.getFoldersByName(folderName);
  if (folders.hasNext()) return folders.next();
  return parentFolder.createFolder(folderName);
};

const downloadPodcast = (podcastTitle, episodeUrl, episodeTitle) => {
  try {
    const blob = UrlFetchApp.fetch(episodeUrl).getBlob();
    const folder = getPodcastFolder(podcastTitle);
    const file = folder.createFile(blob);
    SpreadsheetApp.getActiveSheet().appendRow([
      new Date(),
      `=HYPERLINK("${episodeUrl}";"${episodeTitle}")`,
      `https://drive.google.com/file/d/${file.getId()}/view`,
    ]);
  } catch (f) {
    console.error(f);
  }
};

These details provide you peace of mind that your podcast manager is operating well and updating your Google Drive with the most recent episodes of your favorite shows.

Set up your personal podcast manager right away using Google Drive and Sheets to make sure you never again miss an episode! Enjoy your listening!

Leave a Comment