Working with time values in Google Sheets Apps Script

Google Sheets stores time internally relative to an epoch of 30 Dec 1899 00:00:00 using the time zone set in the spreadsheet, which is retrievable using getSpreadsheetTimeZone.

Google Apps Script uses JavaScript, which supports datetime values using either local time (with no timezone info attached), or datetimes relative to the Unix epoch 1 Jan 1970 00:00:00+0000.

Working with these two time formats using Google Apps Script code is, predictably, a huge hassle. It doesn't help that the JavaScript Date API is carelessly designed.

If you're working with dates or datetimes (values that have both a date and a time) in Google Sheets, you just create the appropriate JavaScript Date object in local time and use it in a call to setValue(s). The problem is when you're trying to work with cells that contain times only, without a date component.

The first thing you need to do when working with time is get the Google Sheets epoch in JavaScript Date (localtime) format.

function getEpoch() {
    return new Date(Utilities.formatDate(
        new Date('30 Dec 1899 00:00:00'),
        SpreadsheetApp.getActive().getSpreadsheetTimeZone(),
        'd MMM yyyy HH:mm:ss Z'));
}

Then to create a time in Google Sheets format, you would get the epoch using this function and add the appropriate number of milliseconds to get to the time you want.

function makeTime(hour, minute, second) {
    return new Date(getEpoch().getTime() +
        (hour * 3600 + minute * 60 + second) * 1000);
}

To convert it the other way, you need to get the millisecond offset from midnight and add it to an arbitrary date, because JavaScript Dates must included a date component. I'm using the Unix epoch in local time.

function getTime(v) {
    var u = new Date(v.getTime() - getEpoch().getTime());
    return new Date(1970, 0, 1, u.getUTCHours(), u.getUTCMinutes(),
                    u.getUTCSeconds(), u.getUTCMilliseconds());
}