Google spreadsheet script deleting new rows

Question

This is really my first time trying to get a google script to work, so please excuse my inexperience.

I have a spreadsheet that I want to have rows deleted if it goes past a specific amount(eight in my case).

I think this should work but I must be missing something. My problem is no rows being deleted, but no errors being given either.

function killNewRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getDataRange();  

  if (data.length > 8) {
    for (var row = data.length; row >= 9; row--) {
       sheet.deleteRow(row);
      }
    }
  }

Serge insas Serge insas · Accepted Answer · 2014-02-14T19:36:38

There are special methods for this kind of routines... here is a working example :

function killNewRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var maxRow = sheet.getMaxRows();
  if(lastRow>8){
    sheet.deleteRows(9, maxRow-8);
  }
}

btw, note the deleteRows(start,numberOfRows) method that will be faster than deleting one by one every row.

EDIT : sheet example here, make your own copy and don't forget to add a trigger from script editor > ressources > add new trigger > from spreadsheet > onChange.

You can of course make that all automatic by simply adding an 'on change' trigger that calls this function.

Google spreadsheet script deleting new rows

1 Answers