如何替换getActiveCell()以适配Google Apps Script定时触发任务

Hey Antonio, great question—this is a common pitfall when moving from manual script runs to time-driven triggers! The issue is that getActiveCell() only exists when you're interacting with the sheet manually; when a trigger runs, there's no "active" cell to reference. Here's how to refactor your script to target a specific range instead, so it works reliably with triggers:

Modified Script Code #

function MoveRows() { 
  var sheetNameToWatch = "Requests"; 
  var sheetNameToMoveTo = "Approved"; 
  var statusColumn = 5; // Adjust this to your status column (e.g., 5 = column E)
  var triggerValue = "Approved"; // The value that triggers a row move

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sourceSheet = ss.getSheetByName(sheetNameToWatch); 
  var targetSheet = ss.getSheetByName(sheetNameToMoveTo);

  // Get all data rows in the source sheet (skip header if needed)
  var lastRow = sourceSheet.getLastRow();
  var dataRange = sourceSheet.getRange(2, 1, lastRow - 1, sourceSheet.getLastColumn()); // Start at row 2 to skip header
  var data = dataRange.getValues();

  // Iterate backwards to avoid index issues when deleting rows
  for (var i = data.length - 1; i >= 0; i--) {
    var currentRowIndex = i + 2; // Offset because we started at row 2
    var status = data[i][statusColumn - 1]; // Column index is 0-based

    if (status === triggerValue) {
      // Copy the entire row to the target sheet
      var rowToMove = sourceSheet.getRange(currentRowIndex, 1, 1, sourceSheet.getLastColumn());
      rowToMove.copyTo(targetSheet.getRange(targetSheet.getLastRow() + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_ALL);
      
      // Delete the row from the source sheet
      sourceSheet.deleteRow(currentRowIndex);
    }
  }
}

Key Changes & Explanations #

• Removed reliance on getActiveCell(): Instead, we explicitly define the range we want to check (all rows in the "Requests" sheet, skipping the header row if you have one).
• Backwards iteration: When deleting rows, iterating from the bottom up prevents row index shifting from breaking the loop (if we went top to bottom, deleting a row would make subsequent rows shift up, leading to skipped rows).
• Configurable trigger conditions: Adjust statusColumn and triggerValue to match your sheet's setup (e.g., if your approval status is in column F, set statusColumn = 6).
• Full row copy: Uses PASTE_ALL to copy values, formatting, and formulas (you can change this to PASTE_VALUES if you only want plain text).

Setting Up the Time Trigger #

Once you've adjusted the script to match your sheet structure:

1. Open the Apps Script editor
2. Click the clock icon (Triggers) in the left sidebar
3. Click "Add Trigger"
4. Configure your trigger:
   • Choose MoveRows as the function to run
   • Select "Time-driven" as the event source
   • Pick your preferred schedule (hourly, daily, etc.)

This script will now run automatically on your chosen schedule, no manual interaction required!

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