Sync new Stripe customers to a CRM sheet

Northwind takes payment through Stripe, which means Stripe quietly holds the most accurate customer list the business has — every person who ever paid is in there. But Stripe is a billing tool, not a CRM, and the sales team works in a spreadsheet. So they keep a separate customer list by hand, it falls behind within a week, and the two never quite agree.

This script mirrors Stripe into the sheet. It pages through every customer in the Stripe account and writes one row each — ID, email, name, and signup date — to a CRM sheet the team already uses. Run it on a schedule and the sheet is always a faithful copy of who has paid, with no manual upkeep.

What you’ll need

• A Stripe account with customers, and a Stripe secret API key (a restricted key with read access to customers is safer than the full secret key).
• That key saved as STRIPE_KEY in Script Properties — see Store API keys and secrets securely.
• A Google Sheet for the output. The script writes to the first tab and clears it on every run, so use a dedicated sheet.

The script

// The spreadsheet that holds the CRM list. The first tab is rebuilt each run.
const CRM_SHEET_ID = '1abcCrmId';

// Stripe returns up to 100 customers per page; this is its maximum.
const PAGE_SIZE = 100;

/**
 * Pages through every Stripe customer and writes them to the CRM sheet,
 * rebuilding the first tab from scratch.
 */
function syncStripeCustomers() {
  // 1. The Stripe secret key lives in Script Properties, never in the code.
  const stripeKey = PropertiesService.getScriptProperties()
    .getProperty('STRIPE_KEY');

  let startingAfter = null;  // cursor for the next page; null on the first call
  const rows = [];

  // 2. Loop one page at a time until Stripe says there is no more.
  do {
    const url = 'https://api.stripe.com/v1/customers?limit=' + PAGE_SIZE +
      (startingAfter ? '&starting_after=' + startingAfter : '');
    const res = JSON.parse(UrlFetchApp.fetch(url, {
      headers: { Authorization: 'Bearer ' + stripeKey },
      muteHttpExceptions: true,
    }).getContentText());

    // 3. Turn each customer on this page into a row.
    //    Stripe's `created` is a Unix timestamp in seconds, so x1000 for ms.
    for (const c of res.data) {
      rows.push([
        c.id,
        c.email,
        c.name || '',
        new Date(c.created * 1000),
      ]);
    }

    // 4. If there is another page, the cursor is the last ID we just saw.
    startingAfter = res.has_more
      ? res.data[res.data.length - 1].id
      : null;
  } while (startingAfter);

  // 5. Bail out if the account has no customers at all.
  if (!rows.length) {
    Logger.log('No Stripe customers found — nothing to write.');
    return;
  }

  // 6. Rebuild the first tab so deleted customers never linger.
  const sheet = SpreadsheetApp.openById(CRM_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 4).setValues([
    ['stripeId', 'email', 'name', 'createdAt'],
  ]);
  sheet.getRange(2, 1, rows.length, 4).setValues(rows);

  Logger.log('Wrote ' + rows.length + ' Stripe customer(s) to the CRM sheet.');
}

How it works

1. syncStripeCustomers reads the Stripe secret key from Script Properties so the credential never sits in the code.
2. It enters a do...while loop that fetches one page of customers at a time. Stripe uses cursor pagination: on the first call there is no cursor, and on later calls starting_after points at the last ID from the previous page.
3. For each customer it builds a row. The signup date needs care — Stripe’s created field is a Unix timestamp in seconds, so it is multiplied by 1000 before being passed to new Date().
4. After each page it checks has_more. If Stripe says there is another page, it sets the cursor to the last ID just seen; if not, the cursor becomes null and the loop ends.
5. If the account turned out to have no customers, it logs a message and stops without touching the sheet.
6. Otherwise it clears the first tab, writes a fresh header, and writes every collected row at once — so customers deleted in Stripe drop off the sheet too.

Example run

For a Stripe account with three customers, the loop pulls them across one or more pages and writes:

stripeId	email	name	createdAt
cus_Oa1	[email protected]	Sam Carter	2025-06-30
cus_Ob2	[email protected]	Dana Lee	2025-07-02
cus_Oc3	[email protected]		2025-07-04

The log reads Wrote 3 Stripe customer(s) to the CRM sheet. Jay has no name set in Stripe, so the name cell is blank rather than undefined — the c.name || '' fallback handles that. If the account had 250 customers, the loop would run three times (100, 100, 50) and the sheet would hold all 250 rows.

Trigger it

The CRM sheet should track Stripe without anyone thinking about it:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose syncStripeCustomers, a Time-driven source, and a Day timer at an off-peak hour such as 5am to 6am.

A daily run is plenty for a customer list. If new signups need to appear sooner, move to an Hour timer, but watch the execution time as the customer count grows.

Watch out for

• This is a full re-sync, not an incremental one. Every run pulls every customer and rebuilds the sheet. That is simple and self-correcting, but on a very large account it is slow — thousands of customers mean many pages and you may approach the six-minute execution limit. At that scale, sync only customers created after the last run.
• The sheet is cleared and rebuilt every run. Do not add manual columns or notes to the first tab — they will be wiped. Keep working notes on a second tab.
• The Stripe key carries account access. Use a restricted key scoped to customer read access, keep it in Script Properties, and never paste it into the code or the sheet.
• A customer in Stripe is not the same as a paying customer. Stripe creates a customer object before a payment may have succeeded, so the sheet includes people who signed up but never paid. Cross-reference subscriptions or charges if you need only paying accounts.
• Test against Stripe’s test mode first. A test secret key hits the same endpoints with test data, so you can confirm the sheet fills correctly before pointing the script at the live account.