求助：Google Sheets删除勾选复选框行脚本运行异常

Hey there! I see exactly what's going wrong with your script—let's break it down and fix it.

The Root Problem #

When you delete rows from top to bottom (like your current forEach loop does), every time you delete a row, all the rows below it shift up by one. This messes up the index tracking: for example, if you delete row 2, the original row 3 becomes the new row 2. When your loop moves to the next index, it skips over that shifted row, leading to missed deletions, and can even accidentally delete unselected rows because the indices no longer match the original data.

Solution 1: Delete Rows from Bottom to Top #

The simplest fix is to iterate backwards through your rows. This way, deleting a row doesn't affect the positions of the rows you haven't checked yet. Here's the adjusted code:

function deleterows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.showRows(1, sheet.getMaxRows()); // Make sure all rows are visible first
  var values = sheet.getRange('K1:K50').getValues();
  
  // Iterate backwards from the last row to the first
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i][0]) { // Check if the checkbox is ticked
      sheet.deleteRows(i + 1); // +1 because sheet rows are 1-indexed
    }
  }
}

Solution 2: Batch Delete Rows (More Efficient) #

If you're dealing with a lot of rows, batch deleting is better for performance. First, collect all the row numbers you want to delete, then sort them in reverse order and delete them all at once:

function deleterows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.showRows(1, sheet.getMaxRows());
  var values = sheet.getRange('K1:K50').getValues();
  var rowsToDelete = [];
  
  // Collect all rows with ticked checkboxes
  values.forEach(function(r, i) {
    if (r[0]) {
      rowsToDelete.push(i + 1); // Store 1-indexed row number
    }
  });
  
  // Sort rows in reverse order so we delete from bottom to top
  rowsToDelete.sort(function(a, b) { return b - a; });
  
  // Batch delete each row
  rowsToDelete.forEach(function(row) {
    sheet.deleteRows(row);
  });
}

Why This Works #

• Backward iteration: By starting from the last row and moving up, deleting a row doesn't change the indices of the rows we still need to check.
• Batch deletion: Collecting rows first lets us avoid index confusion entirely, and sorting in reverse ensures we handle the highest rows first, keeping lower row positions stable.

Give either version a try—they should fix both the partial deletions and accidental row removals you're seeing!

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