Proper way to convert dates to unix timestamp in Google Apps Script

Question

I have a column with dates and needed to convert them to Unix timestamp. I've managed by converting dates to strings and doing a bit of juggling but was hoping to get insight into a more "elegant" use of GAS date handling and formating. Below is the function I used to get the job done:

/** FUNCTION to convert date column to Unix timestamp
 *
 */
function getUnixTime() {
  //Set date range to iterate on.
  var sheet = SpreadsheetApp.getActiveSheet();
  var dateRange = sheet.getRange(1, 1, sheet.getLastRow(), 1);

  //Set date format IMPORTANT values will be picked up as displayed.
  dateRange.setNumberFormat('D/M/YYYY');
  var dates = dateRange.getDisplayValues();

  //Set range that will hold unixTime values.
  var unixTime = dateRange.offset(0, 1, sheet.getLastRow(), 1).getValues();

  //Check date and (empty) unixTime value ranges.
  Logger.log(dates);
  Logger.log(unixTime);

  //Iterate through date and convert to unixTime values.
  for (var i = 0; i < dates.length; i++) {

    //Set variables to iterate on.
    var cellDate = dates[i][0];
    var firstIndexOfSlash = dates[i][0].indexOf('/');
    var lastIndexOfSlash = dates[i][0].lastIndexOf('/');

    //Set day, month, year holders.
    var day = cellDate.slice(0, firstIndexOfSlash);
    var month = cellDate.slice(firstIndexOfSlash + 1, lastIndexOfSlash);
    var year = cellDate.slice(lastIndexOfSlash + 1);

    //Set dates to be compared.
    var fromTime = new Date(Number(year),Number(month) - 1,Number(day),9,0,0).getTime();
    var unixEpoch = new Date(1970,0,1,0,0,0).getTime();
    unixTime[i][0] = ((fromTime - unixEpoch) / 1000).toString();

  }

//Check unixTime values are correct.
Logger.log(unixTime);

var unixRange = dateRange.offset(0, 1, sheet.getLastRow(), 1).setValues(unixTime);

}

question from:https://stackoverflow.com/questions/65842003/proper-way-to-convert-dates-to-unix-timestamp-in-google-apps-script

Answer 1

You can just use getTime()

Bear in mind that if the date is formatted as a date in the spreadsheet, when Apps Script reads the value it will automatically behave as a date object.

Further, Apps Script just uses the built-in JavaScript Date object, so you can use any methods found here:

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date

I made a sample spreadsheet with dates in range A1:A3 with this script:

function convertToUTC() {
  let file = SpreadsheetApp.getActive();
  let sheet = file.getSheetByName("Sheet1");
  let range = sheet.getRange("A1:A3");
  let values = range.getValues();

  utcTimes = values.map(row => {
    let date = row[0]
    return [date.getTime() / 1000] // from ms to s
  })

  let outRange = sheet.getRange("B1:B3")
  outRange.setValues(utcTimes)
}

Will give the times in unix UTC (seconds)

This also uses map as a nice and concise way to transform one array into another.

• getTime
• map