Pull every Stripe transaction into Google Sheets. Free.

/**
 * Free Stripe -> Google Sheets Transaction Puller
 * By Makeinfo -- https://www.makeinfo.co/products/stripesync
 *
 * Pulls every Stripe charge -- succeeded, failed, refunded, pending, all of
 * it -- created in the trailing N months, and writes it to the active sheet.
 *
 * SETUP
 * 1. In Stripe: Developers -> API keys -> Create restricted key.
 *    Name it something like "Google Sheets read-only", grant it
 *    "Charges: Read" only, leave every other resource at "None".
 *    Copy the key (starts with rk_live_ or rk_test_) -- Stripe only shows
 *    it once. Do NOT use a secret key (sk_live_/sk_test_); this script
 *    never needs write access to your Stripe account.
 * 2. In a blank Google Sheet: Extensions -> Apps Script.
 * 3. Delete the boilerplate in Code.gs and paste this whole file in its place.
 * 4. Fill in CONFIG below.
 * 5. Save, then run pullStripeTransactions from the Run button. The first
 *    run asks you to authorize the script -- that's Google asking
 *    permission to edit this spreadsheet, not Stripe. Reload the sheet tab
 *    afterward and use the "Stripe Transactions" menu to re-run any time.
 *
 * Every run clears the active sheet and rewrites it from scratch -- this is
 * a snapshot of the trailing window, not an append log.
 */

// --------------------------- CONFIG ---------------------------
const CONFIG = {
  // Restricted key only. Stripe Dashboard -> Developers -> API keys ->
  // Create restricted key -> grant "Charges: Read" -> Create key.
  // Starts with rk_live_ or rk_test_.
  STRIPE_API_KEY: 'rk_live_your_restricted_key_here',

  // How many months back to pull, counting from right now.
  TRAILING_MONTHS: 3,
};
// ----------------------------------------------------------------

const STRIPE_API_BASE = 'https://api.stripe.com/v1';
const PAGE_SIZE = 100;

const SHEET_HEADERS = [
  'Charge ID',
  'Date',
  'Amount',
  'Currency',
  'Status',
  'Customer Email',
  'Description',
  'Payment Method',
  'Refunded Amount',
];

/**
 * Adds a menu so re-running this is one click, instead of going back into
 * the Apps Script editor every time. Runs automatically when the sheet opens.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Stripe Transactions')
    .addItem('Pull trailing transactions', 'pullStripeTransactions')
    .addToUi();
}

/**
 * Entry point. Run this to pull every charge from the trailing
 * CONFIG.TRAILING_MONTHS months into the active sheet.
 */
function pullStripeTransactions() {
  const ui = SpreadsheetApp.getUi();

  const keyCheck = validateApiKey_(CONFIG.STRIPE_API_KEY);
  if (!keyCheck.ok) {
    ui.alert('Stripe key looks wrong', keyCheck.message, ui.ButtonSet.OK);
    return;
  }

  const range = getTrailingMonthsRange_(CONFIG.TRAILING_MONTHS);

  let charges;
  try {
    charges = fetchAllCharges_(CONFIG.STRIPE_API_KEY, range.gte, range.lt);
  } catch (err) {
    ui.alert('Stripe API error', err.message, ui.ButtonSet.OK);
    return;
  }

  writeChargesToActiveSheet_(charges);

  ui.alert(
    'Done',
    'Pulled ' + charges.length + ' charge' + (charges.length === 1 ? '' : 's') +
      ' from the last ' + CONFIG.TRAILING_MONTHS + ' month' + (CONFIG.TRAILING_MONTHS === 1 ? '' : 's') + '.',
    ui.ButtonSet.OK
  );
}

/**
 * A light sanity check, not real validation -- Stripe itself is the source
 * of truth on whether a key actually works. This only catches the two most
 * common mistakes: leaving the placeholder in, or pasting a full secret key.
 */
function validateApiKey_(key) {
  if (key && key.indexOf('sk_') === 0) {
    return {
      ok: false,
      message:
        'This looks like a Stripe secret key (sk_...). Use a restricted key ' +
        '(rk_...) instead -- Stripe Dashboard -> Developers -> API keys -> ' +
        'Create restricted key, grant it "Charges: Read" only, and paste that here.',
    };
  }
  if (!key || key.indexOf('rk_') !== 0) {
    return {
      ok: false,
      message:
        'CONFIG.STRIPE_API_KEY does not look like a Stripe restricted key ' +
        '(it should start with rk_live_ or rk_test_). Update CONFIG at the top of the script.',
    };
  }
  return { ok: true, message: '' };
}

/** Trailing N months, ending now, as Stripe created[gte]/created[lt] unix-seconds. */
function getTrailingMonthsRange_(months) {
  const now = new Date();
  const start = new Date(now);
  start.setMonth(start.getMonth() - months);

  return {
    gte: Math.floor(start.getTime() / 1000),
    lt: Math.floor(now.getTime() / 1000),
  };
}

/**
 * Pages through GET /v1/charges via starting_after cursor pagination,
 * 100 charges per request (Stripe's max page size), until has_more is false.
 */
function fetchAllCharges_(apiKey, createdGte, createdLt) {
  const charges = [];
  let startingAfter = null;
  let hasMore = true;

  while (hasMore) {
    const params = {
      limit: PAGE_SIZE,
      'created[gte]': createdGte,
      'created[lt]': createdLt,
    };
    if (startingAfter) params.starting_after = startingAfter;

    const page = stripeGet_(apiKey, '/charges', params);
    charges.push.apply(charges, page.data);

    hasMore = page.has_more && page.data.length > 0;
    if (hasMore) {
      startingAfter = page.data[page.data.length - 1].id;
    }
  }

  return charges;
}

/** One authenticated GET against the Stripe API using Apps Script's native UrlFetchApp. */
function stripeGet_(apiKey, path, params) {
  const query = Object.keys(params)
    .map(function (key) {
      return encodeURIComponent(key) + '=' + encodeURIComponent(params[key]);
    })
    .join('&');
  const url = STRIPE_API_BASE + path + (query ? '?' + query : '');

  const response = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + apiKey },
    muteHttpExceptions: true,
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status !== 200) {
    let message = body;
    try {
      const parsed = JSON.parse(body);
      if (parsed.error && parsed.error.message) message = parsed.error.message;
    } catch (e) {
      // Body wasn't JSON -- fall back to the raw text above.
    }
    throw new Error('Stripe returned ' + status + ': ' + message);
  }

  return JSON.parse(body);
}

/** Clears the active sheet and rewrites headers + one row per charge. */
function writeChargesToActiveSheet_(charges) {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();

  const rows = charges.map(chargeToRow_);
  const values = [SHEET_HEADERS].concat(rows);

  sheet.getRange(1, 1, values.length, SHEET_HEADERS.length).setValues(values);
  sheet.getRange(1, 1, 1, SHEET_HEADERS.length).setFontWeight('bold');
  sheet.setFrozenRows(1);
  sheet.autoResizeColumns(1, SHEET_HEADERS.length);
}

/**
 * Maps one raw Stripe charge object to a sheet row. Amounts are converted
 * from Stripe's smallest-currency-unit integers (e.g. cents) to a decimal
 * by dividing by 100 -- correct for two-decimal currencies (USD, EUR, GBP...)
 * but not for zero-decimal currencies (JPY, KRW...). If you bill in one of
 * those, adjust the division for that row.
 */
function chargeToRow_(charge) {
  return [
    charge.id,
    new Date(charge.created * 1000),
    charge.amount / 100,
    charge.currency ? charge.currency.toUpperCase() : '',
    charge.status,
    charge.receipt_email || (charge.billing_details && charge.billing_details.email) || '',
    charge.description || '',
    (charge.payment_method_details && charge.payment_method_details.type) || '',
    charge.amount_refunded / 100,
  ];
}