Deleting rows in google sheets using Google Apps Script

Question

I encountered the weirdest error while trying to delete rows that match a specific value using Google Apps Script.

Here is my Code:

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?

Answer 1

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

Answer 2

To delete blank rows from a single named sheet, assuming column 1 has data in valid rows. Search and delete from highest row number to lowest row number.

// 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 = range.getValue();
    if (data == '') {
      sheet.deleteRow(i);
    }
  }
}

Answer 3

refer to lock function suggested by google team:

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

That way, you got your deleting job work once at a time! The system thread won't go on to other jobs until 30 seconds is up or releasing the lock.

google dev-document: https://developers.google.com/apps-script/reference/lock/lock

Answer 4

increment i only if you didn't delete a row

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

Answer 5

You can just delete the rows alter the last row using the deleteRows function like this:

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

Answer 6

So, is this what it should look like?

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

}

Answer 7

Copy pasting from: https://gist.github.com/dDondero/285f8fd557c07e07af0e

Instead of looping through the rows twice, you can count how mwny rows have been deleted, to calculate the correct index for the row that you will delete next.

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++;
    }
  }
};

Answer 8

This is a script I used to delete everything but certain values, but can be easiliy modified to keep certain strings and runs a lot faster than looping through all the data filters out and deletes the values you want to remove and bulk deletes. my data had about 10000 rows so loop would have taken forever.

function DeleteCertainRows(){

  columnCopy("D","Z","YourSheetName");//Copy from your target column in this case D to another column in this case Z

  replaceInSheet("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 values

  DeleteValueInColumn("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();


};

Answer 9

Update 2020

A faster and a more modern JavaScript approach would be to use forEach and reverse() to iterate backwards.

It makes more sense to flatten the values array since it concerns data of a single column.

Solution:

function myFunction() {
  const doc = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = doc.getSheetByName("file.csv");
  const values = sheet.getRange("N2:N").getValues().flat();
  values.reverse().forEach((r,i)=>{
      if (r=='del'){
         sheet.deleteRow(values.length-i+1);
      }
  });
}