Substack Growth Dashboard in Google Sheets (2026)

Substack's dashboard is good at showing where you are today. It is less good at answering "how did last quarter compare?" in a format you control, or at feeding numbers into an investor update, a team report or your own charts. This guide builds a growth and revenue dashboard in Google Sheets from data you already own: your subscriber export, your RSS feed, and, if Substack has enabled it for you, the Publisher API. You get copy-paste formulas, a weekly routine and an Apps Script that logs a snapshot for you. Facts are as of October 2026. For the developer side of Substack data, see our Substack API guide.

newsletterapi is an independent project, not affiliated with Substack. Nothing below depends on newsletterapi.

The problem

Writers and operators who want their numbers in their own tools keep building the same workaround. One writer described their setup: "What I do have is a full data export every month, read into a database" (r/Substack). Another named the blocker: "The real blocker seems to be that Substack doesn’t offer an API, which makes consolidating your own data almost impossible." (r/Substack). On X, the wish is simply "easy api access for internal reporting tools" (@johnpgarrett). A publisher who left Substack listed this among the reasons: "Making a simple Google analytics dashboard is becoming a massive pain." (LinkedIn).

A spreadsheet fed by a weekly export solves most of this, with no API and no login sharing.

What should a Substack growth dashboard track?

Metric	Where it comes from	How it gets into Sheets
Total, free and paid subscribers	Subscriber CSV export	Import, then formulas or the weekly script
New subscribers per week	Subscription date column in the export	Formula by week
Gross annualized revenue	Home overview card	Type it in weekly
Paid retention and churn	Retention tab	Note key figures monthly
Per-post new subscribers and opens	Each post's stats page, or Publisher API post stats	Manual, or HTTP call where enabled
Daily totals	Publisher API /subscribers/counts, where enabled	Apps Script
Latest posts	Your RSS feed	IMPORTFEED

Substack's metrics guide defines the headline numbers: total subscribers are "free and paying subscribers", and gross annualized revenue is "how much money you make per year, based on annualizing your current paid subscriptions". The subscriber dashboard guide adds that the paid figure "includes gifts, complimentary, and free trial subscriptions", so the sheet below groups those together to match. Paid retention lives on the Retention tab, which shows paid cohorts at 30 days, 6 months and 12 months. For what each dashboard number means, see Substack analytics explained.

Step 1: export your subscriber list

From Substack's help centre: open your Subscribers page, scroll to All subscribers, click the three dots above the list, choose Export, then pick all columns or visible columns only. Visible columns include "the date someone subscribed and their type of plan"; all columns add activity such as last open. Names are not included.

Substack doesn't publish the CSV's column headers, so open your file once and note the exact header text for three columns: the email address, the subscription date and the plan type. Everything below is keyed on those names rather than column letters, so it survives column changes. Our subscriber export guide covers the columns in more detail.

Step 2: set up the sheet and formulas

Create a spreadsheet with four tabs: Subscribers, Config, Weekly and Posts.

1. In Subscribers, use File, Import, upload the CSV and choose Replace current sheet (Google help). Do this each time you refresh.
2. In Config, put the three header names in B1 (email), B2 (subscription date) and B3 (plan type), exactly as they appear in row 1 of the import.

Now the headline numbers, anywhere on a summary tab:

Total subscribers
=COUNTA(INDEX(Subscribers!A2:Z, 0, MATCH(Config!B1, Subscribers!A1:Z1, 0)))

Free subscribers
=COUNTIF(INDEX(Subscribers!A2:Z, 0, MATCH(Config!B3, Subscribers!A1:Z1, 0)), "free")

Paid, comp, gift or trial
=B1-B2

Joined in the last 7 days
=LET(dates, INDEX(Subscribers!A2:Z, 0, MATCH(Config!B2, Subscribers!A1:Z1, 0)),
     days, ARRAYFORMULA(IF(ISNUMBER(dates), INT(dates), IFERROR(DATEVALUE(LEFT(dates, 10)), 0))),
     SUMPRODUCT(--(days >= TODAY() - 7)))

The third formula assumes the first two sit in B1 and B2; adjust the references to your layout. COUNTIF is not case-sensitive, so "Free" and "free" both count. The date formula uses LET and SUMPRODUCT, and copes with dates Sheets has already converted as well as ISO text such as 2026-10-20T10:00:00Z.

For a signups-by-week chart, put a Monday in Weekly!A2, =A2+7 in A3 and fill down, then in B2:

=LET(dates, INDEX(Subscribers!A2:Z, 0, MATCH(Config!B2, Subscribers!A1:Z1, 0)),
     days, ARRAYFORMULA(IF(ISNUMBER(dates), INT(dates), IFERROR(DATEVALUE(LEFT(dates, 10)), 0))),
     SUMPRODUCT((days >= A2) * (days < A2 + 7)))

Fill down and insert a column chart. One caveat: a single export describes the list on the day you download it, so people who joined in a given week and have since left may be missing from that week's count. The weekly snapshot in Step 3 fixes this going forward.

Finally, in Posts!A1 pull your latest posts from your feed with IMPORTFEED, using the feed address from Substack's help centre:

=IMPORTFEED("https://yourname.substack.com/feed", "items", TRUE, 20)

That lists titles, links and dates next to your growth numbers, so you can see which posts line up with good weeks. Paid posts are cut at the paywall in the feed, which doesn't matter here.

Step 3: automate the weekly snapshot with Apps Script

Formulas show the current export; a history needs one row per week. This Apps Script does that. Each week you drop the new CSV into a Google Drive folder, and on Monday morning the script reads the newest CSV, counts it and appends a row. It reads files with Folder.getFiles, parses them with Utilities.parseCsv, and remembers the last file in Script Properties so it never logs the same export twice.

Open Extensions, Apps Script from your sheet, paste this in, set folderId to the ID in your Drive folder's URL, then run createWeeklyTrigger once and approve the permissions:

// Substack growth dashboard: append one row per week from the subscriber CSV you drop in a Drive folder.
const CONFIG = {
  folderId: "PASTE_YOUR_DRIVE_FOLDER_ID",
  sheetName: "Weekly log",
  // Substack doesn't document its CSV headers. Leave these blank to auto-detect,
  // or paste the exact header text from your own file.
  emailHeader: "",
  dateHeader: "",
  planHeader: "",
};

function appendWeeklySnapshot() {
  const file = newestCsv_(CONFIG.folderId);
  if (!file) throw new Error("No CSV in the folder yet. Export from Substack and drop it in.");
  const props = PropertiesService.getScriptProperties();
  if (props.getProperty("lastFileId") === file.getId()) {
    console.log("No new export since last run; skipping.");
    return;
  }
  const rows = Utilities.parseCsv(file.getBlob().getDataAsString("UTF-8"));
  const s = summarise_(rows, CONFIG, new Date());
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(CONFIG.sheetName) || ss.insertSheet(CONFIG.sheetName);
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(["Week of", "Total", "Free", "Paid, comp, gift or trial", "Joined in last 7 days", "File"]);
  }
  sheet.appendRow([s.asOf, s.total, s.free, s.notFree, s.newLast7, file.getName()]);
  props.setProperty("lastFileId", file.getId());
}

function newestCsv_(folderId) {
  const files = DriveApp.getFolderById(folderId).getFiles();
  let newest = null;
  while (files.hasNext()) {
    const f = files.next();
    if (!/\.csv$/i.test(f.getName())) continue;
    if (!newest || f.getDateCreated() > newest.getDateCreated()) newest = f;
  }
  return newest;
}

function findColumn_(headers, override, pattern, label) {
  const lower = headers.map((h) => String(h).trim().toLowerCase());
  const i = override ? lower.indexOf(override.trim().toLowerCase()) : lower.findIndex((h) => pattern.test(h));
  if (i === -1) throw new Error(`Couldn't find the ${label} column. Set CONFIG.${label}Header to the exact header in your CSV.`);
  return i;
}

function summarise_(rows, cfg, now) {
  const headers = rows[0];
  const email = findColumn_(headers, cfg.emailHeader, /e-?mail/, "email");
  const date = findColumn_(headers, cfg.dateHeader, /(created|subscri|start|joined).*([_ ]at$|date)|^date$/, "date");
  const plan = findColumn_(headers, cfg.planHeader, /(subscription|plan).*type|^type$|plan/, "plan");
  const weekAgo = now.getTime() - 7 * 24 * 60 * 60 * 1000;
  let total = 0, free = 0, newLast7 = 0;
  for (const r of rows.slice(1)) {
    if (!r[email]) continue; // skip blank lines
    total++;
    if (String(r[plan]).trim().toLowerCase() === "free") free++;
    const t = Date.parse(r[date]);
    if (!isNaN(t) && t >= weekAgo) newLast7++;
  }
  const asOf = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy-MM-dd");
  return { asOf, total, free, notFree: total - free, newLast7 };
}

// Run once by hand to schedule the weekly run (Monday, starting within the 09:00 hour).
function createWeeklyTrigger() {
  ScriptApp.newTrigger("appendWeeklySnapshot")
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}

The script guesses the three columns from their header names and stops with a clear error if it can't find one; set the CONFIG headers to be certain. Google notes that for time-driven triggers "The time might be slightly randomized", so a 09:00 trigger runs some time in that hour (Apps Script docs). We tested the counting logic in Node.js on 2026-10-24 with sample CSVs, standing in for the Apps Script services; run it once by hand on your own file before relying on the trigger.

The weekly routine then takes a couple of minutes: on Monday before 09:00, export the CSV, drop it in the folder, and type that week's gross annualized revenue from your Home page into a column next to the log. Once the row is written, you can delete the raw Subscribers import if you only want counts in the sheet.

Optional: daily counts from the Publisher API

If Substack has enabled the read-only Publisher API for your publication (see Substack API key), Apps Script can fill a Daily tab with UrlFetchApp. The path and field names come from the open-source substack-publisher-mcp wrapper (self-reported): /subscribers/counts returns daily rows, newest first, with total_email_subscribers and paid_subscribers, and free is total minus paid. Save your key under Project Settings, Script properties as SUBSTACK_PUBLISHER_API_KEY, never in the code:

function appendPublisherCounts() {
  const props = PropertiesService.getScriptProperties();
  const key = props.getProperty("SUBSTACK_PUBLISHER_API_KEY");
  if (!key) throw new Error("Add SUBSTACK_PUBLISHER_API_KEY to Script properties first.");
  const since = props.getProperty("lastCountDate") || "2025-12-31";
  const res = UrlFetchApp.fetch(
    "https://publisher-api.substack.com/v1/subscribers/counts?startDate=" + since,
    { headers: { authorization: key, accept: "application/json" }, muteHttpExceptions: true }
  );
  if (res.getResponseCode() !== 200) {
    throw new Error("Publisher API returned " + res.getResponseCode() + ": " + res.getContentText().slice(0, 200));
  }
  const days = JSON.parse(res.getContentText())
    .filter((d) => d.date > since) // skip days already written
    .sort((a, b) => (a.date < b.date ? -1 : 1)); // the API returns newest first
  if (!days.length) return;
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Daily") || ss.insertSheet("Daily");
  if (sheet.getLastRow() === 0) sheet.appendRow(["Date", "Total", "Paid", "Free"]);
  for (const d of days) {
    sheet.appendRow([d.date, d.total_email_subscribers, d.paid_subscribers, d.total_email_subscribers - d.paid_subscribers]);
  }
  props.setProperty("lastCountDate", days[days.length - 1].date);
}

Add a daily time-driven trigger for it the same way. These are counts only: the API cannot list subscribers, so the weekly export is still where per-person data comes from.

What this dashboard can't show

• Live per-subscriber events. Substack has no subscriber webhooks; see Substack webhooks.
• Stripe-level revenue. Refunds, fees and payouts live in Stripe; our guide to why Substack and Stripe numbers don't match explains the gaps.
• Who is going quiet. Activity columns in the all-columns export can show it; see finding inactive subscribers.

Can I skip the formulas?

The newsletterapi Analytics API takes the exports you upload on a reminder and returns growth by week or month, cohorts and engagement segments as JSON or CSV, which Sheets can pull in with IMPORTDATA or a short Apps Script fetch. You can also ask for the same numbers in plain language through our MCP server. It reads only your exports and never your Substack login. To get access, see pricing or email hello@newsletterapi.io.