Building an Automated Reporting Dashboard With Google Apps Script

The Weekly Report Trap

Every Monday, you spend 2–3 hours pulling data from Google Analytics, Stripe, Paystack, your CRM, and your support desk into a report nobody reads carefully. The data is always 2 days old. The charts are manual. And if you skip a week, you lose track of trends entirely.

This is the reality for most small businesses across Nigeria, Ghana, and Kenya. You know you need data-driven decisions, but the reporting overhead makes it impractical.

Google Apps Script solves this completely. It's free, runs on Google's servers, and connects to every Google product plus external APIs.

What Your Automated Dashboard Does

Reporting Tools Compared

Apps Script wins because it's completely free, sends automated emails, and you can customise every calculation. No other free tool does all three.

Step 1: Create Your Dashboard Sheet

Set up a Google Sheet with three tabs:

Tab 1: Raw Data

Tab 2: KPIs

Use spreadsheet formulas referencing Raw Data:

B2: =SUMIFS('Raw Data'!B:B, 'Raw Data'!A:A, ">="&TODAY()-30)  → Last 30 days revenue
B3: =COUNTIFS('Raw Data'!C:C, ">0", 'Raw Data'!A:A, ">="&TODAY()-30)  → Transactions
B4: =B2/B3  → Average transaction value
B5: =SUMIFS('Raw Data'!E:E, 'Raw Data'!A:A, ">="&TODAY()-30)  → New leads
B6: =B5/B2  → Cost per lead (if you track ad spend)

Tab 3: Charts

Insert charts based on KPI data. These update automatically when raw data refreshes.

Step 2: Fetch Data From External APIs

Paystack Revenue

function fetchPaystackRevenue() {
  const SECRET_KEY = 'sk_test_your_key';
  const today = new Date();
  const thirtyDaysAgo = new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000);

  const response = UrlFetchApp.fetch(
    'https://api.paystack.com/transaction/totals', {
      headers: { Authorization: 'Bearer ' + SECRET_KEY },
      payload: {
        from: thirtyDaysAgo.toISOString().split('T')[0],
        to: today.toISOString().split('T')[0]
      }
    }
  );

  const data = JSON.parse(response.getContentText());
  return {
    totalVolume: data.data.total_transactions,
    totalAmount: data.data.total_volume / 100  // Paystack returns in kobo
  };
}

Google Analytics Data

function fetchAnalyticsData() {
  const propertyId = 'properties/YOUR_PROPERTY_ID';

  const request = AnalyticsData.newRunReportRequest();
  request.dateRanges = [{ startDate: '30daysAgo', endDate: 'today' }];
  request.metrics = [{ name: 'activeUsers' }, { name: 'screenPageViews' }];

  const report = AnalyticsData.Properties.runReport(request, propertyId);

  return {
    activeUsers: report.rows?.[0]?.metricValues?.[0]?.value || 0,
    pageViews: report.rows?.[0]?.metricValues?.[1]?.value || 0
  };
}

Step 3: Build the Update Script

function updateDashboard() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw Data');
  const today = new Date().toISOString().split('T')[0];

  // Fetch all data sources
  const paystack = fetchPaystackRevenue();
  const analytics = fetchAnalyticsData();
  const leads = countNewLeads();
  const tickets = countSupportTickets();

  // Append new row
  const lastRow = sheet.getLastRow() + 1;
  sheet.getRange(lastRow, 1, 1, 7).setValues([
    [today, paystack.totalAmount, paystack.totalVolume,
     analytics.activeUsers, leads, tickets,
     calculateMRR()]
  ]);

  // Flush to ensure data is written
  SpreadsheetApp.flush();
}

Step 4: Auto-Email the Weekly Report

function sendWeeklyReport() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KPIs');

  const revenue = sheet.getRange('B2').getValue();
  const transactions = sheet.getRange('B3').getValue();
  const avgValue = sheet.getRange('B4').getValue();
  const leads = sheet.getRange('B5').getValue();

  // Get chart as image
  const chartSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Charts');
  const charts = chartSheet.getCharts();
  const chartImage = charts[0].getBlob();

  const htmlBody = `
    <h2>Weekly Business Report</h2>
    <p><strong>Revenue (30d):</strong> ₦${Number(revenue).toLocaleString()}</p>
    <p><strong>Transactions:</strong> ${transactions}</p>
    <p><strong>Avg Transaction:</strong> ₦${Number(avgValue).toLocaleString()}</p>
    <p><strong>New Leads:</strong> ${leads}</p>
    <img src='cid:chart' width='600'>
  `;

  MailApp.sendEmail({
    to: 'founder@yourcompany.com',
    subject: 'Weekly Report — ' + new Date().toLocaleDateString(),
    htmlBody: htmlBody,
    inlineImages: { chart: chartImage }
  });
}

Step 5: Set Up Automated Triggers

Anomaly Detection (Bonus)

function checkAnomalies() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw Data');
  const lastRow = sheet.getLastRow();

  const todayRevenue = sheet.getRange(lastRow, 2).getValue();
  const yesterdayRevenue = sheet.getRange(lastRow - 1, 2).getValue();

  if (todayRevenue < yesterdayRevenue * 0.5) {
    MailApp.sendEmail({
      to: 'founder@yourcompany.com',
      subject: '⚠️ Revenue Drop Alert',
      body: "Today's revenue (₦" + todayRevenue + ") is less than 50% of yesterday's (₦" + yesterdayRevenue + "). Investigate immediately."
    });
  }
}

Common Gotchas

This system turns 3 hours of manual reporting into a zero-touch weekly email. Your data stays current, your charts update automatically, and you catch problems before they compound.