Google Sheets dashboard with Apps Script: see warehouse status at a glance
Introduction: days disappear just from opening Excel tabs
Working in a real warehouse, you’ll usually have an inbound sheet, outbound sheet, stock summary, putaway status sheet, and load management sheet all open at the same time, constantly switching between them. Before the morning meeting, you end up staring only at Excel and Google Sheets tabs, pulling numbers from each sheet, and the actual on-site checks keep getting pushed back. This post is written for that situation: a Google Sheets dashboard that lets you see inbound, outbound, and stock on one screen, automatically.
In this part, we’ll pull data from multiple already-operating sheets and summarize the numbers into a single DASHBOARD sheet, and have it refresh automatically every 30 minutes via an Apps Script time-based trigger. When stock or workload goes over a threshold, we’ll also highlight cells by background color so the operation team can react immediately.
Designing a Google Sheets Apps Script dashboard: which numbers matter?
A dashboard is a screen that shows “what matters right now” in the smallest possible set of numbers. From real use across several warehouses, it’s more effective not to dump many metrics at once, but to start with 4–5 things you check every day and validate those first. In this post we’ll use the following five indicators as examples:
- Today’s inbound count
The number of inbound orders or pallets scheduled for today’s date. It’s a basis to gauge the volume coming in during the morning and how much to prepare for the afternoon peak.
- Un-putaway count
From the stock summary sheet, count items that don’t yet have a final location assigned or remain in a “waiting for putaway” status. As this number rises, aisles in front of racks start to clog, so it’s very useful for deciding what needs to be cleared first.
- Top 10 stock by location
Show the top 10 records ranked by SKU, location, and on-hand quantity. It helps you quickly see if certain SKUs are overstocked, or which physical areas have heavy build-up.
- Today’s outbound quantity
The total quantity shipped today (in pallets or boxes). For the outbound team, this is used to check progress versus target and decide whether to add more manpower.
- Open load count
The number of loads or trucks that haven’t been closed out yet. If this number exceeds a threshold toward the evening, it’s a signal to re-prioritize docking or consider adding equipment.
We’ll place these five indicators in a table on the DASHBOARD sheet and make them all recalculate with a single refreshDashboard() function. Then we’ll configure an Apps Script time-based trigger so it auto-refreshes every 30 minutes, keeping things up to date without pressing any buttons.
The code in this post reads the sheets created in parts 1–7 as-is. Sheet names are INBOUND, OUTBOUND, STOCK, and LOADS, and column positions follow the header order defined in the earlier parts. If you’ve been following along, you shouldn’t need to change anything. Only if you’ve changed sheet names or column order, adjust the SHEET_ and COL_ values at the top of DASHBOARD_CONFIG to match your setup.
Structuring the DASHBOARD sheet: first make it readable for humans
Before you touch the script, deciding on the dashboard sheet layout makes later maintenance far easier. A layout that’s worked well in real operations is:
- Sheet name:
DASHBOARD - Column A: metric names
- Column B: metric values (actual numbers)
- Column C: thresholds or buffer values
- Column D onward: explanations or units
Example layout:
- A2:
Today’s scheduled inbound count, B2: value, C2: threshold (optional) - A3:
Un-putaway count, B3: value, C3: threshold (e.g. 50) - A4:
Today’s outbound quantity, B4: value, C4: threshold (optional) - A5:
Open load count, B5: value, C5: threshold (e.g. 5) - A7:
Top 10 stock itemstitle, A8: headersLocation,Model,Stock qty, A9 onward: data rows (10 rows)
These row numbers are computed in a single place in dashboardRows_(). If the “sheet creation” and “fill values” functions each compute row numbers separately, even a one-row mismatch will make your headers get overwritten by data. So if you want to change placement, just adjust ROW_FIRST_KPI, KPI_COUNT, and TOP_N in DASHBOARD_CONFIG, and both functions will follow. Decide in advance which cells will have color alerts (e.g. B3, B5), and design C3/C5 to contain only numbers. Put threshold explanations like “pallets” or “loads” in column D, so the code handles only pure numbers and you avoid errors.
Step 1 — Function to auto-create the DASHBOARD sheet
This function creates the DASHBOARD sheet if it doesn’t exist and sets up basic labels and table structure. If DASHBOARD already exists, it keeps the title and labels but resets the value areas.
- Paste location: Google Sheets → Extensions → Apps Script →
Code.gs - After pasting: save, then run
testCreateDashboardSheet_()once to create the sheet.
// → Settings used only in this part. Sheet and column names match parts 1–7.
const DASHBOARD_CONFIG = {
SHEET_DASHBOARD: 'DASHBOARD', // → Sheet newly created in this part
SHEET_INBOUND: 'INBOUND', // → Inbound schedule sheet from Part 1
SHEET_OUTBOUND: 'OUTBOUND', // → Outbound history sheet from Part 2
SHEET_STOCK: 'STOCK', // → Stock summary sheet from Part 3
SHEET_LOADS: 'LOADS', // → Load management sheet from Part 7
COL_INBOUND: { ETA_DATE: 4, STATUS: 6 }, // → Col D ETA date, Col F status
COL_OUTBOUND: { QTY: 4, TIMESTAMP: 5 }, // → Col D quantity, Col E timestamp
COL_STOCK: { LOCATION: 1, MODEL: 2, STOCK: 5 }, // → Col A location, Col B model, Col E stock
COL_LOADS: { STATUS: 4 }, // → Col D status
DONE_STATUS: 'PUTAWAY_DONE', // → Status value used for completed putaway in Part 1
OPEN_STATUS: 'OPEN', // → Status value used for open loads in Part 7
ROW_FIRST_KPI: 2, // → First metric row (A2)
KPI_COUNT: 4, // → Number of metrics
TOP_N: 10 // → How many top stock lines to show
};
// Central place to calculate where the table starts.
// If creation and fill functions each compute rows on their own,
// headers will get wiped if they ever go one row out of sync.
function dashboardRows_() { // → Dashboard row layout
const first = DASHBOARD_CONFIG.ROW_FIRST_KPI; // → First metric row
const title = first + DASHBOARD_CONFIG.KPI_COUNT + 1; // → One blank row, then stock table title
return {
kpiFirst: first, // → First metric row (A2)
stockTitle: title, // → "Top 10 stock items" title row (A7)
stockHeader: title + 1, // → Stock table header row (A8)
stockData: title + 2 // → First stock data row (A9)
};
}
function createDashboardSheet_() { // → Create/initialize DASHBOARD sheet
const ss = SpreadsheetApp.getActiveSpreadsheet(); // → Current spreadsheet
let sheet = ss.getSheetByName(DASHBOARD_CONFIG.SHEET_DASHBOARD); // → Existing sheet
if (!sheet) { // → If missing,
sheet = ss.insertSheet(DASHBOARD_CONFIG.SHEET_DASHBOARD); // → create it
}
const rows = dashboardRows_(); // → Get row layout
sheet.getRange('A1').setValue('Warehouse dashboard'); // → Title
sheet.getRange('A1').setFontSize(16).setFontWeight('bold'); // → Title style
const labels = [ // → Metric names in column A (fixed order)
['Today’s scheduled inbound count'], // → B2
['Un-putaway count'], // → B3
['Today’s outbound quantity'], // → B4
['Open load count'] // → B5
];
sheet.getRange(rows.kpiFirst, 1, labels.length, 1).setValues(labels); // → Write labels
sheet.getRange(rows.kpiFirst, 2, labels.length, 1).clearContent(); // → Values will be filled by refresh
// Thresholds in column C are entered manually by the operator.
// If numbers are already there, never wipe them.
const thRange = sheet.getRange(rows.kpiFirst, 3, labels.length, 1); // → Threshold range
const kept = thRange.getValues().map(function (r) { // → Check row by row
const n = Number(r[0]); // → Try to cast to number
return (r[0] !== '' && Number.isFinite(n)) ? [r[0]] : ['']; // → Keep if numeric
});
thRange.setValues(kept); // → Write back
sheet.getRange(rows.stockTitle, 1) // → Stock table title
.setValue('Top ' + DASHBOARD_CONFIG.TOP_N + ' stock items').setFontWeight('bold');
sheet.getRange(rows.stockHeader, 1, 1, 3) // → Stock table header
.setValues([['Location', 'Model', 'Stock qty']]).setFontWeight('bold');
sheet.getRange(rows.stockData, 1, DASHBOARD_CONFIG.TOP_N, 3).clearContent(); // → Clear data range
sheet.setColumnWidths(1, 3, 150); // → Set width for columns A–C
}
function testCreateDashboardSheet_() { // → Helper function to run in the editor
createDashboardSheet_(); // → Create sheet
Logger.log(JSON.stringify(dashboardRows_())); // → Log which rows contain what
}Step 2 — Refresh inbound, outbound, stock, and loads at once with refreshDashboard()
This function reads data from each operational sheet and fills the dashboard metrics and top-10 stock table. To prevent values from clashing when multiple users run the script at the same time, it uses LockService for locking, and ensures lock.releaseLock() runs by putting it in a finally block.
Rows where dates can’t be parsed or quantities aren’t valid numbers are excluded from aggregation and logged to Logger.log with how many rows were problematic. Since Number('ABC') gives NaN, and one NaN in a sum can turn the whole total into NaN, we must filter such values out.
- Paste location: directly below the code above
- After pasting: save, then run
testRefreshDashboard_()to check that the numbers fill in
// Check if a value is a date and return yyyy-MM-dd string; otherwise return empty string.
function ymd_(v, tz) { // → Date normalization
if (v === '' || v === null || v === undefined) return ''; // → Empty cell
const d = (v instanceof Date) ? v : new Date(v); // → Parse as date
if (isNaN(d.getTime())) return ''; // → Not a valid date
return Utilities.formatDate(d, tz, 'yyyy-MM-dd'); // → Normalized for comparison
}
// Return data rows excluding header. If sheet is missing or empty, return empty array.
function sheetRows_(ss, name) { // → Read data rows
const sh = ss.getSheetByName(name); // → Find sheet
if (!sh || sh.getLastRow() < 2) return []; // → No sheet or header only
return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}
function refreshDashboard() { // → Refresh entire dashboard
const lock = LockService.getScriptLock(); // → Global script lock
lock.waitLock(30000); // → Wait up to 30 seconds
try {
const ss = SpreadsheetApp.getActiveSpreadsheet(); // → Current spreadsheet
const tz = ss.getSpreadsheetTimeZone(); // → Spreadsheet timezone
const dash = ss.getSheetByName(DASHBOARD_CONFIG.SHEET_DASHBOARD); // → DASHBOARD sheet
if (!dash) { // → If missing,
throw new Error('DASHBOARD sheet not found. Please run testCreateDashboardSheet_ first.');
}
const rows = dashboardRows_(); // → Row layout
const today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd'); // → Today’s date
const skipped = []; // → Records skipped from aggregation
// 1) & 2) INBOUND — today’s scheduled inbound count and un-putaway count
const ci = DASHBOARD_CONFIG.COL_INBOUND; // → Inbound column positions
let inboundToday = 0; // → Today’s inbound count
let pendingPutaway = 0; // → Of those, not yet put away
sheetRows_(ss, DASHBOARD_CONFIG.SHEET_INBOUND).forEach(function (row, i) {
const day = ymd_(row[ci.ETA_DATE - 1], tz); // → ETA date
if (!day) { // → Date not parseable
skipped.push('INBOUND row ' + (i + 2) + ': ETA_DATE not a valid date');
return; // → Exclude from counts
}
if (day !== today) return; // → Skip if not today
inboundToday++; // → Count today’s inbound
const status = String(row[ci.STATUS - 1] || '').trim(); // → Status
if (status !== DASHBOARD_CONFIG.DONE_STATUS) { // → If not done,
pendingPutaway++; // → count as un-putaway
}
});
// 3) OUTBOUND — sum of outbound quantities processed today
const co = DASHBOARD_CONFIG.COL_OUTBOUND; // → Outbound column positions
let outboundQty = 0; // → Total quantity
sheetRows_(ss, DASHBOARD_CONFIG.SHEET_OUTBOUND).forEach(function (row, i) {
if (ymd_(row[co.TIMESTAMP - 1], tz) !== today) return; // → Only today
const qty = Number(row[co.QTY - 1]); // → Quantity as number
if (!Number.isFinite(qty)) { // → Non-numeric quantity
skipped.push('OUTBOUND row ' + (i + 2) + ': quantity not numeric');
return; // → Don’t pollute total with NaN
}
outboundQty += qty; // → Add to total
});
// 4) LOADS — count of loads not yet closed
const cl = DASHBOARD_CONFIG.COL_LOADS; // → Load column positions
let openLoads = 0; // → Open load count
sheetRows_(ss, DASHBOARD_CONFIG.SHEET_LOADS).forEach(function (row) {
const status = String(row[cl.STATUS - 1] || '').trim().toUpperCase(); // → Status
if (status === DASHBOARD_CONFIG.OPEN_STATUS) openLoads++; // → Count OPEN
});
dash.getRange(rows.kpiFirst, 2, DASHBOARD_CONFIG.KPI_COUNT, 1).setValues([
[inboundToday], // → B2
[pendingPutaway], // → B3
[outboundQty], // → B4
[openLoads] // → B5
]);
// 5) STOCK — top N by stock quantity
const cs = DASHBOARD_CONFIG.COL_STOCK; // → Stock column positions
const picked = []; // → Collected records
sheetRows_(ss, DASHBOARD_CONFIG.SHEET_STOCK).forEach(function (row, i) {
const qty = Number(row[cs.STOCK - 1]); // → Stock quantity
if (!Number.isFinite(qty)) { // → Non-numeric stock
skipped.push('STOCK row ' + (i + 2) + ': stock quantity not numeric');
return; // → Exclude from ranking
}
if (qty <= 0) return; // → No need to show <= 0 stock
picked.push({ loc: row[cs.LOCATION - 1], model: row[cs.MODEL - 1], qty: qty });
});
picked.sort(function (a, b) { return b.qty - a.qty; }); // → Sort descending
const table = []; // → Always write exactly TOP_N rows
for (let i = 0; i < DASHBOARD_CONFIG.TOP_N; i++) { // → Fill missing rows with blanks
const r = picked[i]; // → ith record
table.push(r ? [r.loc, r.model, r.qty] : ['', '', '']); // → Or blank row
}
dash.getRange(rows.stockData, 1, DASHBOARD_CONFIG.TOP_N, 3).setValues(table);
applyDashboardColors_(dash, rows, [inboundToday, pendingPutaway, outboundQty, openLoads]);
if (skipped.length) { // → If any records were skipped,
Logger.log('Skipped ' + skipped.length + ' rows: ' + skipped.join(' / '));
}
} finally {
lock.releaseLock(); // → Always release lock, success or failure
}
}
function testRefreshDashboard_() { // → Helper function to run in the editor
refreshDashboard(); // → Perform refresh
const dash = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName(DASHBOARD_CONFIG.SHEET_DASHBOARD); // → DASHBOARD sheet
const rows = dashboardRows_(); // → Row layout
const kpi = dash.getRange(rows.kpiFirst, 1, DASHBOARD_CONFIG.KPI_COUNT, 2).getValues();
Logger.log(JSON.stringify(kpi)); // → Log labels and values together
}Which sheets and columns are read?
The code reads columns based on the header order defined in the previous parts:
INBOUND(Part 1) —CONTAINER,MODEL,QTY,ETA_DATE,PUTAWAY,STATUS
→ Use column D ETA_DATE to pick today’s rows, and count rows where column F STATUS is not PUTAWAY_DONE as un-putaway.
OUTBOUND(Part 2) —ORDER_NO,LOCATION,MODEL,QTY,TIMESTAMP
→ For rows where column E TIMESTAMP is today, sum column D QTY.
STOCK(Part 3) —Location,Model,InQty,OutQty,Stock
→ Take the top 10 rows by column E Stock and show columns A and B alongside.
LOADS(Part 7) —LOAD_NO,DOOR,CARRIER,STATUS
→ Count rows where column D STATUS is OPEN.
If your sheet names or column order differ, just adjust the SHEET_ and COL_ values in DASHBOARD_CONFIG to match your own sheets; the rest of the logic can stay the same.
Step 3 — Threshold-based color alerts
It’s easy to miss danger zones when looking at plain numbers. In the field, color-based alerts trigger reactions much faster. This function reads thresholds from column C and colors the corresponding cells in column B. If you leave a threshold blank, that row won’t be colored at all, so you can limit thresholds to only the metrics that need them.
Each value gets exactly one color. At least double the threshold is red, at least the threshold is yellow, and anything below stays white. If you let multiple rules overlap on the same value, the later ones override the earlier, and “double or more” might never be visible. To avoid that, the conditions are chained with else if so exactly one branch is chosen.
- Paste location: directly under
testRefreshDashboard_() - After pasting: in the
DASHBOARDsheet, set numeric thresholds (e.g. C3 = 50, C5 = 5), then runtestRefreshDashboard_()again
// Only metrics with thresholds in column C will be colored.
// >= 2× threshold: red; >= threshold: yellow; below: white — one color per value.
function applyDashboardColors_(dash, rows, values) { // → Threshold-based coloring
const n = DASHBOARD_CONFIG.KPI_COUNT; // → Number of metrics
const thresholds = dash.getRange(rows.kpiFirst, 3, n, 1).getValues(); // → Column C thresholds
const colors = []; // → Colors to apply
for (let i = 0; i < n; i++) { // → For each metric
const th = Number(thresholds[i][0]); // → Threshold
const v = Number(values[i]); // → Current value
let color = '#ffffff'; // → Default white
if (Number.isFinite(th) && th > 0 && Number.isFinite(v)) { // → Only if threshold is valid
if (v >= th * 2) { // → At least double threshold
color = '#f4cccc'; // → Reddish (immediate action)
} else if (v >= th) { // → At least threshold
color = '#fff2cc'; // → Yellowish (caution)
}
}
colors.push([color]); // → Add to list
}
dash.getRange(rows.kpiFirst, 2, n, 1).setBackgrounds(colors); // → Apply all at once
}How to check it’s working:
- In the
DASHBOARDsheet, enter 50 in C3 and 5 in C5. - Adjust your data so B3 and B5 change, then run
testRefreshDashboard_(). - When values are below threshold, cells stay white; above threshold they turn yellow; at double or more they turn red. If that’s what you see, it’s working correctly.
Step 4 — Add onOpen menu and set up a time-based trigger
It’s convenient to have both a manual menu button to refresh on demand and a time-based trigger that refreshes every 30 minutes automatically. The menu lets the team run it directly, and the trigger keeps things up to date at night and on weekends.
4-1. Add a “Refresh dashboard” menu item in onOpen
When the spreadsheet is opened, this adds a Warehouse tools → Refresh dashboard menu entry. If you’re already using onOpen from a previous part, merge them by adding a call to addDashboardMenu_() inside your existing onOpen.
function onOpen() { // → Runs automatically when sheet opens
const menu = SpreadsheetApp.getUi().createMenu('Warehouse tools'); // → Shared series menu
addDashboardMenu_(menu); // → Add entries from this part
menu.addToUi(); // → Attach to UI
}
function addDashboardMenu_(menu) { // → Call this when merging menus
menu.addItem('Refresh dashboard', 'refreshDashboard'); // → Menu item
}4-2. Time-based Apps Script trigger to auto-refresh every 30 minutes
Run this function once, and it will create a time-based trigger that calls refreshDashboard() every 30 minutes. If an identical trigger already exists, it’s removed first to avoid duplicate runs.
function createDashboardTrigger_() { // → Create time-based trigger
const funcName = 'refreshDashboard';
// Delete existing time-based triggers for the same function
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(tr => {
if (tr.getHandlerFunction() === funcName &&
tr.getEventType() === ScriptApp.EventType.TIME_BASED) {
ScriptApp.deleteTrigger(tr);
}
});
// Create new trigger to run every 30 minutes
ScriptApp.newTrigger(funcName)
.timeBased()
.everyMinutes(30)
.create();
Logger.log('Dashboard auto-refresh trigger created');
}Note that “every 30 minutes” is approximate: depending on Google’s infrastructure, execution may drift a bit. There is no guarantee of exact clock times, such as exactly on the hour and half hour.
Practical notes from running this in small warehouses
- Fewer metrics = more use on the floor
If you start with many metrics like by inbound line, by customer, by outbound channel, almost nobody will look at all of them. Start from 4–5 key ones (inbound, outbound, putaway, loads), then add more only when the team explicitly asks.
- Set thresholds at levels the team genuinely feels
If you set the un-putaway threshold at 10 pallets, most warehouses will always be in red. It’s better to look at how much can realistically be processed per day with your people and equipment, plus the last week’s average un-putaway volume, then set C3 to 1.2–1.5 times that. For loads, think of “the point just before both docks are constantly blocked.”
- Use 30–60 minutes for triggers
Even if inbound/outbound happens minute by minute, decisions are usually made in 30–60 minute windows. A 5-minute trigger hits execution quotas faster and greatly increases error-handling overhead. In practice, 30 or 60 minutes is more than enough.
- Avoid concurrency and bad input
We’ve had cases where multiple people spam the refresh menu right before a meeting and numbers get messed up. LockService helps prevent this. Also, use checks like Number.isFinite() and qtyVal < 0 to skip non-numeric or negative values, and log which rows were skipped to make later data cleanup easier.
- Whitelist status codes, skip the rest but keep a log
For text-based columns like putaway status or load status, people are prone to ad-hoc values. Keep a list of allowed states like ['WAIT_PUTAWAY', 'PENDING_PUTAWAY'] or ['OPEN', 'IN_PROGRESS'], count only those, and log everything else. This keeps your aggregates clean while giving you concrete examples for user training.
Common errors and how to fix them
- Error:
DASHBOARD sheet not found
You ran refreshDashboard() before creating DASHBOARD, or renamed the sheet.
→ Run testCreateDashboardSheet_() first, then check that the sheet name matches DASHBOARD_CONFIG.SHEET_DASHBOARD.
- No color changes on B3/B5
If you enter values like “50 pallets” in C3/C5, Number() conversion fails and they’re treated as if there were no thresholds.
→ Put plain numbers in C3/C5 and move descriptions like “pallets” to column D or as cell notes.
- Today’s inbound/outbound always show 0
Dates might be stored as text, or in a format that can’t be parsed. ymd_() tries simple string forms, but not completely free-form text.
→ Format date columns as “Date” and have users use the date picker UI.
- Outbound total is lower than reality
Quantities containing spaces, symbols, or negatives (e.g. cancellations) are skipped. If you see log messages like OUTBOUND row ...: quantity not numeric, open those rows, decide a clean data rule, and adjust.
Wrap-up: two quick tests you can run today
With just Google Sheets and Apps Script you can build a working operations dashboard that shows inbound, outbound, stock, and loads on a single screen. You don’t need separate BI tools to set thresholds and color alerts that are good enough for real decision-making on the floor.
Two simple steps you can try today:
- Open the Apps Script editor and run
testCreateDashboardSheet_()once to create theDASHBOARDskeleton. - Confirm that your
INBOUND / OUTBOUND / STOCK / LOADSsheets from parts 1–7 exist, then runtestRefreshDashboard_().
Once you see the four metrics and top-10 stock table fill in automatically on the DASHBOARD sheet, you’ll have a feel for how to adjust thresholds and which items to show. Finally, run createDashboardTrigger_() to enable the 30-minute auto-refresh, and from tomorrow morning you should notice the preparation time for your daily meeting drop sharply.
