Triage inbound Gmail enquiries into a Sheets queue

1. Step 01·Trigger

   New enquiry email arrives in Gmail

2. Step 02·Log

   Sender and subject logged to a Sheets queue

3. Step 03·Flag

   Row flagged with priority by rule and an owner assigned

A time-driven Apps Script that checks your inbox every five minutes, writes each new enquiry as one row in a Google Sheet (received time, sender, subject, a High or Normal priority from keywords you choose, an owner handed out in turn, a status and a link back to the email), and labels the thread so it is not logged again. The sheet becomes the single list of what is outstanding. Your team changes the Status column by hand as work is done.

1. 1

   Create the sheet

   Make a new Google Sheet, name the file Enquiry queue, and copy the ID from its URL (the string between /d/ and /edit).

2. 2

   Create the script

   Open script.google.com in the same account that owns the inbox, create a project and paste the code below into Code.gs.

3. 3

   Fill in CONFIG

   Set SPREADSHEET_ID, list the OWNERS, adjust HIGH_WORDS, and set ALERT_TO. SEARCH decides which mail counts as an enquiry: in:inbox newer_than:7d logs everything that lands in the inbox. Narrow it, for example to:enquiries@yourcompany.example, if the inbox carries other mail.

4. 4

   Run it once by hand

   Pick logEnquiries and press Run. Authorise Gmail, Sheets and email access when asked. Open the sheet: you should see the header row and one row per recent thread.

5. 5

   Install the trigger

   Run installTrigger once. It sets logEnquiries to run every five minutes and replaces any earlier copy.

6. 6

   Add a view for the team

   In the sheet, add a filter on the Status column and a conditional format on Priority so High rows stand out. These are sheet features and are not touched by the script, because it only ever appends rows.

7. 7

   Turn on failure emails

   In Triggers, open the trigger and set Failure notification settings to notify you immediately.

// Logs new inbox enquiries to a Google Sheets queue with a priority and an owner.
// Paste into Code.gs in a new project at script.google.com, edit CONFIG, run installTrigger() once.

const CONFIG = {
  SPREADSHEET_ID: 'PASTE_THE_SPREADSHEET_ID_HERE',
  SHEET_NAME: 'Queue',
  DONE_LABEL: 'Logged-to-queue',                // created automatically
  SEARCH: 'in:inbox newer_than:7d',
  MAX_THREADS: 30,                              // per run
  OWNERS: ['alex@example.com', 'sam@example.com'], // handed out in turn
  HIGH_WORDS: ['urgent', 'asap', 'complaint', 'refund', 'cancel'],
  // Stop well before the 6 minute limit. On a gmail.com account the whole day's
  // trigger time is 90 minutes, so lower this (for example 60 * 1000) there.
  TIME_BUDGET_MS: 4 * 60 * 1000,
  ALERT_TO: 'ops@example.com',
  ALERT_EVERY_MS: 6 * 60 * 60 * 1000,
};

const HEADERS = ['Received', 'From', 'Subject', 'Priority', 'Owner', 'Status', 'Message ID', 'Link'];
const ID_COLUMN = 7; // 1-based position of 'Message ID'

function logEnquiries() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;
  try {
    const sheet = getSheet_();
    const lastRow = sheet.getLastRow();
    // One read of the ID column, indexed in memory. No sheet reads inside the loop.
    const seen = {};
    if (lastRow > 1) {
      sheet.getRange(2, ID_COLUMN, lastRow - 1, 1).getValues().forEach(function (r) { seen[r[0]] = true; });
    }
    const started = Date.now();
    const me = Session.getEffectiveUser().getEmail().toLowerCase();
    const label = GmailApp.getUserLabelByName(CONFIG.DONE_LABEL) || GmailApp.createLabel(CONFIG.DONE_LABEL);
    const threads = GmailApp.search(CONFIG.SEARCH + ' -label:' + CONFIG.DONE_LABEL, 0, CONFIG.MAX_THREADS);

    const rows = [];
    const handled = [];
    const existing = Math.max(lastRow - 1, 0);
    for (const thread of threads) {
      if (Date.now() - started > CONFIG.TIME_BUDGET_MS) break; // the next run picks up the rest
      const message = thread.getMessages()[0]; // the enquiry itself, not later replies
      handled.push(thread);
      // 'm:' stops Sheets reading an all-digit ID as a number and losing precision.
      const id = 'm:' + message.getId();
      const from = message.getFrom();
      if (seen[id] || (me && from.toLowerCase().indexOf(me) !== -1)) continue;
      const text = (message.getSubject() + ' ' + message.getPlainBody().slice(0, 2000)).toLowerCase();
      const high = CONFIG.HIGH_WORDS.some(function (w) { return text.indexOf(w) !== -1; });
      rows.push([
        message.getDate(),
        safe_(from),
        safe_(message.getSubject()),
        high ? 'High' : 'Normal',
        CONFIG.OWNERS[(existing + rows.length) % CONFIG.OWNERS.length],
        'New',
        id,
        thread.getPermalink(),
      ]);
      seen[id] = true;
    }
    if (rows.length) {
      // One batched write, then label. A crash between the two cannot double-log
      // because the message ID is already in the sheet.
      sheet.getRange(lastRow + 1, 1, rows.length, HEADERS.length).setValues(rows);
    }
    handled.forEach(function (t) { t.addLabel(label); });
  } catch (err) {
    alertOnce_('Enquiry queue failed: ' + err.message);
  } finally {
    lock.releaseLock();
  }
}

function getSheet_() {
  const ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  const sheet = ss.getSheetByName(CONFIG.SHEET_NAME) || ss.insertSheet(CONFIG.SHEET_NAME);
  if (sheet.getLastRow() === 0) sheet.appendRow(HEADERS);
  return sheet;
}

// Stops a sender-controlled subject like "=IMPORTXML(...)" running as a formula.
function safe_(value) {
  return /^[=+@\t\r-]/.test(value) ? "'" + value : value;
}

function alertOnce_(message) {
  const props = PropertiesService.getScriptProperties();
  const last = Number(props.getProperty('lastAlertAt') || 0);
  if (Date.now() - last < CONFIG.ALERT_EVERY_MS) return;
  MailApp.sendEmail(CONFIG.ALERT_TO, 'Enquiry queue needs attention', message);
  props.setProperty('lastAlertAt', String(Date.now()));
}

function installTrigger() {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return t.getHandlerFunction() === 'logEnquiries'; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
  ScriptApp.newTrigger('logEnquiries').timeBased().everyMinutes(5).create();
}

Quotas

Google's quota table lists a 6 minute limit per execution, 20 triggers per user per script, and a total trigger runtime of 90 minutes a day on a gmail.com account or 6 hours a day on Google Workspace. Gmail read and write operations (excluding send) are capped at 20,000 a day on gmail.com and 50,000 a day on Workspace. The trigger-runtime budget is the one to watch. This trigger runs every 5 minutes, which is 288 runs a day. On a gmail.com account that budget works out to an average of about 19 seconds per run; on Workspace, about 75. A run that finds nothing new is short, but a backlog run that uses its full 4 minute budget costs 240 seconds, so about 22 of those use up a gmail.com day. Open Executions in the Apps Script editor to see how long your runs really take. To tune it: lengthen the interval by editing everyMinutes in the install function (Google allows 1, 5, 10, 15 or 30; for longer use everyHours), lower TIME_BUDGET_MS, or narrow the search so fewer threads are read. The queue reads one message per thread, which is well inside the Gmail limit for a normal enquiry inbox, but a mailbox that receives thousands of messages a day needs a tighter search.

Idempotency

The script reads the Message ID column once at the start of each run and skips any message already in the sheet, so a crash between writing rows and labelling threads cannot create duplicates. The IDs are stored with an m: prefix so Sheets keeps them as text; do not delete, edit or re-sort that column.

Stays fast as the queue grows

Each run reads the Message ID column once, checks new enquiries against that list in memory, and adds all new rows in one write. It does not look through the sheet again for every email, so a queue with thousands of rows still runs quickly.

Formula injection

Senders control the subject line. A subject starting with an equals sign would be run as a formula by Sheets, so the script prefixes such text with an apostrophe before writing it. Keep that step if you change the columns.

Priority is only keywords

A keyword list catches the obvious cases and misses the rest. Treat High as a prompt to look, not a judgement, and review the list monthly against what people actually write.

Fair owner assignment

Owners are handed out in turn by row count. It does not know about leave, workload or skills. If someone is away, remove them from OWNERS for the week.

Who gets alerted

A run that fails emails ALERT_TO at most once every 6 hours. Use a shared mailbox so the alert is not tied to one person.

Only the first message

Only the first message in a thread is logged. Replies stay in Gmail. If you want the row to change when a customer replies, that is a bigger build.