How to Build a Code Visualizer in Google Apps Script
A practical guide to monkey-patching, audit logs, and building a live operations dashboard for your Sheets automations
Building and understanding are two different things. Now there's AI and everyone is vibe coding. You can build a car from a kit and still not know what the engine is doing. This article will teach you step by step what you need to know. The how, the why and the what for. The project Code Visualizer project and I will walk you through how to build it
Before You Start
Here is what you need:
A Google account
A Google spreadsheet you want to monitor
Basic familiarity with the Apps Script editor (Extensions > Apps Script in any Google Sheet)
No prior experience with monkey-patching or web apps required. We are building everything from scratch and explaining every line.
What Is a Code Visualizer?
A Code Visualizer is a real-time monitoring layer that sits on top of your Apps Script project and intercepts every sheet operation your code performs, logs the details to an audit sheet, and streams those logs to a live web app dashboard that updates every two seconds.
It's a CCTV for your spreadsheet. While your automation runs in the background, the visualizer is quietly recording what happened, when it happened, in which cell, on which sheet, what the value was before, and what it became after. All without you adding a single extra line to your production code.
Think of it like this: A surgeon has a monitor in the operating room showing the patient's heartbeat, blood pressure, and oxygen levels in real time. Without it, the surgeon would have to stop and check manually. The monitor makes everything visible at once. The Code Visualizer is that monitor for your Apps Script code.
What We Are Building
Go to Apps Script and create these three files. Each one has one job.
VisualizerAgent.gs : Watches the code and logs every sheet operation
Code.gs: Reads the logs and serves them to the browser
Visualizer.html: Shows the logs as a live, colour-coded dashboard
Together they form a pipeline: When the script runs, every operation gets intercepted and recorded, and the dashboard updates in the browser every two seconds automatically.
Part 1: The Core Technique
What Is Monkey-Patching and Why Does It Work?
This is the most important concept in the entire project.
Monkey-patching means replacing a built-in function with your own version at runtime, so that your code runs first and the original code runs after as if nothing changed.
Normally when your script calls setValue(), Google's built-in code runs and the cell updates. But you cannot see it happening.
After monkey-patching, when your script calls setValue(), the code runs first. It logs the operation: the sheet name, the cell address, the old value, the new value, the timestamp. Then it calls Google's original setValue() to update the cell. The result is identical. The only difference is the record, when the cells update.
Case scenario: A mail worker's job is to deliver letters. Normally they just deliver it and leave. Now imagine the mail company place an assistant(middle man) between the sender and the worker. The assistant or the middle man intercepts each letter first, writes down who sent it, who it is addressed to, and what time it ought to arrived, then hands it to the mail worker to deliver. The recipient gets their letter. The sender does not notice the interception. But now there is a record of every mail that was delivered; the source and the time of collection.
Why this technique?
This technique works because JavaScript lets you replace functions on prototypes at runtime. You are not editing Google's code, rather swapping what setValue means on your own copy of the Range prototype, in your own script's memory, while it runs.
Part 2: VisualizerAgent.gs
This file sits inside any Apps Script project you want to monitor. It intercepts every sheet write operation and logs the details to a hidden audit sheet. You do not touch your existing code at all. You just add this file and it starts watching automatically.
Step 1: The Outer Wrapper
The very first and last lines of the file look like this:
(function() {
// ... everything else goes here ...
})();
WHAT: This wraps all the code in a private bubble called an IIFE (Immediately Invoked Function Expression).
WHY: Apps Script runs all .gs files in the same global scope. Without this wrapper, a variable named queue in the agent file could clash with a variable named queue in your business logic file. The IIFE keeps everything inside it private.
Case scenario:
Two people are writing on the same whiteboard. If they both write 'total = 5' they overwrite each other. But if each person works inside their own section separated by a dividing line, their variables coexist without conflict. The IIFE is that dividing line.
Step 2: The CONFIG Object
const CONFIG = {
AUDIT_SHEET_NAME: ' Audit Log',
MAX_QUEUE_SIZE: 50,
FLUSH_INTERVAL: 2000,
DEBUG: true
};
WHAT: A single object that holds all the settings for the agent.
WHY: Grouping all your settings in one place means you only ever need to change one value in one location. Set DEBUG to false to disable logging entirely without deleting anything.
A Bit of Detailed Explanation
How it does it | CONFIG is an object with four keys. AUDIT_SHEET_NAME is the name of the hidden sheet where logs go. MAX_QUEUE_SIZE is the maximum number of entries to hold before forcing a write. FLUSH_INTERVAL (2000 means 2000 milliseconds = 2 seconds) is how long to wait before writing. DEBUG being true means logging is turned on. |
Why it has to be there | If settings were scattered across the file as loose values, you would need to go through the whole file every time you wanted to change one thing. By putting them all here, any change to how the agent works happens in one place. |
Observational Note (Very Important)
Notice the space before 'Audit Log' in the sheet name. It's deliberate.
Google Sheets sorts tabs alphabetically. A name starting with a space sorts before the letter A, so the audit tab always appears at the far left of the tab bar.
Step 3: Three State Variables
const queue = [];
let flushTimer = null;
let isInitialized = false;
queue
An array that temporarily holds log entries before they are written. Writing to a sheet is slow. Batching 10 writes into one sheet operation is much faster than 10 individual writes.
How it does it | queue is an empty array. Every time your code calls setValue() or setBackground(), the details get added to this array instead of being written to the sheet immediately. When the queue is flushed, all items are written at once. |
Why it has to be there | Writing to spreadsheet is one of the slowest things Apps Script can do (It is very slow). If every single setValue() triggered an immediate sheet write, a function with 100 operations would be so slow and might hit Google's execution time limit. The queue collects operations and writes them together in one fast batch. |
flushTimer
A reference to a countdown timer. Every new operation resets the timer. When the timer completes with no new operations arriving, the queue is written to the sheet.
What it does | Holds the timer that decides when to write the queue to the sheet. |
How it does it | setTimeout() schedules a function to run after a delay. It returns a timer ID. Storing that ID in flushTimer means you can cancel it (with clearTimeout) if a new operation arrives before the timer fires. |
Why it has to be there | It's called debouncing. The concept behind it is: every new operation resets the clock. Only when operations stop arriving for a full 2 seconds does the flush happen. This prevents constant writing while a function is still running. |
isInitialized
A flag to ensure init() only runs once, even if it is called multiple times.
HOW it does it | It starts as false. The first time init() runs, it is set to true. Every time init() is called after that, the first thing it checks is this flag. If it is true, init() immediately stops and does nothing. |
WHY it has to be there | In Apps Script, triggers like onEdit can fire many times. Without this guard, init() would run over and over, recreating the audit sheet and re-applying patches on top of patches. One flag prevents an entire category of bug. |
Step 4: The init() Function
function init() {
if (isInitialized) return;
isInitialized = true;
ensureAuditSheet();
patchSpreadsheetMethods();
console.log('Code Visualizer Agent initialized');
}
WHAT: The startup function. It creates the audit sheet and applies the monkey-patches.
WHY: The guard clause (if isInitialized) means the setup only ever runs once. isInitialized is set to true immediately after so that even a recursive call cannot trigger it again. ensureAuditSheet() checks whether the audit sheet exists and creates it if not. patchSpreadsheetMethods() does the actual monkey-patching.
A Bit of Detailed Explaination
if (isInitialized) return;
This is the guard clause. It simply means : If the agent is already initialized, stop immediately. The return keyword exits the function right there. Nothing else in init() runs.
isInitialized = true;
Immediately after the guard, the flag is flipped to true. This means: even if init() is called again one millisecond later, the guard will catch it.
ensureAuditSheet();
Calls the function that checks whether the audit sheet already exists. If it does, great. If not, it creates it with the right headers and formatting.
patchSpreadsheetMethods();
Calls the function that does the actual monkey-patching. This is where setValue, setValues, setBackground, and clear get replaced with logging versions.
console.log(...)
Writes a message to the Apps Script log viewer. This line is just for you, the developer. When you open the executions panel and see this message, you know that the agent started correctly.
Step 5: The ensureAuditSheet() Function
function ensureAuditSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(CONFIG.AUDIT_SHEET_NAME);
if (!sheet) { sheet = ss.insertSheet(CONFIG.AUDIT_SHEET_NAME);
sheet.appendRow([
'Timestamp', 'Function', 'Type', 'Sheet', 'Range',
'Old Value', 'New Value', 'Status',
'Duration(ms)', 'Stack'
]);
const headerRange = sheet.getRange(1, 1, 1, 10);
headerRange.setFontWeight('bold');
headerRange.setBackground('#4285F4');
headerRange.setFontColor('#FFFFFF');
sheet.hideSheet();
}
return sheet;
}
WHAT: It creates the hidden audit sheet with the right column headers and formatting, but only if it does not already exist.
WHY: getSheetByName() returns null if the sheet does not exist. The if(!sheet) [line 4] block only runs on first setup. sheet.hideSheet() [line 14] hides the audit tab from normal view so clients and collaborators do not see it or accidentally delete rows.
Step 6: The queueEntry() Function
function queueEntry(entry) {
if (!CONFIG.DEBUG) return;
queue.push([
entry.timestamp || new Date(),
entry.function || getCallerFunction(),
entry.type || 'UNKNOWN',
entry.sheet || '',
entry.range || '',
entry.oldValue !== undefined
? JSON.stringify(entry.oldValue).substring(0, 500) : '',
entry.newValue !== undefined
? JSON.stringify(entry.newValue).substring(0, 500) : '',
entry.status || 'OK',
entry.duration || '',
entry.stack || ''
]);
if (flushTimer) clearTimeout(flushTimer);
flushTimer = setTimeout(flushQueue, CONFIG.FLUSH_INTERVAL);
if (queue.length >= CONFIG.MAX_QUEUE_SIZE) flushQueue();
}
WHAT: Receives one log entry and adds it to the queue. Then resets the debounce timer and checks whether an emergency flush is needed.
WHY: The double slashes [ || ] operator provides fallbacks. That is, if entry.timestamp was not provided, new Date() is used automatically. JSON.stringify() converts arrays and objects to text that can be stored in a cell. substring(0, 500) prevents any single entry from overflowing a cell.
Additional Information
The clearTimeout / setTimeout pattern is debouncing. Every new entry cancels the previous timer and starts a fresh two-second countdown. The sheet is only written to when operations stop arriving for two full seconds, or when 50 entries pile up.
Step 7: The flushQueue() Function
function flushQueue() {
if (queue.length === 0) return;
const sheet = ensureAuditSheet();
if (!sheet) return;
const startRow = sheet.getLastRow() + 1;
const numRows = queue.length;
sheet.getRange(startRow, 1, numRows, 10).setValues(queue);
queue.forEach((row, i) => {
const status = row[7];
let color = '#D9EAD3';
if (status === 'ERROR') color = '#F4CCCC';
if (status === 'WARNING') color = '#FFF2CC';
if (row[2] === 'SET_BACKGROUND') color = '#CFE2F3';
sheet.getRange(startRow + i, 1, 1, 10).setBackground(color);
});
queue.length = 0;
}
WHAT: Writes everything in the queue to the audit sheet in a single operation, colour-codes each row by status, then empties the queue.
WHY: setValues() writes an entire 2D array to a range in one API call. If the queue has 30 entries, this is one sheet write instead of 30. That difference in speed is significant.
Additional Information
queue.length = 0 is the correct way to empty the queue. The alternative (queue = []) would create a brand new array and break the references that other functions inside the IIFE hold to the original queue.
Step 8: The getCallerFunction() Function
function getCallerFunction() {
try {
throw new Error();
} catch (e) {
const lines = e.stack.split('\n');
for (let i = 3; i < lines.length; i++) {
const match = lines[i].match(/at (\S+)/);
if (match && !lines[i].includes('Code Visualizer')) {
return match[1];
}
}
}
return 'unknown';
}
WHAT: It figures out which function in your business logic triggered the current operation.
WHY: When JavaScript throws an error it creates a call stack trace, a text description of every function that was running at that moment. By deliberately throwing and immediately catching an error, you can read that trace without crashing anything.
Extra
e.stack.split() breaks the trace into individual lines. The regex /at (\S+)/ extracts function names. The loop skips lines from the visualizer itself so the first match is always a function from your own code.
Case scenario: You want to know which student caused a noise in the library. You look through the visitor log starting from the most recent entries, but you skip all entries from yourself and the librarian. The first student name you land on is your answer.
Step 9: patchSpreadsheetMethods() and the Four Patches
This is where the monkey-patching actually happens. Before replacing anything, we save the originals.
function patchSpreadsheetMethods() {
const originalSetValue = SpreadsheetApp.Range.prototype.setValue;
const originalSetValues = SpreadsheetApp.Range.prototype.setValues;
const originalSetBackground =
SpreadsheetApp.Range.prototype.setBackground;
const originalClear = SpreadsheetApp.Range.prototype.clear;
Very Important
These four lines copy the originals before overwriting them. Without this step you could never call Google's real setValue again.
Patch 1: setValue
SpreadsheetApp.Range.prototype.setValue = function(value) {
const startTime = Date.now();
const sheet = this.getSheet();
let oldValue;
try { oldValue = this.getValue(); }
catch (e) { oldValue = '[unavailable]'; }
const result = originalSetValue.call(this, value);
queueEntry({
timestamp: new Date(), type: 'SET_VALUE',
sheet: sheet.getName(), range: this.getA1Notation(),
oldValue, newValue: value,
duration: Date.now() - startTime
});
return result;
};
This must be a regular function (not an arrow function) so that the this keyword refers to the Range object calling setValue. An arrow function would inherit this from the outer scope and break everything.
Additional
originalSetValue.call(this, value) runs Google's real function with the correct Range context. return result is vital for method chaining. Code like range.setValue(x).setBackground('red') depends on setValue returning the range.
Patch 2: setValues (batch writes)
SpreadsheetApp.Range.prototype.setValues = function(values) {
const startTime = Date.now();
const sheet = this.getSheet();
const result = originalSetValues.call(this, values);
queueEntry({
type: 'SET_VALUES', sheet: sheet.getName(),
range: this.getA1Notation(),
newValue: `[Batch: \({values.length} rows x \){values[0]?.length || 0} cols]`,
duration: Date.now() - startTime
});
return result;
};
No oldValue is read here. Reading back an entire range before writing would be slow. For batch writes the log records the dimensions instead. values[0]?.length uses optional chaining: if values is empty, the ?. stops and returns undefined instead of crashing.
Patch 3: setBackground (with color validation)
SpreadsheetApp.Range.prototype.setBackground = function(color) {
const isValid = /^#[0-9A-F]{6}$/i.test(color) ||
['red','blue','green','yellow','white','black',
'orange','purple','gray','grey'].includes(color?.toLowerCase());
queueEntry({
type: 'SET_BACKGROUND', sheet: this.getSheet().getName(),
range: this.getA1Notation(),
oldValue: this.getBackground(), newValue: color,
status: isValid ? 'OK' : 'ERROR'
});
if (!isValid) console.error(`Color Error: "${color}" not valid`);
return originalSetBackground.call(this, color);
};
Apps Script does not throw an error when you pass an invalid hex code like '#ZZZ999'. It silently ignores the instruction and the cell stays unchanged. You would never know it failed without this validation step. The visualizer catches it and flags it as an error in the audit log.
Patch 4: clear
SpreadsheetApp.Range.prototype.clear = function() {
const sheet = this.getSheet();
queueEntry({
type: 'CLEAR_RANGE',
sheet: sheet.getName(),
range: this.getA1Notation(),
oldValue: '[cleared]'
});
return originalClear.apply(this, arguments);
};
}
Extra
originalClear.apply(this, arguments) passes all arguments through to the original, whatever they are. The clear() method accepts optional arguments to specify what to clear. Using apply ensures your patch never accidentally changes that behaviour.
Step 10: Auto-Initialize
init();
window.flushAuditLog = flushQueue;
Extra
init() calls the startup function the instant the file loads. so, you do not need to call it yourself anywhere. Just adding the file to a project is enough.
window.flushAuditLog = flushQueue exposes the flush function to the outside world. Everything inside the IIFE is private by default. This line creates a public door so external code can force an immediate write when needed, such as right before a script completes.
Part 3: Code.gs
Code.gs is the web app backend. It knows about all the projects you are monitoring. When the dashboard asks for data, Code.gs reads the audit sheets and sends the information back.
The CONFIG Object
const CONFIG = {
PROJECTS: [
{
id: 'YOUR_SCRIPT_ID',
name: 'My Project',
sheetId: 'YOUR_SPREADSHEET_ID',
auditSheetName: ' Audit Log',
type: 'container-bound',
color: '#4285F4'
}
],
MAX_ENTRIES: 1000,
REFRESH_INTERVAL: 2000
};
Add one entry per project you want to monitor. The sheetId is the long string in your spreadsheet's URL between /d/ and /edit.
The doGet() Function
function doGet(e) {
const template = HtmlService.createTemplateFromFile('Visualizer');
template.config = JSON.stringify({
projects: CONFIG.PROJECTS.map(p => ({
id: p.id, name: p.name, color: p.color, type: p.type
})),
refreshInterval: CONFIG.REFRESH_INTERVAL
});
return template.evaluate()
.setTitle('Code Visualizer')
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
When you deploy the script as a web app and visit the URL, Google automatically calls doGet(). This is the entry point.
Extra
HtmlService.createTemplateFromFile() reads your Visualizer.html and lets you inject server-side values into it. template.config is how the project list gets passed to the browser. CONFIG.PROJECTS.map() strips out sensitive fields like sheetId that the browser does not need.
getAllProjectLogs()
function getAllProjectLogs(projectFilter) {
const allLogs = [];
const errors = [];
const projects = projectFilter
? CONFIG.PROJECTS.filter(p => p.id === projectFilter)
: CONFIG.PROJECTS;
projects.forEach(project => {
try {
const logs = getProjectLogs(project);
allLogs.push(...logs);
} catch (e) {
errors.push({ project: project.name, error: e.message });
}
});
allLogs.sort((a, b) => new Date(b.timestamp) - new Date(a.timestamp));
return {
logs: allLogs.slice(0, CONFIG.MAX_ENTRIES),
errors,
totalCount: allLogs.length,
projectsConnected: projects.length - errors.length
};
}
Very Important
The dashboard calls this every two seconds. Each project fetch is wrapped in try/catch so if one spreadsheet is unreachable the others continue loading. allLogs.push(...logs) uses the spread operator to merge all project logs into one flat array. The sort puts the most recent entries first.
getProjectLogs()
function getProjectLogs(project) {
const ss = SpreadsheetApp.openById(project.sheetId);
const sheet = ss.getSheetByName(project.auditSheetName);
if (!sheet || sheet.getLastRow() <= 1) return [];
const lastRow = sheet.getLastRow();
const startRow = Math.max(2, lastRow - 499);
const data = sheet.getRange(startRow, 1, lastRow - startRow + 1, 10).getValues();
return data
.map(row => ({
project: project.name,
timestamp: row[0], function: row[1], type: row[2],
sheet: row[3], range: row[4], oldValue: row[5],
newValue: row[6], status: row[7], duration: row[8],
stack: row[9]
}))
.filter(log => log.timestamp);
}
Very Important
Math.max(2, lastRow - 499) reads a maximum of 500 rows from the bottom of the sheet. If the sheet has fewer than 500 rows it reads from row 2 (skipping the header). .map() converts raw array rows into named objects so you read log.timestamp instead of log[0]. .filter() removes empty rows.
Part 4: Visualizer.html
The Dashboard
This file is what you see in the browser. It handles its own HTML structure, CSS styling, and JavaScript behaviour. The key patterns worth understanding are below.
CSS Variables
:root {
--bg-primary: #0f172a;
--bg-secondary: #1e293b;
--text-primary: #f8fafc;
--accent-blue: #3b82f6;
--accent-green: #10b981;
--accent-red: #ef4444;
--accent-yellow: #f59e0b;
}
Variables defined at :root are available everywhere in the CSS. To change the entire dashboard's primary colour you change one line. Every element that uses that variable updates instantly. This is called a design token system.
Configuration Injection
const CONFIG = JSON.parse('<?= config ?>');
Information
When doGet() runs on the server and calls template.evaluate(), every occurrence of <?= config ?> inside the HTML is replaced with the value of template.config before the page is sent to the browser. JSON.parse() converts the string back into a real JavaScript object.
refreshData()
function refreshData() {
const projectFilter = document.getElementById('projectFilter').value;
google.script.run
.withSuccessHandler(data => {
allLogs = data.logs;
updateDashboard(data);
renderTimeline();
updateTimestamp();
})
.withFailureHandler(error => {
showError('Failed to fetch data: ' + error.message);
})
.getAllProjectLogs(projectFilter || null);
}
google.script.run is Apps Script's bridge between your HTML page and your server-side .gs functions. The call is asynchronous. The browser sends the request and continues running. When the data arrives, withSuccessHandler fires and the page updates.
Additional Information
Always attach both withSuccessHandler and withFailureHandler. Without the failure handler, server errors disappear silently.
escapeHtml()
function escapeHtml(text) {
const div = document.createElement('div');
div.textContent = text;
return div.innerHTML;
}
Cell values might contain characters like < or > that have special meaning in HTML. If inserted raw into innerHTML they would be interpreted as tags and break the page. This function creates an invisible div, sets its textContent (which always treats the value as plain text), then reads the innerHTML (which has escaped the special characters)
Very Important
This is XSS prevention. Without it a malicious value in a cell could inject scripts into your page. With escapeHtml it is impossible.
Part 5: How to Deploy It
Step 1: Deploy the Visualizer Web App
Create a new standalone Apps Script project at script.google.com. Add Code.gs and Visualizer.html. In the CONFIG object, register each project you want to monitor with its script ID and spreadsheet ID.
Deploy as a web app. Set Execute as: Me and Access: Anyone (or your organisation's domain). Copy the generated URL.
Step 2: Add the Agent to Your Target Project
Open the Apps Script project you want to monitor. Create a new file called VisualizerAgent.gs and paste the complete agent code into it.
The script auto-initializes when it loads. You do not call init() manually and you do not need to modify any of your existing functions.
Step 3: Verify and Watch
Open your visualizer URL in a browser. Run any function in your monitored project. Watch the operations appear in real time on the dashboard.
You will see the function name, the sheet and range affected, the before and after values, timing, and status, all within two seconds.
Why This Project Is Important
- Separation of concerns
The monitoring logic lives in its own file. Your business logic files never know it exists. The visualizer is easy to add, remove, or update without touching your actual automation.
2. Batched writes over real-time writes
Writing to a Google Sheet on every single operation would slow your script considerably. Queuing entries and flushing in batches keeps the monitoring overhead minimal.
3. Debounced flush with an emergency threshold
The 2-second timer prevents constant writing. The 50-entry emergency flush prevents data loss during rapid bursts of operations.
4. Graceful failure on the monitoring side
If the audit sheet cannot be written to for any reason, the failure is logged and the original operation proceeds. The monitoring layer never blocks your code.
Key Takeaways
Monkey-patching lets you add observability to any method without touching the code that calls it. You are not modifying Google's code. You are swapping what a method means in your script's memory while it runs.
Audit sheets are a portfolio-worthy pattern. Any time you build automations for clients, a built-in audit trail demonstrates professionalism and makes support conversations and documentations easier.
Separating monitoring code into its own file keeps your project clean. You can disable or update the logging layer at any time without touching business logic.
Batching writes to a sheet is always better than writing one row at a time. This principle applies to any automation that needs to log, notify, or record events.
A real-time web app dashboard built on google.script.run is easier than it looks. Consider this pattern for any Apps Script project that needs a client-facing interface.
Final Thought
Being an Apps Script developer has taught me a lot. You will learn when your code breaks, but having a tool makes it easier to find out why, where, and when it breaks.
Stick around, there's plenty more to come. In my next post, I’ll be diving deeper into the world of Google Workspace and advanced automation. We'll explore everything from real-time dashboards and audit logs to the finer points of google.script.run.
If you're looking to master Apps Script debugging and build professional-grade spreadsheet tools, you're in the right place. See you in the next one.
