Google Sheets Automation Basics: Formulas, Triggers, and AI

Google Sheets automation starts with features you already have access to but probably have not used yet. Before reaching for Zapier or Make, you can automate a surprising amount directly inside Sheets using formulas, built-in triggers, simple Apps Script, and the newer AI features. This guide covers all four layers, from simplest to most powerful, with examples relevant.

Layer 1: Formulas That Do the Work for You

You probably already use SUM and AVERAGE. But several Google Sheets formulas act like mini automations, pulling data, transforming it, and updating results automatically whenever your source data changes.

IMPORTRANGE: Connect Sheets Automatically

If your business tracks sales in one spreadsheet and expenses in another, IMPORTRANGE pulls data from one sheet into another automatically.

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

Every time the source sheet updates, the destination sheet updates too. This is automation without any external tool. A Nairobi retail business could keep individual branch sales in separate sheets and pull all of them into a master summary.

QUERY: SQL-like Filtering

QUERY lets you filter and sort data using simple commands:

=QUERY(A1:E100, "SELECT A, B, E WHERE E > 10000 ORDER BY E DESC")

This pulls all rows where column E (say, transaction amount) exceeds a reasonable cost, sorted from highest to lowest. The result updates live. Combine this with IMPORTRANGE and you have a live dashboard pulling filtered data from multiple sources.

ARRAYFORMULA: One Formula for an Entire Column

Instead of dragging a formula down hundreds of rows, ARRAYFORMULA applies it to every row at once:

=ARRAYFORMULA(IF(B2:B<>"", B2:B * 1.16, ""))

This adds a notable share VAT to every value in column B, automatically extending to new rows. When you add a new entry in column B, the VAT calculation appears instantly. No dragging, no forgetting.

VLOOKUP and INDEX/MATCH: Automatic Data Retrieval

When a customer places an order and you enter their ID, VLOOKUP can automatically pull their name, phone number, and address from a customer database sheet:

=VLOOKUP(A2, Customers!A:D, 2, FALSE)

This eliminates manual lookups and reduces errors. For businesses managing customer data alongside order data, these formulas save hours per week.

Layer 2: Conditional Formatting and Data Validation

These features automate visual feedback and data entry rules.

Conditional formatting highlights cells based on rules you set. Examples:

• Turn cells red when an invoice is overdue (date in column D is before today)
• Highlight rows green when payment status changes to "Paid"
• Show a yellow warning when inventory drops below a threshold

Data validation controls what can be entered in a cell:

• Dropdown lists for status fields (Pending, Paid, Cancelled)
• Number ranges for quantity fields (must be between 1 and 1000)
• Date restrictions for scheduling (no past dates allowed)

These are not flashy, but they prevent the data entry errors that cause problems downstream. If your M-Pesa transaction tracking sheet requires a specific format for phone numbers, data validation catches mistakes at the point of entry.

Layer 3: Apps Script for Time-Based Triggers

Google Apps Script is a coding environment built into Google Sheets. Before you skip this section, know that many useful scripts are short (5-10 lines) and can be copied from examples without deep coding knowledge.

Sending Automatic Email Reminders

This script sends an email when a due date in your sheet has passed:

function sendReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Invoices");
  var data = sheet.getDataRange().getValues();
  var today = new Date();

  for (var i = 1; i < data.length; i++) {
    var dueDate = new Date(data[i][3]); // Column D: due date
    var status = data[i][4]; // Column E: status
    var email = data[i][2]; // Column C: email

    if (dueDate < today && status !== "Paid") {
      MailApp.sendEmail(email, "Payment Reminder",
        "Your invoice is overdue. Please process payment.");
    }
  }
}

You can set this to run daily using Apps Script's built-in triggers: go to the script editor (Extensions > Apps Script), paste the code, then set a time-based trigger to run every morning.

Auto-Archiving Old Rows

Move completed or old entries to an archive sheet automatically:

function archiveCompleted() {
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Active");
  var archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
  var data = source.getDataRange().getValues();

  for (var i = data.length - 1; i >= 1; i--) {
    if (data[i][4] === "Completed") { // Column E: status
      archive.appendRow(data[i]);
      source.deleteRow(i + 1);
    }
  }
}

Set this to run weekly, and your active sheet stays clean while all completed records are preserved in the archive.

Setting Up Triggers

In the Apps Script editor, click the clock icon (Triggers). Click "Add Trigger" and choose:

• Which function to run
• When to run it (time-driven: every hour, every day, every week)

This runs automatically in the background. You do not need to open the spreadsheet for triggers to fire.

Layer 4: AI Features in Google Sheets

Google has added AI capabilities directly into Sheets. These work alongside the Gemini integration for Workspace users.

Smart Fill detects patterns in your data and suggests completions. If you format dates consistently in one column, Smart Fill can apply the pattern to new entries. If you extract first names from full names in a few rows, it suggests doing the same for the rest.

Explore panel (the small star icon at the bottom right) answers questions about your data in plain language. Type "What was the total sales for March?" and it generates the answer along with the formula used. This is useful for quick analysis without building formulas yourself.

Gemini in Sheets (available with Google One AI Premium or Workspace plans) takes this further. You can ask Gemini to:

• Create formulas by describing what you need ("Calculate the running average of column C over the last 7 entries")
• Generate pivot tables from plain language descriptions
• Suggest data cleaning steps for messy datasets

For Kenyan businesses already using Google Sheets as their primary data tool, these AI features add capability without requiring new software or subscriptions beyond what you may already pay for Workspace.

Connecting Sheets to External Automation

Google Sheets also serves as a data layer for external automation tools:

Zapier, Make, and n8n all have Google Sheets integrations. A new row in a sheet can trigger an automation in any of these platforms. This means your sheet can remain the central place where data lives, while external tools handle actions like sending messages, updating other systems, or processing payments.

Google Forms to Sheets is a built-in connection. Every form response automatically appears in a linked sheet, where your formulas, conditional formatting, and scripts are already waiting.

For practical examples of connecting Sheets to automation platforms, see our Zapier beginner guide or Make beginner guide.

Our AI and Automation for Beginners course includes hands-on Google Sheets automation exercises.

For the broader automation picture, visit the No-Code Automation Guide. To understand tool availability in our region, check AI tools that work well in Kenya.

FAQ

Do I need to know how to code to automate Google Sheets?

No. Layers 1 and 2 (formulas and data validation) require no coding at all. For Layer 3 (Apps Script), you can copy and modify existing scripts with minimal coding knowledge. Many useful automations work entirely with formulas and built-in features.

Is Google Sheets automation free?

The formulas, conditional formatting, data validation, and Apps Script are all free with any Google account. The AI features (Gemini in Sheets) require a paid Google Workspace plan or Google One AI Premium subscription. You can accomplish significant automation on the free tier.

Can Google Sheets handle large datasets for automation?

Google Sheets supports up to 10 million cells per spreadsheet. For most Kenyan small businesses, this is more than enough. If you are processing thousands of rows daily, consider whether a proper database might serve you better for the raw data, with Sheets handling summaries and reports.

How do I track M-Pesa transactions in Google Sheets automatically?

The most common approach is to receive M-Pesa confirmation emails (or SMS forwarded to email) and use a Zapier or Make automation to extract transaction details and log them to a Sheet. Some M-Pesa payment integrations also support webhooks that can push data to a Sheet via Apps Script.

Can Apps Script triggers work on the free Google account?

Yes. Apps Script triggers work on both free Google accounts and Workspace accounts. Free accounts have a daily quota (email sending limits, script runtime limits), but these are generous enough for most small-scale automations.