Monday 15 July 2013

Google Sheets - Script to delete date expired rows -


I am trying to get a script that automatically rows with the old date of 3 days (within 4 days)

I found this script, which I was hoping to optimize ... Function DeleteOldEntries () {

  var ss = SpreadsheetApp.getActiveSpreadsheet () ; Give your sheet name instead of // Sheet1 var sheet = ss.getSheetByName ("Foglio1"); Var Datenz = Sheet.GetData Range (); Var lastrow = datarange.getLastRow (); Var currentDate = new date (); Var oneweekago = New Date (); Oneweekago.setDate (currentDate.getDate () - 7); For (i = last; i> = 2; i--) {var tempdate = sheet.getRange (i, 1) .getValue (); If (tempdate & lt; oneweekago) {sheet.deleteRow (i); }}}   

But there seems to be an error in the script that I have to figure it out before adjusting for 4 days instead of 7 (this part is easy)

My sheet has 3 columns with the date of column C, if this information is useful

"Date in column C, if that information is useful"

It really is! Rather than trying to get a date in column A, it is done in this line:

  var tempdate = sheet.getRange (i, 1) .getValue ();   

You should see the value in column C as:

  var tempdate = sheet.getRange (i, 3) .getValue ();   

To be more efficient, you should compare these at the array level, look at it as below and it will be very fast ...

  Var ss = Spreadsheet App.getActiveSpreadsheet (); Var letter = ss.getSheetByName ("Foglio1"); Var Datenz = Sheet.GetData Range (); Var lastrow = datarange.getLastRow (); Var values ​​= datarange.getValues ​​(); // Get all the data in a 2D array var data currentDate = new date (); Var oneweekago = New Date (); Oneweekago.setDate (currentDate.getDate () - 7); For (i = last; i> = 2; i--) {var tempdate = value [i-1] [2]; // arrays 0 are indexed then line1 = value [0] and col3 = [2] if (tempdate & lt; oneweekago) {sheet.deleteRow (i); }}}    

No comments:

Post a Comment