Google Sheets outbound load summary automation
Introduction: Automating per-load outbound checks in Google Sheets
When you manage outbound shipments in a warehouse or logistics site with Google Sheets, it’s surprisingly hard to see at a glance what and how much is loaded onto a single truck (a “Load”). Without a per-load summary sheet, you end up manually matching the order list and picking list to verify, and when you total pallet counts, gross weight, and quantity by model, small discrepancies pop up all the time. This post walks through a Google Sheets outbound summary automation: how to use Google Sheets and Apps Script to automatically aggregate and validate outbound details per Load, step by step.
This article adds a “per-load summary” layer on top of the inventory/outbound automation scripts from previous posts. The key is to use the Load number in the OUTBOUND data as the basis to auto-aggregate by model, pallet, and weight, and neatly collect everything in the SUMMARY sheet. If you bundle this with a function that closes out the Load status as LOADED, you can handle pre‑ and post‑loading validation with one coherent script set.
Here’s the target flow for this post:
1) In the LOADS sheet, register one row of Load information per truck.
2) In the OUTBOUND sheet, assign a Load number to each outbound row.
3) The buildLoadSummary(loadNo) function gathers rows with the same Load number and totals ITEM, pallets, and weight.
4) It outputs one block per Load in the SUMMARY sheet and logs any anomalies as warnings.
5) The closeLoad(loadNo) function then flips the LOADS STATUS to LOADED to finish.
By following this, you’ll have Google Sheets Apps Script outbound aggregation that’s practical enough for day‑to‑day operations.
Sheet structure design: Roles of LOADS, OUTBOUND, and SUMMARY
To run Google Sheets warehouse automation reliably, you first need a cleanly separated sheet structure. This post explains based on three sheets that are most commonly used in real operations. In your actual environment, you can add more columns, but it’s better to keep the basic skeleton intact.
First, the LOADS sheet has one row per truck. With just Load number, door, carrier, and status, you can already manage the basics. Use column A as LOAD_NO, B as DOOR, C as CARRIER, and D as STATUS. When an outbound plan is set, assign a Load number and door here, and set the initial status to OPEN.
Second, the OUTBOUND sheet is where actual outbound scan data accumulates. Whether it’s barcode scans or manual entry, every outbound event is stored here as a record. In this post, we’ll assume A: DATETIME, B: LOAD_NO, C: ORDER_NO, D: ITEM, E: QTY, F: PALLET, G: WEIGHT. The Apps Script will use these column positions to run the per-load outbound aggregation.
Third, the SUMMARY sheet stores the per-load summaries. If you design it so that each Load uses one block and you stack those blocks downward, you can handle multiple Loads per day and still keep the full history. Each block starts with “Load summary / Load number / timestamp,” followed by totals of QTY, PALLET, and WEIGHT by ITEM, and ends with overall Load totals and any warning messages.
A common real‑world problem is changing the sheet structure midstream and breaking all your scripts. In this post, the sheet names and column indices are集中 in top‑level constants (LOAD_CONFIG), so you can tweak the structure later by editing just the constants, and the whole script adapts. This pattern is key to making Google Sheets warehouse management automation maintainable.
Apps Script basics and shared constants
Now open Google Sheets Apps Script and define shared settings and utility functions. Even if your outbound summary script is split across multiple functions, it’s much safer to manage sheet names and column layouts in one place.
The code below defines sheet names and column indices used across the entire script, plus a simple date‑formatting helper.
Where to paste: Google Sheets → Extensions → Apps Script → at the very top of Code.gs.
What to do after pasting: Just save; no need to run anything yet.
// Change only this block to match your sheet structure
const LOAD_CONFIG = { // → settings collection
SHEET_LOADS: 'LOADS', // → Load info sheet name
SHEET_OUTBOUND: 'OUTBOUND', // → outbound sheet name
SHEET_SUMMARY: 'SUMMARY', // → summary sheet name
COL_LOADS: { // → LOADS column positions
LOAD_NO: 1, // → column A
DOOR: 2, // → column B
CARRIER: 3, // → column C
STATUS: 4 // → column D
},
COL_OUTBOUND: { // → OUTBOUND column positions
DATETIME: 1, // → column A
LOAD_NO: 2, // → column B
ORDER_NO: 3, // → column C
ITEM: 4, // → column D
QTY: 5, // → column E
PALLET: 6, // → column F
WEIGHT: 7 // → column G
},
STATUS: { // → Load status values
OPEN: 'OPEN', // → before loading
LOADED: 'LOADED', // → loading complete
CANCELLED: 'CANCELLED' // → cancelled
}
};
function getSheet_(name) { // → get sheet object
const sh = SpreadsheetApp.getActive().getSheetByName(name); // → find in active file
if (!sh) { // → if not found
throw new Error('Sheet not found:' + name); // → throw error
}
return sh; // → return sheet
}
function formatDateTime_(date) { // → convert date to string
if (!(date instanceof Date)) { // → type check
return ''; // → if not date, return empty
}
const tz = Session.getScriptTimeZone(); // → script time zone
return Utilities.formatDate(date, tz, 'yyyy-MM-dd HH:mm'); // → format string
}How to check it: getSheet_ takes a sheet name as an argument, so running it directly with ▶ fails because nothing is passed in. Paste the small test function below at the bottom of the file, run that instead, and check the execution log (View → Executions) for the sheet name.
function testGetSheet() { // → run this one from the editor
const sh = getSheet_(LOAD_CONFIG.SHEET_LOADS); // → call it with an argument
Logger.log('Found sheet: ' + sh.getName()); // → prints to the execution log
}Designing per-load aggregation: What to auto‑validate
When you design Google Sheets per-load outbound automation, it’s important to include not just simple totals but also the must‑check validation items from the floor. From real operations, I at least include the following:
First, quantity totals by ITEM. When the same ITEM code appears across multiple rows, summing QTY and showing it clearly makes cross‑checking against sales orders and picking lists much easier. Rows with QTY of 0 or negative are highly likely to be scan errors, so they should be flagged as warnings.
Second, total pallet count. On the dock, the very first question is usually “How many pallets?” If you show pallet counts per ITEM along with total pallets for the Load, both floor staff and drivers can understand it immediately. Partial pallets can simply be summed as decimals.
Third, total weight. You need to confirm you’re not exceeding the truck’s allowed weight, so it’s better to aggregate weight by ITEM and for the entire Load. If some items don’t have weight data yet, keep their WEIGHT as 0 and add weight data later.
Fourth, duplicate order numbers within a Load. If the same ORDER_NO is scanned multiple times within a Load, you need to tell whether it’s actual duplicate picking or one order split across two pallets. The code in this post only detects duplicates within the same Load; it doesn’t handle cross‑Load duplicates. If you want cross‑Load checks, you’ll need an additional validation routine across the entire OUTBOUND sheet.
Based on these rules, the Apps Script function buildLoadSummary takes a single Load number as input, gathers all rows in OUTBOUND with that Load, aggregates QTY, PALLET, and WEIGHT by ITEM, and outputs the result to the SUMMARY sheet.
Generating per-load outbound summaries: Implementing buildLoadSummary
One check is not optional here. If a QTY, PALLET or WEIGHT cell holds something like ABC or 1,000 pcs, Number() returns NaN, and adding NaN turns the whole running total into NaN — one typo in one row and every ITEM subtotal plus the Load total prints as NaN. Worse, NaN is neither qty === 0 nor qty < 0, so the existing warnings never fire. The code below therefore checks all three values with Number.isFinite first, records which row is broken, and leaves that row out of the totals.
Now implement the core function buildLoadSummary(loadNo). This function takes one Load number, filters OUTBOUND for rows matching that Load, aggregates by ITEM, and appends a “Load summary block” to the SUMMARY sheet. At the same time, it collects warnings for zero quantities, negative quantities, missing ITEM codes, and duplicate order numbers within the Load.
This code generates a summary for a single Load.
Where to paste: In Code.gs, right below LOAD_CONFIG and the utility functions.
What to do after pasting: Save, then run testBuildLoadSummary once.
function buildLoadSummary(loadNo) { // → generate Load summary
if (!loadNo) { // → validate input
throw new Error('Enter a Load number.'); // → error message
}
const ss = SpreadsheetApp.getActive(); // → active spreadsheet
const shOutbound = getSheet_(LOAD_CONFIG.SHEET_OUTBOUND); // → OUTBOUND sheet
const shSummary = getSheet_(LOAD_CONFIG.SHEET_SUMMARY); // → SUMMARY sheet
const dataRange = shOutbound.getDataRange(); // → full range
const values = dataRange.getValues(); // → 2D array
if (values.length < 2) { // → only header
throw new Error('No data in OUTBOUND.'); // → error
}
const header = values[0]; // → header row
const rows = values.slice(1); // → data rows
const cLoad = LOAD_CONFIG.COL_OUTBOUND.LOAD_NO; // → Load column
const cItem = LOAD_CONFIG.COL_OUTBOUND.ITEM; // → item column
const cQty = LOAD_CONFIG.COL_OUTBOUND.QTY; // → quantity column
const cPallet = LOAD_CONFIG.COL_OUTBOUND.PALLET; // → pallet column
const cWeight = LOAD_CONFIG.COL_OUTBOUND.WEIGHT; // → weight column
const cOrder = LOAD_CONFIG.COL_OUTBOUND.ORDER_NO; // → order number column
const itemMap = {}; // → per-ITEM aggregation
const orderSet = {}; // → order number seen set
const duplicatedOrders = []; // → duplicate order list
const warnings = []; // → warning messages
let totalWeight = 0; // → Load total weight
let totalPallet = 0; // → Load total pallets
let rowCount = 0; // → row count for this Load
rows.forEach((row, idx) => { // → iterate each row
const rowLoad = String(row[cLoad - 1] || '').trim(); // → Load value
if (rowLoad !== String(loadNo).trim()) { // → if other Load
return; // → skip
}
rowCount++; // → increment row count
const item = String(row[cItem - 1] || '').trim(); // → ITEM
const qty = Number(row[cQty - 1] || 0); // → quantity as number
const pallet = Number(row[cPallet - 1] || 0); // → pallets
const weight = Number(row[cWeight - 1] || 0); // → weight
const orderNo = String(row[cOrder - 1] || '').trim(); // → order number
if (!item) { // → missing item
warnings.push('Missing ITEM: OUTBOUND row ' + (idx + 2)); // → log row position
}
if (!Number.isFinite(qty) || // → quantity is not a number
!Number.isFinite(pallet) || // → pallets is not a number
!Number.isFinite(weight)) { // → weight is not a number
warnings.push('Value is not a number: OUTBOUND row ' + (idx + 2) +
' — check qty / pallet / weight'); // → log the row and the cause
return; // → skip the row so totals never turn NaN
}
if (qty === 0) { // → zero quantity
warnings.push('Quantity 0: OUTBOUND row ' + (idx + 2)); // → log row position
}
if (qty < 0) { // → negative quantity
warnings.push('Negative quantity: OUTBOUND row ' + (idx + 2)); // → log row position
}
if (orderNo) { // → if order number exists
if (orderSet[orderNo]) { // → already seen
duplicatedOrders.push(orderNo); // → record as duplicate
} else { // → first time
orderSet[orderNo] = true; // → add to set
}
}
if (!itemMap[item]) { // → first time for ITEM
itemMap[item] = { // → create aggregation record
qty: 0, // → quantity
pallet: 0, // → pallets
weight: 0 // → weight
};
}
itemMap[item].qty += qty; // → sum quantity
itemMap[item].pallet += pallet; // → sum pallets
itemMap[item].weight += weight; // → sum weight
totalWeight += weight; // → add to Load total weight
totalPallet += pallet; // → add to Load total pallets
});
if (rowCount === 0) { // → no rows for this Load
throw new Error('Load' + loadNo + 'not found in OUTBOUND.'); // → error
}
// Build data to output into SUMMARY
const now = formatDateTime_(new Date()); // → timestamp
const output = []; // → output array
output.push(['Load summary', loadNo, now, '']); // → title row
output.push(['ITEM', 'TOTAL_QTY', 'TOTAL_PALLET', 'TOTAL_WEIGHT']); // → header
Object.keys(itemMap).sort().forEach(item => { // → sort by ITEM
const rec = itemMap[item]; // → ITEM aggregate
output.push([ // → append row
item, // → item
rec.qty, // → total quantity
rec.pallet, // → total pallets
rec.weight // → total weight
]);
});
output.push(['', '', '', '']); // → blank line
output.push(['Load total', '', totalPallet, totalWeight]); // → overall Load totals
if (duplicatedOrders.length > 0) { // → if duplicates exist
warnings.push('Duplicate order number (within same Load):' + duplicatedOrders.join(', ')); // → add message
}
if (warnings.length > 0) { // → if there are warnings
output.push(['', '', '', '']); // → blank line
output.push(['Warning', 'Details', '', '']); // → warning header
warnings.forEach(msg => { // → each warning
output.push(['', msg, '', '']); // → append row
});
}
// Paste into SUMMARY sheet (one block per Load)
const lastRow = shSummary.getLastRow(); // → current last row
const startRow = lastRow === 0 ? 1 : lastRow + 2; // → block start row
const range = shSummary.getRange(startRow, 1, output.length, 4); // → output range
range.setValues(output); // → write values
return { rowCount, totalPallet, totalWeight, warnings, duplicatedOrders }; // → summary of results
}
function testBuildLoadSummary() { // → test function
const testLoadNo = 'L20260809-01'; // → sample Load number
const result = buildLoadSummary(testLoadNo); // → run summary
Logger.log(JSON.stringify(result)); // → log result
}How to check it: Add 3–5 sample rows with Load L20260809-01 in LOADS and OUTBOUND, then run testBuildLoadSummary. If a new summary block appears at the bottom of SUMMARY, it’s working. Then put ABC in one QTY cell on purpose and run it again: the totals should stay numeric and the warnings should contain a line like Value is not a number: OUTBOUND row 3.
Closing a Load: Changing STATUS to LOADED with closeLoad
If you only create summaries and don’t change the Load status, you’ll later lose track of which Loads have already shipped. To really deploy per-load outbound automation, you should bundle in a closing step. Here, the closeLoad(loadNo) function updates STATUS to LOADED in the LOADS sheet, and uses a lock to prevent conflicts when multiple users run it.
This code changes a specific Load’s STATUS to LOADED to mark loading complete.
Where to paste: In Code.gs, below buildLoadSummary.
What to do after pasting: Save, then run testCloseLoad.
function closeLoad(loadNo) { // → close Load status
if (!loadNo) { // → validate input
throw new Error('Enter a Load number.'); // → error message
}
const lock = LockService.getScriptLock(); // → script lock object
try { // → attempt to lock
lock.waitLock(5000); // → wait up to 5 seconds
const shLoads = getSheet_(LOAD_CONFIG.SHEET_LOADS); // → LOADS sheet
const dataRange = shLoads.getDataRange(); // → full range
const values = dataRange.getValues(); // → 2D array
if (values.length < 2) { // → only header
throw new Error('No data in LOADS.'); // → error
}
const cLoad = LOAD_CONFIG.COL_LOADS.LOAD_NO; // → Load column
const cStatus = LOAD_CONFIG.COL_LOADS.STATUS; // → status column
let targetRow = -1; // → found row index
for (let i = 1; i < values.length; i++) { // → loop from row 2
const rowLoad = String(values[i][cLoad - 1] || '').trim(); // → Load value
if (rowLoad === String(loadNo).trim()) { // → if match
targetRow = i + 1; // → actual row number
break; // → stop loop
}
}
if (targetRow === -1) { // → not found
throw new Error('Load' + loadNo + 'not found in LOADS.'); // → error
}
const statusCell = shLoads.getRange(targetRow, cStatus); // → STATUS cell
const currentStatus = String(statusCell.getValue() || '').trim(); // → current status
if (currentStatus === LOAD_CONFIG.STATUS.LOADED) { // → already LOADED
Logger.log('Already in LOADED status:' + loadNo); // → just log
return; // → exit
}
statusCell.setValue(LOAD_CONFIG.STATUS.LOADED); // → set to LOADED
Logger.log('Load status updated:' + loadNo); // → log completion
} catch (e) { // → error handling
throw e; // → rethrow
} finally { // → always run
lock.releaseLock(); // → release lock
}
}
function testCloseLoad() { // → test closeLoad
const testLoadNo = 'L20260809-01'; // → sample Load number
closeLoad(testLoadNo); // → run status change
}How to check it: In LOADS, create a row for L20260809-01 with STATUS OPEN, then run testCloseLoad. If STATUS changes to LOADED, it’s working.
One‑click from the menu: A user‑friendly outbound summary menu
Running test functions from the Apps Script editor isn’t realistic on the warehouse floor. For warehouse staff to use this directly, add a “Create Load summary” item to the shared “Warehouse Tools” menu that appears when the sheet opens and prompts for a Load number, then runs the summary. That’s much closer to real‑world usage.
The code below adds a custom menu when the sheet opens and prompts for a Load number before calling buildLoadSummary. If needed, you can uncomment one line to chain closeLoad and finish everything in one go.
Where to paste: In Code.gs, under the previous code.
What to do after pasting: Save, then reload the sheet.
function onOpen() { // → runs when sheet opens
const menu = SpreadsheetApp.getUi() // → user interface
.createMenu('Warehouse Tools'); // → menu shared by the series
addLoadMenu_(menu); // → attach this part's items
menu.addToUi(); // → add menu to UI
}
function addLoadMenu_(menu) { // → when merging, call only this
menu.addItem('Create Load summary', 'menuBuildLoadSummary'); // → menu item
}
function menuBuildLoadSummary() { // → called from menu
const ui = SpreadsheetApp.getUi(); // → UI object
const resp = ui.prompt( // → show input dialog
'Enter Load number', // → title
'Enter the Load number to summarize.', // → prompt text
ui.ButtonSet.OK_CANCEL // → buttons
);
if (resp.getSelectedButton() !== ui.Button.OK) { // → if cancelled
return; // → exit
}
const loadNo = resp.getResponseText().trim(); // → input value
if (!loadNo) { // → empty value
ui.alert('Load number is empty.'); // → warning
return; // → exit
}
try { // → try execution
const result = buildLoadSummary(loadNo); // → run summary
// Uncomment the line below to mark the Load as LOADED automatically
// closeLoad(loadNo); // → close Load
let msg = 'Load summary created:' + loadNo; // → base message
msg += '\nRow count:' + result.rowCount; // → row count
msg += '\nTotal pallets:' + result.totalPallet; // → total pallets
msg += '\nTotal weight:' + result.totalWeight; // → total weight
if (result.warnings.length > 0) { // → if warnings
msg += '\nWarnings:' + result.warnings.length; // → warning count
}
ui.alert(msg); // → show result
} catch (e) { // → catch errors
ui.alert('Error:' + e.message); // → show error message
}
}How to check it: Reload the sheet. If you see a “Warehouse Tools” menu at the top, click “Create Load summary,” enter a Load number, and verify that a new block appears in SUMMARY and a completion popup appears.
Merging several parts into one project: if you paste this next to an earlier part, onOpen() is declared twice — only the last one survives and the earlier menu disappears. That is why every part of this series uses the same menu name, Warehouse Tools, and keeps its own entries in a helper such as addLoadMenu_(menu). To merge, delete this part's onOpen() and add a single line — addLoadMenu_(menu); — inside the onOpen() from Part 1. Everything then lives under one Warehouse Tools menu: inbound, outbound, stock, rack layout, barcode scan and Load summary. Do the same with constants that repeat across parts (SHEET_INBOUND and friends): keep one copy and delete the rest, because declaring the same const twice in one project is an error by itself.
The finished onOpen() once the whole series is merged
If you have collected Part 1 through this post into one project, delete each part's own onOpen() and keep only the one below. Leave every add…Menu_ helper as it is.
function onOpen() { // → runs when the sheet opens
const menu = SpreadsheetApp.getUi().createMenu('Warehouse Tools'); // → build the menu once
addInboundMenu_(menu); // → Part 1 · Open Inbound UI
addStockMenu_(menu); // → Part 3 · Refresh Stock
addFloorMapMenu_(menu); // → Part 4 · Refresh rack layout
addScanMenu_(menu); // → Part 6 · Open Scan Input
addLoadMenu_(menu); // → Part 7 · Create Load summary
menu.addToUi(); // → attach it to the sheet
}If you have only added some of the parts, keep those lines and delete the rest. Part 2 (outbound) lives inside the Part 1 sidebar and Part 5 (daily report) runs on a time-driven trigger, so neither adds a menu item of its own.
Practical tips: Pre‑deployment checklist and operating guidelines
Before rolling this out on the floor, test thoroughly with a small set of sample data. I recommend this simple checklist:
First, create a test Load with 3–5 sample rows. In LOADS, create a dummy Load like LTEST-01 and set STATUS to OPEN. In OUTBOUND, add 2–3 ITEMs and 2–3 order numbers with the same Load number. Intentionally include one or two rows with quantity 0 or missing ITEM to confirm that warnings are emitted.
Second, reproduce summaries and warnings. Run LTEST-01 from the menu or testBuildLoadSummary and inspect the block in SUMMARY. Check that ITEM totals match your manual calculations and that zero quantity, negative quantity, missing ITEM, and duplicate order numbers within the same Load all appear correctly in the warning area. This is where you validate that your Google Sheets Apps Script outbound aggregation is in sync with real‑world data.
Third, confirm closeLoad behavior. Run testCloseLoad or uncomment the closeLoad call in the menu handler and try closing the test Load as LOADED. The LockService prevents two users from closing the same Load at the exact same time, so just confirm that STATUS changes to LOADED exactly once as intended.
Fourth, document operating rules. It helps to clearly document who runs the summary and when. For example: “After picking is complete, the dock operator runs the per‑Load outbound summary to validate, and if everything looks good, truck loading starts.” With rules like this, the script naturally embeds itself into the team’s workflow.
If you’re not yet comfortable with sheet design and basic automation, it’s better to first go through the earlier post on inventory structure and movement logic, Build a Warehouse Inventory System in Google Sheets with Apps Script, then add this Load summary function on top.
Conclusion: Start with one test Load today
Once you set up this Google Sheets logistics summary script, you can just click the same menu every time a truck leaves and automatically generate a per‑Load outbound summary. You’ll cut down on manual filtering and calculator checks in Excel, and you’ll get consistent, repeatable checks for quantities, pallets, weight, and duplicate orders.
A concrete next step is to create three sheets—LOADS, OUTBOUND, and SUMMARY—in the structure above, enter one test Load, and click the Create Load summary menu. After one run, you’ll naturally start to see which additional fields you’d like to aggregate and what other warnings you’d like to add. From there, you can gradually extend LOAD_CONFIG and the script to fit your operation.
