← All posts · 2026-08-27 · Sophia Excel & Automation

Google Sheets KPI Auto-Rollup: Nightly Trigger History (Full Code)

Intro: So you don’t have to recalculate KPIs every day

When you look up ways to automate KPI aggregation in Google Sheets, most of what you’ll find focuses on how to compute “today-based” numbers. In real-world operations, though, what matters more than today’s numbers is the trend compared with yesterday, last week, and last month. Manually changing the date and recalculating takes effort, and on busy days it’s easy to miss updates.

In this post, we’ll build on the daily KPI calculation logic from the previous article, Google Sheets Appointment KPI Aggregation: On-time Rate & Dwell Time Automation, and organize a structure where an Apps Script nightly trigger automatically saves yesterday’s KPIs as historical records each day.

We’ll connect everything at once: automatic KPI history storage in Google Sheets, Apps Script nightly trigger setup, daily KPI accumulation in Sheets, recent N-day history lookup, and automatic cleanup of old history.

Once this structure is in place, operators can see yesterday’s metrics and last week’s trend directly on the Google Sheets logistics KPI dashboard every morning, without any manual work.

Why store daily KPI history separately?

If you manage appointments, check-ins, and dock work as one flow, you can get a decent picture of today’s situation with just a few sheets. But KPIs are not just about “how many trucks came in today”; they’re data for reading patterns over time. For example, questions like these are hard to answer clearly with a single-date dashboard:

Has the on-time arrival rate been steadily improving over the past 4 weeks?
Is the delay ratio for a particular carrier consistently high?
Is there a tendency for average dwell time to be longer on certain days or time slots?

Here’s a real problem I ran into in warehouse operations: the dashboard always shows “today-based” views nicely, but when I try to revisit KPIs from a few weeks ago by rolling the date back, I get values that already reflect any edits made to the appointment data since then. In other words, it’s not the snapshot as of that time, but numbers recalculated on modified data later on.

That’s why, for KPI history, it’s safer to design around these principles:

Assume that appointment/check-in data can keep changing throughout the day,
But once the day is fully over (say, 1 a.m.), compute the KPIs once “as of yesterday” and store them as fixed records on a separate sheet.

Doing this:

Leaves daily KPIs as “snapshots as of then,” unaffected by later data edits, and
Makes monthly/weekly trend analysis much simpler, because you only need to read a dedicated KPI history sheet.

Assumption: This post assumes you already created the KPI_DAILY sheet and computeKpiForDate_(dateObj), saveDailyKpi_(kpi) functions in Google Sheets Appointment KPI Aggregation: On-time Rate & Dwell Time Automation.

Here, we reuse that code as-is and add one more “history management layer” on top. We do not re-declare the previous functions/constants in this article.

What we’ll add in this part

To avoid code duplication and name collisions, we’ll follow these rules:

We do not re-declare the KPI_CONFIG constant defined in the previous part.
Any new configuration we need here will be grouped in new constants with a different prefix such as KPI_HISTORY_....
The history sheet will not touch the sheet names used in the previous part (e.g. APPT_MAIN, KPI_DAILY); instead, it’s handled exclusively via constants dedicated to this part.

New components introduced in this article:

KPI_HISTORY_CONFIG — configuration bundle for KPI history
KPI_getHistorySheet_() — creates/returns the history sheet and enforces headers
KPI_saveHistoryForDate_(ymd) — saves/overwrites KPI history for a given date
nightlyRollup() — computes “yesterday” based on New York time, calculates KPIs, writes to both KPI_HISTORY and KPI_DAILY, and logs the result
getKpiHistory(days) — fetches recent N days of KPI history
KPI_cleanupOldHistory_() — cleans up old KPI history
Test functions for each piece, including simple expectation checks
A guide to setting up the Apps Script time-based trigger

From here on, you’ll see the entire final code along with explanations.

The code is not truncated or omitted anywhere.

Define KPI history constants (using names unique to this part)

First, we’ll collect all settings for KPI history in a single constant.

Since we already used KPI_CONFIG earlier, we’ll use a different name, KPI_HISTORY_CONFIG, to avoid collisions.

Apps Script (JavaScript)

// Config constants exclusively for history in this part
const KPI_HISTORY_CONFIG = {                        // → KPI history config bundle
  TZ: 'America/New_York',                           // → Fixed timezone (same as series)
  SHEET_NAME: 'KPI_HISTORY',                        // → History sheet name
  HEADERS: [                                        // → Header list
    'Date',                                         // → YYYY-MM-DD
    'Total Appointments',                           // → Example field
    'On-time Arrivals',                             // → Example field
    'On-time Rate',                                 // → Example field
    'Avg Dwell Time (min)',                         // → Example field
    'Late Appointments',                            // → Example field
    'Late Rate'                                     // → Example field
  ],
  MAX_DAYS: 365                                     // → Retention period (example: 1 year)
};

If you already have KPI_CONFIG from the previous code, leave it as-is and just add this constant.

Do not delete or rename existing constants.

Creating the KPI_HISTORY sheet and ensuring headers

Now we’ll write a helper function that creates the KPI_HISTORY sheet and keeps its header row correct.

We reuse getOrCreateSheet_(ss, name) from the “Appointment Basics Part 1” article and add a thin wrapper for history in this part.

getOrCreateSheet_() was created earlier in Appointment Basics Part 1.

In this article we just call that function directly.

Apps Script (JavaScript)

function KPI_getHistorySheet_() {                             // → Return KPI history sheet
  const ss = SpreadsheetApp.getActive();                      // → Current spreadsheet
  const sheet = getOrCreateSheet_(ss, KPI_HISTORY_CONFIG.SHEET_NAME); // → Find or create sheet

  const headerRange = sheet.getRange(
    1, 1, 1, KPI_HISTORY_CONFIG.HEADERS.length                // → Header row range
  );
  const currentValues = headerRange.getValues()[0];           // → Read existing headers

  const needUpdate = currentValues.some(
    (v, i) => v !== KPI_HISTORY_CONFIG.HEADERS[i]             // → Compare to desired headers
  );

  if (needUpdate) {                                           // → If headers differ
    headerRange.setValues([KPI_HISTORY_CONFIG.HEADERS]);      // → Write headers fresh
  }
  return sheet;                                               // → Return sheet
}

function KPI_testEnsureHistorySheet_() {                      // → Test creating history sheet
  const sheet = KPI_getHistorySheet_();                       // → Prepare sheet
  Logger.log('KPI_HISTORY sheet name: ' + sheet.getName());   // → Check name in logs
}

How to check it works

In the Script Editor, run KPI_testEnsureHistorySheet_.
Verify that a KPI_HISTORY sheet has been created in your spreadsheet and that row 1 has the correct headers.

Date string validation utility: blocking impossible dates

Many core functions in this article rely heavily on date strings in YYYY-MM-DD format.

To block values that only look like dates but don’t actually exist (e.g. 2024-02-30), we’ll extract strict validation into a shared utility that uses regex + Date re-checking.

Apps Script (JavaScript)

/**
 * Strictly validate 'YYYY-MM-DD' and return a Date object
 * - Format check: regex
 * - Existence check: convert to Date, then re-compare year/month/day
 */
function KPI_parseYmdStrict_(ymd) {
  if (!ymd || typeof ymd !== 'string') {
    throw new Error('A valid date string (YYYY-MM-DD) is required: ' + ymd);
  }

  const parts = ymd.match(/^(\d{4})-(\d{2})-(\d{2})$/);       // → Format check
  if (!parts) {
    throw new Error('Must be in YYYY-MM-DD format: ' + ymd);
  }

  const [, y, m, d] = parts;                                 // → Destructure capture groups
  // Never hand the string to new Date(). 'T00:00:00' is read as **local** time, and
  // reading it back with getUTC* shifts the day depending on the time zone.
  const dateObj = new Date(Number(y), Number(m) - 1, Number(d));  // → local calendar midnight

  if (!Number.isFinite(dateObj.getTime())) {
    throw new Error('Invalid date value: ' + ymd);
  }

  const fy = dateObj.getFullYear();
  const fm = dateObj.getMonth() + 1;                       // JS months: 0–11 → +1
  const fd = dateObj.getDate();

  if (fy !== parseInt(y, 10) ||
      fm !== parseInt(m, 10) ||
      fd !== parseInt(d, 10)) {
    throw new Error('Nonexistent date: ' + ymd);              // → Block Feb 30, etc.
  }

  return dateObj;
}

From now on, when functions receive a date string, they’ll use this utility to validate both format and existence.

Saving KPI history by date: overwrite if the date already exists

Now for the core function: saving daily KPI history.

We reuse the previous function computeKpiForDate_(dateObj) as-is.

This function:

Takes a YYYY-MM-DD string and strictly validates that the date exists,
Computes KPIs for that date,
Searches KPI_HISTORY for that date; if found, overwrites that row; if not, appends a new row.

In other words, it’s designed so that each date appears at most once.

Apps Script (JavaScript)

function KPI_saveHistoryForDate_(ymd) {                       // → Save history for a given date
  // Strictly validate date format and existence
  const dateObj = KPI_parseYmdStrict_(ymd);                   // → Returns Date if valid

  const lock = LockService.getScriptLock();                   // → Script-level lock
  lock.waitLock(30000);                                       // → Wait up to 30 seconds

  try {
    const kpi = computeKpiForDate_(dateObj);                  // → Compute KPI using previous function
    if (!kpi || typeof kpi !== 'object') {
      throw new Error('KPI calculation result is invalid');
    }

    // Validate required KPI keys and that values are numbers (adapt to previous structure)
    const requiredKeys = [
      'totalAppts', 'onTimeAppts', 'onTimeRate',
      'avgDwTimeMinutes', 'lateAppts', 'lateRate'
    ];
    requiredKeys.forEach(key => {
      if (!(key in kpi)) {
        throw new Error('Missing KPI field: ' + key);
      }
      const val = kpi[key];
      if (!Number.isFinite(val)) {
        throw new Error('KPI value is not numeric: ' + key + ' = ' + val);
      }
    });

    const sheet = KPI_getHistorySheet_();                     // → Get history sheet
    const lastRow = sheet.getLastRow();
    let targetRow = 0;                                        // → Row to write to

    if (lastRow > 1) {                                        // → If there is data
      const range = sheet.getRange(2, 1, lastRow - 1, 1);     // → Date column from row 2
      const values = range.getValues();

      for (let i = 0; i < values.length; i++) {
        const cellVal = values[i][0];
        if (!cellVal) continue;

        const cellYmd = (cellVal instanceof Date)
          ? Utilities.formatDate(cellVal, KPI_HISTORY_CONFIG.TZ, 'yyyy-MM-dd')
          : String(cellVal);

        if (cellYmd === ymd) {                                // → Found same date
          targetRow = i + 2;                                  // → Actual row number (header offset)
          break;
        }
      }
    }

    if (!targetRow) {                                         // → If the date doesn’t exist yet
      targetRow = lastRow >= 1 ? lastRow + 1 : 2;             // → New row number
    }

    const rowValues = [
      ymd,                                                    // Date
      kpi.totalAppts,                                         // Total Appointments
      kpi.onTimeAppts,                                        // On-time Arrivals
      kpi.onTimeRate,                                         // On-time Rate
      kpi.avgDwTimeMinutes,                                   // Avg Dwell Time (min)
      kpi.lateAppts,                                          // Late Appointments
      kpi.lateRate                                            // Late Rate
    ];

    const targetRange = sheet.getRange(
      targetRow,
      1,
      1,
      rowValues.length
    );
    targetRange.setValues([rowValues]);                       // → Save or overwrite history

    Logger.log(
      '[KPI_saveHistoryForDate_] Saved - date: %s, row: %s',
      ymd,
      targetRow
    );

    return {
      ok: true,
      row: targetRow,
      ymd
    };
  } finally {
    lock.releaseLock();                                       // → Release lock
  }
}

Test function for KPI_saveHistoryForDate_ (with expectation checks)

We’ll add a test that verifies row count doesn’t grow unexpectedly.

Apps Script (JavaScript)

function KPI_testSaveHistoryForDate_() {                      // → Test saving history
  const tz = KPI_HISTORY_CONFIG.TZ;
  const now = new Date();

  const y = Utilities.formatDate(now, tz, 'yyyy');
  const m = Utilities.formatDate(now, tz, 'MM');
  const d = Utilities.formatDate(now, tz, 'dd');
  const ymd = [y, m, d].join('-');                            // → Today’s date string

  const sheet = KPI_getHistorySheet_();

  // Count current data rows (from row 2, col 1, ignoring blanks)
  const allValuesBefore = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countBefore = allValuesBefore.filter(v => v[0] !== '').length;

  const result1 = KPI_saveHistoryForDate_(ymd);               // → First save
  const result2 = KPI_saveHistoryForDate_(ymd);               // → Second save (should overwrite)

  // Two saves should point to the same row
  if (result1.row !== result2.row) {
    throw new Error('Second save did not overwrite the first row (row mismatch)');
  }

  // Check row count after
  const allValuesAfter = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countAfter = allValuesAfter.filter(v => v[0] !== '').length;

  // Row count must not grow except on first-ever insertion for today
  if (countAfter !== countBefore && countBefore !== 0 && countAfter !== countBefore + 1) {
    // Allow +1 only when today’s date is newly inserted on the very first test run
    throw new Error('Row count changed abnormally: before='
      + countBefore + ', after=' + countAfter);
  }

  Logger.log('KPI_testSaveHistoryForDate_ OK - ymd=%s, row=%s', ymd, result1.row);
}

Explanation:

On the first run, if today’s date has never been saved, we allow row count to increase by 1.
If a row for today already exists and you rerun the test, row count should stay the same.
For the same date, result1.row === result2.row confirms that the second run overwrote the first.

nightlyRollup: New York–based “yesterday” auto-aggregation + updating KPI_HISTORY & KPI_DAILY together

Now for the heart of this part: nightlyRollup(). This function:

Creates a Date representing “today” at midnight in America/New_York time,
Steps back one calendar day to get “yesterday” (subtracting 24 hours in milliseconds skips a day right after a DST switch),
Uses computeKpiForDate_(dateObj) to compute KPIs as of yesterday,
Writes the same KPI data to

KPI_HISTORY via KPI_saveHistoryForDate_(ymd) and
KPI_DAILY via saveDailyKpi_(kpi),

Logs the execution result.

Apps Script (JavaScript)

function nightlyRollup() {                                   // → Nightly KPI rollup
  const tz = KPI_HISTORY_CONFIG.TZ || 'America/New_York';

  const now = new Date();                                    // → Current time

  // Build today's date string (New York time)
  const todayY = Utilities.formatDate(now, tz, 'yyyy');
  const todayM = Utilities.formatDate(now, tz, 'MM');
  const todayD = Utilities.formatDate(now, tz, 'dd');
  const todayStr = [todayY, todayM, todayD].join('-');

  // Get yesterday **from the calendar, not from milliseconds.** Subtracting 24 hours
  // loses a whole day right after the DST switch — run it on 2026-03-09 and 'yesterday'
  // comes back as March 7, not March 8, so March 8's KPI is never rolled up at all.
  // `setDate()` moves along the calendar, so a 23- or 25-hour day makes no difference.
  const yesterdayDate = new Date(
    Number(todayY), Number(todayM) - 1, Number(todayD)       // → today's local midnight
  );
  yesterdayDate.setDate(yesterdayDate.getDate() - 1);        // → the day before

  const yY = Utilities.formatDate(yesterdayDate, tz, 'yyyy');
  const yM = Utilities.formatDate(yesterdayDate, tz, 'MM');
  const yD = Utilities.formatDate(yesterdayDate, tz, 'dd');
  const ymd = [yY, yM, yD].join('-');                         // → Yesterday as YYYY-MM-DD

  // 1) Save to history sheet (includes strict date validation)
  const historyResult = KPI_saveHistoryForDate_(ymd);

  // 2) Save using the same basis to KPI_DAILY
  const dateObj = KPI_parseYmdStrict_(ymd);                   // → Reconstruct Date for yesterday
  const kpi = computeKpiForDate_(dateObj);                    // → Compute KPIs
  saveDailyKpi_(kpi);                                         // → Save to KPI_DAILY

  const result = {
    ok: true,
    ymd,
    historyRow: historyResult.row
  };

  Logger.log('[nightlyRollup] Done - %s (row=%s)', ymd, historyResult.row);

  return result;
}

Test function for nightlyRollup (with expectation checks)

Similarly, we’ll verify that running nightlyRollup twice for the same day does not grow the history row count.

Apps Script (JavaScript)

function KPI_testNightlyRollup_() {                           // → Test nightlyRollup
  const sheet = KPI_getHistorySheet_();

  // Current history row count
  const allValuesBefore = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countBefore = allValuesBefore.filter(v => v[0] !== '').length;

  const result1 = nightlyRollup();                            // → First run
  const result2 = nightlyRollup();                            // → Second run (should overwrite)

  if (result1.ymd !== result2.ymd) {
    throw new Error('The base date differs between two consecutive runs');
  }

  if (result1.historyRow !== result2.historyRow) {
    throw new Error('Second nightlyRollup did not overwrite the first row');
  }

  // Row count after both runs
  const allValuesAfter = sheet.getLastRow() > 1
    ? sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues()
    : [];
  const countAfter = allValuesAfter.filter(v => v[0] !== '').length;

  // Except for the very first insertion of yesterday’s row, row count should not grow
  if (countAfter !== countBefore && countBefore !== 0 && countAfter !== countBefore + 1) {
    throw new Error('Row count changed abnormally after nightlyRollup: before='
      + countBefore + ', after=' + countAfter);
  }

  Logger.log('KPI_testNightlyRollup_ OK - %s, row=%s',
    result1.ymd,
    result1.historyRow
  );
}

Building a function to fetch recent N days of KPI history

To show the last 7 or 30 days of trends on your dashboard, you’ll need a function to read the most recent N days from KPI_HISTORY.

To be resilient to any accidental sorting on the sheet, we’ll perform date-based sorting in code.

Apps Script (JavaScript)

function getKpiHistory(days) {                               // → Fetch recent N days of history
  const n = Number(days);
  if (!Number.isFinite(n) || n <= 0) {
    throw new Error('Number of days to fetch must be a positive number: ' + days);
  }

  const sheet = KPI_getHistorySheet_();
  const lastRow = sheet.getLastRow();
  if (lastRow <= 1) {
    return [];                                               // → No data
  }

  const dataRange = sheet.getRange(
    2,
    1,
    lastRow - 1,
    KPI_HISTORY_CONFIG.HEADERS.length
  );
  const values = dataRange.getValues();
  const tz = KPI_HISTORY_CONFIG.TZ;
  const rows = [];

  for (let i = 0; i < values.length; i++) {
    const row = values[i];
    const cellVal = row[0];                                  // → Date value
    if (!cellVal) continue;

    let ymd;
    if (cellVal instanceof Date) {
      ymd = Utilities.formatDate(cellVal, tz, 'yyyy-MM-dd');
    } else {
      ymd = String(cellVal);
    }

    // Validate date format/existence; skip invalid ones
    let d;
    try {
      d = KPI_parseYmdStrict_(ymd);
    } catch (e) {
      continue;
    }

    rows.push({
      ymd,
      date: d,
      totalAppts: row[1],
      onTimeAppts: row[2],
      onTimeRate: row[3],
      avgDwTimeMinutes: row[4],
      lateAppts: row[5],
      lateRate: row[6]
    });
  }

  if (rows.length === 0) return [];

  // Sort ascending by date
  rows.sort((a, b) => a.date.getTime() - b.date.getTime());

  const startIndex = Math.max(0, rows.length - n);
  const recent = rows.slice(startIndex);                     // → Most recent N days

  return recent.map(r => ({
    ymd: r.ymd,
    totalAppts: r.totalAppts,
    onTimeAppts: r.onTimeAppts,
    onTimeRate: r.onTimeRate,
    avgDwTimeMinutes: r.avgDwTimeMinutes,
    lateAppts: r.lateAppts,
    lateRate: r.lateRate
  }));
}

Test function for getKpiHistory

Apps Script (JavaScript)

function KPI_testGetKpiHistory_() {                          // → Test history fetch
  const history7 = getKpiHistory(7);                         // → Last 7 days
  const history30 = getKpiHistory(30);                       // → Last 30 days

  // Basic expectation: 7-day history cannot be longer than 30-day history
  if (history7.length > history30.length) {
    throw new Error('7-day history cannot be longer than 30-day history');
  }

  // Verify dates are in ascending order
  const isSorted = arr => arr.every((r, i) =>
    i === 0 || r.ymd >= arr[i - 1].ymd
  );
  if (!isSorted(history7) || !isSorted(history30)) {
    throw new Error('History data is not sorted in ascending date order');
  }

  Logger.log('Last 7 days: ' + JSON.stringify(history7));
  Logger.log('Last 30 days: ' + JSON.stringify(history30));
}

Automatic cleanup of old KPI history using MAX_DAYS

As time passes, your history sheet will keep growing.

We’ll use KPI_HISTORY_CONFIG.MAX_DAYS to purge any records older than that many days.

To avoid conflicts when multiple runs happen at once, we’ll use LockService.

Apps Script (JavaScript)

function KPI_cleanupOldHistory_() {                          // → Cleanup old history
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const sheet = KPI_getHistorySheet_();
    const lastRow = sheet.getLastRow();
    if (lastRow <= 1) {
      return 0;                                              // → No data
    }

    const range = sheet.getRange(2, 1, lastRow - 1, 1);      // → Date column only
    const values = range.getValues();
    const tz = KPI_HISTORY_CONFIG.TZ;
    const now = new Date();

    // Today’s midnight (New York time)
    const todayY = Utilities.formatDate(now, tz, 'yyyy');
    const todayM = Utilities.formatDate(now, tz, 'MM');
    const todayD = Utilities.formatDate(now, tz, 'dd');
    // The retention cutoff moves along the calendar too — not 24 hours × N
    const cutoff = new Date(
      Number(todayY), Number(todayM) - 1, Number(todayD)     // → today's local midnight
    );
    cutoff.setDate(cutoff.getDate() - KPI_HISTORY_CONFIG.MAX_DAYS);
    const cutoffMs = cutoff.getTime();                       // → anything before this is removable

    const rowsToDelete = [];

    for (let i = 0; i < values.length; i++) {
      const cellVal = values[i][0];
      if (!cellVal) continue;

      let ymd;
      if (cellVal instanceof Date) {
        ymd = Utilities.formatDate(cellVal, tz, 'yyyy-MM-dd');
      } else {
        ymd = String(cellVal);
      }

      let d;
      try {
        d = KPI_parseYmdStrict_(ymd);
      } catch (e) {
        continue;                                            // → Quietly skip weird dates
      }

      if (d.getTime() < cutoffMs) {
        rowsToDelete.push(i + 2);                            // → Actual row number
      }
    }

    // Delete from bottom up so row numbers don’t shift
    rowsToDelete.sort((a, b) => b - a);
    rowsToDelete.forEach(row => {
      sheet.deleteRow(row);
    });

    Logger.log('[KPI_cleanupOldHistory_] Deleted rows: ' + rowsToDelete.length);
    return rowsToDelete.length;
  } finally {
    lock.releaseLock();
  }
}

Test function for KPI_cleanupOldHistory_

Apps Script (JavaScript)

function KPI_testCleanupOldHistory_() {                      // → Test cleanup
  const deleted = KPI_cleanupOldHistory_();
  if (!Number.isFinite(deleted) || deleted < 0) {
    throw new Error('Invalid deleted row count: ' + deleted);
  }
  Logger.log('Number of deleted rows: ' + deleted);
}

Setting up a nightly Apps Script time-based trigger

With the code ready, we’ll configure a Google Sheets time-based trigger so that nightlyRollup() runs automatically in the early morning every day.

Steps to create the trigger

From your Spreadsheet, open Extensions → Apps Script.
In the left sidebar, click Triggers (clock icon).
Click the + Add Trigger button in the bottom right.
Configure the trigger as follows:

Choose which function to run: nightlyRollup
Deployment: Head
Select event source: Time-driven
Type of time-based trigger: Day timer
Time: pick an early-morning window that fits your operation, e.g. 1:00–2:00 a.m.

After saving, the script will ask for permissions the first time. Follow the prompts to approve.

Suggested trigger times (from real operations)

If you already have a separate refresh routine for dashboards, it’s better to stagger the timings, for example:

00:30 — Dashboard refresh (queries, pivots, any view-related updates)
01:30 — Previous-day KPI nightly aggregation (nightlyRollup)

Separating them like this avoids overlapping Apps Script runs and reduces the risk of timeouts or resource conflicts.

How to verify the trigger runs correctly

The next morning, open KPI_HISTORY and check that yesterday’s date has been added as a new row.
In the Script Editor’s Triggers panel, you can see the latest run time, success status, and any error messages.
If there’s an issue, run nightlyRollup() manually from the editor around that time window and inspect the logs.

Practical tips: how to handle dates, locks, and tests

Why aggregate “as of yesterday”?

Appointment and gate check-in flows often span midnight.

If you finalize KPIs “for today” at around 23:00, trucks that arrive afterward won’t be reflected in the KPIs. From experience:

It’s much safer to finalize “yesterday” KPIs in the early hours of today.
If your warehouse has a night shift, running the aggregation after the first shift ends (say 1–2 a.m.) works well.

Even if KPIs are a day behind, this greatly reduces the risk of making decisions based on numbers computed before all the data was in.

Why use LockService?

Time-based triggers can be retried when they fail, and operators might also click “Run” manually, causing overlap.

Functions like KPI_saveHistoryForDate_() and KPI_cleanupOldHistory_() that delete or overwrite rows are particularly unsafe under concurrent execution.

Without locks, you could end up with duplicate rows for the same date or messy timing around deletions.
In this article, all save/cleanup functions use LockService.getScriptLock() so that
only one execution at a time can modify history or perform cleanup, and
even when errors occur, finally ensures the lock is released so future runs are not blocked.

You can reuse this pattern directly for similar batch-processing scripts (saving appointments, check-in logs, etc.) to reduce collision risks.

Common errors and what to check

computeKpiForDate_ is not defined

→ This article assumes the code from Google Sheets Appointment KPI Aggregation: On-time Rate & Dwell Time Automation is already in the same Apps Script project.

→ If you see this error, first confirm that the earlier functions (computeKpiForDate_, saveDailyKpi_, getOrCreateSheet_, etc.) have been copied into this project.

Field name mismatches (totalAppts, onTimeRate, etc.)

→ In real deployments, you may have renamed KPI fields or added/removed some of them.

→ In that case, you must update both the requiredKeys array and the rowValues section inside KPI_saveHistoryForDate_() to match your actual KPI object.

→ Otherwise, by design, the validation code in this article will throw “Missing KPI field / Value not numeric” errors to surface the misalignment.

Nonexistent dates or invalid formats

→ KPI_parseYmdStrict_() blocks those via regex + Date re-checks.

→ For example, calling KPI_saveHistoryForDate_('2024-02-30') will throw a clear error, making debugging easier.

Closing: Start auto-building KPI history from tonight

This article organized a practical, real-world structure for automated KPI aggregation in Google Sheets. In summary:

We defined a KPI history configuration (KPI_HISTORY_CONFIG) and the structure of the KPI_HISTORY sheet,
We used KPI_saveHistoryForDate_(ymd) to store one row per date, overwriting if it already exists,
We created nightlyRollup() to compute New York–based “yesterday” KPIs and save them to
KPI_HISTORY and
KPI_DAILY

simultaneously,

We built getKpiHistory(days) to read the last N days of history for dashboards and reports,
We added KPI_cleanupOldHistory_() to prune entries older than MAX_DAYS, and
We accompanied each feature with test functions that check expected behavior (row counts, row numbers, sort order, etc.).

There’s one concrete step you can take right now:

Paste the full code from this article into the Apps Script project of your current Google Sheets appointment/check-in system, and set up a time-based trigger to run nightlyRollup in the early morning.

Starting tomorrow, without any extra Excel crunching, you’ll see one new row for each day automatically appear in KPI_HISTORY, and your Google Sheets logistics KPI dashboard can reliably show the last 7–30 days of trends based on stable, snapshot-style KPIs.

Google Sheets KPI Auto-Rollup: Nightly Trigger History (Full Code)

Intro: So you don’t have to recalculate KPIs every day

Why store daily KPI history separately?

What we’ll add in this part

Define KPI history constants (using names unique to this part)

Creating the KPI_HISTORY sheet and ensuring headers

Date string validation utility: blocking impossible dates

Saving KPI history by date: overwrite if the date already exists

Test function for KPI_saveHistoryForDate_ (with expectation checks)

nightlyRollup: New York–based “yesterday” auto-aggregation + updating KPI_HISTORY & KPI_DAILY together

Test function for nightlyRollup (with expectation checks)

Building a function to fetch recent N days of KPI history

Test function for getKpiHistory

Automatic cleanup of old KPI history using MAX_DAYS

Test function for KPI_cleanupOldHistory_

Setting up a nightly Apps Script time-based trigger

Steps to create the trigger

Suggested trigger times (from real operations)

How to verify the trigger runs correctly

Practical tips: how to handle dates, locks, and tests

Why aggregate “as of yesterday”?

Why use LockService?

Common errors and what to check

Closing: Start auto-building KPI history from tonight

More in this category

Google Sheets bulk appointment booking: finish with one LockService and setValues call

Google Sheets bulk booking validation: check everything in one paste (final)

How to Auto-Refresh a Google Sheets Dashboard: Refresh Multiple Views at Once and Automate with Triggers

Google Sheets Yard Trailer Status Dashboard — Part 3 (Final)