求助：开发Google Sheet未确认订单自动提醒邮件脚本

Got it, let's solve this problem for your order tracking sheet! I’ll share a Google Apps Script solution (if you’re using Excel instead, just let me know and I can adjust the code) that automatically checks for blank cells in column Q, compiles the relevant details, and sends a reminder email to you and your team.

Step 1: Paste the Script into Your Sheet #

1. Open your order tracking Google Sheet
2. Click Extensions > Apps Script to open the script editor
3. Replace the default code with this:

function sendUnconfirmedOrderReminder() {
  // Replace with your sheet tab name
  const sheetName = "Order Tracking";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  // Get all data from the sheet (adjust range if needed)
  const data = sheet.getDataRange().getValues();
  // Skip header row (change to 0 if your sheet has no header)
  const headerRow = 1;
  
  // Initialize array to store unconfirmed order details
  let unconfirmedOrders = [];
  
  // Loop through each row starting after the header
  for (let i = headerRow; i < data.length; i++) {
    const row = data[i];
    // Column Q is index 16 (arrays start at 0: A=0, B=1, ..., Q=16)
    const confirmationDate = row[16];
    // Skip rows where Q is already filled
    if (confirmationDate !== "") continue;
    
    // Collect required info: A, B, C, D columns (indices 0-3) and P column (index 15)
    const orderInfo = {
      id: row[0],
      customer: row[1],
      orderNumber: row[2],
      product: row[3],
      entryDate: row[15]
    };
    
    unconfirmedOrders.push(orderInfo);
  }
  
  // Exit early if there are no unconfirmed orders
  if (unconfirmedOrders.length === 0) {
    console.log("No unconfirmed orders found — nothing to send!");
    return;
  }
  
  // Build the email content with a readable table
  let emailBody = "Hi Team,\n\nHere’s the list of orders waiting for confirmation (Q column is blank):\n\n";
  emailBody += "| Order ID | Customer | Order Number | Product | Entry Date |\n";
  emailBody += "|----------|----------|--------------|---------|------------|\n";
  
  unconfirmedOrders.forEach(order => {
    emailBody += `| ${order.id} | ${order.customer} | ${order.orderNumber} | ${order.product} | ${order.entryDate} |\n`;
  });
  
  emailBody += "\nPlease fill in the confirmation date in column Q for these orders at your earliest convenience.\n\nThanks,\n[Your Name]";
  
  // Replace with your team's email addresses (comma-separated)
  const recipients = "your.email@example.com, teammate1@example.com, teammate2@example.com";
  const subject = `Unconfirmed Order Reminder: ${unconfirmedOrders.length} Orders Awaiting Action`;
  
  // Send the email
  MailApp.sendEmail({
    to: recipients,
    subject: subject,
    body: emailBody,
    noReply: true
  });
  
  console.log("Reminder email sent successfully!");
}

Step 2: Customize the Script #

Before running, tweak these parts to match your setup:

• const sheetName = "Order Tracking"; → Swap this with your actual sheet tab name
• const recipients = "your.email@example.com, ..."; → Add all team members' emails separated by commas
• Adjust column indices if your sheet uses different columns (remember: arrays start at 0, so A=0, B=1, etc.)
• Update the [Your Name] in the email signature to your name

Step 3: Set Up a Scheduled Trigger #

To make this run automatically (e.g., daily):

1. In the Apps Script editor, click the clock icon (Triggers) on the left sidebar
2. Click Add Trigger
3. Configure the trigger:
   • Choose function: sendUnconfirmedOrderReminder
   • Choose deployment: Head
   • Event source: Time-driven
   • Type of time based trigger: Day timer
   • Time of day: Pick a time your team will see the reminder (e.g., 9:00 AM)
4. Click Save and authorize the script when prompted (you’ll need to allow access to your Google account for email and sheet access)

Testing the Script #

Before setting up the trigger, test it manually:

1. In the Apps Script editor, select sendUnconfirmedOrderReminder from the function dropdown
2. Click the run button ▶️
3. Check your email to confirm the reminder comes through correctly

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