🔔 How to Send Email Alerts or Insights Using BigQuery

Automated email alerts from BigQuery can help you monitor important metrics like errors, transactions, trends, or thresholds—without having to manually check dashboards. In this guide, we’ll walk through several ways to set up these alerts, using tools like Google Apps Script, Analytics Model, Pub/Sub, and App Engine.

🧩 Option 1: Google Apps Script + Google Sheets (Low-Code)

This is a simple and cost-effective method that connects BigQuery to a Google Sheet and sends alerts based on cell values.

✅ How It Works:

1. BigQuery query results are written into a Google Sheet.

2. A script checks the results and sends an email if a condition is met.

3. Can be scheduled to run automatically.
   

🔧 Setup:

Step 1: Enable BigQuery in Apps Script

• Open Google Sheets > Extensions > Apps Script

• In the Apps Script editor, click the + icon next to "Services"

• Add BigQuery API
  

Step 2: Paste This Sample Script (Update Your IDs):

function fetchBigQueryDataToSheet() {

  const projectId = 'your-project-id';

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  sheet.clearContents();

  const query = "SELECT * FROM `your_project.dataset.table`";

  const request = {

    query: query,

    useLegacySql: false,

    location: 'US'

  };

  let queryResults = BigQuery.Jobs.query(request, projectId);

  const jobId = queryResults.jobReference.jobId;

  let sleepTime = 500;

  while (!queryResults.jobComplete) {

    Utilities.sleep(sleepTime);

    sleepTime *= 2;

    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, { location: request.location });

  }

  const rows = queryResults.rows;

  const fields = queryResults.schema.fields;

  sheet.appendRow(fields.map(field => field.name));

  if (rows) {

    for (let row of rows) {

      sheet.appendRow(row.f.map(col => col.v));

    }

  }

  // Example condition

  const value = sheet.getRange("A2").getValue();

  if (value > 1000) {

    MailApp.sendEmail("your@email.com", "🚨 Alert: Threshold Exceeded", `The value is ${value}`);

  }

}

Step 3: Automate It

Use the Triggers tab in Apps Script to run the function every hour/day/etc.

🟣 Option 2: Analytics Model (No-Code, Fast Setup)

Analytics Model is a no-code platform designed specifically for automated alerts and insights using BigQuery—without writing a single line of code.

🔥 Why Use It?

• No coding required

• Direct BigQuery connection

• Auto-generated visualizations

• Simple interface for setting up alerts

• Scalable for multiple users and teams
  

🪄 How to Set It Up:

1. Go to Analytics Model

2. Connect your BigQuery account

3. Add your BigQuery table

4. Create an Insight (a query or logic you want to monitor)

5. Add the Insight to your Feed

6. Define a condition (e.g., "value > 1000") and schedule email notifications

7. Done—your alert will now run automatically
   

This is the best solution if you're not a developer or need a fast, scalable way to handle alerts.

⚙️ Option 3: Pub/Sub + Cloud Functions (Serverless)

This method is recommended for teams with backend or DevOps experience who want a production-grade, scalable solution.

🔄 Workflow:

1. Schedule a BigQuery job to run regularly.

2. The job triggers a Pub/Sub topic.

3. A Cloud Function listens to the topic and:

   • Fetches data

   • Checks conditions

   • Sends email via Gmail API or services like SendGrid
     

Great for:

• Real-time use cases

• Complex notification logic

• Integrating with other APIs or systems
  

🚀 Option 4: App Engine (Custom Logic, Full Control)

You can build a web service on Google App Engine that:

• Queries BigQuery on a schedule

• Analyzes results

• Sends email alerts based on complex business rules
  

Tools You Can Use:

• Python/Flask

• Node.js/Express

• Gmail or third-party APIs

Good for teams that need full flexibility and want alerts to be part of a broader application.

🧠 Summary

Here’s a comparison of the main options:

Method	Coding Required	Setup Time	Best For
Apps Script + Sheets	Low	⏱️ Fast	Simple personal/team use
Analytics Model	❌ No	⚡ Very Fast	No-code business users, quick dashboards
Pub/Sub + Cloud Functions	✅ Yes	🛠️ Medium	Scalable & event-driven apps
App Engine	✅ Yes	🔧 Longer	Full control, large custom systems