CODEWITHSUNDEEP
javascriptgoogle-apps-scriptdelete-row
Màrius Montmany
Màrius Montmany

Reputation: 33

How to remove row if date is older than Today - 15 days

I want to delete all the rows of a sheet that match my criteria which is:

I've tried this code but the formula doesn't work:

function deleterows() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var lastrowtraining = ss.getSheetByName("Training").getRange("D:D").getValues().filter(String).length
var training = ss.getSheetByName("Training").getRange(1,1,lastrowtraining+4,42).getValues()  


//Here I define TODAY - 15  
var today = new Date(new Date().setHours(23,59,59,0,0))
var todayminus15 = new Date(today.setDate(today.getDate()-15))

// Here I define the columns
for(i=0;j<lastrowtraining+3;i++){
  var rowtraining = i+1;
  var date = training[i][0]
  var city = training[i][1]
  var trainingcomplete = training[i][5]

  if(trainingcomplete =='YES' && date <= todayminus15) {training.deleteRow(rowtraining)}
}
}

But I get this error: 

TypeError: Cannot find function deleteRow in object "Date,City Code,Name,Email,Phone,Successfully completed training,Days since registration if not complete,Send reminder?,Acuity ID,Successfully doc
... (which is the content of the entire sheet)

Upvotes: 0

Views: 678

Answers (2)

ADW
ADW

Reputation: 4247

The deleteRow method works on a sheet. Not on the data value array from the sheet.

Try:

var trainingSheet = ss.getSheetByName("Training"); // insert this line
var training = trainingSheet.getRange(1,1,lastrowtraining+4,42).getValues(); // change this line

And then use: trainingSheet.deleteRow(rowtraining)

Instead of: training.deleteRow(rowtraining)

Edit

When deleting rows it is best to go upwards from the bottom. Going downwards causes this problem: say you had to delete rows 5 and 6.

After you delete row 5, all the lower rows have moved up. So when you delete row 6, this is actually the original row 7 that has moved up.

So best to run the loop in reverse like so: 

for (var i = lastrowtraining + 2; i >= 0; i--){

Upvotes: 2

M&#224;rius Montmany
M&#224;rius Montmany

Reputation: 33

Thanks @ADW, @Chris G and @lazy.lizard.

Main problem was that I was triggering action deleteRow on training which was a getValues an deleteRow needs a sheet.

So this half solved the problem:

var training = trainingsheet.getRange(1,1,lastrowtraining+4,42).getValues(); // change this line

But, (I already don't know why) this code only works on the first row that matches the criteria so I add a trigger to the deleterowsnew()function after the row was deleted:

if(trainingcomplete =='YES' && date <= todayminus15) {trainingsheet.deleteRow(rowtraining);deleterowsnew()}

So the final code ends up like this:

function deleterowsnew() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var lastrowtraining = ss.getSheetByName("Training").getRange("D:D").getValues().filter(String).length
  var trainingsheet = ss.getSheetByName("Training")
  var training = trainingsheet.getRange(1,1,lastrowtraining+4,42).getValues()  


  //Here I define TODAY - 15  
  var today = new Date(new Date().setHours(23,59,59,0,0))
  var todayminus15 = new Date(today.setDate(today.getDate()-15))

  // Here I define the columns:
  for(i=0;i<lastrowtraining+3;i++){
    var rowtraining = i+1;
    var date = training[i][0]
    var city = training[i][1]
    var trainingcomplete = training[i][5]

    //I define here the condition and after it's completed and trigger the action again to be completed if another row matches the criteria
    if(trainingcomplete =='YES' && date <= todayminus15) {trainingsheet.deleteRow(rowtraining);deleterowsnew()}

  }
}

Thank you all for the help and hope this can help you on the future!

Upvotes: 0

Related Questions