Build a podcast and media stats tracker

Northwind’s podcast goes out on Spotify and Apple, and each platform reports download numbers in its own dashboard. To see how an episode is really doing, someone had to log into both, copy the figures, and paste them somewhere — a weekly chore that always slipped.

This script collects those numbers automatically. On a schedule it calls each platform’s stats API, reads the last-seven-days download count, and appends a dated row to a single tracking sheet. Over a few weeks that sheet becomes a clean cross-platform history you can chart or summarise — without logging into anything.

What you’ll need

• A Google Sheet to hold the running log. The script appends rows with a date, a platform name, and a download count, so the sheet can start empty (a header row of Date, Platform, Downloads (7d) is nice to have).
• A stats endpoint for each platform that returns JSON with a downloads_last_7d field. The script ships with placeholder URLs — swap in the real ones for whatever analytics provider you use.
• If those endpoints need authentication, an API key or token saved in Script Properties — see Store API keys and secrets securely.

The script

// The spreadsheet that holds the running stats log.
const PODCAST_SHEET_ID = '1abcPodcastId';

// One entry per platform: a label and its stats endpoint.
const PLATFORMS = [
  { name: 'spotify', url: 'https://api.spotify.example/podcast-stats' },
  { name: 'apple', url: 'https://api.apple.example/podcast-stats' },
];

/**
 * Calls each platform's stats API and appends one dated row per
 * platform to the tracking sheet.
 */
function pullPodcastStats() {
  const sheet = SpreadsheetApp.openById(PODCAST_SHEET_ID).getSheets()[0];
  const today = new Date();
  let written = 0;

  // 1. Loop through every platform we track.
  for (const platform of PLATFORMS) {
    try {
      // 2. Fetch the platform's stats endpoint.
      const response = UrlFetchApp.fetch(platform.url, {
        muteHttpExceptions: true,
      });

      // 3. Skip this platform if the API did not return a 200.
      if (response.getResponseCode() !== 200) {
        Logger.log(platform.name + ' returned ' + response.getResponseCode());
        continue;
      }

      // 4. Parse the JSON and append a dated row.
      const data = JSON.parse(response.getContentText());
      sheet.appendRow([today, platform.name, data.downloads_last_7d]);
      written++;
    } catch (err) {
      // 5. One bad platform should not stop the others.
      Logger.log('Failed to pull ' + platform.name + ': ' + err.message);
    }
  }

  Logger.log('Logged stats for ' + written + ' of ' + PLATFORMS.length +
    ' platforms.');
}

How it works

1. pullPodcastStats opens the tracking sheet and records today’s date once, so every row from this run carries the same timestamp.
2. It loops through PLATFORMS, the config array holding each platform’s label and stats URL.
3. For each platform it calls UrlFetchApp.fetch with muteHttpExceptions, so a non-200 response returns normally instead of throwing.
4. It checks the response code. Anything other than 200 is logged and skipped, so a platform outage does not write a junk row.
5. On a good response it parses the JSON, reads downloads_last_7d, and appends a [date, platform, downloads] row.
6. The whole fetch is wrapped in try/catch, so a network error or malformed JSON for one platform is logged and the loop carries on to the next.
7. At the end it logs how many platforms it managed to record.

Example run

The script runs on a Monday with the sheet already holding last week’s rows. It appends two new rows:

Date	Platform	Downloads (7d)
2025-12-01	spotify	1,840
2025-12-01	apple	920
2025-12-08	spotify	2,110
2025-12-08	apple	1,005

The execution log reads Logged stats for 2 of 2 platforms. If Apple’s API had been down, only the Spotify row would appear and the log would note the failure.

Trigger it

This is a scheduled job — weekly suits a weekly download metric.

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger. Choose pullPodcastStats, a Time-driven source, and a Week timer set to your preferred day and hour.
3. Save and approve the authorisation prompt.
4. The sheet grows two rows a week with no manual work.

Watch out for

• The endpoints in PLATFORMS are placeholders. Real podcast analytics APIs (Spotify for Podcasters, Apple Podcasts Connect) have their own URLs, auth, and JSON shapes — adjust data.downloads_last_7d to match each response.
• “Last 7 days” overlaps if you run more often than weekly. Run weekly for a clean non-overlapping series, or switch to a daily metric if you trigger daily.
• Most stats APIs need an access token, often a short-lived OAuth one. Store it in Script Properties and refresh it as the provider requires.
• UrlFetchApp is subject to a daily quota (around 20,000 calls on consumer accounts). Two calls a week is nowhere near it, but be aware if you add many platforms.
• The script trusts that downloads_last_7d exists. If a provider renames the field, the row records undefined — log the raw response while wiring up a new platform.