How to clear/delete data after a given row index in Google Spreadsheets

May 24, 2022

I would like to delete all data after row [x]. For example if the x = 355, I want all data to be deleted after 355th row.

I am able to delete all content in a simple sheet in google spreadsheet with:

var sheet = ss.getSheetByName('foo');
sheet.clearContents();

I’ve tried to do it with getRange method:

var range = SpreadsheetApp
               .getActive()
               .getSheetByName("foo")
               .getRange("A20:E71");
range.clearContent();

but this is simply deleting inside data from A20:E71. What I want is to delete all data after A20th row.

>Solution :

Try:

  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                              .getSheetByName(`foo`)
  
  sheet.getRange(21, 1, sheet.getLastRow()-20, sheet.getLastColumn())
       .clearContent()

The range is defined as all rows beyond the equivalent of A20.

Function:

function myFunction(allRowsAfter) {

  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                              .getSheetByName(`foo`)
  
  sheet.getRange(allRowsAfter+1, 1, sheet.getLastRow()-allRowsAfter, sheet.getLastColumn())
       .clearContent()

}

myFunction(20); // Delete all rows after 20

See: