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

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.

CodePudding user response：

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:

getRange(row, column, numRows, numColumns)

CodePudding user response：

Clear everything after row

function clearAfterRow(r) {//clear everything after row r 
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("foo");
  const sr = r   1;
  sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn()).clearContent();
}

CodePudding user response：

You need to use getLastColumn() and getLastRow() methods.

The number of rows to be deleted depending on a row x can be calculated by lastRowIndex - x 1. In terms of columns you can just delete all columns using 1 as a start value for the range and lastColumnIndex for the number of columns to remove.

Logger.log("Starting script...")

const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = ss.getLastRow();
const lastCol = ss.getLastColumn();

const x = 20;
if(lastRow - x   1 > 0) {
  ss.getRange(20, 1, lastRow - x   1, lastCol).clearContent();
  Logger.log(`Deleting all content in Range (${[x, 1, lastRow - x   1, lastCol]})`);
}

Logger.log("Done.")

However there is one thing you need to account for in case you run this multiple times or x is bigger than the last row with content in you Sheet. You need to check whether the range affects at least one row otherwise you will get an error The number of rows in the range must be at least 1., so check for lastRow - x 1 > 0 before deletion.