Tag: Spreadsheet Management

To get alerted when your business numbers change without drowning in noise, follow four rules. Alert only on events that force a decision. Set each line according to the kind of number it watches. Make every alert say what happened, who owns it, what to do, and by when. Then review the whole list regularly, and for any alert nobody acted on, find out why before you remove it.

Monitoring Is Not Alerting

Monitoring is a number you can look at when you choose to: a dashboard, a scorecard, a monthly report. Alerting is a number that interrupts you. They do different jobs, and most alert problems start when one is used as the other.

Anything that can wait until the next review belongs on the scorecard. How many KPIs a small business should track explains why four to seven numbers is enough to watch. An alert list should be much shorter than that. If you're not sure where a number belongs, put it on the scorecard first. You can promote it later.

Why Alert Systems Decay

An alert system usually starts with enthusiasm. You set up alerts for everything you'd hate to miss. Within a few weeks the messages arrive daily, people skim them, and somebody builds an inbox rule that files them away. When a real problem finally triggers one, it sits in a folder nobody opens.

Software makes sending a notification free. Attention is the part that costs something. The fix is not a better tool. It's a smaller list, with a clear reason for each item to be there.

Industrial plants have dealt with this for a long time. ISA's own explanation of alarm management, built around the ANSI/ISA-18.2 standard, describes rationalization: each alarm is justified, and its consequence, the expected response, and the response time are documented. ISA's overview of alarm rationalization is worth a read. A small business doesn't need that formality, and this article isn't claiming to follow the standard, but the discipline carries over.

What Deserves an Alert

Two tests keep the list short. First, does the number help the business make more revenue, run more efficiently, or cost less to operate? If not, leave it off. Second, if it moves, is there a decision someone must make soon, and can they make it? If the answer is "we'll look at it at the monthly review," it's a report.

Typical alert material: cash projected to fall below payroll, a product about to run out, a payment system that has stopped working, a key customer's orders stopping. Typical report material: utilization, retention, average ticket, anything you review monthly to see which way it's heading.

Two Kinds of Line

A threshold is the line a number crosses to trigger an alert. There are two ways to set one, and they suit different numbers.

A hard limit comes from what the business must be able to do, not from history. Cash is the clearest case. Your minimum cash level should come from what's due in the coming weeks and how quickly you could collect or borrow, not from last year's balances. Two $15,000 payrolls come to $30,000, but that covers only those two payments. A real floor also has to cover rent, vendors, taxes, and a margin for the forecast being wrong. The 13-week cash flow tracker shows one way to lay that out. Keep in mind that a check on week-end balances can miss a dip in the middle of a week. Inventory works the same way: set the reorder point from lead time and how fast you sell. A dangerously low balance can be common, so history isn't a safe guide here.

A variation band comes from how much a number normally moves. It suits performance numbers, such as inquiries, conversion rate, gross margin, or on-time delivery. I set these from the business's own history. I look at how much each metric actually moved over the last six to twelve months. Movement inside that normal range I treat as noise. Movement outside it means something changed. And I set the band metric by metric, because metrics differ.

The numbers below are made up to show why. Take two metrics from the same business.

A 10% rule would alert on a perfectly normal week of 40 inquiries, which sits 17% under the midpoint but inside the usual range. It would stay silent when gross margin falls to 38%, because that is only 9.5% below 42%. Yet at $80,000 of monthly revenue each margin point is worth $800, so four points is $3,200 a month, or $38,400 a year if it lasted. Same rule, two wrong answers.

Two cautions apply. First, a steady drift that stays inside the band is still a trend, so keep watching it on the scorecard. Second, if you have no history yet, start with a rough band from your judgment and revise it after a quarter of real data. A first guess is fine as long as you replace it.

What a Good Alert Says

An alert that only states a number leaves the work to whoever reads it. Make every alert carry four things:

1. The condition, in plain words.
2. The context: how far off, and compared with what.
3. The owner: one named person or role.
4. The action and the deadline.

This is the same conversation a good reporting meeting has: what the numbers mean, who will act on them, and by when. Compare two versions of the same alert.

Weak: Cash is low.

Strong: Projected ending cash for the week of Nov 2 is $24,000, which is $6,000 under the $30,000 minimum. Owner: office manager. Next step: confirm which customer payments are due before Nov 2 and follow up on any overdue ones. Report back by Friday noon.

The strong version needs no meeting to decide who does what. The weak one starts a discussion about whether anyone should worry. The example is made up. In your own alerts, the owner should be a person or a role someone actually holds, not a shared inbox.

When Silence Is the Problem

A quiet inbox is weak evidence. It can mean everything is fine, but it can also mean the data stopped arriving, the monitor stopped running, or an alert is already active and is staying quiet on purpose. Keep three questions separate.

1. Is the data fresh? Did the source refresh recently, with valid values? Use a timestamp for the last successful refresh, not the date of the latest transaction, which can be old for a good reason when nothing happened. A formula like TODAY() or a header with a forecast date doesn't prove anything about freshness.
2. Is the monitor working? Did the check run recently, and did the message get out? A check inside the job that stopped can't announce that the job stopped. Use a separate backup check, such as a heartbeat message that should arrive and gets noticed when it doesn't. Set its cadence to the risk. Platform failure notifications help with runs that fail, but a missed-run check is needed for a job that never starts.
3. Is the condition still active? A good alert system sends one message when a number crosses a line, then stays quiet while it remains crossed. No new message can therefore mean a problem that's already been reported, not a healthy number. Know which alerts are active and who has acknowledged them.

In a spreadsheet, a cell that records when the last successful refresh happened, with a rule that complains when it's too old, is a reasonable design idea. Treat it as a suggestion to try, not a tested recipe.

Plan for Known Noise

Some movement is expected and planned: a promotion that lifts inquiries for two weeks, a seasonal slowdown, a price change, a holiday closure. If an alert keeps firing for a reason everyone already knows, people start ignoring it for that reason and then for the next one.

Handle planned events deliberately, and be careful about which alerts you loosen. For performance numbers, write down the event, the dates, and the temporary band, and name the person who restores the original line. Don't switch off protections for cash, stock, outages, or safety just because the condition is expected. If you have to pause a critical alert, name another way someone will keep watching it and put the restore date on the calendar. A temporary exception with no end date quietly becomes a permanent blind spot.

Repeated alerts have remedies besides moving the line. For performance alerts where a short delay is safe, require the number to stay across the line for a few checks before it fires. Keep urgent hard-limit warnings prompt. Send one message per problem, not one per check. Let the owner acknowledge it, and escalate to a backup if nobody does. The Google SRE book's chapters on monitoring and practical alerting describe persistence, deduplication, and routing for engineering teams. The owner-and-backup response rule here is a proposed small-business routine.

Choose the Channel by Urgency

Match the channel to how fast the response has to be.

• Email suits checks you want a record of and can act on within the day, such as a morning cash check. It's easy to file and to find later.
• Team chat suits events a group handles together, such as a new qualified lead that someone needs to pick up.
• Text message or phone push suits emergencies where you'd want to be interrupted, such as a payment system going offline. If texts fire often, find out why. Either the lines are wrong, or a real problem keeps happening and needs fixing.

Decide what happens when no one responds. A simple rule is enough: if the owner hasn't acknowledged it by a stated time, the message goes to a named backup. This is also how you tell a missed alert from an unnecessary one.

Review the List Regularly

Alert lists need pruning, but pruning is not the same as deleting whatever gets ignored. A quarterly review is a reasonable routine for a small business, not a rule from any standard. Go through each alert and ask three questions.

1. Did it fire? If not for a long time, is the condition still a real risk? A quiet alert may be doing its job, or it may be watching something that no longer matters.
2. When it fired, did the owner act before the alert's own deadline? Compare the response with the deadline you set for that risk, not with a universal one. If they didn't act, find out why. The owner may not have seen it, the message may not have been delivered, nobody may have been named, there may be no backup, or the action may not have been possible. Fix that cause. Retire the alert only if the condition no longer needs a timely response, another alert already covers it, or it was only ever information.
3. Did anyone say it was noisy? Find out what kind of noise it was before you change anything. It could be a real problem that keeps recurring, bad data, the same message sent twice, a number crossing the line and back again within minutes, a message that doesn't say what to do, or the wrong recipient. Move the line outward only when the number's own history supports it, and only for performance numbers. If the breach is real and repeats, that's an operational problem to solve, not a threshold to loosen.

Keep the list in one place with one row per alert, so the review takes minutes instead of an afternoon.

The rows are examples. Yours should be short enough that the owner can name every alert from memory.

Frequently Asked Questions

How many alerts should a small business have?

As few as you can defend. A handful is plenty for most small businesses. If you can't say who acts on an alert and what they do, it doesn't belong on the list yet. Either name the owner and action, or move the number to the scorecard.

Should I alert on a percent change or on a level?

It depends on the number. Use a level, a hard limit, for cash, stock, and anything with a real floor. Use a variation band for performance numbers that move up and down on their own. A flat percentage for everything is the version most likely to be wrong.

What if I don't have 6 to 12 months of history?

Start with a rough band, mark it as provisional, and replace it once you have a quarter of data. Don't wait for perfect history before setting up the few alerts that guard a hard limit.

How do I set one up in a spreadsheet?

How to set up automatic alerts in Google Sheets covers the mechanics. This article is about deciding which alerts deserve to exist.

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.