Front to Google Sheets SLA log (nightly)

PromptCreate

Every weekday at 11pm in the org's timezone, export every Front conversation that was closed or archived during that day into a designated Google Sheets tab so we can run weekly SLA and volume reporting off structured data. Build this as a code workflow (deterministic pipeline), not an agent.

Trigger: cron, Monday through Friday at 23:00 in the org's local timezone.

Steps:

1. Compute the day's window as [today 00:00, today 23:59] in the configured timezone. This is the SLA reporting window.

2. Call Front's Search Conversations with a query that scopes to conversations archived during that window (status:archived plus the date range). Page through all results using the returned pagination cursor.

3. For each conversation, call Get Conversation to load the full object (subject, inbox, assignee, tags, recipient handle, created_at) and List Conversation Messages to enumerate messages in chronological order. Derive first_response_at as the timestamp of the first outbound teammate message that follows the first inbound message, and resolved_at as the archive timestamp of the conversation. Compute first_response_minutes and resolution_minutes as the deltas from opened_at.

4. Append one row per conversation to a designated Google Sheets tab using Append Values with valueInputOption=USER_ENTERED and insertDataOption=INSERT_ROWS. Columns, in this exact fixed order:

conversation_id, subject, inbox, sender_email, assignee_email, tags (comma-separated), opened_at, first_response_at, resolved_at, first_response_minutes, resolution_minutes.

Configuration inputs to expose to the user at setup: the target spreadsheet ID, the target sheet/tab name, and the timezone to interpret the daily window in. Column order is fixed; the pipeline should be deterministic and safe to re-run over the same window (appending duplicates is acceptable — the user can clear the day's rows before rerunning). Handle Front pagination and 429 rate-limit responses with backoff.

What does this prompt do?

Runs automatically every weekday at 11pm.
Pulls every Front conversation that was closed or archived during the day.
Appends one row per conversation to a Google Sheets tab with fixed columns like inbox, assignee, tags, first response time, and resolution time.
Gives your team a durable, structured log you can build weekly SLA and volume reports on top of.

What do I need to use this?

A Front account with admin access, so you can create an API token that can read conversations and messages.
A Google account with edit access to the spreadsheet you want to log into.
A Google Sheets file with a dedicated tab set aside for the daily log.

How can I customize it?

Change when it runs. For example, every hour, only on weekdays, or every day of the week.
Point it at a different spreadsheet or a different tab inside the same file.
Add or remove columns, like the first tag only, the contact name, or the channel type.

FAQs

Will this overwrite rows I already have in the sheet?

No. It only appends new rows below what's already there, so your historical log stays intact.

Which conversations get logged?

Any Front conversation that was archived or closed during the day the workflow runs. Open conversations are ignored until they close.

Can I build reports on top of the sheet?

Yes. Because every row uses the same fixed columns, you can drop pivot tables, charts, or a Looker Studio dashboard on top of the log.

What happens if I need to rerun it for the same day?

The workflow uses a fixed daily window, so rerunning it will append the same rows again. Clear the day's rows first, or add a dedupe step by conversation ID.

Does this replace Front's built-in Analytics?

It complements it. Front Analytics emails CSVs to individuals; this gives you a shared, always-current spreadsheet your whole team can query and chart from.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.