如何使用Google Apps Script检查Google表格全表是否存在指定值？

Got it, the main issue with your current code is that SpreadsheetApp.getActiveRange() only checks the currently selected range in the active sheet—not the entire spreadsheet. To fix this, we'll create a helper function that iterates through every sheet in your Google Sheet and checks every cell for your target value.

Here's the modified code with clear explanations:

function doGet(e) { return HtmlService.createHtmlOutput("Hi there"); }

// Helper function to check the ENTIRE spreadsheet for a value
function checkEntireSpreadsheetForValue(spreadsheetId, searchValue) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const allSheets = spreadsheet.getSheets();
  
  // Loop through each sheet in the spreadsheet
  for (const sheet of allSheets) {
    // Get only cells with actual data (avoids empty rows/columns)
    const sheetData = sheet.getDataRange().getValues();
    
    // Check each row for the search value in any column
    for (const row of sheetData) {
      // Return true immediately if the value is found in any cell
      if (row.some(cell => cell === searchValue)) {
        return true;
      }
    }
  }
  // Return false if value isn't found in any sheet
  return false;
}

function doPost(e) {
  // Keep your existing Telegram integration logic
  var data = JSON.parse(e.postData.contents);
  var text = data.message.text;
  var id = data.message.chat.id;
  var userName = data.message.from.username;
  
  // Note: Ensure variables like `name` and `answer` are defined in your script!
  if(/^#/.test(text)) {
    var sheetName = text.slice(1).split(" ")[0];
    var sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName) 
      ? SpreadsheetApp.openById(ssId).getSheetByName(sheetName) 
      : SpreadsheetApp.openById(ssId).insertSheet(sheetName);
    var comment = text.split(" ").slice(1).join(" ");
    sheet.appendRow([userName, new Date(), id, name, comment, answer]);
  }
  
  // Check if your target value exists anywhere in the spreadsheet
  var searchString = "marsad01"; // Replace with your desired value (e.g., "Sam")
  var isSearchStringFound = checkEntireSpreadsheetForValue(ssId, searchString);
  
  if(isSearchStringFound){
    // Run your task if the value exists
    sendMessage(id, answer, name);
  } else {
    // Run alternative task if value isn't found
    sendGreetingMessage(id, answer, name);
  }
}

Key Changes Explained: #

• Helper Function: checkEntireSpreadsheetForValue handles the full spreadsheet scan:
  • Opens your spreadsheet using ssId
  • Loops through every sheet in the file
  • Uses getDataRange() to focus only on cells with data (no wasted checks on empty rows/columns)
  • Exits early as soon as the value is found (boosts efficiency)
• Replaced Range Check: We swapped the limited getActiveRange() check with our helper function to cover the entire spreadsheet
• Flexibility: If you need partial matches (e.g., cells containing "Sam" instead of exact matches), modify the condition to cell.toString().includes(searchValue) instead of cell === searchValue

Quick Checks: #

• Make sure ssId is properly defined in your script (you’re already using it, so double-check it’s set correctly)
• Confirm variables like name and answer are declared somewhere in your code (they’re referenced but not shown in your original snippet)

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