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

问题描述

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

I encountered the weirdest error while trying to delete rows that match a specific value using Google Apps 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.

这里有我遗漏的东西吗?

Is there something that I'm missing here?

推荐答案

当从工作表中删除一行时，即使脚本继续运行，它下面的行也会重新编号.如果脚本随后还尝试删除这些行，则结果是不可预测的.出于这个原因，删除行时应该从下到上进行.就你而言，就像这样:

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 Script 删除 Google 表格中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！