How to delete only last 3 days of data in sheet using Javascript?

user_16526687 0
Viewed 54

I'm currently working on a script that should delete data of the last three days in the sheet without disturbing the fourth day's data as my data gets updated only for 3 days I want to delete and update only for 3 days of data in the sheet.

I'm facing an issue with the below code it deletes the data from the last row to the topmost row whereas I want to delete data only for the last three days any help on how to fix this is much appreciated.

Thanks in advance.

my code:

  var sheet =  SpreadsheetApp.openById('xxxxxxxxxxxxxxxxx').getActiveSheet();

  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();


  var currentDate = new Date();
  var oneweekago = new Date();
  oneweekago.setDate(currentDate.getDate() - 7);

  for (i = lastrow; i >= 2; i--) {
    var tempdate = sheet.getRange(i, 1).getValue();

    if (tempdate < oneweekago) {
      sheet.deleteRow(i);
    }
  }
}
google-apps-script google-sheets
1 Answers
  var sheet =  SpreadsheetApp.openById('xxxxxxxxxxxxxxxxx').getActiveSheet();

  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();

  var days = 3;
  var currentDate = new Date();
  var closeDate = new Date();
  closeDate.setDate(currentDate.getDate() - days);
  closeDate = closeDate.getTime();
  
  var dates = datarange.getValues();

  for (let i = dates.length; i >= 1; i--) {
    var tempdate = new Date(dates[i][0]).getTime();

    if (tempdate < closeDate) {
      break;
    }
    sheet.deleteRow(i + 1);
  }
}
user_161251390
Related