Portfolio Project

Interactive Zendesk + CSAT Dashboard inside Google Sheets

One Google Apps Script that pulls tickets and CSAT survey responses from Zendesk, filters by date range and tag, aggregates the metrics, and renders charts directly into a spreadsheet — giving any support lead a live analytics view with zero infrastructure.

JavaScript Google Apps Script Zendesk REST API Google Sheets Charts Service
Zendesk API
⚙️
Apps Script
Google Sheets

Live Dashboard

Pick a filter mode, then click refresh to simulate the pipeline

Range: last 7 days • Tag filter: Prefix = coke_utc_* • Last updated: —
Total Tickets 📊
—
Matched filter in range
Open 🔴
—
Awaiting agent action
Solved 🟢
—
Resolved in range
Avg CSAT Rating ⭐
—
0 responses

🏷️ Top Matched Tags

Waiting...
📋

Click Refresh All Data to compute the tag breakdown

📈 Status Breakdown

Waiting...
📊

Status distribution will render here

⭐ CSAT Rating Category

No responses
💬

Good / bad / neutral breakdown from survey responses

💬 Recent CSAT Comments

0 comments
📝

Latest customer comments will appear here

📄 Raw Data — Ticket Details (Mock)

0 tickets
ID Subject Status Priority Matched Tag All Tags Updated

📖 Metrics Glossary

What each number and chart on the dashboard above actually means

📊

Total Tickets

Raw Data sheet
FormulaCOUNT(ticket) after filter
Read as

Volume of tickets inside the date range and tag filter. A sudden spike usually signals a trending incident or campaign fallout.

🏷️

Matched Tag

Top Matched Tags panel
FormulacountBy(rows, 'tag')
Read as

Issue category trend. If Billing dominates, payment flow is probably broken. The label strips the filter prefix so it stays readable.

📈

Status Breakdown

Status panel
FormulacountBy(rows, 'status')
Read as

Workflow distribution. High open = backlog building. High solved = productive day. pending = waiting on customer reply.

🎯

Tag Filter Mode

Set Tag Filter menu
OptionsALL · EXACT · PREFIX
Read as

ALL = no filter. EXACT = match one tag literally. PREFIX = match a tag family (coke_utc_*) — useful for multi-tenant tagging.

⭐

Avg CSAT Rating

CSAT Data sheet
FormulaΣrating / count
Read as

Mean of the 1–5 rating scale. Industry benchmark for support is ≥ 4.0. Drop > 0.3 week-over-week deserves a retro.

💬

Rating Category

CSAT Rating Category panel
FormulacountBy(csat, 'ratingCategory')
Read as

Zendesk classifies each rating as good, bad, or neutral. Ratio of good vs bad = proxy for CSAT%.

📝

CSAT Comments

Recent CSAT Comments panel
Sourceanswers[].type === 'open_ended'
Read as

Qualitative signal. Numbers tell you what happened; comments tell you why. Read bad ones first to find fixable patterns.

🗓️

Date Range

Set Date Range menu
Scopecreated_at ∈ [from, to]
Read as

All metrics respect this window. Blank = last 7 days. Tickets are matched by created_at, CSAT by survey submitted_at.

⚡ How It Works

Three core pieces from the Google Apps Script

1. Incremental Ticket Export (with pagination)
function fetchTicketsFromIncrementalExport(range) {
  let url = buildZendeskUrl(
    '/api/v2/incremental/tickets.json',
    { start_time: range.fromUnixSeconds }
  );
  while (hasMore) {
    const data = zendeskGet(url);
    data.tickets.forEach(t =>
      ticketMap[t.id] = t);
    hasMore = data.next_page
      && !data.end_of_stream
      && data.end_time < range.toUnixSeconds;
    url = data.next_page;
    Utilities.sleep(650);
  }
  return Object.values(ticketMap);
}
2. Tag filter — ALL / EXACT / PREFIX
function getMatchedTagInfo(tags, tagFilter) {
  if (tagFilter.mode === 'all')
    return { tag: '', value: '' };

  for (const value of tagFilter.values) {
    for (const tag of tags) {
      const matched = tagFilter.mode === 'exact'
        ? tag === value
        : tag.indexOf(value) === 0;
      if (matched) return { tag, value };
    }
  }
  return { tag: '', value: '' };
}
3. Link CSAT response → ticket
function extractTicketId(response) {
  // e.g. "zen:ticket:12345"
  for (const zrn of response.subject_zrns || []) {
    const m = String(zrn)
      .match(/^zen:ticket:(\d+)$/);
    if (m) return m[1];
  }
  return '';
}

// Then fetch in batches of 100
// via /api/v2/tickets/show_many?ids=...

🧩 Extending the Dashboard

The refresh pipeline follows a repeatable 3-step pattern. Pick a category below to see ideas and a concrete code recipe.

1

Extract

Add the field inside buildRawTicketRows or buildCsatRows.

→
2

Aggregate

Write a reducer function. Reuse countBy() when possible.

→
3

Render

Push a new block into writeTicketMetrics or writeCsatMetrics.

End-to-end example

Adding a CSAT Score (%) metric

Industry standard: good / (good + bad) × 100. More meaningful than a raw average because it ignores neutral responses.

Step 1 — Extract
Nothing to do. ratingCategory is already built by buildCsatRows().
Step 2 — Aggregate
function calculateCsatScore(csatRows) {
  const good = csatRows.filter(r => r.ratingCategory === 'good').length;
  const bad  = csatRows.filter(r => r.ratingCategory === 'bad').length;
  if (good + bad === 0) return '';
  return Number(((good / (good + bad)) * 100).toFixed(1));
}
Step 3 — Render
// inside writeCsatMetrics()
const metrics = [
  ['Metric', 'Value'],
  ['Total CSAT Responses', csatRows.length],
  ['Average Rating', averageRating],
  ['CSAT Score (%)', calculateCsatScore(csatRows)],  // ← new row
];

✨ Key Features

🎯

3 Tag Filter Modes

ALL (no filter), EXACT (match one tag), or PREFIX (match a whole tag family like coke_utc_*).

📦

Incremental Export

Cursor-based pagination through /incremental/tickets with rate-limit + retry-after handling built in.

⭐

CSAT + Ticket Join

Survey responses are mapped back to their source ticket via subject_zrns, then re-filtered by tag.

📈

Auto-Rendered Charts

Pie chart for tag + CSAT category, column chart for status — all drawn directly on the Dashboard sheet.