Build a help-desk SLA timer from Gmail

Northwind promises support customers a first response within four hours. That promise is easy to make and hard to measure — without a record of when each ticket arrived and when someone first replied, nobody can say whether the team is actually hitting it or quietly slipping.

This script turns the support inbox into that record. It scans recent threads under a support label, works out the gap between the customer’s opening message and the team’s first reply, and logs every thread to a sheet with a breached flag. New threads are picked up on each run and never logged twice, so the sheet grows into a running first-response log you can summarise, chart, or report on at the end of the month.

What you’ll need

• A Gmail account that receives support mail, with incoming tickets labelled support (a filter that applies the label on arrival works well).
• A Google Sheet with one tab and these columns in row 1: threadId, subject, from, receivedAt, respondedAt, breached.
• The Sheet’s ID, taken from its URL.
• The address your team replies from, so the script can tell a customer message apart from a team reply.

The script

// The spreadsheet that logs first-response times.
const SLA_SHEET_ID = '1abcSlaSheetId';

// The SLA target, in milliseconds. 4 hours here — see "Watch out for"
// before relying on this if you do not run support 24/7.
const SLA_MS = 4 * 60 * 60 * 1000;

// The address support replies from. Used to spot the team's first reply.
const TEAM_ADDRESS = '[email protected]';

// How far back to scan for threads on each run.
const SEARCH_QUERY = 'label:support newer_than:14d';

/**
 * Scans recent support threads, measures first-response time against the
 * SLA, and appends any new threads to the log sheet. Designed to run on a
 * short interval so threads are picked up soon after they arrive.
 */
function trackSlaTimers() {
  const sheet = SpreadsheetApp.openById(SLA_SHEET_ID).getSheets()[0];

  // 1. Build a set of thread IDs already in the sheet, so a thread is
  //    never logged twice.
  const known = new Set(sheet.getRange('A2:A').getValues().flat());

  // 2. Pull recent support threads from Gmail.
  const threads = GmailApp.search(SEARCH_QUERY);

  const rows = [];
  for (const thread of threads) {
    const id = thread.getId();

    // Skip threads already in the log.
    if (known.has(id)) continue;

    const msgs = thread.getMessages();
    const first = msgs[0];

    // 3. Skip threads the team started — those are not customer tickets.
    if (first.getFrom().includes(TEAM_ADDRESS)) continue;

    // 4. Find the team's first reply, if there is one yet.
    const reply = msgs.find((m) => m.getFrom().includes(TEAM_ADDRESS));
    const responded = reply ? reply.getDate() : null;

    // 5. Measure elapsed time. If there is no reply yet, measure against
    //    "now" — a thread can breach the SLA before anyone replies.
    const elapsed = (responded || new Date()) - first.getDate();

    rows.push([
      id,
      first.getSubject(),
      first.getFrom(),
      first.getDate(),
      responded || '',
      elapsed > SLA_MS,
    ]);
  }

  // 6. Append all new threads in one write.
  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 6)
      .setValues(rows);
    Logger.log('Logged ' + rows.length + ' new support thread(s).');
  } else {
    Logger.log('No new support threads to log.');
  }
}

How it works

1. trackSlaTimers opens the log sheet and reads column A into a Set of thread IDs it has already seen. Membership checks against a Set are fast and keep the run from logging the same thread twice.
2. It searches Gmail for threads labelled support from the last 14 days.
3. For each thread it skips any ID already in the sheet, then looks at the first message. If that message is from the team’s own address, the thread was started internally and is skipped — it is not a customer ticket.
4. It scans the thread’s messages for the first one sent from TEAM_ADDRESS, which is the team’s first response.
5. It measures elapsed time between the customer’s opening message and that reply. If there is no reply yet, it measures against the current time, so a slow ticket can register a breach before anyone has answered.
6. A thread breached the SLA when the elapsed time exceeds SLA_MS. All new rows are appended to the sheet in a single write.

Example run

The support inbox has three new threads when the timer runs at 14:00:

Thread	Customer message	Team reply	Elapsed	breached
”Login not working”	09:10	10:05	55 min	FALSE
”Invoice query”	08:00	13:30	5h 30m	TRUE
”Export is slow”	12:45	(no reply yet)	1h 15m	FALSE

After the run, three rows are appended to the log sheet. The “Invoice query” row carries breached = TRUE, and the “Export is slow” row has a blank respondedAt — on a later run, once the team replies, that thread keeps its existing row rather than being re-logged.

A pivot table or filter over the breached column then gives you the month’s breach rate.

Trigger it

Run this on a short interval so threads are logged soon after they arrive:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose the trackSlaTimers function, an event source of Time-driven, a Minutes timer, and Every 15 minutes.
4. Save and approve the authorisation prompt the first time.

Watch out for

• SLA_MS is a flat four-hour window, including nights and weekends. If Northwind only staffs support during business hours, a ticket arriving at 5pm Friday will look badly breached by Monday. Swap the elapsed-time calculation for one that counts only working hours if that matters.
• A thread is logged once, when it is first seen. If the team replies after the thread has already been logged, the existing row keeps respondedAt blank — the script does not revisit old rows. Add a separate pass to update pending rows if you need that.
• It detects the team’s reply by matching TEAM_ADDRESS in the from field. A reply sent from a personal address, or an alias the script does not know about, will be missed and the thread will look unanswered.
• Auto-replies and “we received your ticket” acknowledgements count as a first response if they are sent from TEAM_ADDRESS. Exclude them by subject or content if your SLA means a human reply.
• The search window is 14 days. A thread that is first answered more than 14 days after it arrived would drop out of the search before being logged — fine for a healthy help desk, but worth knowing.