CODEWITHSUNDEEP
javascriptdatetimegoogle-apps-scriptgoogle-sheets
ABB1987
ABB1987

Reputation: 145

Delete rows if date-time is before yesterday

I'm quite new to google app scripts, and even javascript.

I have a spreadsheet which logs tweets from twitter.

Col A = Date/Time, Col B = User ID, Col C = Username, Col D = Tweet.

ROW 1 and 2 are headers, tweets start on row 3.

Im trying to delete all rows with date before yesterday (and after yesterday, once I get this to work).

Using the following code:

function deleteBeforeDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Tweetz");

  var DeleteBefore = new Date();
  DeleteBefore.setHours(00,00,00,00);
  DeleteBefore.setDate(DeleteBefore.getDate() - 1);

  var data = sheet.getRange(3,1,s.getLastRow(),4).getValues();
  var m=0;
  for(i=0;i<data.length;i++)
  {
    if (data[i][0] < DeleteBefore)
    {
      sheet.deleteRow(i+2-m); 
      m++;
    }
  }
}

For some reason it deletes everything before around 5:20AM rather than midnight. I Can't get my head around it.

Any help with this would be VERY much appreciated.

THANKS!!!

Upvotes: 1

Views: 375

Answers (2)

ABB1987
ABB1987

Reputation: 145

I got it to work by adding some time to account for 6hrs time zone difference.

WORKING CODE FOR ANYONE IN THE FUTURE:

function DeletePreviousDates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Tweetz");

  var TodaysDate = new Date();
  var TodaysDate2 = TodaysDate;
  TodaysDate2.setHours(00, 00, 00, 001)
 
  var DeleteBefore = new Date(TodaysDate2.getTime() - (29 * 60 * 60 * 1000) + (10)); //Hours, Mins, Secs, Milisecs (10ms after midnight, gmt-6)
    
  var data = sheet.getRange(3,1,sheet.getLastRow(),4).getValues();
  var m=0;
  for(i=0;i<data.length;i++)
  {
    if (data[i][0] <= DeleteBefore)
    {
      sheet.deleteRow(i+3-m); 
      m++;
    }
  }
}

subtracting (29 * 60 * 60 * 1000) + (10));

this is time in ms

Replace 29 with 24 for one day (29 here is due to time zone difference)

+10 is 10ms before midnight.

Upvotes: 1

Cooper
Cooper

Reputation: 64040

Try this:

function deleteBeforeDate() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1");
  const dt = new Date();
  const DeleteBefore = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate() - 1).valueOf();
  const data = sh.getRange(3, 1, sh.getLastRow() - 2, 4).getValues();//start row 3
  let m = 0;
  data.forEach((r, i) => {
    if (new Date(r[0]).valueOf() < DeleteBefore) {
      sh.deleteRow(i + 3 - m++);
    }
  });
}

Check the timezones for your spreadsheet and your script they could be different.

Upvotes: 0

Related Questions