Build a Stripe revenue dashboard

Stripe’s own dashboard is good, but it lives behind a login and resists being sliced the way Northwind’s finance team actually wants it. They want charges in a spreadsheet — next to the targets tab, ready to pivot, ready to chart — and they want it current without anyone exporting a CSV every morning.

This script pulls the last 30 days of Stripe charges into a Google Sheet and rebuilds the tab on every run. Point a pivot table or a chart at that tab once, and an hourly trigger keeps the numbers fresh underneath it. It is the data feed behind a revenue dashboard — MRR and churn are then just formulas over this raw charges table.

What you’ll need

• A Stripe account with charges in it, and access to the Stripe dashboard.
• A Stripe restricted API key with read access to charges. Create one under Developers → API keys → Create restricted key, and grant only Charges: Read. A read-only key limits the damage if it ever leaks.
• That key saved as STRIPE_KEY in Script Properties — see Store API keys and secrets securely.
• A Google Sheet to hold the data. Copy its ID from the URL into the config below; the script writes to the first tab and manages the header itself.

The script

// The Google Sheet that holds the charges table.
const DASHBOARD_SHEET_ID = '1abcStripeId';

// How far back to pull charges, in days.
const LOOKBACK_DAYS = 30;

// Stripe's "list charges" endpoint caps page size at 100.
const PAGE_SIZE = 100;

// Column headers for the charges tab, in write order.
const HEADERS = ['date', 'id', 'amount', 'ccy', 'status', 'customer'];

/**
 * Pulls the last LOOKBACK_DAYS of Stripe charges into the dashboard sheet,
 * paging through every result and rebuilding the tab from scratch.
 */
function pullStripeCharges() {
  // Read the key from Script Properties — never hard-code it.
  const key = PropertiesService.getScriptProperties().getProperty('STRIPE_KEY');
  if (!key) {
    throw new Error('Missing STRIPE_KEY in Script Properties.');
  }

  // Stripe filters by a Unix timestamp in seconds, so convert from the
  // JavaScript milliseconds clock.
  const since = Math.floor((Date.now() - LOOKBACK_DAYS * 86400000) / 1000);

  // 1. Page through every charge since the cutoff. Stripe returns at most
  //    PAGE_SIZE per request and signals more with has_more / a cursor.
  const charges = [];
  let startingAfter = '';
  do {
    let url = 'https://api.stripe.com/v1/charges' +
      '?limit=' + PAGE_SIZE +
      '&created[gte]=' + since;
    if (startingAfter) url += '&starting_after=' + startingAfter;

    const response = UrlFetchApp.fetch(url, {
      headers: { Authorization: 'Bearer ' + key },
      muteHttpExceptions: true,
    });
    if (response.getResponseCode() !== 200) {
      throw new Error('Stripe API error: ' + response.getContentText());
    }

    const page = JSON.parse(response.getContentText());
    charges.push(...page.data);

    // The cursor for the next page is the id of the last row, but only
    // while Stripe says there is more to fetch.
    startingAfter = page.has_more ? page.data[page.data.length - 1].id : '';
  } while (startingAfter);

  // 2. Flatten each charge into a spreadsheet row. Amounts come back in
  //    the smallest currency unit (pence/cents), so divide by 100.
  const rows = charges.map((c) => [
    new Date(c.created * 1000),
    c.id,
    c.amount / 100,
    c.currency.toUpperCase(),
    c.status,
    c.customer || '',
  ]);

  // 3. Rebuild the tab from scratch so it always reflects the latest pull.
  const sheet = SpreadsheetApp.openById(DASHBOARD_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, HEADERS.length).setValues(rows);
  }
  Logger.log('Wrote ' + rows.length + ' charges to the dashboard.');
}

How it works

1. Configuration lives in named constants at the top — the sheet ID, how far back to look, the page size, and the column headers. Change the dashboard by editing these, not by hunting through the function.
2. pullStripeCharges reads the API key from Script Properties and converts the lookback window into a Unix timestamp in seconds, the format Stripe’s created[gte] filter expects.
3. It pages through the results in a do...while loop. Stripe returns at most 100 charges per request and sets has_more when there are more; the script uses the last charge’s id as the starting_after cursor for the next page, so a busy account is fully captured rather than truncated at 100.
4. Each charge is flattened into a six-column row. The amount arrives in the smallest currency unit — pence or cents — so it is divided by 100 to give a normal money value.
5. It clears the first tab, writes the header, then writes every row in a single setValues call. Rebuilding from scratch keeps the tab a faithful mirror of the last 30 days with no stale leftovers.

Example run

For an account with a few dozen recent charges, the dashboard tab fills with rows like these:

date	id	amount	ccy	status	customer
2025-07-03 09:14	ch_3Pa…	49.00	GBP	succeeded	cus_Qk…
2025-07-03 08:51	ch_3Pa…	199.00	GBP	succeeded	cus_Rm…
2025-07-02 22:07	ch_3Pz…	49.00	GBP	failed	cus_Sn…

From there, MRR and churn are spreadsheet formulas, not more code: sum amount where status is succeeded for a recurring period, and count failed rows against successful ones for a churn signal. Build a pivot table or chart on top once and the hourly refresh keeps it live.

Trigger it

This is a background feed, so run it on a clock instead of by hand:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose pullStripeCharges, an event source of Time-driven, and an Hour timer set to every hour.
4. Save, and approve the authorisation prompt the first time.

Hourly is a sensible default for a finance dashboard. If the team only looks once a day, a daily timer cuts the API calls with no real loss.

Watch out for

• Use a restricted, read-only key. This script only reads charges — a key scoped to Charges: Read cannot issue refunds or change anything if it leaks.
• The script rebuilds the whole tab each run. Do not type notes or extra columns into that tab — they will be wiped. Add a separate tab for analysis and reference this one with formulas.
• Amounts are in the smallest currency unit. Forgetting the / 100 turns £49.00 into £4,900 — the script handles it, but keep it in mind if you adjust the mapping.
• Multi-currency accounts mix ccy values in one column. Sum by currency, or convert to a single currency before totalling, rather than adding raw amounts.
• Very large accounts mean many pages, and each page is one UrlFetchApp call against Apps Script’s daily quota and the six-minute execution limit. If a run times out, shorten LOOKBACK_DAYS or store a cursor and resume on the next trigger.