Automate Business Alerts with Google Sheets and Google Chat

For many small and medium-sized enterprises (SMEs) in the Philippines, the humble spreadsheet is the unsung hero of daily operations. It tracks inventory, monitors sales, and manages projects. But let's be honest: it's a manual process. Someone has to remember to check the sheet, identify issues, and then alert the right people.

What if things fall through the cracks? An urgent order gets missed. A customer isn't followed up on time. A critical deadline is overlooked. These small misses can add up, costing you time, money, and customer trust.

But what if you could give your spreadsheet a voice? What if it could automatically detect problems and instantly notify your team where they're already communicating?

At Grassr Solutions, we specialize in building custom, affordable business systems. But we also believe in empowering businesses with tools they already have. Today, we're going to show you how to combine three powerful Google tools—Sheets, Apps Script, and the Chat API—to create a simple yet powerful automated alert system for your business.

The Power Trio: Sheets, Apps Script & Chat

Think of these three tools as a team working together to automate your workflow:

• Google Sheets: Your familiar and flexible database. This is where you store your operational data—orders, inventory, deadlines, you name it.

• Google Apps Script: The "secret sauce." This is a scripting platform built into Google Workspace that acts as the brain of our operation. It can read your Sheet, apply business logic ("if this, then that"), and take action. Think of it as macros on steroids.

• Google Chat API: The messenger. Once Apps Script identifies something that needs attention, it uses the Chat API to send a formatted, automated message directly to a specific team or group chat.

Together, they form a lightweight, custom notification system without any additional software costs.

A Real-World Example: The Overdue Pickup Monitor

Let's make this concrete. Imagine you run a wholesale business. Customers place orders, your warehouse team prepares them, and then they wait for pickup. The problem? Some orders sit on the shelf for weeks, tying up inventory and capital. You need a way to flag these overdue orders so your sales team can follow up.

Here’s the goal & setup: Automatically send a daily alert to the Sales team's Google Chat space, listing all orders that haven't been picked up within a specific timeframe.

1. A Google Sheet named "Pickup Monitoring" tracks all orders.

2. Google Apps Script runs once a day to check the sheet.

3. A Google Chat space for the Sales team to receive the notifications.

4. The script will look at each order and check:

   • Is it marked as "Received" but not yet "Released"?

   • Has it been more than 7 days for a "City" pickup?

   • Has it been more than 30 days for an "Out of Town" pickup?

5. If any orders meet these criteria, the script bundles them into a single, neat message and pings the team.

The Code: Your Free Automation Script

Here is the exact Google Apps Script to make this happen. You can copy and paste this directly into your own Google Sheet's script editor.

/**
 * Configuration: Set your Sheet Name, Chat Space ID, and Location-specific thresholds.
 */
const SHEET_NAME = "Pickup Monitoring"; // <-- CHANGE THIS to your sheet's name
const CHAT_SPACE_ID = "spaces/YOUR_SPACE_ID_HERE"; // <-- IMPORTANT: REPLACE THIS

// Define configurations for different locations and their overdue thresholds
const LOCATION_CONFIGS = [
  {
    name: "CITY", // The exact string to match in your "Location" column (case-insensitive)
    label: "City", // A user-friendly label for notifications
    daysThreshold: 7
  },
  {
    name: "OUT OF TOWN",
    label: "Out of Town",
    daysThreshold: 30
  }
  // Add more configurations here if needed
  // e.g., { name: "WAREHOUSE B", label: "Warehouse B", daysThreshold: 15 }
];

/**
 * Main function to find overdue items and send a notification to Google Chat.
 * This function should be triggered daily.
 */
function checkOverduePickupsAndNotify() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(SHEET_NAME);

    if (!sheet) {
      Logger.log(`Error: Sheet "${SHEET_NAME}" not found.`);
      sendErrorMessageToChat(`Script Error: Sheet "${SHEET_NAME}" not found.`);
      return;
    }

    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();

    // Use an object to store overdue items, keyed by location label
    const allOverdueItemsByLocation = {};
    LOCATION_CONFIGS.forEach(config => {
      allOverdueItemsByLocation[config.label] = []; // Initialize an empty array
    });

    const today = new Date();
    today.setHours(0, 0, 0, 0); // Set time to midnight for day comparison

    // Start from row 1 to skip header (index 0)
    for (let i = 1; i < values.length; i++) {
      const row = values[i];

      // Assign columns to variables for readability
      const invDr = row[1];          // Column B: INV/DR
      const sheetLocation = row[2];  // Column C: Location
      const receivedStatus = row[4]; // Column E: Received (TRUE/FALSE)
      const dateTimeReceivedStr = row[5]; // Column F: DateTime Received
      const customer = row[6];       // Column G: Customer
      const releasedStatus = row[8]; // Column I: Released (TRUE/FALSE)

      if (!sheetLocation || !dateTimeReceivedStr || invDr === "") {
        continue;
      }

      const normalizedSheetLocation = sheetLocation.toString().trim().toUpperCase();

      for (const config of LOCATION_CONFIGS) {
        if (normalizedSheetLocation === config.name.toUpperCase() &&
            receivedStatus === true &&
            releasedStatus === false)
        {
          let dateTimeReceived;
          try {
            dateTimeReceived = new Date(dateTimeReceivedStr);
            if (isNaN(dateTimeReceived.getTime())) {
              Logger.log(`Row ${i + 1} (${invDr}): Skipped. Invalid date format.`);
              continue;
            }
            dateTimeReceived.setHours(0, 0, 0, 0);
          } catch (e) {
            Logger.log(`Row ${i + 1} (${invDr}): Skipped. Error parsing date.`);
            continue;
          }

          const thresholdDate = new Date(today);
          thresholdDate.setDate(today.getDate() - config.daysThreshold);
          thresholdDate.setHours(0, 0, 0, 0);

          if (dateTimeReceived <= thresholdDate) {
            allOverdueItemsByLocation[config.label].push({
              invDr: invDr,
              receivedDate: Utilities.formatDate(new Date(dateTimeReceivedStr), Session.getScriptTimeZone(), "yyyy-MM-dd"),
              customer: customer
            });
            break; 
          }
        }
      }
    }

    // --- Prepare and Send Message ---
    let messageText = "";
    let hasAnyOverdueItems = false;

    LOCATION_CONFIGS.forEach(config => {
      const overdueItemsForThisLocation = allOverdueItemsByLocation[config.label];
      if (overdueItemsForThisLocation.length > 0) {
        if (!hasAnyOverdueItems) {
          messageText += `*Overdue Pickups Alert*\n\n`;
          hasAnyOverdueItems = true;
        } else {
            messageText += "\n\n---\n\n";
        }

        messageText += `*Location: ${config.label}*\n`;
        messageText += `The following INV/DRs were received more than ${config.daysThreshold} days ago and have not yet been picked up:\n\n`;

        overdueItemsForThisLocation.forEach(item => {
          messageText += `- *INV/DR:* ${item.invDr} | ${item.customer || 'N/A'} (Received: ${item.receivedDate})\n`;
        });
      }
    });

    if (hasAnyOverdueItems) {
      messageText += `\nPlease follow up with the customers on these orders. Thank you!`;
      sendMessageToChat(CHAT_SPACE_ID, messageText);
    } else {
      // Optional: Send a "no items" message for a daily health check
      let checkedLocations = LOCATION_CONFIGS.map(c => c.label).join(", ");
      // sendMessageToChat(CHAT_SPACE_ID, `*Daily Pickup Check (${checkedLocations})*\n\nNo overdue pickups found today.`);
      Logger.log('No overdue items to report.');
    }

  } catch (error) {
    Logger.log(`Error in checkOverduePickupsAndNotify: ${error}`);
    sendErrorMessageToChat(`Script Error: ${error.message}`);
  }
}

/**
 * Sends a simple text message to the specified Google Chat space.
 */
function sendMessageToChat(spaceId, text) {
  const payload = { text: text };
  try {
    if (typeof Chat === 'undefined') {
        Logger.log("Google Chat Advanced Service is not enabled. Please enable it.");
        return;
    }
    Chat.Spaces.Messages.create(payload, spaceId);
  } catch (e) {
    Logger.log(`Error sending message to space ${spaceId}: ${e}`);
  }
}

/**
 * Helper function to send error messages to the chat space for monitoring.
 */
function sendErrorMessageToChat(errorMessage) {
   const fullErrorMessage = `*SCRIPT ERROR*\n\nAn error occurred while checking for overdue pickups:\n\`\`\`\n${errorMessage}\n\`\`\`\nPlease check the script logs.`;
   sendMessageToChat(CHAT_SPACE_ID, fullErrorMessage);
}

How to Set It Up: A 5-Minute Guide

1. Prepare Your Google Sheet: Create a new Google Sheet or use an existing one. Make sure it has columns that correspond to the script (e.g., "INV/DR", "Location", "Received", "DateTime Received", "Customer", "Released").

2. Open the Script Editor: In your Google Sheet, go to Extensions > Apps Script. A new browser tab will open with the script editor.

3. Paste and Configure the Code: Delete any placeholder code and paste the script above. Crucially, change the SHEET_NAME and CHAT_SPACE_ID variables at the top to match your setup. To get your Chat Space ID, open the space in your browser and copy the part of the URL after spaces/.

4. Enable the Google Chat API: This is a vital step. In the script editor, click on Services in the left-hand menu. Click the + Add a service button, find Google Chat API in the list, and click Add. This gives your script permission to post messages.

5. Set It to Run Automatically: We want this to run every day without any manual effort.

   • In the script editor, click the Triggers icon (it looks like a clock).

   • Click + Add Trigger in the bottom right.

   • Set it up as follows:

     • Choose which function to run: checkOverduePickupsAndNotify

     • Select event source: Time-driven

     • Select type of time-based trigger: Day timer

     • Select time of day: 8am to 9am (or whenever you want the daily report).

     • Click Save. You will be asked to authorize the script's permissions.

That’s it! Your automated watchdog is now on duty.

From Smart Alerts to a Full Business System

This simple automation is incredibly powerful. It saves time, prevents human error, and ensures proactive communication. But it's also just the beginning.

As your business grows, you'll find the limitations of a spreadsheet-based system. Managing complex relationships between data, providing different user access levels, and generating sophisticated reports can become a challenge.

This is where Grassr Solutions steps in.

Our systems are:

• Affordable: Designed for the budget of Philippine SMEs.

• Custom-Fit: We build what you need, without the bloat of expensive, one-size-fits-all software.

• Cross-Platform: Works seamlessly on any device—desktop, tablet, or mobile.

• Data-Driven: We turn your data into automated reports and real-time dashboards to empower your decision-making.

We take the core principle you just learned—smart, custom automation—and build it into a robust, scalable business system tailored specifically for your needs.

Whether you need a comprehensive Inventory and Operations System, an HR and Payroll Platform, or a specialized tool for your industry, we build it.