How to Automatically Upload Podcasts to Google Drive with Google Sheets

You use Google Sheets as your very own podcast manager that will automatically upload your favorite podcasts to Google Drive and instantly sync them across all your devices.

This tutorial describes how you can use Google Sheets to create your own podcast manager. You can specify a list of your favorite podcast shows in Google Sheets and it will automatically upload new episodes to your Google Drive in neatly organized folders.

The configuration is very simple, the application is completely open-source and you do not need any programming language.

How does Drive Podcast Manager work?

You need to place your favorite podcast links in column A of Google sheet as shown in the screenshot below.

The app will automatically upload the latest episodes of each podcast to your Google Drive. You can open the MP3 files from your Google Drive or find them directly in the same Google sheet.

The application will create a new folder, titled Podcasts in your Google Drive. In this folder, it will create subfolders for each podcast show with the same folder name as the podcast title.

Upload podcasts to Google Drive

Here’s how you can create your own podcast manager with Google Sheets and Google Drive.

1. Click here to make a copy of the Google Spreadsheet in your Google Account.

2. Open the copied spreadsheet, go to the Subscriptions sheet and enter the RSS feed links of your favorite podcasts in column A. You can use our Apple Podcasts search utility to find the RSS feed for any podcast listed on Apple Podcasts.

3. Go to the Extensions menu and choose Script Editor to open the underlying Google Apps Script file.

4. Choose the Install function in the functions list and click Run to install the app. You may need to authorize the app once as it needs permission to save files to Google Drive on your behalf.

That’s it. The app will create a cron job that will run every few hours in the background and upload the latest episodes of your favorite podcasts to your Google Drive.

We even have an MP3 player built into Google Sheets that will play the latest episode of each podcast when you click on the Play button.

Technical details

If you’re curious how it all works, here are the technical details.

The application uses the Spreadsheet API to read the list of podcasts from Google Sheet. It then uses the Apps Script XML service to parse the RSS feed and extract new podcast episodes that have been posted since the last check.

All podcast RSS feeds must have a label with a label inside. The contains the URL of the MP3 file and this is what the application uses to obtain the download URL of the corresponding episode.

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 };
};

Once the app has a list of new episodes, it uses the UrlFetch service to download the podcasts and saves them to Google Drive in a folder specific to the podcast show.

The app then writes a new row to Google Sheet with the Google Drive file link and a timestamp of the episode download.

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);
  }
};