Background
Over about a year I wrote five separate Apps Script automations for the team: consolidating data across a folder of Sheets, cleaning and validating SKU dimension data, scraping structured data out of emails and attachments, generating summary tables, and deduplicating rows in a processing log. Different jobs, same question at the start of each one: how much of the boring 80% do I have to write again this time.
There's no npm install here
Every Apps Script project bound to a Sheet, Doc, or Form is its own isolated environment. No package registry, no bundler, no import reaching across projects by default.
Apps Script does have an answer for this: Libraries. Publish a script as a library, add it as a dependency somewhere else, call its functions by namespace. I looked at it and passed, not because it's a bad feature, just because it didn't fit this situation.
Every consuming project pins a specific version number. Fix a bug in the library and it doesn't reach anyone until they manually bump the version, and these scripts were mostly going to be inherited by people who weren't going to do that reliably. Debugging across a library boundary in the standalone editor adds a layer of indirection that isn't worth it for scripts this small. And a library call hides the implementation behind a namespace, while a self-contained file puts the whole thing in front of whoever opens it, which mattered more given who was actually going to maintain these after me.
So what I actually did was copy a template forward, not import a dependency. That's a real tradeoff: less "fix it once and it propagates everywhere," more "open any one file and understand the whole thing without chasing a reference."
The shape of the template
All five utilities (GasUtilities.js, SheetCleanerUtils, EmailScrapperUtils, SummaryGeneratorUtility, LogCleanerUtils) start from the same shell. An IIFE using the revealing module pattern, a private batched Log object, a set of private helpers, one public interface object returned at the bottom.
const GasUtils = (function () {
const Log = (function () {
/* ...batched logger, see below... */
})();
function getFilesFromFolder(options) {
/* ... */
}
function findSheet(spreadsheet, rule) {
/* ... */
}
function prepareSheets(sheetNames) {
/* ... */
}
function extractAndAppend(options) {
/* ... */
}
// --- Public Interface ---
return {
getFilesFromFolder: getFilesFromFolder,
findSheet: findSheet,
prepareSheets: prepareSheets,
extractAndAppend: extractAndAppend,
Log: Log,
};
})();The header comment on the log-cleaning utility, the last of the five, just says it plainly: Author: Sean Yang (based on existing utilities). Copying the shell was the plan, not something I'm dressing up after the fact.
Batching the logger
The naive version of Log.info(...) just calls console.log(...) every time. Fine for a script that runs ten times. Less fine once it's iterating over a folder with a few hundred files in it. Apps Script's execution log gets slow to render and eventually truncates, and every console.log call inside a tight loop has its own overhead.
So Log pushes messages into an array and only writes them out once, at the end:
const Log = (function () {
let batch = [];
let enabled = false;
const log = (level, message) => {
if (enabled) {
batch.push(`[${level}] ${new Date().toLocaleTimeString()} - ${message}`);
}
};
return {
enable: () => (enabled = true),
disable: () => (enabled = false),
info: (message) => log("INFO", message),
warn: (message) => log("WARN", message),
flush: () => {
if (batch.length > 0) {
console.log(batch.join("\n"));
batch = [];
}
},
};
})();enable() and disable() let you turn logging off for a production run without deleting every call site, which beats hunting down every Log.info by hand.
The version above is from the first utility I wrote, and it only has info and warn. By the time I got to the summary-table and email-scraping ones, I'd added an error level to the shell. That's the actual cost of copy-forward instead of a shared library: later files got the improvement, the earlier ones didn't, and nothing syncs them back up on its own. If that drift ever gets expensive enough, that's the point where promoting this into a real shared Library starts paying for its version-pinning tax.
Batching the writes too
Same instinct applies to the Sheets service itself. extractAndAppend, which does the heavy lifting in the consolidation utility, reads a whole source range in one call, builds the output rows in memory, and writes them in one call:
function extractAndAppend(options) {
const { sourceSheet, targetSheet, rule, sourceName, headersAdded } = options;
const data = sourceSheet
.getDataRange()
.getValues()
.filter((row) => row.join("").trim() !== "");
if (data.length === 0) return;
// ...build headerRow / dataRows in memory...
if (dataRows.length > 0) {
dataRows.forEach((row) => row.push(sourceName));
targetSheet
.getRange(
targetSheet.getLastRow() + 1,
1,
dataRows.length,
dataRows[0].length,
)
.setValues(dataRows);
}
}Every call into the Sheets service is a remote call. Loop over rows and call setValue() per cell, and what should be a handful of API calls turns into rows × columns of them. You won't notice on a ten-row test sheet. You will notice the first time someone points the script at a folder with fifty files in it.
What the public interface buys you
Anything not listed in that return { ... } block is invisible outside the file, trapped in the IIFE's closure. SheetCleanerUtils and EmailScrapperUtils can both have a private helper with the same name in the same project and it's a non-issue, because neither one leaks past its own closure. The public object ends up being the entire surface area anyone else has to read to understand what the file does.
Where it landed
Five automations that don't share a dependency but do share a spine. Anyone opening the sheet-cleaning script for the first time already knew roughly where the logging lived and where the entry points were, because they'd seen the same shape in the consolidation script first. That consistency ended up mattering more day to day than a shared import would have. It's also the version of this I taught in an internal session later on, walking the team through the shell so people could write their own automations against it instead of routing every request through me.