Key takeaways
- Automate the handoffs, not just the calculations.
- Use built-in features before writing Apps Script.
- Choose the trigger to match the event: edit, form submit, or schedule.
- Log runs and alert on failure, or the automation will fail quietly.
On this page
Map the manual workflow
Start with the steps a person performs each week: collecting responses, copying rows, checking a status, sending a reminder, producing a summary. Write them in order and mark which steps need judgement.
The repeatable steps are candidates for automation. The judgement steps should remain human, with the sheet presenting the information needed for the decision. Automating a bad process only makes the bad process faster.
Use built-in features first
Many workflows need no code. Data validation keeps entries consistent, conditional formatting highlights exceptions, and protected ranges prevent accidental edits. Formulas such as QUERY, FILTER, and IMPORTRANGE can assemble a view from other sheets.
Google Sheets can also alert on changes, and Google Forms can notify the owner when a response arrives. Connected Sheets lets teams analyse large sources without importing every row, where access allows. These features are easier to maintain than a script and usually sufficient for a small team.
Choose the right trigger
Apps Script can run on a schedule, when a user edits a cell, when a form is submitted, or when the spreadsheet opens. Installable triggers run under the account that created them and can access services that simple triggers cannot.
The trigger should match the event that matters. A time-driven trigger suits a daily summary or a reminder. An edit trigger suits validation or a status update. A form-submit trigger suits intake. Using an edit trigger for a heavy job will slow the sheet and may run more often than intended.
function onFormSubmit(event) {
const sheet = event.range.getSheet();
const row = event.range.getRow();
const status = sheet.getRange(row, 5);
if (status.getValue() === "") {
status.setValue("Needs review");
sheet.getRange(row, 6).setValue(new Date());
}
}Write scripts that can fail safely
A script should do one job, read its configuration from a settings sheet, and write a log when it runs. Use LockService when two executions could overlap, and keep row references stable by using IDs rather than row numbers.
Wrap the main steps in error handling and send an alert with the failure details. Quotas apply to Apps Script, so batch operations and avoid unnecessary reads and writes inside loops. A script that silently stops is worse than a manual reminder.
Connect the workflow end to end
Apps Script can call external services with UrlFetchApp, send email with GmailApp or MailApp, create files in Drive, and add events to Calendar. That makes Sheets a practical front end for a small workflow: intake, validation, notification, and a record of what happened.
Keep credentials out of the sheet and use the permissions the script actually needs. Document who owns the trigger, what it touches, and how to disable it. The handover matters as much as the code.
- Form submit: validate the row and notify the owner.
- Time-driven: refresh a summary and email it.
- Edit: update a status and log the change.
- Web app: accept a request from outside the sheet.
Decide when to leave Sheets
Sheets has limits, and automation makes them arrive sooner. A script that reads every row on every edit, a file with years of history, or a workflow with dozens of simultaneous users will become slow or unreliable. Watch for repeated quota errors, long run times, and edits that overwrite each other.
Move the records to a database when the sheet is acting as the system of record, and keep Sheets as an input or reporting view. Use a BI tool when the audience needs governed dashboards rather than a shared grid. The automation can remain useful at the edge of a better data model, and the right time to move is before an automated process fails at a critical moment.
Frequently asked questions
Is Apps Script required to automate Google Sheets?+
No. Formulas, validation, protected ranges, and notifications cover many workflows. Use Apps Script when the step needs logic, an external service, or a trigger that built-in features cannot provide.
What is the difference between a simple and an installable trigger?+
Simple triggers run automatically for events such as edit or open but have restrictions. Installable triggers require authorisation, run as the installer, and can access more services.
Why does an onEdit script not run when a formula changes?+
Edit triggers respond to user edits, not recalculated formula results. Use a time-driven trigger or a different design if the change originates in a formula.
How can automations be stopped from sending duplicate emails?+
Record a sent flag or timestamp on the row and check it before sending. Use LockService to prevent overlapping runs from processing the same row.
Need help applying this?
Improve your excel & google sheets.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
Google Sheets Apps Script triggers explained
Triggers decide when Apps Script runs, and the wrong type runs too often, too late, or without permission. Choose the event and make runs repeatable.
Fix the spreadsheet or replace it? A simple guide
A spreadsheet is often the right tool. Here is how to decide whether to fix it, automate it, or replace it.