Convert Google Sheet date to its number format before importing to Apps Script

0
votes

I needed a script that will delete rows from a Google sheet if their timestamp goes past the inactivity date, which is 31 days past from today's date. I found a script that could do this and just modified it a little to fit my needs. It didn't work. I spent 30 minutes trying to figure out what isn't working and finally realized through logs, that when Apps Script pulls the date from the sheet, it pulls this:

Mon Aug 24 06:33:33 GMT+01:00 2020

instead of the number format of dates for Google Sheet. I need a way to convert that date to the number format. This is the script that I was working on:

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW - Roll Call');
  var values = sheet.getDataRange().getValues();
  var today = Date.now()/86400000 + 25569;
  var inactivityDate = today-31;
  for (var i = values.length - 1; i >= 0;  i--) {
    if (values[i][0] < inactivityDate) {
      sheet.deleteRow(i+1);
    }
  }
}
2
javascriptgoogle-apps-scriptgoogle-sheets
Can you share your sheet? – Rafa Guillermo
I don't know how to mark a question as closed but I've already taken care of it with the help from Krzysztof's answer. Converting it into the timestamp that Javascript uses meant that I could then convert that timestamp to the date system that Google Sheet uses – John Aaron Marasigan

2 Answers

0
votes

This is a pretty simple problem, taking into account that the Date constructor already supports most of the common date formats.

Converting your date to the timestamp is as simple as passing it as an argument to it.

function dateToTimestamp(date) {
  return new Date(date).getTime();
}

dateToTimestamp("Mon Aug 24 06:33:33 GMT+01:00 2020")

It will return a timestamp of 1598247213000

0
votes

If the Date column in the Google Sheet is of type Date, the getValues() method will return the column value as date object.

function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RAW - Roll Call');
  var values = sheet.getDataRange().getValues();
  var today = new Date();
  var today = Date.now()/86400000 + 25569;
  var inactivityDate = today-31;
  for (var i = values.length - 1; i >= 0;  i--) {
    var diffInDays = (today - values[i][0])/(1000*60*60*24);
    if (diffInDays < 31) {
      sheet.deleteRow(i+1);
    }
  }
}