Auto-Fill "Last Contacted" From Gmail in Google Sheets (30 Lines of Apps Script)

#tutorial #googleappsscript #javascript #productivity

If you track clients, leads or candidates in a Google Sheet, you probably have a Last contacted column, and it's probably wrong. Nobody updates it by hand reliably. Gmail already knows the answer, so let's have the sheet ask Gmail.

By the end of this post you'll have:

a script that fills Last contacted, Last from (Me/Them), Last subject and a thread link for every row,
a status formula that turns those columns into Needs reply / Follow up / Gone cold,
an optional daily trigger, so it updates itself every morning.

No add-ons, no third-party server. Everything runs in your own Google account.

1. Set up the sheet

Make a tab called Contacts with this header row:

A	B	C	D	E	F	G
Name	Email	Last contacted	Last from	Last subject	Thread	Status

Fill in A and B. The script writes C to F, and G is a formula.

2. Add the script

In the sheet, open Extensions › Apps Script, delete the placeholder code, and paste:

const SHEET_NAME = 'Contacts';
const FIRST_ROW = 2;   // row 1 is the header
const EMAIL_COL = 2;   // column B
const OUT_COL = 3;     // write to C:F

function updateLastContacted() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const lastRow = sheet.getLastRow();
  if (lastRow < FIRST_ROW) return;

  const emails = sheet
    .getRange(FIRST_ROW, EMAIL_COL, lastRow - FIRST_ROW + 1, 1)
    .getValues();

  // Your own addresses, so we can tell "Me" from "Them".
  const me = [Session.getActiveUser().getEmail(), ...GmailApp.getAliases()]
    .map(a => a.toLowerCase());

  const rows = emails.map(([raw]) => {
    const email = String(raw).trim().toLowerCase();
    if (!email) return ['', '', '', ''];

    // Newest thread where this address appears in From, To or Cc.
    const query = `{from:${email} to:${email} cc:${email}} -in:drafts -in:chats`;
    const threads = GmailApp.search(query, 0, 1);
    if (!threads.length) return ['', '', '', ''];

    const msgs = threads[0].getMessages().filter(m => !m.isDraft());
    const last = msgs[msgs.length - 1];
    if (!last) return ['', '', '', ''];
    const from = last.getFrom().toLowerCase();
    const fromMe = me.some(a => from.includes(a));

    return [last.getDate(), fromMe ? 'Me' : 'Them', last.getSubject(), threads[0].getPermalink()];
  });

  sheet.getRange(FIRST_ROW, OUT_COL, rows.length, 4).setValues(rows);
}

How it works:

GmailApp.search() takes the same query syntax as the Gmail search box. Curly braces mean OR, so {from:x to:x cc:x} matches any thread that involves the address. Results come back newest first, so asking for one result gives you the most recent conversation.
Drafts are filtered out. Otherwise a half-written reply would count as "contacted".
GmailApp.getAliases() covers send-as addresses, so mail you send from an alias still counts as Me.
The script reads one block of emails and writes one block of results. Writing cell by cell is the usual reason Apps Script scripts are slow.

Click Run and pick updateLastContacted. The first time, Google asks you to authorize the script. Because it's your own unverified script, you'll see "Google hasn't verified this app". Click Advanced › Go to (project name). That's expected for personal scripts: it means Google hasn't reviewed it, not that anything is wrong.

3. Turn columns into a follow-up list

In G2, paste this and fill it down:

=IF(C2="", "No history",
  IF(D2="Them", "Needs reply",
  IF(TODAY()-INT(C2)>21, "Gone cold",
  IF(TODAY()-INT(C2)>3, "Follow up", "Waiting"))))

What it says:

Needs reply: they wrote last. That's on you, today.
Follow up: you wrote last more than 3 days ago.
Gone cold: nobody has written in 3 weeks.
Waiting: you wrote recently, so give them time.

Change 3 and 21 to suit your work. Recruiters often use 2 days, and B2B sales a week. Then add conditional formatting (Format › Conditional formatting › Text is exactly "Needs reply", red fill) and a filter view on G. That filter view is now your morning to-do list.

4. Run it every morning (optional)

Add this function and run it once:

function installDailyTrigger() {
  ScriptApp.newTrigger('updateLastContacted')
    .timeBased()
    .everyDays(1)
    .atHour(7)   // in the script's time zone (Project Settings)
    .create();
}

Check Triggers (the clock icon) to make sure there's only one, because running the installer twice gives you two triggers.

Limits you will hit (and how to deal with them)

6-minute runtime. Each Apps Script execution is capped at 6 minutes, and each row is one Gmail search plus a thread read. A few hundred rows is usually fine. For thousands, process a slice per run: save the next row index with PropertiesService.getScriptProperties() and pick up from there on the next trigger.
Daily Gmail quotas. Consumer Gmail accounts get lower daily read quotas than Workspace accounts. A daily run over a normal contact list stays well under them, but don't put this on a 1-minute trigger.
Group threads. If a thread includes other people, the last message might be from a colleague, which counts as Them. If that matters, check last.getTo() and last.getCc() as well.
Shared mailboxes. The script only sees the Gmail account it runs as.

Ways to extend it

Add rows from a label: search label:new-lead, add every sender who isn't already in column B, then remove the label.
Email yourself the list: after updating, collect the rows marked Needs reply or Follow up and send them to yourself with MailApp.sendEmail() (it's your own address, so you're not emailing anyone else).
Auto next-follow-up date: when Last from is Me and the next-follow-up cell is empty, set it to the date plus 3 days.

The companion script that labels threads waiting on them and waiting on me: https://karunalabs.hashnode.dev/how-to-find-unanswered-emails-in-gmail-both-directions-with-a-free-apps-script
Not ready for code? A comparison of free ways to track Gmail follow-ups, from Gmail nudges to spreadsheet templates: https://medium.com/@labskaruna/free-streak-alternatives-in-2026-what-you-actually-get-without-paying-49-a-seat-1c694891d3af

The point stands either way. If a fact already exists in Gmail, don't make a human copy it into a spreadsheet.

This article was written with AI assistance. The script was checked against the Apps Script reference before publishing.

Questions about the script? Drop them in the comments. Happy to help debug queries or quota issues.

Top comments (2)

Hayrullah Kar • Oct 15

One edge in the Me/Them check: from.includes(a) is a substring test, so if your address is ann@acme.com, a reply from joann@acme.com counts as Me, and the row says Waiting when it should say Needs reply. getFrom() returns "Name

", so pull the address out of the angle brackets (falling back to the whole string when there are none) and compare with ===. The second one is quieter. In a time-driven trigger, Session.getActiveUser().getEmail() can come back empty depending on the account and domain setup, and an empty string is a substring of every From header, so every message would read as Me. Session.getEffectiveUser() is the account the script actually runs as, which is what you want here, and filtering empty strings out of the me array closes it either way.