Google Sheet联动BigQuery时AppScript邮件触发器创建失败求助

问题背景 #

我是一名数据分析师，遇到以下技术问题：

• 现有Google Sheet通过查询连接至Google BigQuery（BQ）表，查询每小时自动运行，BQ表新增条目时同步至Sheet
• 尝试用AppScript创建触发器，期望Sheet更新时自动发送邮件，但多次报错，核心提示「数据源不支持」，且无法读取关联数据源的Sheet内容，已注释Range相关代码

原代码 #

// Helper function to get the active sheet
function getActiveSheet(e) {
  if (!e) {
    Logger.log("No event object, this function should be triggered by an event.");
    return null;
  }
  return e.source.getActiveSheet();
}

// Function to send email
function sendEmail(e) {
  if (!e) {
    Logger.log("No event object, this function should be triggered by an event.");
    return;
  }

  var sheet = getActiveSheet(e);
  if (!sheet) {
    Logger.log("No active sheet found.");
    return; // Exit if no sheet is found
  }


  var range = e.range;
  // if (!range) {
  //   Logger.log("No range found.");
  //   return; // Exit if no range is found
  // }
  
  // Define email details
  var recipient = "example@email.com."; // Change this to your email
  var subject = "EOI Google Sheet Updated";
  var message = "Your Sheet link was updated: \n\n";
  
  // Get the updated values
  // var values = range.getValues();
  // for (var i = 0; i < values.length; i++) {
  //   for (var j = 0; j < values[i].length; j++) {
  //     message += values[i][j] + " ";
  //   }
  //   message += "\n";
  // }
  
  // Send the email
  MailApp.sendEmail(recipient, subject, message);
}

// Function to create a trigger for the sendEmail function
function createEditTrigger() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('sendEmail')
    .forSpreadsheet(sheet)
    .onChange()
    .create();
}

问题原因 #

Google Sheet的onChange/onEdit触发器仅响应人工编辑或AppScript直接修改单元格的操作，而BigQuery同步属于外部数据源更新，不会触发这类触发器。此外，外部数据源关联的Sheet区域无法通过常规的e.range读取，会返回「数据源不支持」错误。

解决方案：定时检查行数变化实现通知 #

通过定时触发器定期检查Sheet的行数，对比上次记录的行数，若行数增加则判定为有新数据同步，触发邮件通知。

完整实现代码 #

// 配置项
var CONFIG = {
  SHEET_NAME: "你的BQ同步表名称", // 替换为实际Sheet名称
  RECIPIENT: "example@email.com", // 替换为收件人邮箱
};

// 初始化：创建定时触发器并记录初始行数
function setupTrigger() {
  // 删除已存在的同名触发器（避免重复）
  var existingTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < existingTriggers.length; i++) {
    if (existingTriggers[i].getHandlerFunction() === "checkForNewData") {
      ScriptApp.deleteTrigger(existingTriggers[i]);
    }
  }

  // 创建定时触发器（和BQ查询频率保持一致，每小时执行一次）
  ScriptApp.newTrigger("checkForNewData")
    .timeBased()
    .everyHours(1)
    .create();

  // 记录初始行数到脚本属性
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  var lastRow = sheet.getLastRow();
  PropertiesService.getScriptProperties().setProperty("LAST_RECORDED_ROW", lastRow.toString());
  
  Logger.log("触发器已创建，初始行数记录为：" + lastRow);
}

// 检查是否有新数据并发送邮件
function checkForNewData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) {
    Logger.log("未找到指定Sheet：" + CONFIG.SHEET_NAME);
    return;
  }

  // 获取上次记录的行数和当前行数
  var scriptProps = PropertiesService.getScriptProperties();
  var lastRecordedRow = parseInt(scriptProps.getProperty("LAST_RECORDED_ROW")) || 0;
  var currentLastRow = sheet.getLastRow();

  // 若当前行数大于上次记录的行数，说明有新数据
  if (currentLastRow > lastRecordedRow) {
    // 获取新增的行数据
    var newRows = sheet.getRange(lastRecordedRow + 1, 1, currentLastRow - lastRecordedRow, sheet.getLastColumn()).getValues();
    
    // 构建邮件内容
    var subject = "EOI Google Sheet已更新：新增" + (currentLastRow - lastRecordedRow) + "条数据";
    var message = "Sheet已同步BigQuery新数据，新增内容如下：\n\n";
    newRows.forEach(function(row) {
      message += row.join("\t") + "\n";
    });
    message += "\nSheet链接：" + SpreadsheetApp.getActiveSpreadsheet().getUrl();

    // 发送邮件
    MailApp.sendEmail(CONFIG.RECIPIENT, subject, message);
    
    // 更新记录的行数
    scriptProps.setProperty("LAST_RECORDED_ROW", currentLastRow.toString());
    Logger.log("已发送邮件，更新记录行数为：" + currentLastRow);
  } else {
    Logger.log("无新数据，当前行数：" + currentLastRow);
  }
}

使用步骤 #

1. 替换代码中CONFIG对象里的SHEET_NAME、RECIPIENT为实际值
2. 执行setupTrigger函数，授权脚本所需权限
3. 脚本会每小时自动检查一次，有新数据时发送邮件

注意事项 #

• 首次执行setupTrigger时需要授权访问Spreadsheet、Mail和Script Properties
• 脚本属性会持久化存储上次行数，即使脚本重启或Spreadsheet关闭也不会丢失
• 可根据BQ查询的实际频率调整触发器的执行间隔（比如改为everyMinutes(30)实现每30分钟检查一次）

内容的提问来源于stack exchange，提问作者Jasmin