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

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

Intro: Handling Duplicate Triggers and the 6‑Minute Limit in One Go

When you look up how to auto-refresh a Google Sheets dashboard, most examples stop at “create one function per view and attach a button.” Once you add more than three views—daily appointments, status overview, yard trailers—you end up with three buttons and three time triggers. The result is that multiple time triggers stack on the same code, three functions run at once, and they keep hitting the 6‑minute Google Apps Script execution limit and stopping.

This post assumes you already have the setups from these earlier parts in place: How to Automate Appointment Status in Google Sheets: Create a TODAY Sheet — Dashboard Part 1, Build an Appointment Status Overview Dashboard in Google Sheets — Dashboard Part 2, and Create a Yard Trailer Status View in Google Sheets — Dashboard Part 3 (Revised & Final).

On top of that, we will wire everything so that a single DASH_refreshAllViews function refreshes all three views at once, and DASH_resetRefreshTrigger keeps the time trigger structure to “always exactly one trigger.”

In a real warehouse, I ran into the same issues trying to see appointments, check‑ins, and yard status on a single screen. Manually refreshing each sheet or attaching a separate trigger to each function quickly became unmanageable when the schedule shifted even a little. So I reorganized it around one principle: “one dashboard refresh function, and one time trigger tied to that function.”

Overall Structure: Tie 3 Views to One Function, and Prepare Summary Numbers Too

In this part we’ll build two main pieces.

First, DASH_refreshAllViews(). This function will call the three functions from the previous parts in sequence—DASH_updateApptDailyView(), DASH_updateApptStatusView(), and DASH_updateYardStatusView()—to handle “refresh multiple Google Sheets views at once.” In the Apps Script triggers UI, we will register only this function so time triggers don’t get scattered. The code is written on the assumption that DASH_updateApptDailyView returns a number (e.g., refreshed appointment count), and DASH_updateApptStatusView and DASH_updateYardStatusView each return either a number or undefined.

Second, DASH_getDashboardData(). This function quickly scans the already‑computed TODAY sheet and returns a compact summary object with today’s appointment count, yard trailer count, and last refreshed time. This is convenient to reuse when expanding your “Google Sheets dashboard automation” into web apps, internal portals, or additional report sheets. We also add a simple test function that validates expected formats (date string and numeric fields).

All code in this post uses the DASH_ and DASH_CONFIG4 prefixes. That way, even if you paste it into the same project as previous parts, constant and function names won’t collide. Since TODAY sheet layouts differ by company, treat DASH_getDashboardData() as an “example aggregation” and adjust it to your actual column layout.

Shared Dashboard Settings: Centralize Sheet, Time, and Trigger Names

It’s safer to centralize reused values as constants. You want to be able to change sheet names, max execution time, and trigger target function names from a single place for easier maintenance.

1) What this code does

• Collects shared dashboard settings into a single constant (DASH_CONFIG4).

2) Where to paste

• In Google Sheets → Extensions → Apps Script → Code.gs at the top (it can also go under other constants).

3) After pasting

• Just save. No need to run anything yet.

// Shared dashboard settings constant  // → Settings used only in this part

const DASH_CONFIG4 = {                         // → Config for Dashboard Part 4
  TODAY_SHEET_NAME: 'TODAY',                   // → Dashboard sheet name
  MAX_REFRESH_MINUTES: 5,                      // → Allowed total refresh time (minutes)
  TIMEZONE: 'America/New_York',                // → Common time zone
  TRIGGER_FUNCTION_NAME: 'DASH_refreshAllViews', // → Trigger target function name
};

How to verify: if the Apps Script editor saves without any error markers, you’re good.

Refresh All 3 Views at Once: Handle Locking and Execution Time Together

If you want to refresh all three views in one go, you must guard against two risks. One is concurrent writes to TODAY when triggers and manual runs overlap. The other is over‑running the 6‑minute execution limit when the workload is heavy.

So in DASH_refreshAllViews() we first grab a script lock via LockService.getScriptLock(), and exit immediately if we fail to acquire it. We also calculate the “remaining time” before each view runs. Once we’re close to the configured limit (MAX_REFRESH_MINUTES), we skip the remaining views and only log a warning. That way, even while “auto‑refreshing a Google Sheets dashboard,” we protect against exceeding the execution limit.

1) What this code does

• Acquires a script lock, refreshes all three views in sequence, and skips later views if there’s not enough time left.
• Logs success/failure and counts for each view.
• Provides a test function DASH_testRefreshAllViews() for quick verification.

2) Where to paste

• At the bottom of the same Code.gs file, under your existing dashboard functions.

3) After pasting

• Run DASH_refreshAllViews once from the editor to grant permissions.
• Then run DASH_testRefreshAllViews and check the logs.

function DASH_refreshAllViews() {                        // → Refresh all three views at once
  const lock = LockService.getScriptLock();              // → Script lock object
  const lockWaitMs = 5 * 1000;                           // → Wait up to 5 seconds

  if (!lock.tryLock(lockWaitMs)) {                       // → Try to acquire the lock
    Logger.log('[DASH_refreshAllViews] Another execution is in progress. Skipping this run.'); // → Duplicate run prevention log
    return { success: false, reason: 'LOCKED' };         // → Return on lock failure
  }

  const started = new Date();                            // → Record start time
  const log = [];                                        // → Execution log array
  const results = {                                      // → Per-view result summary
    daily: { ok: false, count: 0, skipped: false, error: null },
    status: { ok: false, count: undefined, skipped: false, error: null },
    yard: { ok: false, count: undefined, skipped: false, error: null },
  };

  try {                                                  // → Wrap the entire execution
    const maxMs = DASH_CONFIG4.MAX_REFRESH_MINUTES * 60 * 1000; // → Allowed total time (ms)

    // Internal helper: check if there is enough time left
    const hasTime = () => {                              // → Remaining time checker
      const now = new Date();                            
      const elapsed = now.getTime() - started.getTime(); // → Elapsed time
      return elapsed < maxMs;                            // → Within limit?
    };

    // 1. Refresh daily appointments view
    if (hasTime()) {                                     // → Check time margin
      try {
        const dailyCount = DASH_updateApptDailyView();   // → Call Part 1 function (returns number)
        results.daily.ok = true;                         // → Mark success
        results.daily.count = Number.isFinite(dailyCount) ? dailyCount : 0; // → Record count
        log.push(`Daily view updated: ${results.daily.count}`); // → Result log
      } catch (e) {
        results.daily.error = e.message || String(e);    // → Save error message
        log.push(`Daily view FAILED: ${results.daily.error}`); // → Failure log
      }
    } else {
      results.daily.skipped = true;                      // → Skipped due to lack of time
      log.push('Daily view skipped due to time limit');  // → Skip log
    }

    // 2. Refresh appointment status view
    if (hasTime()) {                                     // → Check remaining time again
      try {
        const statusResult = DASH_updateApptStatusView(); // → Call Part 2 function (number or undefined)
        results.status.ok = true;                        // → Mark success
        if (Number.isFinite(statusResult)) {             // → If it returned a number
          results.status.count = statusResult;
          log.push(`Status view updated: ${statusResult}`); // → Count log
        } else {
          log.push('Status view updated');               // → Success log
        }
      } catch (e) {
        results.status.error = e.message || String(e);   // → Save error message
        log.push(`Status view FAILED: ${results.status.error}`); // → Failure log
      }
    } else {
      results.status.skipped = true;                     // → Skipped due to lack of time
      log.push('Status view skipped due to time limit'); // → Skip log
    }

    // 3. Refresh yard trailer view
    if (hasTime()) {                                     // → Check remaining time again
      try {
        const yardResult = DASH_updateYardStatusView();  // → Call Part 3 function (number or undefined)
        results.yard.ok = true;                          // → Mark success
        if (Number.isFinite(yardResult)) {               // → If it returned a number
          results.yard.count = yardResult;
          log.push(`Yard view updated: ${yardResult}`);  // → Count log
        } else {
          log.push('Yard view updated');                 // → Success log
        }
      } catch (e) {
        results.yard.error = e.message || String(e);     // → Save error message
        log.push(`Yard view FAILED: ${results.yard.error}`); // → Failure log
      }
    } else {
      results.yard.skipped = true;                       // → Skipped due to lack of time
      log.push('Yard view skipped due to time limit');   // → Skip log
    }

  } finally {                                            // → Always runs at the end
    const ended = new Date();                            // → End time
    const elapsedMs = ended.getTime() - started.getTime(); // → Elapsed time (ms)
    const elapsedMin = (elapsedMs / 1000 / 60).toFixed(2); // → Convert to minutes

    log.push(`Elapsed minutes: ${elapsedMin}`);          // → Time log

    if (elapsedMs > DASH_CONFIG4.MAX_REFRESH_MINUTES * 60 * 1000) { // → Limit exceeded
      log.push(
        `WARNING: refresh exceeded ${DASH_CONFIG4.MAX_REFRESH_MINUTES} minutes`, // → Warning
      );
    }

    Logger.log('[DASH_refreshAllViews] ' + log.join(' | ')); // → Output all logs
    lock.releaseLock();                                   // → Release lock
  }

  const anyOk = results.daily.ok || results.status.ok || results.yard.ok; // → Any success?
  return {                                              // → Return summary
    success: anyOk,                                     // → Did any view succeed?
    results,                                            // → Detailed results
  };
}

// Test function to check DASH_refreshAllViews behavior
function DASH_testRefreshAllViews() {
  const result = DASH_refreshAllViews();                // → Run full refresh
  Logger.log(JSON.stringify(result));                   // → Log result as JSON
}

How to verify: run DASH_refreshAllViews from the editor, then check execution logs for messages like Daily view updated, Status view updated, Yard view updated, or ... skipped due to time limit without errors.

When you run DASH_testRefreshAllViews, you should see JSON like {"success":true/false,"results":{...}} in the logs. Use that to quickly see whether each view actually ran, and where anything was skipped or failed.

If runtime tends to be long, you can set DASH_CONFIG4.MAX_REFRESH_MINUTES to a more conservative 3–4 minutes and adjust which views “must always run” and which can roll into the next cycle.

Collecting Display Numbers: A Helper that Reads Summary Values from TODAY

In a logistics dashboard, short summary numbers are often more useful on the screen than long tables. If the TODAY sheet already computes what you need, a light‑weight function that extracts just the key values for display is ideal.

In this example, DASH_getDashboardData() returns an object with four fields:

• Today’s date string (date)
• Number of rows in TODAY where column A has a value (treated as appointment count)
• Number of rows where column J contains 'YARD' (example: trailers in yard)
• Execution timestamp string (refreshedAt)

Because TODAY layouts differ, treat this as a sample. In particular, column J may not match the yard status block from previous parts exactly, so make sure to adjust the aggregation rule to your own sheet before using it in production.

We also include a test function that checks whether date matches today and totalAppts is numeric, and logs PASS/FAIL.

1) What this code does

• Scans TODAY to count appointments and yard trailers, and returns those plus the current timestamp as an object.
• Uses a test function that verifies date equals today and totalAppts is a number, then logs PASS/FAIL.

2) Where to paste

• Directly below DASH_refreshAllViews and DASH_testRefreshAllViews in the same file.

3) After pasting

• Run DASH_testGetDashboardData and check the logs for the result and PASS status.

function DASH_getDashboardData() {                        // → Build summary data for display
  const ss = SpreadsheetApp.getActiveSpreadsheet();       // → Current spreadsheet
  const sheet =
    ss.getSheetByName(DASH_CONFIG4.TODAY_SHEET_NAME);     // → Find TODAY sheet

  if (!sheet) {                                           // → If sheet is missing
    throw new Error('Cannot find TODAY sheet.');          // → Throw error
  }

  const lastRow = sheet.getLastRow();                     // → Last row number
  let totalAppts = 0;                                     // → Appointment count
  let yardCount = 0;                                      // → Example yard trailer count

  // Limit read range safely to maximum 10 columns
  const maxCol = Math.min(10, sheet.getLastColumn());

  if (lastRow > 1) {                                      // → Only if there is data
    const range = sheet.getRange(2, 1, lastRow - 1, maxCol); // → A2 to max column
    const values = range.getValues();                     // → Read values

    values.forEach((row) => {                             // → Loop through each row
      // If column A has a value, count as one appointment
      if (row[0]) {
        totalAppts += 1;
      }

      // Only count J column (10th) as 'YARD' when that column actually exists
      if (row.length > 9 && row[9] === 'YARD') {
        yardCount += 1;
      }
    });
  }

  const now = new Date();                                 // → Current time
  const tz = DASH_CONFIG4.TIMEZONE;                       // → Time zone
  const dateStr = Utilities.formatDate(now, tz, 'yyyy-MM-dd'); // → Date string
  const timeStr = Utilities.formatDate(now, tz, 'HH:mm:ss');   // → Time string

  return {                                                // → Return summary object
    date: dateStr,                                        // → Today’s date
    totalAppts: totalAppts,                               // → Appointment count
    yardTrailers: yardCount,                              // → Yard trailer count
    refreshedAt: `${dateStr} ${timeStr}`,                 // → Refresh timestamp
  };
}

// Test function for DASH_getDashboardData behavior and expected values
function DASH_testGetDashboardData() {
  const data = DASH_getDashboardData();                   // → Get summary data
  const today = Utilities.formatDate(
    new Date(),
    DASH_CONFIG4.TIMEZONE,
    'yyyy-MM-dd',
  );
  const isValid =
    data.date === today && typeof data.totalAppts === 'number';

  // Log PASS status and the entire object as JSON
  Logger.log(
    '[PASS:' + (isValid ? 'true' : 'false') + ']' + JSON.stringify(data),
  );
}

How to verify: run DASH_testGetDashboardData and check the logs. You should see

• A line starting with "[PASS:true]{...}", and
• JSON like {"date":"2026-08-25","totalAppts":10,"yardTrailers":3,"refreshedAt":"..."}.

Key detail: note the condition row.length > 9 && row[9] === 'YARD'.

• If TODAY has fewer than 10 columns, it safely avoids touching column J.
• If you use a different marker such as Y, yard, or blank cells, this function will not count them as yard rows.

Adjust the condition or extract the marker into a constant so it stays in sync with your actual sheet.

Prevent Duplicate ScriptApp Triggers: Always Keep Only One Trigger

Next we’ll configure a time trigger to run DASH_refreshAllViews on a schedule. In practice, two common problems crop up:

• Multiple time triggers end up pointing to the same function, causing duplicate runs.
• Old test triggers are forgotten, so different schedules overlap.

The way to avoid this is to manage triggers through a single “reset function” that deletes all triggers for that handler and recreates exactly one with the desired schedule. That’s what DASH_resetRefreshTrigger() does.

1) What this code does

• Looks at all project triggers, deletes those whose handler is DASH_refreshAllViews, then creates a new time‑based trigger that runs every 5 minutes.

2) Where to paste

• Below DASH_getDashboardData and DASH_testGetDashboardData in the same file.

3) After pasting

• Run DASH_resetRefreshTrigger once to grant permissions and create the trigger. Check the trigger list in the editor.

function DASH_resetRefreshTrigger() {                       // → Reset time trigger
  const funcName = DASH_CONFIG4.TRIGGER_FUNCTION_NAME;      // → Function name constant
  const triggers = ScriptApp.getProjectTriggers();          // → Current project triggers

  // 1. Remove all existing triggers for the same function
  triggers.forEach((t) => {                                 // → Loop through triggers
    if (t.getHandlerFunction() === funcName) {              // → If it targets our function
      ScriptApp.deleteTrigger(t);                           // → Delete that trigger
    }
  });

  // 2. Create a new time-based trigger
  ScriptApp.newTrigger(funcName)                            // → Create new trigger
    .timeBased()                                            // → Time-based trigger
    .everyMinutes(5)                                        // → Run every 5 minutes (example)
    .create();                                              // → Create trigger

  Logger.log(
    `Time trigger for ${funcName} has been reset.`,         // → Log message
  );
}

How to verify: after running DASH_resetRefreshTrigger, open “Triggers” in the Apps Script editor. You should see exactly one time‑based trigger targeting DASH_refreshAllViews.

Adjust the frequency to match your environment—for example everyMinutes(10) or everyHours(1). Just keep the interval safely longer than DASH_CONFIG4.MAX_REFRESH_MINUTES so each run can complete before the next begins.

Add a Menu Button: Allow Manual One‑Click Full Refresh

Even with automation, there are moments when you want to “refresh everything once right now.” Instead of clicking a separate button for each view, it’s much simpler for operators if the menu has one item: “Refresh Entire Dashboard.”

In this series we’ve been using a shared onOpen() that builds a “Warehouse Tools” (or reservation tools) menu, and each part adds its own items via DASH_addDashboardMenu_. We’ll keep that structure here.

1) What this change does

• Adds a “Refresh Entire Dashboard” item to the existing dashboard menu, wired to DASH_refreshAllViews.

2) Where to modify

• Inside your existing DASH_addDashboardMenu_(menu) function, add one line under the other menu items. (The full function was shown in earlier parts and is not repeated here.)

3) After editing

• Save, then reopen the spreadsheet and check the menu.

// Location: inside the existing DASH_addDashboardMenu_(menu) function, add this line under other items
menu.addItem('Refresh Entire Dashboard', 'DASH_refreshAllViews');  // → Full refresh button

How to verify: reopen the sheet. You should see a menu item labeled “Refresh Entire Dashboard.” Clicking it should refresh all three areas (daily, status, yard) on TODAY at once.

This pattern also makes it easy to add more views later: just extend the call sequence inside DASH_refreshAllViews, while leaving the menu and trigger setup unchanged.

Practical Tips: Checklist for Triggers and Aggregation Rules

From real‑world logistics operations, here are a few points that tie directly into this setup.

First, treating duplicate ScriptApp triggers as a “never allowed” condition is important. Even two time triggers for the same handler can clash by writing to TODAY at the same time and leaving it in unexpected states. Yard status in particular needs to be trustworthy; if two runs interleave and confuse colors or labels, the floor will quickly stop trusting the dashboard. Using a dedicated function like DASH_resetRefreshTrigger as the only way to change triggers is a robust approach.

Second, make the aggregation rules explicit. If, as in this post, DASH_getDashboardData() counts an appointment whenever column A has a value and counts yard trailers only when column J equals 'YARD', then any structural change to TODAY should prompt a revisit of this function first. Otherwise, display numbers will drift away from the underlying table and confuse operators.

Third, size the 6‑minute guardrail for the worst case, not the average. Code that usually finishes in 1–2 minutes may still cross 6 minutes on peak days. Set MAX_REFRESH_MINUTES conservatively and build in a fallback—like skipping some views when time runs short—so you don’t lose the entire refresh exactly when you need it most.

Fourth, make sure the return‑value conventions assumed here match your actual implementations: DASH_updateApptDailyView should return a number; DASH_updateApptStatusView and DASH_updateYardStatusView should return a number or undefined. If previous parts implemented them differently, adjust them to follow this rule first so DASH_refreshAllViews can safely log counts.

Finally, this system is meant as a complementary tool, not a full WMS replacement. It fills the gaps between appointment, arrival, and yard status in a way that matches the real information flow on the floor. Rather than copying it verbatim, adapt DASH_getDashboardData() and the trigger schedule to your own TODAY layout and operational KPIs.

Conclusion: Start by Wiring Up DASH_refreshAllViews and the Trigger

Moving from separate refreshes per view to one function and one time trigger significantly reduces the mental load on operators. At the same time, it resolves two practical issues—duplicate ScriptApp triggers and the 6‑minute execution limit.

The quickest next step is to paste DASH_refreshAllViews, DASH_testRefreshAllViews, DASH_getDashboardData, DASH_testGetDashboardData, and DASH_resetRefreshTrigger into your project, then run DASH_resetRefreshTrigger once to create a single time trigger with a 5–10‑minute interval.

If, the next morning, you open TODAY and see that the daily, status, and yard views are all up to date without anyone pressing Refresh, and the DASH_testGetDashboardData logs show PASS:true, your Google Sheets dashboard auto‑refresh is in place and working as intended.