Build a rolling 12-month KPI dashboard

Northwind’s Dashboard tab is meant to show the last twelve months of revenue at a glance. The trouble with a hand-built dashboard is the window: the month columns are fixed, so as the year rolls on the chart drifts out of date until someone remembers to shuffle everything along. A dashboard that needs maintenance is a dashboard people stop trusting.

This script removes the maintenance. Every night it works out the trailing twelve months from today, totals invoice revenue into each one, and writes the labels and figures into named ranges the dashboard charts read from. The window slides itself, so the dashboard is always current without anyone touching it.

What you’ll need

• An Invoices sheet with a header row and at least these columns: date, amount, and client.
• A Dashboard tab (in its own spreadsheet here) containing two named ranges: MonthLabels, a single row of twelve cells for the month names, and Revenue, a single row of twelve cells for the totals.
• Whatever charts you want, pointed at those two named ranges so they redraw automatically when the values change.

The script

// The spreadsheet holding the raw invoice rows.
const INVOICES_SHEET_ID = '1abcInvoicesSheetId';

// The spreadsheet holding the Dashboard tab and its named ranges.
const DASHBOARD_SHEET_ID = '1abcDashboardSheetId';

// How many months the rolling window covers.
const WINDOW_MONTHS = 12;

/**
 * Rebuilds the rolling 12-month revenue rollup: works out the trailing
 * months, totals invoices into each, and writes labels and figures into
 * the Dashboard's named ranges.
 */
function rebuildRolling12() {
  // 1. Load every invoice as a plain object keyed by column name.
  const invoices = readSheet(INVOICES_SHEET_ID);
  if (!invoices.length) {
    Logger.log('No invoices found — nothing to roll up.');
    return;
  }

  // 2. Build the list of trailing months and their display labels.
  const months = lastNMonths(WINDOW_MONTHS);
  const labels = months.map((m) => Utilities.formatDate(m, 'GMT', 'MMM yy'));

  // 3. Total each month's revenue: invoices on or after the month start
  //    and before the next month start.
  const totals = months.map((monthStart) => {
    const nextMonth = new Date(monthStart);
    nextMonth.setMonth(nextMonth.getMonth() + 1);
    return invoices
      .filter((inv) => inv.date >= monthStart && inv.date < nextMonth)
      .reduce((sum, inv) => sum + inv.amount, 0);
  });

  // 4. Write the labels and totals into the dashboard's named ranges.
  const dash = SpreadsheetApp.openById(DASHBOARD_SHEET_ID);
  dash.getRangeByName('MonthLabels').setValues([labels]);
  dash.getRangeByName('Revenue').setValues([totals]);
  Logger.log(`Rebuilt ${WINDOW_MONTHS}-month rollup ending ${labels.at(-1)}.`);
}

/**
 * Returns an array of Date objects, one per month, each set to the 1st,
 * running from (n-1) months ago up to the current month.
 */
function lastNMonths(n) {
  const out = [];
  const cursor = new Date();
  cursor.setDate(1);
  for (let i = n - 1; i >= 0; i--) {
    const month = new Date(cursor);
    month.setMonth(month.getMonth() - i);
    out.push(month);
  }
  return out;
}

/**
 * Reads a sheet and returns its rows as objects keyed by the header row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((row) =>
    Object.fromEntries(header.map((key, i) => [key, row[i]]))
  );
}

How it works

1. rebuildRolling12 calls readSheet to load every invoice as an object, so the rest of the code uses inv.date and inv.amount rather than column numbers, then stops early if the sheet is empty.
2. lastNMonths builds twelve Date objects, each pinned to the 1st of its month, running from eleven months ago up to the current month.
3. Those dates are formatted into short labels like Jul 25 for the MonthLabels range.
4. For each month it computes the next month’s start and sums every invoice whose date falls in that half-open window — on or after the month start, strictly before the next.
5. It opens the dashboard spreadsheet and writes the twelve labels and twelve totals into the MonthLabels and Revenue named ranges in one call each.
6. Because the charts point at those named ranges, they redraw with the fresh numbers the moment the values change.

Example run

On a 2025-08-06 run, lastNMonths(12) produces a window from September 2024 to August 2025. After totalling the Invoices sheet, the named ranges hold:

Range	Values
MonthLabels	Sep 24, Oct 24, Nov 24, … Jul 25, Aug 25
Revenue	18400, 21250, 19900, … 24100, 9300

When the script runs again on 1 September, the window shifts one month: Sep 24 drops off the front, Sep 25 appears on the end, and every chart bound to the ranges follows along — no column shuffling required.

Trigger it

Run this nightly so the dashboard is fresh each morning:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for rebuildRolling12, time-driven, Day timer, set to the 1am–2am slot.
3. Save. Each night the window slides and the totals refresh.

Watch out for

• The current month is partial. The last column always counts an in-progress month, so its figure climbs through the month and is not comparable to the eleven complete months beside it.
• The date column must hold real date values, not text. A date typed as a string will fail the >= and < comparisons and that invoice will be silently excluded from every total.
• The named ranges must be exactly twelve cells wide. If MonthLabels or Revenue is sized differently, setValues will throw a range-mismatch error.
• It totals every invoice regardless of status. Draft or cancelled invoices count too unless you filter them out before the reduce.
• All months are bucketed in GMT. If Northwind’s invoices straddle a very different timezone, an invoice dated late on a month boundary could land in the neighbouring bucket.