Score support tickets for urgency with AI

Northwind’s support queue does not arrive sorted. A panicked “everything is down” sits in the same list as a routine “how do I change my password”, and until someone reads each ticket, nobody knows which is which. That reading is the bottleneck — and the urgent ticket waits behind the calm ones.

This script triages the queue before a human touches it. For every open ticket with no priority yet, it sends the subject and body to Claude and asks for a single label — high, normal or low — then writes that label straight back into the sheet. The team opens a queue that is already sorted by how much it matters.

What you’ll need

• A Tickets Google Sheet with a header row and these columns: subject, body, status (the script only looks at rows where this is open), and an empty priority column for the output.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The script

// The sheet holding incoming support tickets.
const TICKETS_SHEET_ID = '1abcTicketsId';

// Only rows with this status are scored.
const OPEN_STATUS = 'open';

/**
 * Reads every open ticket with no priority yet, asks Claude to rate
 * its urgency, and writes the label (high/normal/low) back to the sheet.
 */
function scoreOpenTickets() {
  const sheet = SpreadsheetApp.openById(TICKETS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and map header names to column indexes.
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  if (!rows.length) {
    Logger.log('No tickets to score — nothing to do.');
    return;
  }
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  let scored = 0;
  rows.forEach((r, i) => {
    // 2. Skip rows already scored, or any ticket that is not open.
    if (r[col.priority] || r[col.status] !== OPEN_STATUS) return;

    // 3. Ask Claude for an urgency label and write it into the values grid.
    const score = priorityFor(r[col.subject], r[col.body]);
    values[i + 1][col.priority] = score;
    scored++;
  });

  // 4. Write the whole grid back in one call — far faster than cell by cell.
  sheet.getDataRange().setValues(values);
  Logger.log('Scored ' + scored + ' open tickets.');
}

/**
 * Sends one ticket's subject and body to Claude and returns a single
 * lower-cased urgency label: high, normal or low.
 */
function priorityFor(subject, body) {
  const prompt =
    "Rate the urgency of this Northwind support ticket as 'high', " +
    "'normal', or 'low'. Return only the label.\n\n" +
    'Subject: ' + subject + '\n\nBody: ' + body;
  return callClaude(prompt).toLowerCase();
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model: 'claude-haiku-4-5-20251001',
      max_tokens: 30,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. scoreOpenTickets reads the whole sheet into a values grid and builds a col lookup mapping each header name to its index, so the code refers to fields by name. It stops early if there are no ticket rows.
2. For each row it skips two cases: a ticket that already has a priority (scored on an earlier run) and any ticket whose status is not open.
3. priorityFor builds a prompt with the ticket’s subject and body and asks for exactly one label. max_tokens is just 30 — the answer is a single word, so the call stays cheap and fast.
4. The label is written into the in-memory values grid rather than the sheet, so nothing is saved one cell at a time.
5. After the loop, the entire grid is written back with a single setValues call — much faster than a write per ticket and gentler on the quota.

Example run

Say the Tickets sheet holds open rows like these, with an empty priority column:

subject	body	status	priority
Cannot log in at all	The whole team is locked out before a client call	open	(blank)
Question about invoices	Where do I download last month’s invoice?	open	(blank)
Typo on dashboard	Small spelling mistake on the reports page	open	(blank)

After a run, the priority column is filled:

subject	priority
Cannot log in at all	high
Question about invoices	normal
Typo on dashboard	low

The team can now sort the queue by priority and work the high ones first.

Trigger it

Tickets arrive all day, so run the triage on a frequent schedule:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose scoreOpenTickets, a Time-driven source, and a Minutes timer — every 15 or 30 minutes keeps the queue close to live.

Because the script only scores rows with a blank priority, each run spends API calls on new tickets only.

Watch out for

• The label is a prediction, not a rule. Claude reads tone and content, but it cannot know that one customer is a key account. Let the team override the label by hand when they know better.
• Re-scoring needs a blank cell. Once a ticket has a priority it is skipped forever — clear the cell if a ticket’s situation changes and you want a fresh score.
• setValues rewrites the whole grid. If someone edits the sheet while the script runs, their change can be overwritten. Run it on a schedule rather than while people are working in the sheet, or read and write a narrower range.
• Constrain the output. The prompt asks for only high, normal or low, and toLowerCase normalises the case — but if Claude ever adds a stray word, validate the label against the allowed set before trusting it.
• One ticket per call. For a large backlog this is slow and can approach the script runtime limit — batch several tickets into one prompt and ask for a JSON array of labels.