How to Track Membership Renewals in Google Sheets and Email Reminders Before They Expire (2026)

Clubs, associations, gyms, studios, alumni groups and nonprofits often keep their member list in a spreadsheet, and the renewal chase usually happens by hand: someone scrolls the list, spots who's about to expire, and writes the emails one by one, or forgets to. This guide turns a member list in Google Sheets into a renewal tracker. It works out each expiry date from the term, shows who is Active, in a grace period or Lapsed, lists who expires in the next 30 days, and adds a free Apps Script that emails each member 30 days, 7 days and on the day their membership expires, plus one optional “come back” note two weeks after.

The difference from the other reminder guides here is who gets the email. Contract renewal alerts and the warranty tracker remind you. This one writes to the members themselves, one personal email each, so it starts in a preview mode that sends everything to you first.

Step 1: a Members tab

Create a tab named Members with these headers in row 1. The script finds columns by name, but the formulas below assume this order (A to L):

ColHeaderWhat goes in it
ANameThe name you'd use in “Hi …”. Use first names here if that's how you write to members
BEmailOne address per member
CPlane.g. Single, Family, Student
DJoinedThe date they first joined (kept for your records; the script doesn't use it)
ETerm startThe start of the current paid term
FTerm monthsLength of the term, e.g. 12 for annual or 1 for monthly
GExpiresA formula (Step 2), formatted as a date
HDays leftA formula (Step 2)
IStandingA formula (Step 2): Active, Grace period or Lapsed
JStatusLeave blank for normal members. Cancelled, Lifetime, Honorary or Do not email stops reminders
KRenew linkOptional: a personal renewal or payment link for this member. Blank uses one link for everyone
LSent logLeave empty; the script records what it sent

Format D, E and G as dates (Format › Number › Date). A date typed as text, like “Jan 11”, won't calculate.

Step 2: expiry date, days left and standing

In G2, fill down. EDATE adds whole months and handles short months (a 1-month term starting January 31 ends February 28):

=IF(OR(E2="",F2=""),"",EDATE(E2,F2))

That ends the term on the same day of the month it started. If your terms run “through” the day before (January 1 to December 31), use EDATE(E2,F2)-1. If everyone expires on the same date, like a calendar-year membership, use =IF(E2="","",DATE(YEAR(E2),12,31)) instead.

In H2, the days remaining (format it as a plain number; negative means it has expired):

=IF(G2="","",G2-TODAY())

In I2, a readable standing with a 30-day grace period (change the 30 to match your rules). A Status like Lifetime or Cancelled shows instead:

=IF(J2<>"",J2,IF(G2="","",IF(G2>=TODAY(),"Active",IF(TODAY()-G2<=30,"Grace period","Lapsed"))))

TODAY() recalculates every day, so Days left and Standing update on their own.

Step 3: who expires soon, and a quick count

On a new tab, this lists every member whose membership expires in the next 30 days, soonest first, skipping anyone with a Status set:

=SORT(FILTER({Members!A2:A,Members!B2:B,Members!C2:C,Members!G2:G,Members!H2:H},
  Members!G2:G<>"",Members!H2:H>=0,Members!H2:H<=30,Members!J2:J=""),5,TRUE)

A FILTER that finds nothing shows #N/A; wrap it in IFERROR(…,"No one expiring") if you like. Format the fourth column as a date. For a dashboard, count members by standing and by plan:

=COUNTIF(Members!I2:I,"Active")
=COUNTIF(Members!I2:I,"Grace period")
=QUERY(Members!A2:L,"select C, count(A) where I = 'Active' group by C label count(A) 'Active members'",0)

Step 4: color the list

Select A2:L, open Format › Conditional formatting, pick Custom formula is, and add these rules (the $ keeps each rule on column H and I while it colors the whole row):

Custom formulaColorMeaning
=AND(ISNUMBER($H2),$H2>=0,$H2<=30,$J2="")YellowExpires within 30 days
=$I2="Grace period"OrangeExpired, still in the grace period
=$I2="Lapsed"GrayLapsed

Step 5: email members before they expire

Sheets can't send an email when a date arrives on its own; its notification rules react to edits, not the calendar. A short Apps Script on a daily trigger does it. Open Extensions › Apps Script, paste this, and set ORG_NAME and RENEW_URL. Leave PREVIEW = true, save, and run sendMembershipReminders once to approve permissions. Every email that would go out today lands in your own inbox, with the real recipient in the subject, and nothing is logged. When the previews look right, set PREVIEW = false and add a trigger: Triggers › Add trigger › sendMembershipReminders › Time-driven › Day timer.

/**
 * Membership renewal reminders from a "Members" tab, emailed to each member.
 * Headers used (any order): Name, Email, Expires, Sent log.
 * Optional: Status (stops reminders), Renew link (per-member link),
 * and any other column you name in the messages as {{Column name}}.
 */
const SHEET_NAME  = 'Members';
const ORG_NAME    = 'Riverside Running Club';
const RENEW_URL   = 'https://example.org/renew';  // used when a row has no Renew link
const REPLY_TO    = '';               // e.g. 'membership@yourclub.org' (blank = you)
const REMIND_DAYS = [30, 7, 0];       // days before Expires
const LAPSED_DAYS = 14;               // one "come back" note this many days after; 0 = off
const STOP_STATUS = /^(cancelled|canceled|lifetime|honorary|do not email)$/i;
const PREVIEW     = true;             // true: everything goes to you, nothing is logged
const MAX_EMAILS  = 50;               // safety cap per run

const SUBJECT_SOON = 'Your {{Org}} membership expires {{When}}';
const BODY_SOON =
  'Hi {{Name}},\n\nYour {{Org}} membership expires {{When}} ({{Expires on}}). ' +
  'To keep it going, you can renew here:\n{{Renew link}}\n\n' +
  "If you've already renewed, thank you, and please ignore this note.\n\n{{Org}}";
const SUBJECT_LAPSED = 'Your {{Org}} membership has expired';
const BODY_LAPSED =
  'Hi {{Name}},\n\nYour {{Org}} membership ended on {{Expires on}}. ' +
  "We'd love to have you back. You can renew any time here:\n{{Renew link}}\n\n" +
  "If you'd rather not hear from us about this again, just reply and say so.\n\n{{Org}}";

function sendMembershipReminders(now) {
  const lock = LockService.getDocumentLock();
  if (!lock.tryLock(30000)) return;  // another run is still going
  try {
    // A time-driven trigger passes an event object, so only use a real Date.
    return remindMembers_(now instanceof Date ? new Date(now) : new Date());
  } finally {
    lock.releaseLock();
  }
}

function remindMembers_(today) {
  const sh = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  if (!sh) throw new Error(`No tab named ${SHEET_NAME}.`);
  const range = sh.getDataRange();
  const rows = range.getValues(), shown = range.getDisplayValues();
  const head = rows[0].map((h) => String(h).trim());
  const col  = (name) => head.indexOf(name);
  const c = { name: col('Name'), email: col('Email'), exp: col('Expires'),
              status: col('Status'), link: col('Renew link'), log: col('Sent log') };
  if ([c.name, c.email, c.exp, c.log].includes(-1)) {
    throw new Error('Need Name, Email, Expires and Sent log columns.');
  }
  // Check every {{placeholder}} before anything is sent.
  const extra = ['Org', 'When', 'Expires on', 'Renew link'];
  const tags  = [SUBJECT_SOON, BODY_SOON, SUBJECT_LAPSED, BODY_LAPSED].join(' ')
    .match(/{{[^{}]+}}/g) || [];
  const unknown = [...new Set(tags.map((t) => t.slice(2, -2).trim()))]
    .filter((k) => !head.includes(k) && !extra.includes(k));
  if (unknown.length) {
    throw new Error(`No column for: ${unknown.map((k) => `{{${k}}}`).join(', ')}. Nothing was sent.`);
  }

  const tz = Session.getScriptTimeZone();
  today.setHours(0, 0, 0, 0);
  const isDate = (v) => v instanceof Date && !isNaN(v);
  const ymd    = (d) => Utilities.formatDate(d, tz, 'yyyy-MM-dd');
  const nice   = (d) => Utilities.formatDate(d, tz, 'MMMM d, yyyy');
  const daysTo = (d) => {
    const x = new Date(d); x.setHours(0, 0, 0, 0);
    return Math.round((x - today) / 86400000);
  };
  const when  = (n) => (n === 0 ? 'today' : n === 1 ? 'tomorrow' : `in ${n} days`);
  const EMAIL = /^[^\s@,;]+@[^\s@,;]+\.[^\s@,;]+$/;
  // Values go in literally: a "$1" or "{{x}}" inside a cell is not re-filled.
  const fill  = (tpl, vals) => tpl.replace(/{{([^{}]+)}}/g, (_, k) => vals[k.trim()]);

  const due = [];
  let badEmail = 0;
  for (let r = 1; r < rows.length; r++) {
    const row = rows[r];
    if (c.status !== -1 && STOP_STATUS.test(String(row[c.status]).trim())) continue;
    const exp = row[c.exp];
    if (!isDate(exp)) continue;
    const left = daysTo(exp);
    let hit;
    if (left >= 0) {
      // The one reminder that fits today: no pile of earlier ones after a gap.
      hit = REMIND_DAYS.filter((n) => left <= n).sort((a, b) => a - b)[0];
    } else if (LAPSED_DAYS > 0 && -left >= LAPSED_DAYS && -left < LAPSED_DAYS + 7) {
      // A one-week window, so long-lapsed members don't all get mail on day one.
      hit = -LAPSED_DAYS;
    }
    if (hit === undefined) continue;
    const sent = String(row[c.log] || '').split(';').filter(String);
    const key  = `M${ymd(exp)}@${hit}`;
    if (sent.includes(key)) continue;
    const to = String(row[c.email]).trim();
    if (!EMAIL.test(to)) { badEmail++; console.log(`Row ${r + 1}: check the email address`); continue; }
    const vals = {};
    head.forEach((h, i) => { if (h) vals[h] = shown[r][i]; });
    Object.assign(vals, {
      Org: ORG_NAME, When: when(left), 'Expires on': nice(exp),
      'Renew link': String(c.link === -1 ? '' : shown[r][c.link]).trim() || RENEW_URL,
    });
    due.push({ r: r + 1, to, left, lapsed: hit < 0, vals,
               log: sent.concat(key).slice(-9).join(';') });
  }

  // Soonest expiry first, lapsed notes last, in case the quota runs out.
  due.sort((a, b) => (a.lapsed - b.lapsed) || (a.left - b.left));
  const me = Session.getEffectiveUser().getEmail();
  let sent = 0;
  for (const d of due) {
    if (sent >= MAX_EMAILS || MailApp.getRemainingDailyQuota() < 1) break;
    const subject = fill(d.lapsed ? SUBJECT_LAPSED : SUBJECT_SOON, d.vals);
    const msg = {
      to: PREVIEW ? me : d.to,
      subject: PREVIEW ? `[PREVIEW to ${d.to}] ${subject}` : subject,
      body: fill(d.lapsed ? BODY_LAPSED : BODY_SOON, d.vals),
      name: ORG_NAME,
    };
    if (REPLY_TO) msg.replyTo = REPLY_TO;
    MailApp.sendEmail(msg);
    sent++;
    if (!PREVIEW) {
      // Log right after each email, so a crash can't cause a repeat.
      sh.getRange(d.r, c.log + 1).setValue(d.log);
      SpreadsheetApp.flush();
    }
  }
  const result = { sent, left: due.length - sent, badEmail, preview: PREVIEW };
  console.log(JSON.stringify(result));
  return result;
}

How it behaves:

We mock-tested this logic before publishing (30 checks): preview mode sent only to the owner and logged nothing; the real run sent today's, 7-day, late 30-day and 30-day reminders in that order, then the lapsed note; reruns the same day and the next day sent nothing; the day-of reminder followed the 7-day one; a renewed member got a fresh reminder for the new date; cancelled, lifetime, long-lapsed and malformed-address rows were skipped; values like $1 went in literally; an unknown placeholder stopped the run with nothing sent; and the quota and per-run caps left the rest for the next run without logging them.

Recording a renewal

Tips

Want renewal reminders without maintaining a script? SheetReminders (disclosure: we make it) is a Google Sheets add-on that watches a date column and emails the right person before it arrives. Point it at the Expires column from Step 2 and the Email column: several reminders per date (like 30, 7 and 0 days), personalized messages with {{Column name}} and {{Days left}}, skipping rows whose Status says Done, and a sent log written back to the row. It never sends twice for the same date and reminder, and sheet data stays inside your Google account. 14-day free trial, no credit card required. It is live on the Google Workspace Marketplace.
See SheetReminders

Related Google Sheets guides

Instructions refer to Google Sheets and Apps Script as they worked on November 13, 2026. Not affiliated with Google or any other vendor named here. Google Sheets and Gmail are trademarks of Google LLC. Features change, so verify in your own account. Questions or corrections: hello@loomwise.app.