Smart Life US
Google Sheets activity log automation | Apps Script 9
Introduction — the problem and what we’ll cover
Even if the staff in charge of inbound/outbound changes, you need to be able to immediately tell who saved what so responsibility for warehouse work is clear. When you manage inventory with Google Sheets, questions like “Who changed this quantity?” and “When was this outbound processed?” come up frequently. With only the built‑in features, tracking cell change history is cumbersome, and when several people work at the same time it’s hard to see at a glance who did what. This post introduces a practical pattern for implementing automatic Google Sheets activity logging using Apps Script to reduce exactly these situations.
This is part 9 of the series that started with Build a warehouse inventory system in Google Sheets | Apps Script automation and has covered inbound/outbound automation, aggregation, reports, rack layout, barcode scanning, outbound summaries, and dashboards. We’ll assume that inbound, outbound, and inventory aggregation are already automated, and that records are saved when the person in charge simply clicks a button. On top of that, the goal of this post is to add a LOG sheet that automatically accumulates who entered what and when, so that when something goes wrong you can trace it back with a Google Sheets Apps Script logging structure.
Designing the LOG sheet — only what you need, nothing excessive
When you design automatic Google Sheets user activity logging, the first decision is “what do we log?”. After operating several warehouses in practice, we found that six pieces of data are enough to trace inventory and outbound work:
- TIMESTAMP: when the action occurred
- USER: who executed it
- ACTION: what kind of action (e.g., PUTAWAY_SAVE, PICKING_SAVE, etc.)
- TARGET: what entity was touched (e.g., order number, pallet ID, etc.)
- BEFORE: summary of the value before the change
- AFTER: summary of the value after the change
With just this much you can easily answer “Who adjusted inventory for this order, and when?” or “What does a particular user’s activity pattern look like?”. To keep personal data minimal, it’s safer to log a single line such as the Google account email or an internal ID instead of a full name. In environments with frequent external sharing, managing a separate internal ID worked better.
You don’t have to create the LOG sheet by hand. The createLogSheet_() function below creates the sheet and the six headers only if it doesn’t exist; if it already exists, it just uses it. The LOG sheet will be write‑only from Apps Script and read‑only for users; they shouldn’t edit it directly. If needed, you can create a separate dashboard sheet—as in Google Sheets dashboard with Apps Script: one‑glance warehouse status—to summarize recent history with filters or pivot tables.
LOG sheet and base config code — user identification and retention in one place
Once the LOG sheet exists, you need a shared configuration section in Apps Script. It’s helpful to centrally control which sheet you log to, whether you store users as emails or IDs, and how many rows of logs you keep.
This code groups together the LOG sheet name and user identification method.
Where to paste: Google Sheets → Extensions → Apps Script → top of Code.gs.
After pasting: save (⌘S or Ctrl+S), then run testCreateLogSheet_ once from the function list.
// Change only this block to fit your environment
const LOG_CONFIG = { // → Config collection for this part
SHEET_LOG: 'LOG', // → Activity log sheet name
SHEET_SETTINGS: 'SETTINGS', // → SETTINGS sheet from part 2 (for ID mapping)
USER_MODE: 'EMAIL', // → 'EMAIL' or 'ID'
SETTINGS_ID_COL: 1, // → SETTINGS column A: internal ID
SETTINGS_EMAIL_COL: 2, // → SETTINGS column B: Google account email
HEADER: ['TIMESTAMP', 'USER', 'ACTION', 'TARGET', 'BEFORE', 'AFTER'], // → LOG headers
MAX_LOG_ROWS: 5000, // → Max number of data rows to keep in LOG
ARCHIVE_PREFIX: 'LOG_', // → Prefix for archive sheets (LOG_2026-08)
TIMEZONE: 'America/New_York' // → Time zone for naming archive sheets
};
// Create LOG sheet with headers if it doesn't exist; otherwise reuse it.
function createLogSheet_() { // → Prepare LOG sheet
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
let sh = ss.getSheetByName(LOG_CONFIG.SHEET_LOG); // → Existing LOG sheet
if (!sh) { // → If not found
sh = ss.insertSheet(LOG_CONFIG.SHEET_LOG); // → Create new
}
const head = sh.getRange(1, 1, 1, LOG_CONFIG.HEADER.length); // → Header range
if (String(head.getValues()[0][0] || '').trim() !== LOG_CONFIG.HEADER[0]) {
head.setValues([LOG_CONFIG.HEADER]).setFontWeight('bold'); // → Write headers
sh.setFrozenRows(1); // → Freeze first row
}
return sh; // → Return sheet object
}
// User name to log. EMAIL mode: email; ID mode: internal ID from SETTINGS.
function getLogUser_() { // → User identification
const email = String(Session.getActiveUser().getEmail() || '').trim(); // → Session email
if (LOG_CONFIG.USER_MODE !== 'ID') { // → EMAIL mode
return email || 'UNKNOWN'; // → Return email
}
const sh = SpreadsheetApp.getActive().getSheetByName(LOG_CONFIG.SHEET_SETTINGS);
if (!sh || sh.getLastRow() < 2 || !email) { // → If mapping table is missing
return email || 'UNKNOWN'; // → Fall back to email
}
const wide = Math.max(LOG_CONFIG.SETTINGS_ID_COL, LOG_CONFIG.SETTINGS_EMAIL_COL);
const rows = sh.getRange(2, 1, sh.getLastRow() - 1, wide).getValues(); // → Read mapping table
for (let i = 0; i < rows.length; i++) { // → Check row by row
const mail = String(rows[i][LOG_CONFIG.SETTINGS_EMAIL_COL - 1] || '').trim();
if (mail.toLowerCase() === email.toLowerCase()) { // → Case-insensitive compare
const id = String(rows[i][LOG_CONFIG.SETTINGS_ID_COL - 1] || '').trim();
if (id) return id; // → Use internal ID
}
}
return email; // → If not in mapping, return email
}
function testCreateLogSheet_() { // → Helper for running from editor
const sh = createLogSheet_(); // → Prepare sheet
Logger.log('LOG headers: ' + JSON.stringify(
sh.getRange(1, 1, 1, LOG_CONFIG.HEADER.length).getValues()[0])); // → Verify via logs
Logger.log('Current user: ' + getLogUser_()); // → Check how the user is recorded
}How to verify: run testCreateLogSheet_ and check the execution log. You should see ["TIMESTAMP","USER","ACTION","TARGET","BEFORE","AFTER"] plus the current user name. If a LOG tab appears in the sheet, you’re ready.
writeLog function — one shared call from all save functions
Next is the core LOG recording function. The goal is straightforward: from any save function (inbound, outbound, inventory adjustments, etc.) you should be able to call a single shared function and automatically leave a record in the LOG sheet. Once this is in place, you can change your saving logic freely and still reuse the writeLog part.
This code appends one activity row to the last row of the LOG sheet.
Where to paste: same Apps Script project, in Code.gs, right under getLogUser_.
After pasting: save, then run testWriteLog_() once to grant permissions.
// Add a single activity row to the LOG sheet.
// If you write to getLastRow() + 1 directly, two people saving at the same time can both target
// the same row and overwrite each other's log. appendRow is documented by Google as atomic,
// so this problem doesn't occur. However, "rows won't collide" does *not* mean your
// surrounding work will be treated as a single atomic transaction.
function writeLog_(action, target, beforeText, afterText) { // → Record a single log entry
const sh = createLogSheet_(); // → LOG sheet (create if missing)
sh.appendRow([ // → Safely append after the last row
new Date(), // → TIMESTAMP
getLogUser_(), // → USER
String(action || ''), // → ACTION
String(target || ''), // → TARGET
(beforeText === undefined || beforeText === null) ? '' : String(beforeText), // → BEFORE
(afterText === undefined || afterText === null) ? '' : String(afterText) // → AFTER
]);
}
function testWriteLog_() { // → Helper for running from editor
writeLog_('TEST_ACTION', 'TEST_TARGET', 'Before value example', 'After value example'); // → Write one row
const sh = createLogSheet_(); // → LOG sheet
const last = sh.getRange(sh.getLastRow(), 1, 1, LOG_CONFIG.HEADER.length).getValues()[0];
Logger.log('Last written row: ' + JSON.stringify(last)); // → Verify via logs
}How to verify: select and run testWriteLog_ in the editor. You should see the last row echoed in the execution log, and a TEST_ACTION row added to the end of the LOG sheet. Run it twice and confirm that you get two rows. If you see only one, you’re overwriting a specific row instead of appending.
archiveLog for retention — keeping LOG from growing too large
In real use, your LOG sheet will quickly reach tens of thousands of rows in just a few months. As the row count grows, filters, sorts, and pivots in Google Sheets become noticeably slower. To avoid that, it’s better to move older records into monthly archive sheets (LOG_YYYY-MM) once the sheet passes a certain number of rows.
This code checks if the LOG sheet’s data row count exceeds MAX_LOG_ROWS. If it does, it moves the oldest rows into archive sheets. Each archive sheet name is based on the month the log row was written, not the month you run the cleanup. If you use the cleanup date, logs from March might end up in LOG_2026-08, which isn’t really monthly archiving. When moving multiple months at once, logs are split across LOG_2026-03, LOG_2026-04, etc. Reading, writing, and deletion must complete as a single unit, so the whole process is wrapped in LockService. Otherwise, two people could hit the menu at the same time, read the same rows, each add them to an archive sheet, and then both delete them from LOG.
Where to paste: right under the writeLog_ function.
After pasting: run testArchiveLog_() to confirm archive sheet creation.
// When LOG gets too big, move older rows into a per-month sheet based on each row's timestamp.
// If you name archives using the cleanup date instead, March logs can get mixed into LOG_2026-08,
// which defeats the idea of monthly archiving.
function archiveLog_() { // → Archive old logs (with concurrency lock)
// If two people trigger "Archive old logs" at the same time, they can both read the same rows,
// insert them into the archive sheet twice, and then delete rows from LOG that have already
// been moved. Reading, writing, and deleting must be treated as a single unit.
const lock = LockService.getScriptLock(); // → Global script lock
lock.waitLock(30000); // → Wait up to 30 seconds (throw error if not obtained)
try {
return archiveLogCore_(); // → Actual work is in the function below
} finally {
lock.releaseLock(); // → Always release, success or failure
}
}
function archiveLogCore_() { // → Actual archive work (call only under lock)
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sh = ss.getSheetByName(LOG_CONFIG.SHEET_LOG); // → LOG sheet
if (!sh) return 0; // → Nothing to do if missing
const dataRows = sh.getLastRow() - 1; // → Data row count excluding header
if (dataRows <= LOG_CONFIG.MAX_LOG_ROWS) return 0; // → Nothing to archive yet
const moveCount = dataRows - LOG_CONFIG.MAX_LOG_ROWS; // → Number of rows to move
const cols = LOG_CONFIG.HEADER.length; // → Column count
const values = sh.getRange(2, 1, moveCount, cols).getValues(); // → Oldest rows first
const byMonth = {}; // → Bucket rows by month
values.forEach(function (row) { // → Row by row
const d = (row[0] instanceof Date) ? row[0] : new Date(row[0]); // → TIMESTAMP
const key = isNaN(d.getTime()) // → If timestamp can't be parsed
? 'UNKNOWN' // → Group under UNKNOWN
: Utilities.formatDate(d, LOG_CONFIG.TIMEZONE, 'yyyy-MM'); // → Month of record
if (!byMonth[key]) byMonth[key] = []; // → Initialize bucket if needed
byMonth[key].push(row); // → Add row to its month
});
Object.keys(byMonth).sort().forEach(function (key) { // → Process months in order
const name = LOG_CONFIG.ARCHIVE_PREFIX + key; // → e.g. LOG_2026-03
let arc = ss.getSheetByName(name); // → Find archive sheet
if (!arc) { // → If missing
arc = ss.insertSheet(name); // → Create it
arc.getRange(1, 1, 1, cols).setValues([LOG_CONFIG.HEADER]).setFontWeight('bold');
arc.setFrozenRows(1); // → Freeze header
}
const rows = byMonth[key]; // → Rows for this month
arc.getRange(arc.getLastRow() + 1, 1, rows.length, cols).setValues(rows); // → Append
});
sh.deleteRows(2, moveCount); // → Delete moved rows (clear, don't leave blanks)
Logger.log('Archived ' + moveCount + ' rows → ' + Object.keys(byMonth).sort().join(', '));
return moveCount; // → Return number of rows moved
}
function testArchiveLog_() { // → Helper for running from editor
Logger.log('Rows moved: ' + archiveLog_()); // → Log the result
}How to verify: temporarily set MAX_LOG_ROWS to something like 5, run testWriteLog_ several times, then run testArchiveLog_(). You should see new tabs like LOG_2026-08, and the oldest rows in LOG should have moved there. When you’re done testing, set MAX_LOG_ROWS back to 5000.
Adding logs to save functions — with Lock and numeric validation
Now you can attach logs to your actual save functions and complete Google Sheets inbound/outbound history tracking. You should already have savePutaway from part 1 and savePicking from part 2. Instead of rewriting those, we’ll wrap them in a new function. The original functions stay untouched, and you only change the name that the sidebar calls, so it’s easy to roll back. Below is an example wrapper around savePutaway.
In practice, three things were especially important:
- Use
LockServiceinside the saving function so multiple users can save at the same time without corrupting data. - Validate required fields (order number, item, location, etc.) and numeric quantities before saving.
- Call
writeLogonly for rows that were successfully saved, so failed operations don’t get logged.
Here’s a pattern for adding logs to the putaway save function.
Where to paste: under the archiveLog_ function. Keep the part 1 code unchanged.
After pasting: in the part 1 sidebar (Sidebar.html), change the one line that calls .savePutaway(data) to .savePutawayWithLog(data).
// Wrap part 1's savePutaway to add logging. Part 1's code remains unchanged.
// In the sidebar, just change the call name from savePutaway → savePutawayWithLog.
const DONE_STATUS_ = 'PUTAWAY_DONE'; // → Completion status value set by part 1's savePutaway
function savePutawayWithLog(data) { // → Putaway save + log
const container = String((data && data.container) || '').trim(); // → Container number
const model = String((data && data.model) || '').trim(); // → Model
const before = readInboundStatus_(container); // → Read status before change
const result = savePutaway(data); // → Call part 1's save function as is
// (If this throws, no log will be written)
// Do not re‑read AFTER values from the sheet. If you re‑read, another user could change
// the same record in between and their values would appear in *your* log, plus you pay
// the cost of scanning INBOUND again.
writeLog_( // → Log only after save completes
'PUTAWAY_SAVE', // → ACTION
container + ' / ' + model, // → TARGET
'STATUS=' + (before || '(blank)'), // → BEFORE
'STATUS=' + DONE_STATUS_ + // → AFTER (the status this save produces)
', LOC=' + String((data && data.location) || '') +
', QTY=' + String((data && data.qty) || '')
);
return result; // → Return original result to the sidebar
}
// Read current status (column F) of this container from INBOUND. Used for BEFORE/AFTER.
function readInboundStatus_(container) { // → Read status
if (!container) return ''; // → Empty if container is missing
const sh = SpreadsheetApp.getActive().getSheetByName('INBOUND'); // → INBOUND sheet from part 1
if (!sh || sh.getLastRow() < 2) return ''; // → Missing or empty
const rows = sh.getRange(2, 1, sh.getLastRow() - 1, 6).getValues(); // → Columns A–F
for (let i = 0; i < rows.length; i++) { // → Scan row by row
if (String(rows[i][0]).trim() === container) { // → Match container in column A
return String(rows[i][5] || '').trim(); // → Return STATUS from column F
}
}
return ''; // → Not found
}
function onOpen() { // → Auto‑run when sheet is opened
const menu = SpreadsheetApp.getUi().createMenu('Warehouse Tools'); // → Shared menu for the series
// If earlier parts are already in the project, their menus are attached too.
// Without these lines, merging several parts leaves **only the last onOpen alive**
// and the earlier menus vanish without a single error.
if (typeof addInboundMenu_ === 'function') { addInboundMenu_(menu); } // → Part 1 receiving
if (typeof addStockMenu_ === 'function') { addStockMenu_(menu); } // → Part 3 stock
if (typeof addFloorMapMenu_ === 'function') { addFloorMapMenu_(menu); } // → Part 4 floor map
if (typeof addScanMenu_ === 'function') { addScanMenu_(menu); } // → Part 6 barcode
if (typeof addLoadMenu_ === 'function') { addLoadMenu_(menu); } // → Part 7 load summary
if (typeof addDashboardMenu_ === 'function') { addDashboardMenu_(menu); } // → Part 8 dashboard
addLogMenu_(menu); // → Add this part's items (Part 9)
menu.addToUi(); // → Attach to sheet UI
}
function addLogMenu_(menu) { // → Only this is called when integrating parts
menu.addItem('Archive old logs', 'archiveLog_'); // → Menu item
}One clarification: this wrapper pattern does not use a single lock for both save and log. If savePutaway itself uses a lock, the save is safe, but another user could still change the same container between the time you read BEFORE and the time you save, so your BEFORE value might reflect their changes. In a typical warehouse where one person owns a given container, this isn’t an issue. But if you absolutely require the BEFORE/AFTER pair to refer to one and only one operation, don’t wrap—edit savePutaway from part 1 and insert writeLog_(...) directly inside its try block just above the return. That way, save and log complete under the same lock, with perfect consistency, at the cost of modifying the original code.
How to verify: save a putaway operation from the sidebar. As in part 1, one row should be added to LOCATIONS, and the INBOUND status should switch to PUTAWAY_DONE. In addition, a row with PUTAWAY_SAVE should appear in the LOG sheet. If BEFORE shows STATUS=(blank) and AFTER shows STATUS=PUTAWAY_DONE, LOC=…, QTY=…, then Google Sheets inbound/outbound history tracking is correctly wired.
When combining multiple parts into one project: if you simply paste this into the same Apps Script project as previous parts, you’ll end up declaring onOpen() twice, and only the last one will work—earlier menus disappear. To avoid that, this series uses a single menu name, Warehouse Tools, and each part’s items go into a helper like addLogMenu_(menu). When merging, delete the onOpen() in this part, and add one line addLogMenu_(menu); inside the existing onOpen() from part 1 instead. That way all features—Inbound, Outbound, Inventory, Rack layout, Barcode scan, Load summary, Dashboard, and Activity log—appear under one “Warehouse Tools” menu. You can extend the outbound save function using the same pattern to call writeLog_('PICKING_SAVE', …).
Practical tips — handling personal data, performance, and errors
Here are some lessons learned from applying Google Sheets user activity logging in real operations.
First, be aware that Session.getActiveUser().getEmail() does not always return a value. For personal Gmail accounts, external users, and some execution contexts (like certain installable triggers), it may return an empty string, which becomes UNKNOWN in your logs. This doesn’t break anything, but you cannot assume “we can always tell who did what.” In your actual account environment, run testWriteLog_ once and confirm that USER shows correctly. If it’s empty, it’s safer to set USER_MODE to ID and rely on the mapping table.
Second, minimize personal data. Storing full email addresses in USER makes tracking easy but may be problematic if LOG is ever included in externally shared reports. In that case, setting USER_MODE to ID lets you log only internal IDs by reading the A (internal ID) and B (email) columns in the SETTINGS sheet from part 2. When issues arise, only admins consult the mapping table to find the actual user.
Third, treat the LOG sheet as read‑only + hidden. If frontline staff accidentally delete or edit rows in LOG, later root‑cause analysis becomes difficult. Hide the LOG sheet and expose only what you need via a separate view sheet using FILTER or Apps Script. For example, next to an order detail view you can add a small table that “shows only LOG entries for this order,” which ties in naturally with reports like Automating outbound summary in Google Sheets.
Fourth, don’t over‑granularize logs if you care about performance. If one button click starts writing multiple log rows, your row count grows explosively. For typical inventory and outbound workflows, “one button = one log row” turned out to be the sweet spot. For exceptional bulk adjustments, you can make BEFORE/AFTER more descriptive, but still keep it to a single summary row.
Finally, don’t hide errors—show the row number and reason. In parts 1 and 2 the save functions already throw errors like “Quantity must be a number greater than or equal to 1,” and the wrapper simply passes them through. Because save failures throw before reaching writeLog_, failed operations are not recorded in LOG, which is intentional. In my experience, it’s cleaner to show failed attempts only as on‑screen pop‑ups and reserve LOG for completed saves, which keeps analysis and maintenance simpler.
Conclusion — start by creating a LOG sheet today
When you run inventory on Google Sheets, someone will inevitably ask, “Who changed this value and when?”. By using a LOG sheet and Apps Script to automatically record activity, you can answer in seconds and dramatically reduce finger‑pointing between staff.
The Google Sheets activity log automation structure covered here is something you set up once and then extend easily: for any future inbound, outbound, or adjustment function, just add a single writeLog_ call.
A concrete next step: open your inventory Google Sheet, create a LOG sheet, paste in LOG_CONFIG, createLogSheet_, getLogUser_, writeLog_, and archiveLog_ in order, then run testWriteLog_() once. From the moment that first test row appears in LOG, your sheet stops being “a spreadsheet where it’s unclear who changed what” and starts functioning as a system where you can always retrace actions with evidence.
