使用Google Apps脚本删除Google表格中的行 [英] Deleting rows in google sheets using Google Apps Script

问题描述

在尝试使用Google脚本删除与特定值匹配的行时遇到了最奇怪的错误.

I encountered the weirdest error while trying to delete rows that match a specific value using google script.

这是我的代码:

function myFunction() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("file.csv");

  var values = sheet.getRange("N2:N").getValues();

  var row_del = new Array();

  for(var i=0;i<values.length;i++)
  {
    if(values[i] == 'del'){
      row_del.push(i+2); // This line was added for debugging purposes.
     // sheet.deleteRow(i+2) was the line that was in this condition
     // (i+2) is used because row 1 has headers and the range starts from 0.
    }
  }

//  Logger.log(row_del);
//  GmailApp.sendEmail("my_email_address", "subject", row_del)

  for (var i = 0; i < row_del.length; i++)
  {
    sheet.deleteRow(row_del[i]);
  }

}

我编写的代码选择了应该删除的行号，但是在我的第一次尝试中并未删除所有这些行.我应多次执行脚本，以删除这些行.

The code that I have written picks up the row numbers that should be deleted but not all these rows are deleted in my first try. I should execute my script a number of times for these rows to be deleted.

如果我的代码有错误，它将显示出来，并且如果逻辑错误，则必须删除不正确的行.我没有遇到这两种情况，我应该多次执行此功能.

If my code has an error, it should show up and if the logic is wrong, incorrect rows must be deleted. I encounter neither of these scenarios and I should just execute this function multiple times.

我这里缺少什么吗?

推荐答案

从工作表中删除一行后，即使脚本继续运行，该行下面的行也会重新编号.如果脚本随后尝试也删除这些行，则结果是不可预测的.因此，删除行时应从下至上进行操作.在您的情况下，就像这样:

When a row is deleted from a sheet, the rows below it get renumbered even as the script continues to run. If the script subsequently tries to also delete those rows, the result is unpredictable. For this reason, when deleting rows one should proceed from bottom to top. In your case, like so:

for (var i = row_del.length - 1; i>=0; i--) {
  sheet.deleteRow(row_del[i]); 
}

这篇关于使用Google Apps脚本删除Google表格中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！