• Jobs
  • About Us
  • professionals
    • Home
    • Jobs
    • Courses and challenges
  • business
    • Home
    • Post vacancy
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

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

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.

about 3 years ago · Juan Pablo Isaza
3 answers
Answer question

0

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)
about 3 years ago · Juan Pablo Isaza Report

0

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();
}
about 3 years ago · Juan Pablo Isaza Report

0

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.

about 3 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recommend me some offers
I have an error