Google Sheets中实现后台监听聚合表值变化并推送至Telegram Bot的方案咨询

Got it, let's fix this. The onChange() trigger won’t work for your scenario because it only fires when the spreadsheet is actively open and changes happen during an active user session. For background API-driven updates, we’ll use a time-based trigger paired with snapshot comparison—this checks your aggregation table on a schedule, compares it to a saved "last known" state, and sends alerts to your Telegram Bot when differences are detected.

Step 1: Prepare a Snapshot Storage Sheet #

First, create a hidden sheet in your spreadsheet named Snapshot—this will store the previous state of your aggregation table so we can spot changes later.

Step 2: Full Implementation Code #

Replace the placeholders (like your Telegram Bot token, chat ID, and aggregation sheet name) with your actual values:

// Replace these with your own details
const TELEGRAM_BOT_TOKEN = "your-bot-token-here";
const TELEGRAM_CHAT_ID = "your-chat-id-here";
const AGGREGATION_SHEET_NAME = "YourAggregationSheetName"; // Name of your target sheet
const SNAPSHOT_SHEET_NAME = "Snapshot";

function checkAndNotifyChanges() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const aggSheet = ss.getSheetByName(AGGREGATION_SHEET_NAME);
  const snapshotSheet = ss.getSheetByName(SNAPSHOT_SHEET_NAME);

  // Fetch current data from aggregation sheet (adjust range if needed)
  const currentData = aggSheet.getDataRange().getValues();
  // Fetch saved snapshot data
  const snapshotData = snapshotSheet.getDataRange().getValues();

  // Initialize snapshot on first run
  if (snapshotData.length === 0) {
    snapshotSheet.getRange(1, 1, currentData.length, currentData[0].length).setValues(currentData);
    return;
  }

  // Compare data to find changes
  const changes = [];
  for (let row = 0; row < currentData.length; row++) {
    for (let col = 0; col < currentData[row].length; col++) {
      if (currentData[row][col] !== snapshotData[row][col]) {
        changes.push({
          row: row + 1, // Convert to 1-indexed for readability
          col: col + 1,
          oldValue: snapshotData[row][col] || "N/A",
          newValue: currentData[row][col]
        });
      }
    }
  }

  // Send alert if changes are found
  if (changes.length > 0) {
    let message = "📊 Aggregation Sheet Updated:\n";
    changes.forEach(change => {
      message += `Row ${change.row}, Col ${change.col}: *${change.oldValue}* → *${change.newValue}*\n`;
    });
    sendToTelegram(message);

    // Update snapshot with latest data
    snapshotSheet.clearContents();
    snapshotSheet.getRange(1, 1, currentData.length, currentData[0].length).setValues(currentData);
  }
}

function sendToTelegram(message) {
  const url = `https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage`;
  const payload = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify({
      chat_id: TELEGRAM_CHAT_ID,
      text: message,
      parse_mode: "Markdown"
    })
  };
  UrlFetchApp.fetch(url, payload);
}

function setUpTimeTrigger() {
  // Delete existing triggers to avoid duplicates
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === "checkAndNotifyChanges") {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  // Create new time trigger (adjust frequency to match your API refresh rate)
  ScriptApp.newTrigger("checkAndNotifyChanges")
    .timeBased()
    .everyMinutes(15) // Change to hourly, daily, etc., as needed
    .create();
}

Step 3: Activate the Background Trigger #

1. Run the setUpTimeTrigger() function once (you’ll need to authorize the script to access your spreadsheet and send HTTP requests).
2. This will create a background trigger that runs checkAndNotifyChanges() at your specified interval—no need to keep the spreadsheet open.

Key Adjustments & Tips #

• Range Optimization: If your aggregation table doesn’t use the entire sheet, replace getDataRange() with a specific range like getRange("A1:Z50") to speed up checks.
• Telegram Setup: Make sure your Bot is created via @BotFather, and start a chat with it to get your chat ID (use @getidsbot to find this).
• Trigger Frequency: Match the interval to your API refresh schedule—no need to check more often than your data updates.
• Error Handling: Add try/catch blocks if you want to handle network errors or missing sheets gracefully.

Why Your Original onChange() Failed #

Google Sheets’ onChange() trigger is built for user-initiated changes or updates that happen while the spreadsheet is actively open. Background updates (like importjson() refreshes) don’t trigger it because there’s no active user session. The snapshot comparison method works entirely in the background, so it’s perfect for your use case.

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