基于Google表格搭建到期提醒系统的Apps Script开发求助

Hey Luca, I’ve put together a complete, beginner-friendly script tailored to your needs—with detailed comments to help you adjust it for your specific spreadsheet layout. Let’s break this down step by step:

Key Features #

• Daily automatic scanning of all your sheets (custom functions for each sheet’s unique layout)
• Sends an email with a clean HTML table of rows marked in scadenza or scaduto
• Prevents duplicate reminders by marking rows with reminder sent in the stato column and coloring the cell
• Easy to extend for additional sheets

Full Script Code #

// Configuration - Update these values to match your needs
const CONFIG = {
  emailRecipient: "your-email@example.com", // Replace with your email address
  emailSubject: "Daily Deadline Reminder: Contracts & Projects",
  reminderSentColor: "#FFFF00" // Yellow background for "reminder sent" cells
};

/**
 * Main function to trigger daily reminder emails
 */
function sendReminderEmails() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let allReminderRows = [];

  // Call scan functions for each sheet (add more as needed)
  allReminderRows = allReminderRows.concat(scanSheet1(spreadsheet.getSheetByName("Sheet1"))); // Replace "Sheet1" with your actual sheet name
  allReminderRows = allReminderRows.concat(scanSheet2(spreadsheet.getSheetByName("Sheet2"))); // Add more sheets here

  // Send email only if there are rows to remind about
  if (allReminderRows.length > 0) {
    const htmlTable = generateHtmlTable(allReminderRows);
    sendEmailWithHtml(htmlTable);
    
    // Optional: Send as PDF instead of HTML (uncomment below and comment the line above)
    // sendEmailWithPdf(spreadsheet, allReminderRows);
  }
}

/**
 * Example scan function for Sheet1 - MODIFY THIS FOR YOUR SHEET
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - The sheet to scan
 * @returns {Array} Array of rows that need reminders
 */
function scanSheet1(sheet) {
  const reminderRows = [];
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const headers = values[0]; // Assuming first row is headers
  
  // Define column indices for this sheet (adjust based on your layout)
  const statoCol = headers.indexOf("stato") + 1; // 1-based index for "stato" column
  const statusCol = sheet.getLastColumn(); // Last column has "in scadenza" or "scaduto"
  
  // Loop through rows (skip header row)
  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const currentStatus = row[statusCol - 1]; // Convert to 0-based index
    const currentStato = row[statoCol - 1];

    // Check if row needs a reminder and hasn't been sent yet
    if ((currentStatus === "in scadenza" || currentStatus === "scaduto") && currentStato !== "reminder sent") {
      // Add row to reminder list (include headers for context)
      reminderRows.push([...headers, ...row]); // Combine headers and row data for clarity
      
      // Mark "stato" column as "reminder sent" and color the cell
      sheet.getRange(i + 1, statoCol).setValue("reminder sent").setBackground(CONFIG.reminderSentColor);
    }
  }
  
  return reminderRows;
}

/**
 * Example scan function for Sheet2 - COPY AND MODIFY THIS FOR ADDITIONAL SHEETS
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet - The sheet to scan
 * @returns {Array} Array of rows that need reminders
 */
function scanSheet2(sheet) {
  const reminderRows = [];
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const headers = values[0];
  
  // Adjust these indices for your second sheet's layout
  const statoCol = headers.indexOf("stato") + 1;
  const statusCol = sheet.getLastColumn();
  
  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const currentStatus = row[statusCol - 1];
    const currentStato = row[statoCol - 1];

    if ((currentStatus === "in scadenza" || currentStatus === "scaduto") && currentStato !== "reminder sent") {
      reminderRows.push([...headers, ...row]);
      sheet.getRange(i + 1, statoCol).setValue("reminder sent").setBackground(CONFIG.reminderSentColor);
    }
  }
  
  return reminderRows;
}

/**
 * Generate an HTML table from reminder rows
 * @param {Array} rows - Array of rows to include in the table
 * @returns {string} HTML table string
 */
function generateHtmlTable(rows) {
  if (rows.length === 0) return "";
  
  let html = "<table style='border-collapse: collapse; width: 100%;'>";
  
  // Add header row
  html += "<tr>";
  rows[0].slice(0, rows[0].length/2).forEach(header => { // First half is headers
    html += `<th style='border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;'>${header}</th>`;
  });
  html += "</tr>";
  
  // Add data rows
  rows.forEach(row => {
    html += "<tr>";
    row.slice(rows[0].length/2).forEach(cell => { // Second half is row data
      html += `<td style='border: 1px solid #ddd; padding: 8px;'>${cell || ""}</td>`;
    });
    html += "</tr>";
  });
  
  html += "</table>";
  return html;
}

/**
 * Send email with HTML table content
 * @param {string} htmlTable - HTML table to include in email
 */
function sendEmailWithHtml(htmlTable) {
  const htmlBody = `
    <p>Hi there,</p>
    <p>Here are today's deadline reminders:</p>
    ${htmlTable}
    <p>Best regards,</p>
    <p>Your Google Sheets Reminder System</p>
  `;
  
  MailApp.sendEmail({
    to: CONFIG.emailRecipient,
    subject: CONFIG.emailSubject,
    htmlBody: htmlBody
  });
}

/**
 * Optional: Send email with PDF of the reminder rows
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} spreadsheet - The active spreadsheet
 * @param {Array} rows - Array of rows to include in PDF
 */
function sendEmailWithPdf(spreadsheet, rows) {
  // Create a temporary sheet to hold the reminder rows
  const tempSheet = spreadsheet.insertSheet("TempReminderSheet");
  tempSheet.getRange(1, 1, rows.length, rows[0].length/2).setValues(rows.map(row => row.slice(rows[0].length/2)));
  
  // Generate PDF
  const pdf = spreadsheet.getAs("application/pdf").setName("Deadline_Reminders.pdf");
  
  // Send email
  MailApp.sendEmail({
    to: CONFIG.emailRecipient,
    subject: CONFIG.emailSubject,
    body: "Please find attached today's deadline reminders.",
    attachments: [pdf]
  });
  
  // Delete temporary sheet
  spreadsheet.deleteSheet(tempSheet);
}

/**
 * Set up daily time-driven trigger (run this once)
 */
function createDailyTrigger() {
  // Delete existing triggers to avoid duplicates
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === "sendReminderEmails") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Create new daily trigger (runs at 9 AM local time)
  ScriptApp.newTrigger("sendReminderEmails")
    .timeBased()
    .everyDays(1)
    .atHour(9)
    .create();
  
  SpreadsheetApp.getUi().alert("Daily reminder trigger created successfully!");
}

How to Use This Script #

1. Open your Google Spreadsheet and go to Extensions > Apps Script to open the script editor.
2. Replace the default code with the script above.
3. Update the CONFIG section:
   • Change emailRecipient to your email address.
   • Adjust emailSubject if needed.
4. Modify the scan functions:
   • For each sheet in your spreadsheet, copy the scanSheet1 function, rename it (e.g., scanSheet3), and update:
     • The sheet name in spreadsheet.getSheetByName("Sheet1") to match your sheet.
     • Ensure statoCol correctly points to your "stato" column (the script uses the header name to find the index automatically).
5. Add all scan functions to the main sendReminderEmails function (follow the examples for Sheet1 and Sheet2).
6. Set up the daily trigger:
   • Run the createDailyTrigger function once. You’ll need to authorize the script (follow the prompts—click "Advanced" and "Go to [Script Name]" to allow permissions).
7. Test the script:
   • Manually run the sendReminderEmails function to check if it works correctly.

Beginner Tips #

• Column Indices: The script uses header names to find columns, so you don’t have to count columns manually.
• Duplicate Prevention: Once a row is marked reminder sent, it won’t be included in future emails unless you remove that status.
• PDF Option: If you prefer PDFs over HTML tables, uncomment the sendEmailWithPdf line in sendReminderEmails and comment out the sendEmailWithHtml line.

内容的提问来源于stack exchange，提问作者Luca E.