Google Apps Script 实战：构建自动化数据处理与邮件通知系统

Google Apps Script 是 Google 提供的基于 JavaScript 的云端脚本平台，能够无缝连接 Google Workspace 中的各种服务（Sheets、Gmail、Drive、Calendar 等），并支持调用外部 API。对于中小型团队来说，它是一个零成本、免部署的自动化利器。本文将带你从零搭建一个完整的自动化数据处理与邮件通知系统，涵盖数据采集、清洗、存储和告警通知的全流程。

Google Cloud Development

一、Google Apps Script 平台概览

Google Apps Script（简称 GAS）运行在 Google 的服务器上，无需配置本地开发环境。每个脚本项目与一个 Google 账号绑定，支持触发器（Triggers）、Web 应用、插件等多种运行方式。

1.1 核心优势

零运维成本：脚本运行在 Google 云端，无需购买服务器或配置域名
原生集成：直接调用 SpreadsheetApp、GmailApp、DriveApp 等内置服务，无需 OAuth 配置
触发器机制：支持时间驱动触发（每分钟/每小时/每天）和事件驱动触发（onEdit、onChange）
免费额度充足：Gmail 每天 100 封邮件，Sheets API 每天 50,000 次读写，足够中小团队使用

1.2 使用限制

限制项	免费版	Workspace 付费版
单次执行时间	6 分钟	30 分钟
触发器总数	20 个	20 个
日触发次数	90 分钟	120 分钟
Gmail 日发送量	100 封	1500 封
URL Fetch 日调用	20,000 次	20,000 次

Data Dashboard

二、项目架构设计

我们设计的系统由四个模块组成：数据采集模块从外部 API 拉取数据，数据处理模块进行清洗和计算，存储模块写入 Google Sheets，通知模块在检测到异常时发送邮件告警。

2.1 系统流程图

外部 API → 数据采集 → 数据清洗 → 写入 Sheets
                                    ↓
                              阈值检测 → 异常？→ 邮件通知
                                    ↓
                              正常 → 记录日志

2.2 文件结构

automation-project/
├── Code.js            # 主入口和定时触发器
├── DataFetcher.js     # API 数据采集模块
├── DataProcessor.js   # 数据清洗和计算
├── SheetWriter.js     # Google Sheets 读写
├── AlertManager.js    # 邮件通知和告警
└── appsscript.json    # 项目配置文件

三、数据采集模块实现

我们以一个公共天气 API 为例，演示如何从外部服务拉取 JSON 数据。UrlFetchApp 是 Apps Script 内置的 HTTP 客户端，支持 GET/POST 请求。

/**
 * 从 OpenWeatherMap API 获取天气数据
 * @param {string} city - 城市名称
 * @param {string} apiKey - API 密钥
 * @returns {Object} 解析后的天气数据
 */
function fetchWeatherData(city, apiKey) {
  const url = 
    'https://api.openweathermap.org/data/2.5/weather' +
    '?q=' + encodeURIComponent(city) +
    '&appid=' + apiKey +
    '&units=metric&lang=zh_cn';

  const options = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
      'Accept': 'application/json'
    }
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const code = response.getResponseCode();

    if (code !== 200) {
      throw new Error('API 请求失败，状态码: ' + code);
    }

    const data = JSON.parse(response.getContentText());
    return {
      city: data.name,
      temperature: data.main.temp,
      humidity: data.main.humidity,
      description: data.weather[0].description,
      windSpeed: data.wind.speed,
      timestamp: new Date().toISOString()
    };
  } catch (e) {
    Logger.log('数据采集失败: ' + e.message);
    throw e;
  }
}

3.1 批量采集多个数据源

在实际项目中，往往需要同时监控多个数据源。使用 Promise.all 的等价方式——顺序调用加结果汇总，可以实现批量采集：

/**
 * 批量采集多个城市的天气数据
 * @param {string[]} cities - 城市列表
 * @param {string} apiKey - API 密钥
 * @returns {Object[]} 采集结果数组
 */
function batchFetchWeather(cities, apiKey) {
  var results = [];
  var errors = [];

  cities.forEach(function(city) {
    try {
      // 添加随机延迟避免触发速率限制
      Utilities.sleep(Math.random() * 1000 + 500);
      var data = fetchWeatherData(city, apiKey);
      results.push(data);
    } catch (e) {
      errors.push({ city: city, error: e.message });
      Logger.log('采集 ' + city + ' 失败: ' + e.message);
    }
  });

  // 保存错误日志到 PropertiesService
  if (errors.length > 0) {
    var props = PropertiesService.getScriptProperties();
    props.setProperty('lastErrors', JSON.stringify(errors));
  }

  return { success: results, errors: errors };
}

Data Analysis

四、数据处理与存储模块

采集到的原始数据需要清洗后才能写入 Sheets。处理逻辑包括：去重、格式化、计算衍生指标，以及按时间窗口聚合。

4.1 数据清洗函数

/**
 * 清洗天气数据，计算体感温度等衍生指标
 * @param {Object} rawData - 原始天气数据
 * @returns {Object} 清洗后的数据
 */
function processWeatherData(rawData) {
  // 体感温度计算（简化版）
  var feelsLike = rawData.temperature;
  if (rawData.windSpeed > 1.5) {
    feelsLike = 13.12 + 0.6215 * rawData.temperature -
      11.37 * Math.pow(rawData.windSpeed, 0.16) +
      0.3965 * rawData.temperature * Math.pow(rawData.windSpeed, 0.16);
  }

  // 风力等级判定
  var windLevel;
  if (rawData.windSpeed < 0.3) windLevel = '0级（无风）';
  else if (rawData.windSpeed < 1.6) windLevel = '1级（软风）';
  else if (rawData.windSpeed < 3.4) windLevel = '2级（轻风）';
  else if (rawData.windSpeed < 5.5) windLevel = '3级（微风）';
  else if (rawData.windSpeed < 8.0) windLevel = '4级（和风）';
  else windLevel = '5级及以上（清风以上）';

  // 数据风险等级
  var riskLevel = '正常';
  if (rawData.temperature > 35 || rawData.temperature < -10) {
    riskLevel = '高温/低温预警';
  } else if (rawData.windSpeed > 10) {
    riskLevel = '大风预警';
  } else if (rawData.humidity > 90) {
    riskLevel = '高湿预警';
  }

  return {
    city: rawData.city,
    temperature: Math.round(rawData.temperature * 10) / 10,
    feelsLike: Math.round(feelsLike * 10) / 10,
    humidity: rawData.humidity,
    description: rawData.description,
    windSpeed: rawData.windSpeed,
    windLevel: windLevel,
    riskLevel: riskLevel,
    timestamp: rawData.timestamp
  };
}

4.2 写入 Google Sheets

/**
 * 将处理后的数据追加到 Sheets
 * @param {string} spreadsheetId - 表格ID
 * @param {string} sheetName - 工作表名称
 * @param {Object[]} data - 处理后的数据数组
 */
function writeToSheet(spreadsheetId, sheetName, data) {
  var ss = SpreadsheetApp.openById(spreadsheetId);
  var sheet = ss.getSheetByName(sheetName);

  // 首次运行时创建表头
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      '城市', '温度(℃)', '体感温度(℃)', '湿度(%)',
      '天气描述', '风速(m/s)', '风力等级', '风险等级', '采集时间'
    ]);
    // 设置表头样式
    var headerRange = sheet.getRange(1, 1, 1, 9);
    headerRange.setFontWeight('bold');
    headerRange.setBackground('#4285F4');
    headerRange.setFontColor('#FFFFFF');
  }

  // 批量写入数据行
  var rows = data.map(function(item) {
    return [
      item.city, item.temperature, item.feelsLike,
      item.humidity, item.description, item.windSpeed,
      item.windLevel, item.riskLevel, item.timestamp
    ];
  });

  if (rows.length > 0) {
    var startRow = sheet.getLastRow() + 1;
    sheet.getRange(startRow, 1, rows.length, 9).setValues(rows);
  }

  // 自动调整列宽
  for (var i = 1; i <= 9; i++) {
    sheet.autoResizeColumn(i);
  }

  Logger.log('成功写入 ' + rows.length + ' 行数据');
}

五、邮件告警模块

当数据处理模块检测到异常指标时，系统自动发送 HTML 格式的告警邮件，包含详细的数据表格和处理建议。

/**
 * 发送告警邮件
 * @param {Object[]} alerts - 告警数据数组
 * @param {string} recipient - 收件人邮箱
 */
function sendAlertEmail(alerts, recipient) {
  if (!alerts || alerts.length === 0) return;

  var subject = '⚠️ 数据告警 - ' +
    Utilities.formatDate(new Date(), 'GMT+8', 'yyyy-MM-dd HH:mm');

  // 构建 HTML 邮件正文
  var htmlBody = '<div style="font-family:Arial,sans-serif;max-width:600px;">';
  htmlBody += '<h2 style="color:#d93025;">⚠️ 数据异常告警</h2>';
  htmlBody += '<p>以下数据指标超出阈值，请及时关注：</p>';
  htmlBody += '<table style="width:100%;border-collapse:collapse;">';
  htmlBody += '<tr style="background:#f8f9fa;">';
  htmlBody += '<th style="padding:8px;border:1px solid #ddd;">城市</th>';
  htmlBody += '<th style="padding:8px;border:1px solid #ddd;">温度</th>';
  htmlBody += '<th style="padding:8px;border:1px solid #ddd;">风险等级</th>';
  htmlBody += '</tr>';

  alerts.forEach(function(alert) {
    var bgColor = alert.riskLevel.indexOf('预警') >= 0 ? '#fce8e6' : '#fff';
    htmlBody += '<tr style="background:' + bgColor + ';">';
    htmlBody += '<td style="padding:8px;border:1px solid #ddd;">' + alert.city + '</td>';
    htmlBody += '<td style="padding:8px;border:1px solid #ddd;">' + alert.temperature + '℃</td>';
    htmlBody += '<td style="padding:8px;border:1px solid #ddd;color:#d93025;font-weight:bold;">' + alert.riskLevel + '</td>';
    htmlBody += '</tr>';
  });

  htmlBody += '</table>';
  htmlBody += '<p style="color:#666;font-size:12px;margin-top:20px;">';
  htmlBody += '此邮件由 Google Apps Script 自动化系统发送</p>';
  htmlBody += '</div>';

  GmailApp.sendEmail(recipient, subject, '', {
    htmlBody: htmlBody
  });

  Logger.log('已发送告警邮件，包含 ' + alerts.length + ' 条告警');
}

Email Notification

六、主函数与定时触发器配置

将所有模块串联起来，设置定时触发器实现全自动运行。

/**
 * 主函数：编排完整的数据处理流程
 */
function main() {
  var scriptProps = PropertiesService.getScriptProperties();
  var apiKey = scriptProps.getProperty('WEATHER_API_KEY');
  var spreadsheetId = scriptProps.getProperty('SPREADSHEET_ID');
  var alertEmail = scriptProps.getProperty('ALERT_EMAIL');

  if (!apiKey || !spreadsheetId) {
    throw new Error('请先在脚本属性中配置 WEATHER_API_KEY 和 SPREADSHEET_ID');
  }

  var cities = ['北京', '上海', '广州', '深圳', '杭州'];

  // 第一步：批量采集
  Logger.log('开始数据采集...');
  var fetchResult = batchFetchWeather(cities, apiKey);
  Logger.log('采集完成：成功 ' + fetchResult.success.length +
    '，失败 ' + fetchResult.errors.length);

  // 第二步：数据处理
  var processedData = fetchResult.success.map(processWeatherData);

  // 第三步：写入 Sheets
  if (processedData.length > 0) {
    writeToSheet(spreadsheetId, '天气监控', processedData);
  }

  // 第四步：异常检测与告警
  var alerts = processedData.filter(function(item) {
    return item.riskLevel !== '正常';
  });

  if (alerts.length > 0 && alertEmail) {
    sendAlertEmail(alerts, alertEmail);
  }

  Logger.log('执行完成，共 ' + processedData.length + ' 条数据，' +
    alerts.length + ' 条告警');
}

/**
 * 设置每日定时触发器
 * 在 Apps Script 编辑器中手动运行此函数一次即可
 */
function setupDailyTrigger() {
  // 先清除旧触发器
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function(t) {
    ScriptApp.deleteTrigger(t);
  });

  // 创建每天上午 8 点执行的触发器
  ScriptApp.newTrigger('main')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();

  // 创建每天下午 6 点执行的触发器
  ScriptApp.newTrigger('main')
    .timeBased()
    .everyDays(1)
    .atHour(18)
    .create();

  Logger.log('定时触发器设置完成：每天 8:00 和 18:00 执行');
}

6.1 项目配置文件 appsscript.json

{
  "timeZone": "Asia/Shanghai",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "access": "MYSELF",
    "executeAs": "USER_DEPLOYING"
  }
}

七、进阶技巧与最佳实践

7.1 使用 PropertiesService 管理配置

将敏感信息（API Key、邮箱地址、表格 ID）存储在脚本属性中，而不是硬编码在代码里。这样可以在不修改代码的情况下更新配置：

// 设置配置（只需运行一次）
function initConfig() {
  PropertiesService.getScriptProperties().setProperties({
    'WEATHER_API_KEY': 'your-api-key-here',
    'SPREADSHEET_ID': 'your-spreadsheet-id',
    'ALERT_EMAIL': 'admin@example.com'
  });
}

// 读取配置
var config = PropertiesService.getScriptProperties().getProperties();

7.2 错误处理与重试机制

网络请求不稳定是常见问题。实现指数退避重试可以显著提高系统可靠性：

function fetchWithRetry(url, options, maxRetries) {
  maxRetries = maxRetries || 3;
  var delay = 1000; // 初始延迟 1 秒

  for (var i = 0; i < maxRetries; i++) {
    try {
      var response = UrlFetchApp.fetch(url, options);
      if (response.getResponseCode() === 200) {
        return JSON.parse(response.getContentText());
      }
    } catch (e) {
      Logger.log('第 ' + (i + 1) + ' 次请求失败: ' + e.message);
      if (i < maxRetries - 1) {
        Utilities.sleep(delay);
        delay *= 2; // 指数退避
      }
    }
  }
  throw new Error('请求失败，已重试 ' + maxRetries + ' 次');
}

7.3 执行日志与监控

使用 Stackdriver Logging 记录执行日志，便于排查问题。在脚本编辑器中点击 执行记录（Executions）即可查看每次运行的详细日志和耗时。

function logExecution(startTime, rowsProcessed, alertsSent) {
  var elapsed = (new Date().getTime() - startTime) / 1000;
  var logEntry = {
    timestamp: new Date().toISOString(),
    duration: elapsed + 's',
    rowsProcessed: rowsProcessed,
    alertsSent: alertsSent
  };
  console.log('执行报告: ' + JSON.stringify(logEntry));
}

八、总结

Google Apps Script 是一个被严重低估的自动化平台。对于数据处理、定时监控、报表生成等场景，它提供了零运维、零成本的解决方案。本文展示的系统涵盖了从数据采集到告警通知的完整链路，你可以根据实际需求替换数据源（比如接入 Google Analytics API、数据库查询结果、RSS 订阅源等），快速搭建属于自己的自动化工作流。

关键要点回顾：

使用 UrlFetchApp 调用外部 API，配合 muteHttpExceptions 做优雅的错误处理
利用 PropertiesService 管理配置，避免硬编码敏感信息
通过 ScriptApp.newTrigger() 设置定时触发器，实现无人值守运行
使用 GmailApp.sendEmail() 发送 HTML 格式的告警邮件
结合 console.log() 和 Stackdriver 日志进行运行监控
注意免费额度限制，合理设计采集频率和数据量