Google Sheets Automation: Scripts That Work for You

In August 2024, a Google Apps Script I wrote in 20 minutes replaced a manual process that used to take our team 3 hours every Friday. I have a confession: I used to do some absurdly tedious things in Google Sheets. Every Friday afternoon, I would manually sort my client tracker by status, color-code overdue items, copy the summary into an email, and send it to myself as a weekend review. Every Monday, I would manually archive completed rows to a separate sheet. Every time a cell value changed to 'Urgent,' I would get up, walk over to my phone, and set a reminder.

All of this took about 90 minutes per week. That is over 75 hours per year spent on mechanical spreadsheet tasks that follow the exact same steps every time. The kind of work that makes you question your life choices at 4pm on a Friday.

Then I discovered Google Apps Script, and everything changed. Not overnight, and not without some learning curve frustration, but definitively. Today, every one of those tasks runs automatically. My sheets send me emails, archive their own data, color-code themselves, and generate reports while I do actual work. Google Sheets automation is the most underrated productivity tool hiding in plain sight.

This guide is the tutorial I wish I had when I started. No prerequisite coding knowledge required, just a willingness to copy some scripts and understand what they do.

Finding the Apps Script Editor and Writing Your First Script

The google apps script editor is built directly into Google Sheets. You do not need to install anything, sign up for anything, or pay for anything. It is already there, waiting.

To open it, go to any Google Sheet and click Extensions in the top menu, then Apps Script. A new tab opens with a code editor. You will see a file called Code.gs with an empty function called myFunction. This is where your automation lives.

Let me walk you through the simplest possible script so you can see how it works. This google sheets script tutorial starts with the basics before we build anything complex.

In the editor, delete the default content and paste this: function sayHello() { SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('A1').setValue('Hello from Apps Script!'); } Click the Save button (or Ctrl+S), then click the Run button. Google will ask for permissions the first time. Accept them. Now go back to your spreadsheet, and cell A1 will contain 'Hello from Apps Script!'

That is the fundamental pattern of every Google Sheets automation: get a reference to the spreadsheet, get a reference to a sheet or range, and do something with the data. Every script you will build follows this structure, just with more sophisticated logic.

The Apps Script editor also includes a debugger, a logger (Logger.log for output), and an execution history so you can see when your scripts ran and whether they succeeded. I use the execution history daily to verify my automations are running as expected. If something breaks, the error logs tell you exactly which line failed and why.

Five Practical Google Sheets Automations You Can Use Today

Here are five automations I run in my own spreadsheets. Each one solves a real problem, and I will explain not just the what but the why behind each script.

Automation 1: Auto-Send Email When a Cell Changes. This is the automation that convinced me google sheets automation was worth learning. When a cell in my project tracker changes to 'Complete,' the script automatically sends an email to the project stakeholder with a summary of the completed work.

The script uses the onEdit trigger (more on triggers later) to detect changes. When the edited cell is in the Status column and the new value is 'Complete,' it reads the project name, stakeholder email, and completion date from the same row, then uses MailApp.sendEmail() to send a formatted notification. The entire script is about 20 lines of code.

One important note: the simple onEdit trigger cannot send emails because it does not have the necessary permissions. You need to use an installable onEdit trigger instead. I will explain the difference in the triggers section, but the short version is: go to the Triggers page in Apps Script, click Add Trigger, select your function, and choose 'On edit' as the event type. This grants the script permission to send emails and access external services.

Automation 2: Auto-Archive Old Rows. My client tracker accumulates rows over time, and after a project is marked as complete for more than 30 days, I do not need it cluttering my active view. This script runs daily, checks the completion date column, and moves any rows older than 30 days to a separate 'Archive' sheet.

The logic is straightforward: iterate through each row, compare the completion date to today minus 30 days, and if the row qualifies, copy it to the Archive sheet and delete it from the active sheet. The tricky part is iterating in reverse order (bottom to top) when deleting rows, because deleting a row shifts all rows below it up by one. If you iterate top to bottom, you will skip rows. This is a classic bug in google apps script spreadsheet automation that trips up beginners.

Automation 3: Auto-Generate Weekly Reports. Every Monday at 7am, a script runs that reads my sales data, calculates totals by category, computes week-over-week changes, and writes a formatted summary to a 'Reports' sheet. It then sends me an email with the key numbers. This replaced my Friday afternoon report-building ritual entirely.

The script uses SpreadsheetApp to read the data range, plain JavaScript to do the calculations (sums, averages, percentage changes), and formatters to apply currency and percentage styles to the output cells. The email portion uses MailApp.sendEmail with an HTML body that includes a simple table of the results. Total script length: about 50 lines.

Automation 4: Auto-Color-Code by Status. This is the simplest automation on the list but one of the most visually satisfying. When anyone edits the Status column, the script immediately changes the row's background color. 'Not Started' gets light gray, 'In Progress' gets light blue, 'Blocked' gets light red, 'Complete' gets light green.

The script uses the onEdit trigger, checks if the edited cell is in the Status column, reads the new value, and applies the corresponding color using range.setBackground(). The entire script is about 15 lines. It sounds trivial, but when you have a sheet with 50 active projects, the instant visual feedback makes scanning for blocked items effortless. I used to do this manually every morning. Now I do not think about it at all.

Automation 5: Auto-Notify on Approaching Deadlines. This script runs daily and checks a 'Due Date' column against today's date. If a deadline is within 3 days and the status is not 'Complete,' it sends me a notification email listing all the approaching deadlines. If a deadline has passed, it sends a separate 'overdue' alert.

This is the automation I recommend starting with if you manage any kind of deadline-driven work. Missing deadlines because you forgot to check the spreadsheet is embarrassing and entirely preventable. The script checks once a day, and you get a single consolidated email with everything that needs attention. It connects directly to the principle I wrote about in how to stop letting email run your day, except here the email is working for you instead of against you.

Understanding Google Sheets Triggers

Google sheets triggers are the mechanism that makes your scripts run automatically. Without triggers, you would have to manually click Run every time, which defeats the purpose of automation.

There are two types of triggers: simple and installable. Understanding the difference is crucial for anyone serious about google sheets automation.

Simple triggers are functions with reserved names that Google Sheets automatically recognizes. The most common ones are onOpen (runs when the spreadsheet opens), onEdit (runs when any cell is edited), and onSelectionChange (runs when the user selects a different cell). Simple triggers are easy to use but have restrictions: they cannot access services that require authorization (like sending email, accessing other files, or making HTTP requests), they have a 30-second execution limit, and they only run for the user who created them.

Installable triggers are set up through the Apps Script dashboard and can do everything simple triggers can do plus much more. They can send emails, access external APIs, run as a specific user regardless of who edits the sheet, and they have a 6-minute execution limit. To create one, go to the Apps Script editor, click the clock icon on the left sidebar (Triggers), and click 'Add Trigger.' You choose the function, the deployment, the event source, and the event type.

The most powerful google sheets triggers for automation are the time-driven ones. I use a daily trigger for my archive script (runs at midnight), a weekly trigger for my report generator (runs Monday at 7am), and a daily trigger for my deadline checker (runs at 8am). These run in the background regardless of whether I have the spreadsheet open, which is the real magic of automate google sheets workflows.

One important limitation: each Google account can have a maximum of 20 triggers per script project. If you need more, split your automations across multiple script projects. Also, time-driven triggers have a minimum frequency of 1 minute, but running scripts too frequently will hit your daily execution quota.

Macros vs Scripts: When to Use Which

Google sheets macros are often confused with Apps Script, but they serve different purposes and knowing when to use each saves you time.

Macros are recorded actions. You click Extensions, then Macros, then Record macro. Then you perform a series of actions in the spreadsheet (formatting, sorting, moving data), and Google records those actions as a script. When you run the macro, it replays those exact actions. Macros are perfect for repetitive formatting tasks, like applying your standard report style to a new dataset.

The key limitation of google sheets macros is that they are literal recordings. If your data changes shape, size, or location, the macro will still try to perform the original actions on the original cells. It does not adapt. If you recorded a macro that formats columns A through F, and then you add a column G, the macro will not include it.

Scripts are programmatic. They use logic, conditions, loops, and dynamic references. They adapt to changing data because you write them to find data by content, not by fixed cell position. A script that archives completed rows will work whether your sheet has 10 rows or 10,000 rows.

My rule of thumb: use macros for one-off formatting tasks that you apply to similarly structured sheets. Use scripts for anything that needs to run automatically, adapt to changing data, or interact with external services. In practice, I use macros about 10% of the time and scripts 90% of the time. Once you learn the basics of Apps Script, writing a 10-line script is often faster than recording and debugging a macro.

Connecting Google Sheets to External APIs

One of the most powerful and underexplored features of google apps script is the ability to call external APIs directly from your spreadsheet. This opens up a world of possibilities that go far beyond what spreadsheet formulas can do.

The function you need is UrlFetchApp.fetch(url, options). This sends an HTTP request to any URL and returns the response. You can use it to pull data from REST APIs, post data to webhooks, or integrate with virtually any web service that has an API.

For example, I have a script that pulls current exchange rates from a free API every morning and updates a currency conversion sheet. Another script sends a Slack message via webhook whenever a new row is added to a specific sheet. A third script pulls weather data and adds it to a travel planning spreadsheet.

The pattern is always the same: call UrlFetchApp.fetch with the API endpoint, parse the JSON response using JSON.parse, extract the data you need, and write it to your sheet using setValue or setValues. Most APIs return JSON, and Apps Script handles JSON natively since it is JavaScript under the hood.

Important security note: if an API requires an API key, never hardcode it in your script. Use PropertiesService.getScriptProperties().getProperty('API_KEY') to store sensitive credentials. Set the property once through the Script Properties interface, and your key stays out of the code. This is especially important if you share the spreadsheet with others, as they could view the script source.

One practical application that ties into email workflow: I have a script that checks my email task list (managed through the Gmail API) and updates a Google Sheet with pending tasks. This creates a dashboard view of email-based tasks that I can sort, filter, and prioritize visually. The concept is similar to what I described in how AI reads your email and creates tasks, but the spreadsheet version gives you full control over the display and organization.

When to Use Zapier Instead of Apps Script

As much as I love Google Sheets automation through Apps Script, there are situations where Zapier or Make is the better choice. Recognizing this boundary saves you from building fragile scripts when a no-code tool would do the job better.

Use Zapier when the automation involves multiple non-Google services. If you need to connect Shopify to Google Sheets to Slack to Mailchimp, building that chain in Apps Script means dealing with four different APIs, authentication flows, and error handling scenarios. Zapier handles all of that through pre-built integrations. The 15 minutes it takes to set up a Zap versus the 4 hours it takes to code the same thing in Apps Script makes the choice obvious.

Use Zapier when you do not want to maintain code. Scripts break when APIs change, when Google updates the Apps Script runtime, or when someone accidentally edits the script file. If you are not comfortable debugging JavaScript, a Zapier automation is more maintainable. It updates its own integrations and alerts you when something breaks in plain language.

Use Apps Script when the automation is sheet-centric. If the primary action happens inside Google Sheets (formatting, calculating, archiving, generating reports), Apps Script is faster, more reliable, and free. There is no reason to pay for Zapier to do something that Apps Script handles natively. If you want to automate google sheets operations specifically, Apps Script is purpose-built for it.

Use Apps Script when you need speed. Zapier has inherent latency because it polls for changes on a schedule (every 1 to 15 minutes depending on your plan). Apps Script triggers like onEdit fire instantly. If you need a notification the moment a cell changes, Apps Script is the only option.

In my own workflow, I use Apps Script for all five of the automations I described earlier because they are sheet-centric. But my weekly metrics dashboard, which pulls data from five different services into Google Sheets, runs on Make because the multi-service orchestration is dramatically easier with a no-code tool. I wrote about the broader comparison of these tools in my guide on workflow automation for solo founders, and the same principles apply here.

Limitations and Best Practices for Google Sheets Automation

Before you automate everything, there are real limitations to be aware of. Google imposes quotas on Apps Script that can catch you off guard if you do not plan for them.

Daily execution limits: free Google accounts get 90 minutes of total script runtime per day. Google Workspace accounts get 6 hours. If you have multiple scripts running frequently, you can hit this ceiling. Monitor your usage in the Apps Script dashboard under Executions.

Email sending limits: MailApp.sendEmail has a daily limit of 100 emails for free accounts and 1,500 for Workspace accounts. If your automation sends notification emails for every edit in a busy sheet, you will hit this limit fast. Batch your notifications: instead of sending one email per event, collect events throughout the day and send a single digest email.

The 6-minute execution limit for installable triggers means your scripts need to be efficient. Processing 10,000 rows is fine if your logic is simple, but complex calculations on large datasets can time out. If you hit the limit, break your script into chunks using PropertiesService to track progress between runs.

Best practices I have learned the hard way: always add error handling with try-catch blocks so a single failure does not crash the entire script. Log important events using Logger.log so you can debug issues after the fact. Use meaningful variable names because you will come back to this code in six months and forget what 'x' was supposed to be. And keep a backup copy of your scripts in a separate document, because accidentally deleting a script file with active triggers is a special kind of pain.

Google Sheets automation through Apps Script is one of the highest-leverage skills a spreadsheet-heavy professional can learn. The barrier to entry is low: if you can follow a this google sheets script tutorial and paste code into an editor, you can automate your first workflow today. The ceiling is surprisingly high: I have seen solo founders run entire businesses on Google Sheets automations, from order processing to customer communication to financial reporting.

Start with one of the five automations I described. The deadline notifier is the easiest to implement and gives the fastest value. Once you see it working, you will immediately start thinking about what else you can automate. And when your automations grow beyond what spreadsheets can handle, that is where dedicated tools like Mursa come in, bridging the gap between your data in Sheets, your emails in Gmail, and the tasks that tie everything together.