如何在Google Sheets中实现当自动计算的状态变为“All Picked”时，保留条件格式并移动整行

Hey there, the issue with your original code is that the onEdit simple trigger only fires when a user manually edits a cell—changes from array formulas don’t count as manual edits, so it never picks up the "All Picked" status when it updates automatically. Here’s how to fix this:

Step 1: Replace Your Code with This #

Delete the existing onEdit function and paste this updated script. It scans your sheet for rows where column A is "All Picked" and moves them to the bottom, preserving all formulas and formatting:

function moveAllPickedRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  let lastRow = sheet.getLastRow();
  
  // Iterate from bottom to top to avoid row index issues when deleting
  for (let row = lastRow; row >= 3; row--) {
    const status = sheet.getRange(row, 1).getValue();
    
    if (status === "All Picked") {
      // Copy entire row to the bottom (preserves formulas, conditional formatting, etc.)
      sheet.getRange(row, 1, 1, sheet.getLastColumn()).copyTo(
        sheet.getRange(lastRow + 1, 1),
        SpreadsheetApp.CopyPasteType.PASTE_ALL,
        false
      );
      // Delete the original row
      sheet.deleteRow(row);
      // Update lastRow since we added a new row
      lastRow++;
    }
  }
}

Step 2: Set Up an Installable "On Change" Trigger #

Since simple triggers can’t detect formula updates, we need an installable trigger that runs whenever the sheet’s data changes:

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar.
2. Click Add Trigger in the bottom right.
3. Configure the trigger like this:
   • Choose which function to run: moveAllPickedRows
   • Choose which deployment to run: Head
   • Select event source: From spreadsheet
   • Select event type: On change
4. Click Save (you may need to authorize the script with your Google account).

Key Notes #

• We iterate from the bottom row up to the 3rd row (since your formula starts at A3) to prevent skipping rows when deleting. If we went top to bottom, deleting a row would shift the rows below up, making us miss some entries.
• PASTE_ALL ensures that all data, formulas, conditional formatting, and cell styles are copied to the new row.
• The trigger will automatically run whenever any change happens in the sheet (including formula recalculations that update column A to "All Picked").

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