Weekly ServiceNow ITSM Executive Report (Sheets + Gmail)

PromptCreate

Build an agent workflow that runs on a cron trigger every Monday at 8am in the team's local timezone. The job is to produce a weekly ITSM executive report covering the prior calendar week (Monday through Sunday) from ServiceNow, and deliver it to two places: a Google Sheet that accumulates one row per week for trending, and a Gmail message to a configurable leadership distribution list.

Data collection. Use the ServiceNow integration to pull the prior week's activity. Call List Incidents for incidents closed in the prior week (closed_at between last Monday 00:00 and last Sunday 23:59 in the configured timezone). Call List Change Requests for changes whose actual end date falls in the prior week, capturing both successful and failed/backed-out outcomes. Call List Service Requests for service requests fulfilled (closed_complete) in the prior week. Then call Aggregate Query against the incident table to compute: mean time to resolve grouped by priority, count of SLA breaches in the window, total ticket volume grouped by category, and count of incidents grouped by priority. Call Aggregate Query against the change_request table to compute change success rate (successful closures divided by total completed changes) and a count of failed changes. Pull enough detail on failed changes and SLA-breaching incidents to reference specific ticket numbers in the narrative.

Narrative synthesis. The agent then writes a short executive read with these sections: (1) top three issue themes from the closed incidents, each with one or two example incident numbers; (2) MTTR week-over-week movement by priority (compare against the prior row in the Google Sheet if available, otherwise just report this week's values); (3) any SLA breaches that need attention, named explicitly with incident numbers; (4) change success rate and any failed changes worth a post-mortem, with change request numbers; (5) a one-line recommendation for the week (where to focus, what to celebrate, what to escalate).

Sheet delivery. Use Google Sheets Append Values to add a single new row to a configured spreadsheet and tab. Columns: week_start_date, week_end_date, total_incidents_closed, mttr_p1_hours, mttr_p2_hours, mttr_p3_hours, mttr_p4_hours, sla_breach_count, total_changes_completed, change_success_rate_pct, failed_change_count, total_service_requests_fulfilled, top_category. One row per run so trends accumulate.

Email delivery. Use Gmail Send a Message to email a configurable leadership distribution list. Subject like "ITSM Weekly Report: <week_start_date> to <week_end_date>". Body has the narrative at the top, then a clean HTML metric table mirroring the columns appended to the sheet, then a footer linking to the Google Sheet for historical trending.

Configuration inputs the user should set when they install: timezone for the weekly window, target Google Sheet ID and tab name, leadership distribution list email address(es), and optional incident category filter (default: all categories). If the prior week had zero incidents or zero changes, still send the email and still append the row so the trend line stays continuous.

What does this prompt do?

Pulls last week's incidents, change requests, and service requests from ServiceNow every Monday morning.
Calculates the metrics leadership actually asks about: average time to resolve by priority, SLA breaches, change success rate, and ticket volume by category.
Writes a short executive read covering the top issue themes, week over week movement, anything that needs attention, and a recommendation.
Appends one new row per week to a Google Sheet so trends accumulate, and emails the narrative plus a metric table to your leadership distribution list.

What do I need to use this?

A ServiceNow instance with permission to read incidents, change requests, and service requests.
A Google account connected to the Google Sheet you want to track weekly metrics in.
A Gmail account that can send to your leadership distribution list.

How can I customize it?

Change the schedule. Many teams prefer Friday afternoon or the first business day of the month instead of Monday at 8am.
Swap the recipient list, the sender mailbox, or the target Google Sheet without touching the rest of the workflow.
Adjust what counts as a theme or what gets flagged for attention, for example only call out SLA breaches above a certain count or only flag failed changes with a specific risk level.

FAQs

Does this work with any ServiceNow instance?

Yes, as long as the account you connect can read the incident, change request, and service request tables. Most ITSM-licensed instances ship with this access by default.

What does the leadership email actually look like?

A short written summary at the top with the top issue themes and anything that needs attention, followed by a clean metric table covering ticket volume, average resolution time by priority, SLA breaches, and change success rate.

Why send to both a Google Sheet and Gmail?

The sheet is the system of record for trending. One row per week means you can chart resolution time and ticket volume over months without rebuilding anything. The email gives leadership a written read they can forward or paste into a deck.

Can I change the time window from weekly to monthly?

Yes. Change the schedule to monthly and the workflow will pull the prior month's activity instead. The sheet then accumulates one row per month.

What if a metric is zero, like no SLA breaches that week?

The report still goes out. Zero breaches and a high change success rate are good news worth surfacing to leadership.

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.