如何在Google Sheet中匹配指定值（含E60）并获取整行数据发送邮件

Got it, let's tackle your two Google Sheets automation tasks head-on. Since you already know how to send emails, we’ll focus on the data matching and trigger logic that’s giving you trouble.

1. Find Data Using Multiple Cell Values & Send Email #

Let’s assume you have a data sheet with structured rows (e.g., order IDs, customer emails, product names) and a separate section where you input criteria to match. Here’s a script that pulls those criteria, finds the matching row, and sends an email with the relevant data:

function sendEmailByMultipleCriteria() {
  // Get your spreadsheet and sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("OrderData"); // Replace with your data sheet name
  const criteriaSheet = ss.getSheetByName("MatchCriteria"); // Sheet where you input match values

  // Grab your matching criteria from specific cells (adjust these ranges to your setup)
  const targetOrderID = criteriaSheet.getRange("E1").getValue();
  const targetProduct = criteriaSheet.getRange("F1").getValue();

  // Fetch all data from your sheet (skip header row if you have one)
  const allData = dataSheet.getDataRange().getValues();
  const headers = allData[0]; // Store headers if you want to use them in the email

  // Loop through each row to find matches
  for (let i = 1; i < allData.length; i++) {
    const currentRow = allData[i];
    const rowOrderID = currentRow[0]; // Column A = Order ID (adjust index to your column)
    const rowProduct = currentRow[2]; // Column C = Product Name
    const customerEmail = currentRow[1]; // Column B = Customer Email
    const orderDetails = currentRow[3]; // Column D = Order Details

    // Check if all criteria match
    if (rowOrderID === targetOrderID && rowProduct === targetProduct) {
      // Build your email content
      const subject = `Update for Order #${targetOrderID}`;
      let body = `Hi,\n\nWe found your order details:\n`;
      body += `${headers[0]}: ${rowOrderID}\n`;
      body += `${headers[2]}: ${rowProduct}\n`;
      body += `${headers[3]}: ${orderDetails}\n\nThanks!`;

      // Send the email (you already know this part)
      MailApp.sendEmail(customerEmail, subject, body);
      Logger.log(`Email sent to ${customerEmail} for matching order`);
      break; // Remove this if you want to send emails for ALL matching rows
    }
  }
}

Key Notes: #

• Replace sheet names and column indices (like currentRow[0]) to match your actual sheet structure.
• Remove the break statement if you need to match multiple rows instead of just the first one.
• Test first with Logger.log() instead of MailApp.sendEmail() to confirm you’re pulling the right data.

2. Locate Rows Containing "E60" & Send Full Row Data #

This script scans every row in your sheet, checks if any cell contains the string "E60", and sends an email with the entire row’s data (paired with headers for readability):

function sendEmailForE60Rows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("InventoryData"); // Replace with your sheet name
  const recipientEmail = "alert@yourdomain.com"; // Replace with your target email

  const allData = dataSheet.getDataRange().getValues();
  const headers = allData[0]; // Get headers to label row data

  // Loop through each row to find "E60"
  for (let i = 1; i < allData.length; i++) {
    const currentRow = allData[i];
    // Check if any cell in the row contains "E60" (case-sensitive; use toLowerCase() if needed)
    const hasE60 = currentRow.some(cell => typeof cell === "string" && cell.includes("E60"));

    if (hasE60) {
      // Build email body with full row data
      let body = `Found a row containing "E60" (Row ${i+1}):\n\n`;
      headers.forEach((header, index) => {
        body += `${header}: ${currentRow[index]}\n`;
      });

      const subject = `Alert: Row ${i+1} Contains "E60"`;
      MailApp.sendEmail(recipientEmail, subject, body);
      Logger.log(`Alert email sent for row ${i+1}`);
      // Remove this break if you want to alert for ALL rows with "E60"
      // break;
    }
  }
}

Key Notes: #

• The some() method checks every cell in the row—adjust to check only specific columns if needed (e.g., currentRow[4].includes("E60") to only check column E).
• Add .toLowerCase() to make the match case-insensitive: cell.toLowerCase().includes("e60").

Setting Up Triggers (Automate the Script) #

Since you want these actions to trigger automatically:

1. Open the script editor (Tools > Script editor).
2. Click the clock icon (Triggers) in the left sidebar.
3. Click "Add trigger" and configure:
   • Choose the function you want to run.
   • For event source:
     • Use Time-driven to run on a schedule (e.g., daily, hourly).
     • Use From spreadsheet > On edit to run whenever someone edits the sheet (great for the first task when criteria are updated).
4. Save the trigger—Google will ask for permissions the first time you run it.

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