How to Set Up Automatic Alerts in Google Sheets

Google Sheets can email you when something changes, and there are three ways to set it up. Notification settings email you when someone else edits the sheet or submits a form. Conditional notifications email you when a cell changes to a value you choose, but only on certain work or school accounts. A short Apps Script can watch a calculated number, such as the lowest projected cash balance, and it runs on personal and Google Workspace accounts, subject to your permissions and any administrator settings. Start with the simplest method that does the job.

Decide What the Alert Is For First

An alert is a message that asks someone to do something. If nobody knows what to do when it arrives, it becomes noise, and people learn to ignore it. Before you set one up, write down the number it watches, the level that counts as a problem, who gets the email, and what that person does next. This article covers the mechanics, not which numbers deserve an alert.

Also try the free option first: a color that shows up when you open the sheet (Method 3). An email makes sense when nobody opens the sheet often enough to notice the color.

Method 1: Turn On Notification Settings for Edits and Form Submissions

This is the built-in option for knowing that something happened in a shared sheet. It needs no code and no special account. The setting applies to you only, and it doesn’t notify you about your own edits.

1. Open the sheet.
2. Click Tools, then Notification settings, then Edit notifications.
3. Under “Notify me when,” choose Any changes are made or A user submits a form.
4. Under “Notify me with,” choose Email – daily digest or Email – right away.
5. Click Save.

Use “right away” when someone is waiting on the update, such as a lead form that a salesperson should answer the same day. Use the daily digest for a sheet your team edits constantly.

The limit is that these notifications tell you a change happened. They don’t read what the change was, so they can’t tell you that cash dropped below $10,000 or that a stock count hit zero.

Method 2: Conditional Notifications on Certain Work or School Accounts

Conditional notifications email you when a cell’s value changes to something you specify. Google says the feature is available only to certain work or school accounts, so a personal Gmail account won’t see it. If your menu has it, setup takes a minute:

1. Click Tools, then Conditional notifications. You can also right-click a cell.
2. Click Add rule.
3. Under “In this column,” pick a column or a custom range.
4. Click Add condition and set the test, for example, Text is exactly “Completed.”
5. Under “Then take the following action,” type the email addresses or choose a column that contains them.

This works well for a status column. A rule on the “Status” column can email the account manager when a row changes to “Overdue.”

Know the limits before you rely on it:

• You can add individual Gmail or Google Workspace addresses. Group addresses and non-Google addresses such as Outlook or Yahoo aren’t supported.
• Emails may not be immediate, and several changes can be combined into one email.
• Volatile functions recalculate with any change to the sheet, so you may miss changes they produce, especially while the file is closed. Changes that come from outside sources such as Connected Sheets or other documents don’t trigger rules.
• A change in how a value is formatted (decimal places, for example) doesn’t count.

If your account doesn’t have the feature, or you need to check a number that’s calculated across a row, use Method 4.

Method 3: Color the Cell So the Problem Shows Up When You Open the Sheet

Conditional formatting is not an email, but it takes two minutes and works on every account. For many sheets, it’s enough.

For the 13-week cash flow tracker, where ending cash is in B14:N14 and the minimum is in A17:

1. Select B14:N14.
2. Click Format, then Conditional formatting.
3. Under “Format rules,” choose Custom formula is and enter =B14<$A$17.
4. Set a soft red fill with dark red text. Skip the neon colors; people stop seeing them.

The same approach works for inventory below a reorder point, or a receivable past due: pick the cells, choose Less than or a custom formula, and point it at the cell that holds your limit. The color catches the problem when someone opens the sheet. If you need to be told when no one has opened it, go to Method 4.

Method 4: Email an Alert With a Short Apps Script

Apps Script is Google’s built-in scripting tool for Sheets. It’s included with personal and Google Workspace accounts, subject to your account’s permissions and any restrictions your administrator sets on a work account. The script below checks the projected ending cash for all 13 weeks and sends one email when the lowest value falls under your minimum. It’s written for the layout in the cash flow tracker template: a tab named “Cash Flow,” week start dates in B1:N1, projected ending cash in B14:N14, and the minimum cash level in A17. If your sheet is laid out differently, change those references.

The script is strict on purpose. It needs a real date in every cell of B1:N1, a number in every cell of B14:N14, and a number in A17. If anything else is there, such as an error like #REF!, a blank cell, or text, the script stops with an error message and does nothing else. A broken forecast shouldn’t be mistaken for a healthy one, and it can’t clear a standing alert.

function checkCashAlert() {
  var lock = LockService.getScriptLock();
  lock.waitLock(30000);
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Cash Flow");
    if (!sheet) {
      throw new Error('No tab named "Cash Flow".');
    }
    var weeks = sheet.getRange("B1:N1").getValues()[0];
    var ending = sheet.getRange("B14:N14").getValues()[0];
    var minimum = sheet.getRange("A17").getValue();

    if (typeof minimum !== "number") {
      throw new Error("A17 must hold a number: the minimum cash level.");
    }

    var lowest = null;
    var lowestWeek = null;
    for (var i = 0; i < ending.length; i++) {
      var column = String.fromCharCode(66 + i);
      if (!(weeks[i] instanceof Date) || isNaN(weeks[i].getTime())) {
        throw new Error(column + "1 must be a date.");
      }
      if (typeof ending[i] !== "number") {
        throw new Error(column + "14 must be a number, but it holds: " + ending[i]);
      }
      if (lowest === null || ending[i] < lowest) {
        lowest = ending[i];
        lowestWeek = weeks[i];
      }
    }

    var props = PropertiesService.getScriptProperties();
    var alreadyAlerted = props.getProperty("CASH_ALERT_ACTIVE") === "true";

    if (lowest < minimum) {
      if (!alreadyAlerted) {
        var weekText = Utilities.formatDate(lowestWeek, ss.getSpreadsheetTimeZone(), "MMM d, yyyy");
        MailApp.sendEmail(
          "you@yourcompany.com",
          "Cash alert: projected balance falls below your minimum",
          "The lowest projected ending cash is $" + lowest.toLocaleString() +
          " in the week starting " + weekText + ". Your minimum is $" +
          minimum.toLocaleString() + ".\n\n" + ss.getUrl()
        );
        props.setProperty("CASH_ALERT_ACTIVE", "true");
      }
    } else if (alreadyAlerted) {
      props.deleteProperty("CASH_ALERT_ACTIVE");
    }
  } finally {
    lock.releaseLock();
  }
}

To install it:

1. In your sheet, click Extensions, then Apps Script.
2. Delete the sample code and paste the script.
3. Change the tab name ("Cash Flow"), the ranges, and the email address to match your sheet.
4. Click Save project.
5. Select checkCashAlert in the function dropdown and click Run.
6. A box titled “Authorization required” appears, with Cancel and Review permissions buttons. Click Review permissions. Google then opens a separate window where you choose your account and approve access. The script reads your spreadsheet and sends email as you, so expect those two permissions to be listed. Read them before you approve. For a script you wrote or pasted yourself, Google may also warn that the app isn’t verified. After you approve, the run finishes and the execution log at the bottom shows “Execution completed”.

What the Script Does

The script first locks the sheet so two runs can’t overlap, then checks the sheet. It finds the lowest ending cash and the week it falls in, and compares that number with your minimum.

If the lowest number is under the minimum, it sends one email that names the amount, the week, and the sheet link. The week is shown in the spreadsheet’s time zone. It also saves a flag called CASH_ALERT_ACTIVE. While the flag is set, the script stays quiet, so you don’t get the same email every morning while cash stays low. When the lowest number is back at or above the minimum, the script clears the flag, and the next breach sends a new email. If the email can’t be sent, the flag isn’t set, so the next run tries again.

A quiet script is not proof that cash is fine. The script watches weekend balances only, so a balance that dips below your minimum in the middle of a week and recovers by the weekend won’t trigger it. Use it with the dated payment list described in the cash flow tracker article.

Test it before you trust it, and don’t schedule anything until the test passes. The test changes your minimum on purpose, so either do it in a copy of your sheet (File, then Make a copy, and check that the script came along under Extensions, then Apps Script; if it didn’t, paste it in again) or write down your real minimum first and put it back at the end.

1. Write down the number in A17. In any empty cell, enter =MIN(B14:N14) to see your lowest projected ending cash. Call that number L, then delete the cell.
2. Healthy state. Set A17 to L. Run the script. Nothing should arrive, because a balance equal to the minimum is not a breach. This also clears any alert the script remembered from before.
3. Breach. Set A17 to a number above L, such as L plus 1,000. Run the script. The email should arrive and name the lowest amount and its week.
4. Suppression. Run the script again. No second email should arrive.
5. Recovery and re-breach. Set A17 back to L and run the script, which clears the alert. Set A17 above L again and run it. A new email should arrive.
6. Bad data. Click one ending cash cell and copy its formula from the formula bar to a safe place. Replace the formula with a word and run the script. It should stop with a red error in the execution log, such as “J14 must be a number, but it holds: oops”, and send nothing. A real error value such as #REF! is reported the same way. Then paste the original formula back, confirm the numbers return, and run the script again to confirm it works.
7. Put it back. Set A17 to the real minimum you wrote down in step 1 and run the script once more. If your real forecast is below that minimum, the alert stays active; an email is sent only if the flag is not already set.

Schedule the Script So It Runs Without You

A script that you have to run by hand isn’t an alert. Set a trigger so Google runs it on a schedule:

1. In the Apps Script editor, click the Triggers icon (the clock) on the left.
2. Click Add Trigger (bottom right).
3. Under “Choose which function to run,” pick checkCashAlert. Leave the deployment on Head.
4. Change “Select event source” from its default, From spreadsheet, to Time-driven. Set the type of time-based trigger to Day timer and the time of day to 6 am to 7 am. The time zone appears under that choice, for example (GMT-04:00).
5. Under “Failure notification settings,” change Notify me daily to Notify me immediately, so a run that stops with an error reaches you.
6. Click Save.

Google runs the script at some point inside the window you pick and keeps that time consistent from day to day. The window follows the script project’s time zone, which you can see in the trigger dialog and change under Project Settings, in the Time zone box. Your spreadsheet has its own time zone, and the two can differ. The alert email shows the week’s date in the spreadsheet’s time zone.

A trigger runs as the account of the person who created it. Create it from the account that should own the alert, and recreate it if that person leaves.

A once-daily check has a blind spot. If a breach starts and ends between two checks, you won’t hear about it. You can add an On Edit trigger to run the check whenever someone changes a cell by hand. It responds to edits made by people, not to changes made by scripts or other programs, and the lock keeps the two triggers from sending duplicate emails. This script checks the cash minimum only; it isn’t a general tool for alerting on text statuses.

Keep Alerts from Becoming Noise

Three habits keep an alert useful.

Latch the alert. The script above sends an email only when the number crosses into a problem, and stays quiet until it recovers. A script that emails every morning while the number stays low trains you to ignore it. The limit of the latch is that cash can keep falling after the first email without a second one, until it recovers above the minimum and falls again. Name one person who owns the follow-up, and what they do when the email arrives.

Choose the line for the right reason. A cash minimum isn’t a statistical band. Set it from what’s coming: the payroll and fixed payments due in the next few weeks, and how quickly you could collect or borrow if you needed to. For performance numbers such as inquiries or conversion rate, a different rule works. Look at how much the number has moved over the last six to twelve months, treat movement inside that range as ordinary, and set the line outside it. Do this for each number separately, because each moves differently.

Mind your limits. Google caps how many email recipients an account can send to in a day: 100 on a personal Gmail account and 1,500 on a Google Workspace account, according to Google’s Apps Script quotas, which can change. A morning check that emails one person uses one recipient. These limits are shared across everything the account does with Apps Script, and trial accounts can have lower limits.

When the Alert Doesn’t Arrive

A missing email doesn’t prove the numbers are fine. Check these in order:

• The trigger exists. Open the Triggers page in Apps Script and confirm the entry is there.
• The script ran. Open the Executions page and look for a run at the expected time. Each row shows whether it was started from the editor or by a time-driven trigger, and whether it completed or failed. A run that stopped on bad data shows as Failed, with the error message the script threw, such as which cell isn’t a number.
• The names still match. If you renamed the tab or moved the rows, the script stops with an error. Update the references.
• The sheet has errors, blanks, or text where numbers or dates belong. The script stops and doesn’t email. Fix the cells it names.
• The email went to spam. Check the spam folder, then mark the message as not spam.
• The flag is stuck. If the script thinks it already alerted, it stays quiet until the forecast recovers. You can see the flag under Project Settings, in the Script Properties list: CASH_ALERT_ACTIVE with the value true. To reset it, set A17 to your lowest ending cash or below, run the script once, then restore A17 (steps 2 and 7 of the test above).

When Paid Automation Tools Are Worth It

Native tools cost nothing, so use them until they stop being enough. Paid automation services make sense when one event has to update several apps at once, such as texting a manager, creating a CRM record, and posting to a team channel. They also make sense when non-technical people have to build and change rules themselves, or when your volume goes past the daily email limits. Prices change, so check current plans before you commit. How much a small business should spend on data tools covers the spending decision.

Frequently Asked Questions

Can I set up Google Sheets alerts on a personal Gmail account?

Yes, with notification settings (Method 1) and Apps Script (Method 4). Conditional notifications are limited to certain work or school accounts. On a work account, your administrator may restrict scripts.

Do the alerts work when the sheet is closed?

A time-driven script trigger runs on Google’s servers whether or not you have the sheet open. Notification settings email you about changes other people make while you’re away.

Does this cost anything?

No. Notification settings, conditional formatting, and Apps Script are included with your Google account. They do have limits: you have to authorize the script, Apps Script has daily quotas and runtime limits, and a work account can be restricted by an administrator.

Do I need to know how to code?

Not to use this script. You change four things (tab name, ranges, email, trigger) and test it. If you change the layout of your sheet later, update the references too.