Smart Life US
Google Sheets Apps Script Error Handling & Backup | LockService · try/catch · DriveApp Backup
Introduction – Automation runs well, but if it breaks once, it’s over
When you automate warehouse or inventory operations with Google Sheets and Apps Script, there comes a time when protecting the system from breaking becomes more important than simply “making it run.” If a single button press triggers hundreds of inbound/outbound rows, stock aggregation, and dashboard updates, one error or concurrent save conflict can corrupt the data in a way that makes it nearly impossible to recover that day’s work.
This post summarizes error handling and backup patterns for Google Sheets Apps Script that I’ve actually used in real operations. The core consists of three elements. First, use try/catch to catch errors and log them to a dedicated ERROR_LOG sheet. Second, use LockService to reduce concurrent save conflicts. Third, use DriveApp to create automatic backups so that even if someone accidentally breaks something, you have a restorable version. On top of that, we’ll look at how to split edit permissions using Google Sheets protected ranges (from the UI), so you can apply this in the field right away.
Creating an ERROR_LOG sheet – Recording errors with Apps Script try/catch
Once you add a lot of automation, you’ll often hear “I clicked the button and nothing happened.” As an admin, you can open the Apps Script execution logs and find the cause, but you can’t really expect that from people who just press the button on site. So I use a separate ERROR_LOG sheet where anyone can see “when which function failed and why” just by opening the spreadsheet.
The structure is simple. You leave the original “worker” function as it is, and add a common wrapper outside it. The wrapper runs the original function inside try, and in catch it appends a row to the ERROR_LOG sheet. If you prepare four columns—time, function name, message, details—that’s usually enough to investigate most incidents.
Two points matter here. First, since other code may be added to the same project, add a prefix to configuration constants and helper functions to avoid name collisions. In this post I use the prefix WARE_ERROR_. Second, if you log not only the error message but also “under what circumstance it ran” as the detail, it becomes much easier to reproduce and diagnose issues later.
The code below is a helper that receives errors and appends them to the ERROR_LOG sheet.
This code is “a helper that logs one line to the ERROR_LOG sheet for each error.”
Where to paste: Google Sheets → Extensions → Apps Script → at the very bottom of Code.gs.
After pasting: Save (⌘S / CTRL+S), then run testWareErrorLog_() once and check that ERROR_LOG is created.
const WARE_ERROR_CONFIG = { // → Settings used only in this post
LOG_SHEET_NAME: 'ERROR_LOG', // → Error log sheet name
TIMEZONE: 'America/New_York', // → Timezone
};
// → Prepare error log sheet (create if missing)
function WARE_getErrorLogSheet_() { // → Internal helper
const ss = SpreadsheetApp.getActiveSpreadsheet(); // → Current spreadsheet
let sheet = ss.getSheetByName(WARE_ERROR_CONFIG.LOG_SHEET_NAME); // → Find log sheet
if (!sheet) { // → If not found
sheet = ss.insertSheet(WARE_ERROR_CONFIG.LOG_SHEET_NAME); // → Create new sheet
sheet.appendRow(['Time', 'Function', 'Message', 'Details']); // → Add header
}
return sheet; // → Return sheet
}
// → Receive an exception and log one line into ERROR_LOG
function WARE_logError_(funcName, error, detail) { // → Function name, exception, details
const sheet = WARE_getErrorLogSheet_(); // → Get error log sheet
const now = new Date(); // → Current time
const time = Utilities.formatDate( // → Convert to string
now,
WARE_ERROR_CONFIG.TIMEZONE,
'yyyy-MM-dd HH:mm:ss'
);
const message = (error && error.message) ? error.message : String(error); // → Message
const detailText = detail ? String(detail) : ''; // → Detail as string
sheet.appendRow([time, funcName, message, detailText]); // → Append one row
}
// → Wrap a function with this so errors are logged automatically
function WARE_runSafely_(func, funcName, detail) { // → Function to run and its name
try { // → Try
return func(); // → Run original function
} catch (e) { // → If exception occurs
WARE_logError_(funcName, e, detail); // → Log to ERROR_LOG
throw e; // → Rethrow to notify user
}
}
// → Test that the logging feature works correctly
function testWareErrorLog_() { // → Test-only function
try { // → Run test
WARE_runSafely_(function () { // → Wrap with runner
throw new Error('This is a test error'); // → Force an error
}, 'testWareErrorLog_', 'Test run'); // → Function name & details
} catch (e) { // → Ignore exception
}
}After running testWareErrorLog_() in the editor, if you see a new ERROR_LOG sheet in the sheet list with the first data row filled in, it’s working correctly. From this point on, any function you wrap with WARE_runSafely_ will automatically produce logs.
Preventing concurrent save conflicts with LockService – Combining locks and error logs
In environments where multiple users may hit the same “save” button at the same time, concurrent save conflicts become a major issue. If one user reads stock data and starts aggregating while another user edits the same data, the end result may vary depending on timing. You can’t completely eliminate this, but by using LockService you can serialize access so that only “one user at a time” can enter the save function, greatly reducing the chance of conflicts.
In real operations, save and aggregation usually finish in 1–2 seconds, so a 30-second lock wait time covers almost all cases. Note that exceptions can also occur while acquiring the lock. For example, if waiting times out, that should also be recorded as an error. So I wrap the waitLock call itself in try, and in catch I call WARE_logError_ and then rethrow the exception.
The code below is a shared locking wrapper. It combines concurrent save conflict prevention and ERROR_LOG recording by calling WARE_runSafely_ inside.
This code is “a common wrapper that acquires a lock, runs a given function, and logs errors.”
Where to paste: Apps Script → Code.gs, directly below the error logging code above.
After pasting: Run testWareLockedRun_() once or twice and check that test logs are added to ERROR_LOG.
const WARE_LOCK_CONFIG = { // → Lock settings
WAIT_SEC: 30, // → Max wait time (seconds)
};
// → Acquire LockService lock and run with error logging
function WARE_lockedRun_(func, funcName, detail) { // → Function to run and its name
const lock = LockService.getScriptLock(); // → Script lock object
const waitMs = WARE_LOCK_CONFIG.WAIT_SEC * 1000; // → Convert to milliseconds
try { // → Wrap from lock attempt
lock.waitLock(waitMs); // → Wait until lock acquired
return WARE_runSafely_(func, funcName, detail); // → Run with error logging
} catch (e) { // → Exception during lock/run
WARE_logError_(funcName, e, detail || 'Lock wait/run failed'); // → Log error
throw e; // → Rethrow to notify user
} finally { // → Regardless of success/failure
if (lock.hasLock && lock.hasLock()) { // → Only if we hold the lock
lock.releaseLock(); // → Release lock
}
}
}
// → Test function for the lock wrapper
function testWareLockedRun_() { // → Test function
try {
WARE_lockedRun_(function () { // → Call lock wrapper
Utilities.sleep(2000); // → Sleep 2 seconds (simulate work)
throw new Error('Lock test error'); // → Intentionally throw error
}, 'testWareLockedRun_', 'Test run'); // → Name & details
} catch (e) { // → Ignore exception
}
}If you run testWareLockedRun_() a few times in a row and then open ERROR_LOG, you should see several rows whose function name is testWareLockedRun_. For real inbound/outbound save functions, leave the existing function as-is and create a separate “SAFE” wrapper, then connect your buttons or menus to that wrapper, like this:
// → Wrap existing saveInbound with lock+logging runner
function saveInboundSafe() { // → Called from menu/button
return WARE_lockedRun_(saveInbound, 'saveInbound', 'Inbound save button'); // → Name
}Remember that LockService only reduces the risk of “multiple users entering the save function at once”; it does not completely prevent saving the same data twice. To prevent true double-submission of the same input, you need additional logic—such as generating a unique key from the input values and recording it only after a successful run, then rejecting future runs with the same key. That part is highly domain-specific and worth covering in a separate post; here we’ll stay focused on conflict reduction and error logging.
Setting up automatic backups with DriveApp – Keeping a daily copy of the sheet
Even with LockService and error logging, you cannot prevent mistakes like someone manually deleting cells or overwriting formulas. And if incorrect logic has been writing wrong data for several days, it’s hard to choose the exact restore point just from the version history menu. For important operational sheets, I therefore combine everything above with a DriveApp-based automatic backup that “creates a full copy of the file every day.”
The method is simple. You take the current spreadsheet’s file ID, get the file object with DriveApp.getFileById, and call makeCopy to create a copy. If you add a prefix like [Backup] plus the date to the copy’s name, it’s easier to find later. In practice, names such as [Backup] Inventory Management 2026-08-12 have been easy enough to manage.
The code below is a backup function intended to run once a day. You can change it later if you prefer once a week, and so on.
This code “creates a copy of the current spreadsheet in Google Drive with today’s date in the name.”
Where to paste: Apps Script → Code.gs, below the previous code blocks.
After pasting: Run WARE_backupDaily() manually once to grant permissions, then set up a time-based trigger.
const WARE_BACKUP_CONFIG = { // → Backup settings
PREFIX: '[Backup] ', // → Prefix for backup filename
TIMEZONE: 'America/New_York', // → Timezone
};
// → Back up the current spreadsheet with today’s date in the name
function WARE_backupDaily() { // → Intended for daily runs
const ss = SpreadsheetApp.getActiveSpreadsheet(); // → Current spreadsheet
const file = DriveApp.getFileById(ss.getId()); // → Drive file object
const baseName = ss.getName(); // → Original filename
const today = Utilities.formatDate( // → Today’s date string
new Date(),
WARE_BACKUP_CONFIG.TIMEZONE,
'yyyy-MM-dd'
);
const backupName = WARE_BACKUP_CONFIG.PREFIX + baseName + ' ' + today; // → Backup name
file.makeCopy(backupName); // → Create copy
}When you run WARE_backupDaily() once from the editor, you’ll see a permissions screen asking for access to Google Drive. After granting, open the triggers screen using the clock icon on the right side of the Apps Script editor, choose the WARE_backupDaily function, then select “Time-driven” → “Day timer” → your preferred time (for example, early morning). The next day, you should see a new Google Sheets file starting with [Backup] in your Drive—then you know it works.
Practical tips – Number validation, protected ranges, and checking execution logs
Simply pasting code in doesn’t automatically make operations stable. In reality, you must also combine input validation, Google Sheets protected ranges, and regular Apps Script execution log reviews to minimize incidents. Here are the measures that have worked well for me.
First, for fields like quantity and amount that only make sense as numbers, it’s best to validate them again in Apps Script. For example, when your save function reads rows, convert with Number(row[qtyColIndex]) and then check Number.isFinite(qty). If it’s not a valid number, skip that row and use WARE_logError_ to record a message like “Quantity error on row X.” This helps prevent a single bad input from turning an entire total into NaN.
Second, configure Google Sheets protected range edit permissions. While you can also handle Protect/Unprotect from Apps Script, in environments with multiple admins I’ve found it simpler to manage the base settings through the sheet UI and only have code do minimal checks for sheets like ERROR_LOG or summary sheets. A basic permission split might look like this:
- Operators: Can edit only the data entry areas of inbound/outbound request sheets or scan input sheets
- Managers: Can edit aggregation sheets, dashboard sheets, and master settings sheets
- ERROR_LOG sheet: Editable only by managers; team leads have view-only access
To configure protected ranges, right-click the sheet tab, select “Protect,” then define the ranges and assign the users allowed to edit them. In particular, protect the ERROR_LOG sheet and any sheets used for backup settings so they aren’t accidentally deleted.
Third, regularly review Apps Script execution logs and execution history. The ERROR_LOG sheet alone sometimes isn’t enough. For those cases, add Logger.log calls inside test functions and open “Executions” or “Logs” from the “Run” menu in the editor to inspect details. A typical workflow looks like this:
- In the Apps Script editor, select and run
testWareErrorLog_ortestWareLockedRun_. - From the top menu, open “Executions” and click the most recent run.
- If an error occurred, check what exception it was and how long the run took.
- At the same time, go back to the spreadsheet and verify that an ERROR_LOG entry with the same timestamp has been added.
If a function doesn’t seem to run at all, it may be that permissions weren’t granted or the trigger isn’t configured correctly. In that case, run the function directly from the editor again to force the permission prompt, and double-check the trigger screen for the target function name and schedule.
Common error examples include:
Exception: Service invoked too many times(quota exceeded) → Check the time and call count in execution history, then adjust the trigger interval or data range.Authorization is required(authorization error) → Run the function manually from the editor to redo the authorization flow.
Once you’ve documented these steps, when someone on site says “the button isn’t working,” you can usually find the root cause quickly by checking just two places: the ERROR_LOG sheet and the execution history.
Conclusion – Add ERROR_LOG today, schedule backups tomorrow
To summarize, once your Google Sheets Apps Script automation reaches a certain scale, stability becomes more important than raw functionality. Combining try/catch with an ERROR_LOG sheet to make errors visible, LockService to reduce concurrent save conflicts, and DriveApp to keep daily automatic backups has proven to be a robust and pragmatic line of defense in real-world operations. Adding numeric validation, protected range settings, and the habit of reviewing execution logs further reduces the risk of losing data in one blow.
If you want one concrete next step, start by adding WARE_ERROR_CONFIG, WARE_logError_, and WARE_runSafely_ to your current Google Sheets Apps Script project, then pick the most frequently used save function and wrap it with WARE_runSafely_ or WARE_lockedRun_. From the moment the first row appears in the ERROR_LOG sheet, that automation stops being “just code that runs” and becomes a system where problems can be traced and understood.
