Apps Script for Non-Developers: 10 Patterns That Handle 90% of Needs
Apps Script is, quietly, one of the most useful pieces of software Google makes - and one of the most under-used by the people it could help most. If you can write a spreadsheet formula, you can already write Apps Script. The syntax is JavaScript, but you don't need to understand JavaScript as a language to copy, adapt, and ship the kind of automation that would otherwise require a paid Zapier subscription or an engineer's weekend.
The ten patterns below are the ones we see solve real problems most often. Each is a complete, runnable snippet you can paste into the Apps Script editor (Extensions → Apps Script in any Google Sheet) and modify for your needs. Together they cover roughly 90% of what we hear non-developers ask for.
How to use any of these
Open a Google Sheet. Click Extensions → Apps Script. A new tab opens with an editor. Paste any of the snippets below into the editor, hit Save, and then either run the function manually (▶ button) or wire it to a trigger (clock icon in the left sidebar). The official Apps Script docs are surprisingly readable if you need to look up something specific.
Pattern 1: Send an email when a specific cell changes
function onEdit(e) {
const sheet = e.source.getActiveSheet();
if (sheet.getName() !== 'Status') return;
const cell = e.range;
if (cell.getColumn() !== 5) return; // Column E
if (cell.getValue() !== 'Done') return;
const row = cell.getRow();
const taskName = sheet.getRange(row, 1).getValue();
MailApp.sendEmail({
to: '[email protected]',
subject: 'Task completed: ' + taskName,
body: 'Row ' + row + ' marked Done.'
});
}onEdit is a special trigger name - Apps Script runs this function automatically whenever any cell in the sheet changes. The guards at the top filter to just the cell pattern you care about.
Pattern 2: Read a column, write back the result of a transformation
function uppercaseColumnB() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B2:B' + sheet.getLastRow());
const values = range.getValues();
const result = values.map(row => [String(row[0]).toUpperCase()]);
range.setValues(result);
}This is the workhorse pattern. Read a range, transform the values in memory, write them back in one batch operation. Reading and writing in batches (not cell by cell) makes the script 100x faster than the naive version.
Pattern 3: Add a custom menu to the sheet
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Tools')
.addItem('Uppercase column B', 'uppercaseColumnB')
.addItem('Send weekly digest', 'sendWeeklyDigest')
.addToUi();
}This turns any function in your script into a clickable menu item. Once you've shipped a few automations, this is what makes them discoverable for the rest of your team - they don't have to know about Apps Script, they just see a "Tools" menu in the spreadsheet.
Pattern 4: Run something on a schedule
function createDailyTrigger() {
ScriptApp.newTrigger('sendDailyReport')
.timeBased()
.atHour(9)
.everyDays(1)
.create();
}Run createDailyTrigger once. From then on, Apps Script will execute sendDailyReport() every day at 9 am. Works the same way for hourly, weekly, or monthly schedules. The trigger documentation has the full options table.
Pattern 5: Append a row to a sheet from anywhere
function logEvent(eventName, eventValue) {
const sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('Log');
sheet.appendRow([new Date(), eventName, eventValue]);
}The combination of openById and appendRow turns any Google Sheet into a lightweight database that any other script can write into. Useful for cross-sheet logging, simple analytics, or capturing events from a separate web app.
Pattern 6: Call an external API
function fetchWeather(city) {
const url = 'https://api.example.com/weather?q=' + encodeURIComponent(city);
const response = UrlFetchApp.fetch(url, {
headers: { 'Authorization': 'Bearer YOUR_API_KEY' },
muteHttpExceptions: true
});
if (response.getResponseCode() !== 200) return null;
return JSON.parse(response.getContentText());
}UrlFetchApp is the bridge that lets your spreadsheet talk to anything on the internet that has an HTTP API. Stripe, HubSpot, your own backend, the OpenAI API - all of them. muteHttpExceptions is important; without it, any non-200 response throws and aborts your script.
Pattern 7: Use a script as a custom spreadsheet function
/**
* Returns the domain portion of an email address.
* @param {string} email The email address.
* @return The domain (e.g., "gmail.com").
* @customfunction
*/
function EMAIL_DOMAIN(email) {
if (!email || typeof email !== 'string') return '';
const at = email.indexOf('@');
return at === -1 ? '' : email.substring(at + 1);
}Once saved, you can use =EMAIL_DOMAIN(A2) in any cell. The JSDoc comment is what makes it appear in Sheets' formula autocomplete. Custom functions cache their results, so they only re-run when their input changes.
Pattern 8: Process all rows that match a condition
function processNewLeads() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
const status = data[i][3]; // Column D
if (status !== 'New') continue;
const email = data[i][1];
sendWelcomeEmail(email);
sheet.getRange(i + 1, 4).setValue('Processed');
}
}The pattern: read everything, loop, skip the rows that don't match, do something with the ones that do, and mark them so they don't get processed twice. This handles 90% of "do something to all the new submissions" automations.
Pattern 9: Write to the active cell from a UI prompt
function askAndWrite() {
const ui = SpreadsheetApp.getUi();
const response = ui.prompt('Enter a customer name:');
if (response.getSelectedButton() === ui.Button.OK) {
SpreadsheetApp.getActiveRange().setValue(response.getResponseText());
}
}Combine this with Pattern 3 (custom menu) and you've got an interactive form built directly into the spreadsheet. Useful for data-entry workflows where you want a single field of focused input rather than typing into a cell.
Pattern 10: Lock execution to prevent concurrent runs
function processQueue() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // wait up to 5s for the lock
try {
// ... do work that should never run twice in parallel ...
} finally {
lock.releaseLock();
}
}If your script can be triggered by both an onEdit and a time-based trigger - or by multiple users in the same sheet - you can end up with two copies running at the same time and corrupting each other's writes. LockService prevents that. Wrap any sensitive write logic in this pattern.
What these ten don't cover (and what to do about it)
The patterns above handle: triggers (on edit, on open, on schedule), batch reads/writes, custom UI (menus, prompts), API calls, custom functions, and concurrency safety. Combined, they cover the vast majority of "I wish Google Sheets could just..." scenarios.
What they don't cover: anything that needs to be exposed as a public web service (use Apps Script web apps), anything that needs to handle huge volumes of data (Apps Script has a 6-minute execution limit per run), and anything that requires deep state management across runs (use PropertiesService for simple key/value state, or write to a database via Pattern 6).
The two most common gotchas
Quota limits. Free Apps Script accounts can send about 100 emails per day, make about 20,000 URL fetches, and run for 6 hours of total execution per day. The quotas page lists everything. For most teams these are generous; for high-volume use, Workspace accounts get higher limits.
The first run requires authorization. The first time you run a function that touches Gmail, Drive, or external URLs, Google will prompt you to authorize the script. This is normal and a good thing. After you authorize once, it runs silently from then on.
Where to go next
The ten patterns above will carry you a long way. When you hit something they don't cover, the Apps Script documentation is searchable and the community on Stack Overflow is large and helpful. The natural next step beyond Apps Script - when you need things like multi-day workflows, conditional branching across services, or proper version control - is a dedicated workflow tool like n8n or similar. But for the vast majority of in-spreadsheet automation, Apps Script remains the lowest-friction option that exists.