如何修改Google Apps Script将搜索日志保存至独立电子表格？

Here's the adjusted script that saves logs to your separate search_logs spreadsheet:

// Replace this with the ID of your search_logs spreadsheet
const LOG_SPREADSHEET_ID = "YOUR_SPREADSHEET_ID_HERE";

/**
 * Installable trigger function to log postcode searches
 * @param {Object} e The onEdit event object
 */
function postcodeSearchLog(e) {
  // Exit if no event data or empty value
  if (!e || !e.value) return;
  
  const editSheet = e.range.getSheet();
  const sheetName = editSheet.getName();
  
  // Only process edits in the "Postcode Checker" sheet
  if (!sheetName.match(/^(Postcode Checker)$/i)) return;
  
  const editCol = e.range.columnStart;
  const editRow = e.range.rowStart;
  
  // Map valid edit ranges to their respective log sheets
  let targetLogSheet = null;
  if (editCol === 4 && editRow >=7 && editRow <=18) {
    targetLogSheet = "APP_logs(RAW)";
  } else if ((editCol >=10 && editCol <=12) && editRow >=8 && editRow <=18) {
    targetLogSheet = "PUB_logs(RAW)";
  }
  
  // Exit if edit is outside specified ranges
  if (!targetLogSheet) return;
  
  try {
    // Access the external log spreadsheet
    const logSpreadsheet = SpreadsheetApp.openById(LOG_SPREADSHEET_ID);
    let logSheet = logSpreadsheet.getSheetByName(targetLogSheet);
    
    // Create log sheet with headers if it doesn't exist
    if (!logSheet) {
      logSheet = logSpreadsheet.insertSheet(targetLogSheet);
      logSheet.appendRow(["Timestamp", "Postcode"]);
      logSheet.setFrozenRows(1);
    }
    
    // Add the log entry with timestamp and postcode
    logSheet.appendRow([new Date(), e.value]);
  } catch (error) {
    console.error("Failed to write log entry:", error);
  }
}

1. Prepare the Log Spreadsheet

   • Create a new Google Sheet named search_logs (or use your existing one).
   • Copy its ID from the URL (the long string between /d/ and /edit).
   • Paste this ID into the LOG_SPREADSHEET_ID constant in the script.

2. Replace the Old Script

   • Open the script editor of your main "Postcode Checker" spreadsheet.
   • Delete the existing code and paste the modified script above.
   • Save the script (click the floppy disk icon) and name it something like PostcodeSearchLogger.

3. Create an Installable Trigger

   • In the script editor, click the clock icon (Triggers) in the left sidebar.
   • Click Add Trigger.
   • Configure the trigger as follows:
     • Choose which function to run: Select postcodeSearchLog
     • Choose which deployment to run: Select Head
     • Select event source: Choose From spreadsheet
     • Select event type: Choose On edit
   • Click Save and follow the prompts to authorize the script (you may need to allow access to external spreadsheets).

• External Logging: Uses SpreadsheetApp.openById() to access your separate search_logs sheet instead of the main spreadsheet, eliminating bloat and lag.
• Range Validation: Explicitly checks for edits in D7:D18 (column 4) and J8:L18 (columns 10-12) to avoid logging unintended edits.
• Error Handling: Adds a try/catch block to log errors if the script fails to write to the log sheet.
• Installable Trigger: Replaces the simple onEdit trigger (which can't access external resources) with an installable trigger that has proper authorization.

内容的提问来源于stack exchange，提问作者James 'Mook' Pite