A modular, config-driven framework that simplifies building scalable and maintainable Google Sheets solutions using Google Apps Script. By centralizing logic in a structured SHEETCONFIG
object and utilizing a powerful SmartSheet
class, this framework helps you manage layout, data rules, and automation logic consistently across multiple sheets.
This project is the final iteration of concepts developed in previous works:
- π Dynamic Google Sheets Layout
- π Lookup Table Pattern in Apps Script
- β‘ Event Dispatcher in Apps Script
- π§© Centralized Configuration (
SHEETCONFIG
)- Define columns, named ranges, validation rules, formatting rules, and edit behavior in one place.
- π§ SmartSheet Class
- A utility wrapper that interprets
SHEETCONFIG
and simplifies reading/writing data.
- A utility wrapper that interprets
- π‘οΈ Validation & Conditional Formatting
- Attach rules by column or named range with support for list values and styling.
- β‘ Calculated Columns & Named Ranges
- Use inline formulas or functions with
$var
injection to populate dynamic values.
- Use inline formulas or functions with
- π Protected Fields
- Mark columns or named ranges as locked to prevent user editing.
- β
Rule-Based onEdit Handlers
- Handle edit events declaratively using rule conditions and handlers.
- π§ Debug Mode Support
- Switch between production and development sheets easily.
/src
βββ config.gs # Global SHEETCONFIG and constants
βββ SmartSheet.gs # SmartSheet utility class
βββ utils.gs # Utility and helper functions
βββ triggers.gs # Entry points for onOpen, onEdit, installable, etc.
βββ custom-functions.gs # Custom spreadsheet functions
βββ event-handlers.gs # Named event handlers used in config
- Copy the contents of
/src
into your Apps Script project. - Define your sheet layout and logic in
config.gs
using theSHEETCONFIG
object. - Customize
triggers.gs
to apply your configuration and handle edits. - Add formulas, validations, and formatters as needed.
const SHEETCONFIG = readOnlyObject({
Employees: {
layout: {
headerRows: 4,
columns: {
A: { name: 'name', type: 'string' },
B: { name: 'age', type: 'number' },
C: { name: 'joinDate', type: 'date' },
D: { name: 'department', type: 'string' },
E: { name: 'score', type: 'number' },
F: { // calculated column
name: 'relativeScore',
formula: 'IF(ISBLANK($score), "", $score / AVERAGE(E5:E))',
lock: true
}
},
namedRanges: {
'B1': { name: 'selectDepartment', type: 'string' },
'E1': { // calculated named-range
name: 'cellE1',
args: ['selectDepartment'],
formula: forE1,
lock: true
}
}
},
onEditRules: [
{
condition: (e, smartSheet) => {
return e.range.getA1Notation() === smartSheet.getNamedRange('selectDepartment').getA1Notation();
},
handler: selectDepartmentChange
}
],
validationRules: {
column: (col) => ({
department: SpreadsheetApp.newDataValidation()
.requireValueInList(['HR', 'Engineering', 'Sales'], true)
.setAllowInvalid(false)
.build()
}[col] || null),
range: (range) => ({
selectDepartment: SpreadsheetApp.newDataValidation()
.requireValueInList(['HR', 'Engineering', 'Sales'], true)
.setAllowInvalid(false)
.build()
}[range] || null)
},
formattingRules: {
column: (col) => ({
department: () => formatDepartment(departmentList)
}[col] || null)
}
}
});
The framework uses standard Apps Script triggers defined in triggers.gs
:
function onOpen(e)
{
const ss = e.source;
if (DEBUG) clearAll(ss); // optional: reset the spreadsheet in dev
const currentEditors = new Set(ss.getEditors().map(e => e.getEmail()));
const newEditors = editors
.map(e => e.email)
.filter(email => !currentEditors.has(email));
if (newEditors.length > 0) {
try {
ss.addEditors(newEditors);
} catch (e) {
showAlert(`Failed to add editors: ${e.message}`);
}
}
if (!validateSHEETCONFIG()) {
showAlert('Not valid. Check the log.');
return;
}
for (const sheetName in SHEETCONFIG) {
const sheet = ss.getSheetByName(sheetName);
if (!sheet) continue;
const smartSheet = new SmartSheet(sheet);
setNamedRanges(smartSheet);
applyCalculatedColumns(smartSheet);
applyCalculatedNamedRanges(smartSheet);
applyValidationRules(smartSheet);
applyFormattingRules(smartSheet);
}
}
function onEdit(e)
{
const sheet = e.range.getSheet();
const sheetName = sheet.getSheetName();
const rules = SHEETCONFIG[sheetName]?.onEditRules;
if (rules) {
const smartSheet = new SmartSheet(sheet);
for (const rule of rules) {
if (rule.condition(e, smartSheet)) {
rule.handler(e, smartSheet);
return;
}
}
}
}
These triggers:
- Initialize the sheet when opened
- Handle dynamic logic on user edits based on your config
-
SHEETCONFIG
-
Keys:
layout
β define structure:columns
,namedRanges
,headerRows
onEditRules
β define edit logic conditionallyvalidationRules
β dynamic input validationformattingRules
β conditional formatting to be appliednamedFunctions
β for future support
-
-
SmartSheet Methods
getRowData(rowNumber)
getColumnValues(colName)
getNamedRange(name)
getCalculatedColumns()
getColumnValidationRule(name)
getColumnFormattingRule(name)
(and many more)
- Declarative > Imperative: Let config describe what your sheet is and how it behaves.
- Reusable: Avoid boilerplate logic across sheets.
- Maintainable: Add/edit/remove sheet logic in config, not in multiple functions.
- Transparent: Anyone reading the config knows the sheet structure and logic.
Framework by Sunandar Gusti, based on experience building complex automation for Google Sheets using clean, modular patterns.
This project is actively maintained. Here are a few next development goals:
-
β Improve
validateSHEETCONFIG
- Add deeper integrity checks (e.g. missing column names, missing formula for calculated columns, missing handler functions).
-
β Implement Config Caching
- Use
CacheService
to store a serialized version of theSHEETCONFIG
object to improve performance, especially in large spreadsheet.
- Use