Weekly ServiceNow ITSM executive report to Sheets and Gmail

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.