Google Sheets runs a simple onEdit function automatically for every edit, but that simple trigger operates in a restricted sandbox that cannot open a different spreadsheet by ID or call most authorized services, which rules it out for real audit logging.
This tutorial creates an installable version of the same event instead, registered explicitly through ScriptApp, which runs with the full authorization of whoever created it and can therefore write to a completely separate audit spreadsheet.
Every edit captured by the installable handler records the timestamp, the editing user's email, the sheet name, the cell's A1 notation, and both the previous and new values, giving a compliance or operations team a real trail of who changed what.
Because installable triggers must be created through code or the trigger UI rather than simply existing by function name, the setup function createInstallableOnEditTrigger both removes any old trigger and registers a fresh one bound to the active spreadsheet.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Piece | Value | Purpose |
|---|---|---|
| Handler | onEditInstallable | Installable onEdit |
| Watched sheet | Tracker | Edits logged from here |
| Audit sheet | Edit Audit | Who/when/cell/value |
What it does
onEditInstallable receives the same edit event object as a simple trigger, but because it was registered through ScriptApp, it is allowed to open a separate spreadsheet by ID and append a row describing the edit.
Prerequisites
createInstallableOnEditTrigger calls forSpreadsheet on the active spreadsheet and onEdit to bind the handler, producing a trigger that fires with full permissions rather than the restricted permissions a simple onEdit(e) function receives automatically.
Walkthrough
Run createInstallableOnEditTrigger once from the editor, approve the authorization prompt covering both the active spreadsheet and the external audit spreadsheet, and replace AUDIT_SPREADSHEET_ID with the real destination spreadsheet's ID.
Edge cases
The handler reads e.range for the edited cell, e.oldValue for what was there before the edit when available, and Session.getActiveUser().getEmail() to attribute the change, then appends all of it as one row in the external Edit Log sheet.
Testing
If AUDIT_SPREADSHEET_ID is left as a placeholder or the user lacks access to that spreadsheet, SpreadsheetApp.openById throws immediately, which is a deliberate fail-fast behavior so a broken audit log is never silently skipped.
Hardening
Edit a cell in the source spreadsheet after installing the trigger and confirm a corresponding row appears in the audit spreadsheet's Edit Log tab before relying on this trigger for real compliance tracking.
Variations
Because installable triggers run under the identity of whoever created them, removing that person's access to either spreadsheet silently breaks the audit log, so production deployments should install the trigger from a shared service account or documented owner rather than an individual's personal account.
Full code: createInstallableOnEditTrigger() and onEditInstallable()
Replace AUDIT_SPREADSHEET_ID with a real spreadsheet ID, run createInstallableOnEditTrigger once, then edit a cell to confirm the Edit Log fills in.
var AUDIT_SPREADSHEET_ID = 'PUT_EXTERNAL_AUDIT_SHEET_ID_HERE';
function createInstallableOnEditTrigger() {
removeInstallableOnEditTriggers();
ScriptApp.newTrigger('onEditInstallable')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
}
function removeInstallableOnEditTriggers() {
ScriptApp.getProjectTriggers().forEach(function (trigger) {
if (trigger.getHandlerFunction() === 'onEditInstallable') {
ScriptApp.deleteTrigger(trigger);
}
});
}
function onEditInstallable(e) {
var range = e.range;
var sheet = range.getSheet();
var user = Session.getActiveUser().getEmail() || 'unknown';
var logRow = [
new Date(),
user,
sheet.getName(),
range.getA1Notation(),
e.oldValue !== undefined ? e.oldValue : '',
range.getValue()
];
var auditSpreadsheet = SpreadsheetApp.openById(AUDIT_SPREADSHEET_ID);
var auditSheet = auditSpreadsheet.getSheetByName('Edit Log') || auditSpreadsheet.insertSheet('Edit Log');
auditSheet.appendRow(logRow);
}- Line 5: Registers the installable trigger against the active spreadsheet.
- Line 7: Binds the handler to the onEdit event specifically.
- Line 12: Removes prior triggers pointed at the same handler to avoid duplicates.
- Line 21: Reads the editing user's email, available because this is an installable trigger.
- Line 27: Falls back to an empty string when oldValue is not present.
- Line 31: Opens a separate audit spreadsheet by ID, which a simple trigger could not do.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: install an onEdit audit trigger
- 1AUDIT_SPREADSHEET_ID replaced with the real external spreadsheet's ID
- 2createInstallableOnEditTrigger run once and authorization approved
- 3Old onEditInstallable triggers removed before creating a new one
- 4Edit Log sheet exists or the script has permission to create it
- 5Test edit confirmed to produce a matching audit row
- 6Trigger owner identity documented for long-term maintenance
- 7e.oldValue handled correctly for edits that clear a cell
Frequently asked questions
Simple triggers run in a restricted, unauthorized context for security reasons and cannot call services like SpreadsheetApp.openById on a different file, send email, or make external requests.
forSpreadsheet binds the trigger to a specific Spreadsheet object's edit or change events, while forForm binds it to a Form object's submit events; the method you choose depends on which kind of event source you are listening to.
The edit event only includes oldValue when the edited cell previously held a value; clearing an already-empty cell or editing a range spanning multiple cells does not populate it the same way.
The account that created the installable trigger needs edit access to the audit spreadsheet, since the trigger executes under that account's authorization rather than the account of whoever actually made the edit.
Yes, add a condition inside onEditInstallable that checks the sheet name or column before calling appendRow, so only edits in specific ranges generate an audit entry.
Each edit fires its own trigger execution independently, and since this handler only appends a row rather than modifying shared state, concurrent edits do not conflict the way a counter increment would.