多工作表多列实现输入Y自动填充日期的脚本修改需求

Got it, let's tweak that script to work across multiple sheets and columns for your sales lead tracking. Here's a flexible, easy-to-adjust version that fits your needs:

Modified Script #

function onEdit(event) {
  // 🛠️ Configure these values to match your spreadsheet setup
  const config = {
    // List of sheet names you want to monitor (add/remove as needed)
    targetSheets: ["Sales Leads", "Follow-Up Leads"],
    // List of column numbers to trigger the timestamp (e.g., AH=32, AJ=34)
    targetColumns: [32, 34]
  };

  const editedRange = event.range;
  const editedSheet = editedRange.getSheet();
  const editedColumn = editedRange.getColumn();
  const editedValue = editedRange.getValue().toLowerCase(); // Handle both Y and y

  // Check if the edit is in a target sheet and target column
  if (config.targetSheets.includes(editedSheet.getName()) && config.targetColumns.includes(editedColumn)) {
    const adjacentCell = editedSheet.getRange(editedRange.getRow(), editedColumn + 1);
    const currentDate = new Date();

    if (editedValue === "y") {
      adjacentCell.setValue(currentDate);
      // Optional: Set a clean date format (adjust to your preference)
      adjacentCell.setNumberFormat("yyyy-MM-dd");
    } else {
      adjacentCell.clearContent(); // Clear date if value isn't Y/y
    }
  }
}

How to Use This #

1. Open your Google Spreadsheet, go to Extensions > Apps Script
2. Replace any existing code with the script above
3. Update the config section:
   • Add all the sheet names you want to track in targetSheets (wrap each name in quotes, separate with commas)
   • Add the column numbers you want to monitor in targetColumns (e.g., if you track replies in column AH (32) and conversions in column AJ (34), use [32, 34])
4. Save the script (give it a name like LeadTrackingTimestamp)
5. Go back to your spreadsheet—now whenever you type Y or y in any target column of your target sheets, the adjacent cell will auto-fill today's date. If you delete the Y/y, the date will clear too.

Key Improvements Over the Original Script #

• Multi-sheet support: Works across all sheets you list in targetSheets
• Multi-column support: Monitors multiple columns at once
• Simplified case handling: Uses toLowerCase() to catch both Y and y without separate switch cases
• Cleaner date formatting: Optional line to set a readable date format (adjust to your region's preference)
• Flexible configuration: Easy to add/remove sheets or columns without digging into the logic

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