Google Sheets is a fantastic tool for tracking inventory, project statuses, or task assignments. However, a spreadsheet is only useful if people remember to look at it. If you use a Google Sheet to track critical stock levels, you don’t want to manually check it every day; you want the spreadsheet to actively email you the exact moment the inventory drops below a specific threshold.
While third-party automation tools like Zapier charge monthly fees for this functionality, Google Sheets has a vastly more powerful, completely free solution built right in: Google Apps Script.
The Scenario
Imagine you have a project tracker. Column A contains the Task Name, and Column B contains the Status (e.g., “Pending”, “In Progress”, “Done”). We will write a script that automatically fires off an email alert to your inbox the moment any cell in Column B is changed to the word “Done”.
Step 1: Access Google Apps Script
- Open your Google Sheets document.
- In the top menu bar, click on Extensions.
- Select Apps Script. A new browser tab will open displaying a blank coding environment.
Step 2: Write the Automation Code
Delete the default function myFunction() {} block and paste the following JavaScript code into the editor:
function sendEmailOnEdit(e) {
// 1. Get the cell that was just edited
var range = e.range;
var sheet = range.getSheet();
// 2. Check if the edit happened on the correct sheet (e.g., "Sheet1")
if (sheet.getName() !== "Sheet1") return;
// 3. Check if the edit happened in Column B (Column index 2)
if (range.getColumn() !== 2) return;
// 4. Check if the new value of the cell is exactly "Done"
if (e.value === "Done") {
// 5. Gather data for the email
var row = range.getRow();
var taskName = sheet.getRange(row, 1).getValue(); // Gets the task name from Column A
var emailAddress = "[email protected]"; // Change this to your actual email
var subject = "Task Completed: " + taskName;
var message = "The task '" + taskName + "' on row " + row + " has just been marked as Done.";
// 6. Send the email!
MailApp.sendEmail(emailAddress, subject, message);
}
}Make sure to change "[email protected]" to the address where you want to receive the alerts. Click the Save icon (the floppy disk) at the top of the editor.
Step 3: Create the Trigger
The code is written, but Google Sheets doesn’t know when to run it. We must create an “Installable Trigger” to link the code to a physical edit in the spreadsheet.
- On the left-hand sidebar of the Apps Script window, click the Triggers icon (it looks like a small alarm clock).
- Click the large blue Add Trigger button in the bottom right corner.
- Configure the pop-up window exactly like this:
- Choose which function to run: sendEmailOnEdit
- Choose which deployment should run: Head
- Select event source: From spreadsheet
- Select event type: On edit
- Click Save.
Step 4: Grant Permissions
Because your script is attempting to send an email on your behalf, Google will immediately throw a terrifying security warning stating “Google hasn’t verified this app.”
Click Advanced at the bottom of the warning, then click Go to Untitled project (unsafe). Click Allow to grant the script permission to use your Gmail account to send the automated messages.
Return to your Google Sheet. Change a status in Column B to “Done”. Within five seconds, an automated email will land in your inbox alerting you of the completed task.