使用Google Apps脚本删除Google表格中的行

少年乘勇气，百战过乌孙。这篇文章主要讲述使用Google Apps脚本删除Google表格中的行相关的知识，希望能为你提供帮助。
尝试使用谷歌脚本删除与特定值匹配的行时遇到了最奇怪的错误。
这是我的代码：

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]); }}

我编写的代码选择了应该删除的行号，但是在我的第一次尝试中并没有删除所有这些行。我应该多次执行我的脚本以删除这些行。
如果我的代码有错误，它应该显示，如果逻辑错误，则必须删除不正确的行。我没有遇到这些情况，我应该多次执行此功能。
这里有什么我想念的吗？
答案从工作表中删除行时，即使脚本继续运行，它下面的行也会重新编号。如果脚本随后尝试也删除这些行，则结果是不可预测的。出于这个原因，当删除行时，应该从下到上进行。在你的情况下，像这样：

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

另一答案参考谷歌团队建议的锁定功能：

var lock = LockService.getScriptLock(); lock.waitLock(30000); // lock 30 seconds //do whatever you want here lock.releaseLock();

这样，您就可以一次完成一次删除工作！系统线程将在30秒到期或释放锁定之前不会继续执行其他作业。
谷歌开发文档：https://developers.google.com/apps-script/reference/lock/lock
另一答案那么，这应该是什么样子？

function myFunction() { var doc = SpreadsheetApp.getActiveSpreadsheet(); var sheet = doc.getSheetByName("Sheet1"); var values = sheet.getRange("A:A").getValues(); var row_del = new Array(); for(var i=0; i< values.length; i++) { if(values[i] == 'N'){ 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 = row_del.length - 1; i> =0; i--) { sheet.deleteRow(row_del[i]); } }

另一答案仅当您没有删除行时才递增i

function del_F_rows(){ var i=1; while(!sht_balanceHistory.getRange(i,1).isBlank()){ if(sht_balanceHistory.getRange(i,3).getValue()=="F") sht_balanceHistory.deleteRow(i); else i=i+1; } }

另一答案复制粘贴：https://gist.github.com/dDondero/285f8fd557c07e07af0e
您可以计算已删除的行数，而不是循环遍历行，以计算下一步要删除的行的正确索引。

function deleteRows() { var sheet = SpreadsheetApp.getActiveSheet(); var rows = sheet.getDataRange(); var numRows = rows.getNumRows(); var values = rows.getValues(); var rowsDeleted = 0; for (var i = 0; i < = numRows - 1; i++) { var row = values[i]; if (row[0] == 'delete' || row[0] == '') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'. sheet.deleteRow((parseInt(i)+1) - rowsDeleted); rowsDeleted++; } } };

另一答案你可以使用deleteRows函数删除改变最后一行的行，如下所示：

var maxRows = newsheet.getMaxRows(); var lastRow = newsheet.getLastRow(); if (maxRows-lastRow != 0) { newsheet.deleteRows(lastRow+1, maxRows-lastRow); }

另一答案这是我用来删除除了某些值之外的所有内容的脚本，但是可以很容易地修改以保持某些字符串和运行速度比循环遍历所有数据过滤器并删除要删除的值和批量删除的速度快得多。我的数据有大约10000行，所以循环将永远。

function DeleteCertainRows(){columnCopy("D","Z","YourSheetName"); //Copy from your target column in this case D to another column in this case ZreplaceInSheet("Z","YourSheetName","keep String 1","keep"); //find and replace the value with what you want to keep replaceInSheet("Z","YourSheetName","keep String 2","keep"); //Can repeat for additional valuesDeleteValueInColumn("Z","YourSheet","keep"); //filters and deletes all other values Column is case sensitive and cant go past Z}; function replaceInSheet(repColumn,sheetname, to_replace, replace_with) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname); var values = sheet.getRange(repColumn+'1:'+repColumn+sheet.getMaxRows()); var textFinder = values.createTextFinder(to_replace); var replaceall = textFinder.replaceAllWith(replace_with); }; function columnCopy(copyfrm,copyto,sheetname){ var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname); var copyrange = sheet.getRange(copyfrm+'1:'+copyfrm+sheet.getMaxRows()); var pasterange = sheet.getRange(copyto+'1:'+copyto+sheet.getMaxRows()); copyrange.copyTo(pasterange); }; function DeleteValueInColumn(colStr, sheetname, deleteval){ var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname); var filterRange=sheet.getDataRange(); var myFilter = filterRange.createFilter(); var CriteriaBuild = SpreadsheetApp.newFilterCriteria(); var Criteria = CriteriaBuild.whenTextDoesNotContain(deleteval); //change to whenTextContains to delete your string value instead of everything else var myCriteria = Criteria.build(); var str = colStr; var myCol = parseInt(str.charCodeAt(0) - 64); Logger.log(myCol); myFilter.setColumnFilterCriteria(myCol, myCriteria); var deleterange=sheet.getRange('2:'+sheet.getLastRow()); sheet.deleteRows(deleterange.getRow(), deleterange.getNumRows()); myFilter.remove(); };

另一答案【使用Google Apps脚本删除Google表格中的行】要从单个命名工作表中删除空行，假设第1列的数据为有效行。搜索并从最高行号到最低行号删除。

// Deletes any row whose first column is blank // WARNING: Assumes any valid row has data in column 1 function deleteBlankRows() { var doc = SpreadsheetApp.getActiveSpreadsheet(); var sheet = doc.getSheetByName("Sheet3"); var lastRow = SpreadsheetApp.getActiveSheet().getLastRow(); for (var i = lastRow; i > 0; i--) { var range = sheet.getRange(i,1); var data = https://www.songbingjia.com/android/range.getValue(); if (data =='') { sheet.deleteRow(i); } } }