The Google Sheets Quota Tracker That Replaces Your Spreadsheet Mess

Every RevOps team has been there: quota tracking lives in a tangle of spreadsheets with broken formulas, outdated rep lists, and five different versions floating around in email threads. This structured Google Sheets setup replaces that chaos with a single source of truth that updates itself.

Sheet Structure¶

The tracker uses four tabs that work together:

The separation matters. When quotas change, you update one tab. When a rep moves teams, you update another. The dashboard tab calculates everything from the source tabs.

Setting Up the Dashboard Formulas¶

The Dashboard tab uses structured references to pull data from the other tabs. For each rep, the attainment calculation looks like this:

=IFERROR(
  SUMIFS(Actuals!D:D, Actuals!A:A, A2, Actuals!B:B, $B$1) /
  VLOOKUP(A2, Quotas!A:D, 3, FALSE),
  0
)

For ramp-adjusted quotas, multiply the base quota by the ramp percentage:

=VLOOKUP(A2, Quotas!A:D, 3, FALSE) * VLOOKUP(A2, Quotas!A:D, 4, FALSE)

This ensures new hires on a 50% ramp show accurate attainment against their adjusted target, not the full quota.

Automating the Summary With Apps Script¶

Manual updates defeat the purpose. This Apps Script function recalculates the dashboard and highlights reps who are off track:

function updateDashboard() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName("Dashboard");
  const quotas = ss.getSheetByName("Quotas");
  const actuals = ss.getSheetByName("Actuals");

  const currentQuarter = getCurrentQuarter();
  const repData = getRepSummary(quotas, actuals, currentQuarter);

  // Clear and rebuild dashboard
  dashboard.getRange("A2:F100").clearContent();

  repData.forEach((rep, i) => {
    const row = i + 2;
    const attainment = rep.actual / rep.adjustedQuota;
    dashboard.getRange(row, 1, 1, 5).setValues([[
      rep.name, rep.adjustedQuota, rep.actual,
      attainment, rep.adjustedQuota - rep.actual
    ]]);

    // Red highlight if below 60% attainment at mid-quarter
    if (attainment < 0.6 && isPastMidQuarter()) {
      dashboard.getRange(row, 1, 1, 5)
        .setBackground("#f4cccc");
    }
  });
}

function getCurrentQuarter() {
  const now = new Date();
  const q = Math.ceil((now.getMonth() + 1) / 3);
  return `${now.getFullYear()}-Q${q}`;
}

Adding a Weekly Email Digest¶

Set a time-driven trigger to email leadership a snapshot every Monday:

function sendWeeklyDigest() {
  updateDashboard();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dashboard = ss.getSheetByName("Dashboard");
  const data = dashboard.getDataRange().getValues();

  let html = "<h2>Quota Attainment Summary</h2><table border='1'>";
  html += "<tr><th>Rep</th><th>Quota</th><th>Actual</th><th>Attainment</th></tr>";
  data.slice(1).forEach(row => {
    const pct = (row[3] * 100).toFixed(1);
    html += `<tr><td>${row[0]}</td><td>$${row[1].toLocaleString()}</td>`;
    html += `<td>$${row[2].toLocaleString()}</td><td>${pct}%</td></tr>`;
  });
  html += "</table>";

  MailApp.sendEmail({
    to: "[email protected]",
    subject: `Weekly Quota Snapshot - ${getCurrentQuarter()}`,
    htmlBody: html,
  });
}

Install the trigger from Apps Script by navigating to Triggers > Add Trigger and setting sendWeeklyDigest to run every Monday at 8 AM.

Key Takeaways¶

• A structured four-tab Google Sheet with clear data separation eliminates the version-control chaos of ad hoc quota spreadsheets
• Apps Script automates dashboard recalculation and conditional formatting so you never publish stale numbers
• Ramp-adjusted quotas should be calculated automatically to avoid inflating or deflating attainment figures for new hires
• A weekly email digest keeps leadership informed without requiring them to open the spreadsheet