CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsdelete-row
rockinfreakshow
rockinfreakshow

Reputation: 29982

sheet.deleteRows() - Need to delete all the rows with data from top (preferably from 2nd row)

I need to delete 200-300 rows, excluding row 1. The script below is getting an out of bounds error, and it's slow. Can someone help with a way to speed up the process?

** IF POSSIBLE EXCLUDE ROW_1

function clearRange() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('test');

  var start=1;
  var end=300;
  for (var i = start; i <= end; i++) 
    { sheet.deleteRow(i); }

}

SAMPLE SHEET - TAKE COPY

Upvotes: 9

Views: 36290

Answers (5)

Skorejen
Skorejen

Reputation: 435

heey,

if someone wants to delete rows but without deleting the formating of them you can use method clearContent() instead of deleteRows(), you have to use getRange() though

let sheet = SpreadsheetApp.getActiveSheet(); 
sheet.getRange(1,1,sheet.getLastRow(),3).clearContent();

Upvotes: 0

Jay Dadhania
Jay Dadhania

Reputation: 404

Be aware when using the answer by Alan Wells! (Edit: he has corrected his answer.)

getLastRow() returns the index of the last row with content.

But, the second parameter in deleteRows(rowPosition, howMany) is count of rows to be deleted, and not index of the last row.

Therefore, to delete all non-blank rows after a specific row, you would have to do:

function deleteAllBelow(startFrom)
{
    // get last row index
    var lastRowWithContent = sheet.getLastRow();

    if(lastRowWithContent < startFrom)
    {
        // rows below startFrom are already blank!
        return;
    }
    
    // count total number of rows to be deleted
    // +1 is to include the last row
    var totalRowsToBeDeleted = lastRowWithContent - startFrom + 1;
    
    sheet.deleteRows(startFrom, totalRowsToBeDeleted); 
}

Suppose that the last row with content is 20.

Now deleteAllBelow(15); will delete all non-blank rows from 15 to 20, and totalRowsToBeDeleted will be 6 (because 15 and 20 are both included).

This also takes care of cases when there is no non-blank rows at or below your starting row.

Upvotes: 4

Alan Wells
Alan Wells

Reputation: 31300

Don't use a for loop. You can delete multiple rows in a Google Spreadsheet in one operation:

var start, howManyToDelete;

start = 2;//Hard coded row number from where to start deleting

howManyToDelete = sheet.getLastRow() - start + 1;//How many rows to delete -
      //The blank rows after the last row with content will not be deleted

sheet.deleteRows(start, howManyToDelete);

If you wanted to delete from row 2 to row 10, that is 9 rows to delete. The number of rows to delete must be 9. Only row 1 will be left after deleting row 2 to 10.

Apps Script Documentation

Upvotes: 44

Nikhil
Nikhil

Reputation: 182

if you want to delete all the rows you can use clear function. 

// This example assumes there is a sheet named "first"
var ss = SpreadsheetApp.getActiveSpreadsheet();
var first = ss.getSheetByName("first");
first.clear();

App Script Reference

Upvotes: -1

Kevin W.
Kevin W.

Reputation: 101

FYI: getLastRow() returns the index of the last row WITH content. Use getMaxRows() to return the absolute last row (regardless of content).

Upvotes: 7

Related Questions