Extracting just the time for a new date

问题:

I'm having major headaches trying to work with dates and times and it's consuming hours of my programming time, so any help much appreciated.

What I'm trying to do is combine a separate date and time; It's easy enough to separate out the year and month. But when it comes to the time, it outputs:

Sat Dec 30 1899 14:30:00 GMT+1300 (NZDT)

The form field looks like

and the time displays correctly

on the sheet.

How can I get the desired time of 13:00 from this, aside from formatting the entire column as a string (which will break other code in the project)?

回答1:

I've figured out a rudimentary solution after much experimentation. I expect if Google update how times are added from Forms it'll break though. What I've done is taken the millisecond value of a time of 00:00:00 and taken the difference with a given time, then used some simple math to get the hour and minute out of that.

function extractTime(date) {

  var milliseconds =  date.getTime()[phone];
  var time = {};
  time.hours = Math.floor(milliseconds/(1000*60*60));
  time.minutes = Math.round((milliseconds/(1000*60*60)-time.hours)*60);

  return time;

}

Have tested and it seems to work.

回答2:

An alternate method:

function getTimeFromDate(date) {
  return Utilities.formatDate(new Date(), "NZDT", "hh:mm")
}

回答3:

The simplest solution is to use getDisplayValue() to get the cell displayed value as you don't have to worry about the the differences on how Google Sheets and Google Apps Script / JavaScript handle datetime values and the potentially different timezone setting on Google Sheets spreadsheet and the Google Apps Script project.

回答4:

I was encountering the same issue but looking at Amit's post made me realize that the best way to ensure that this will work all the time is by only using Google's settings. Therefore I came up with the following solution which works no matter what the timezone of the spreadsheet is (which is actually the source of the issue):

/**
 * Formats a JSON date string or a date object using the spreadsheet's timezone
 * or the timezone you specify.  Can also be found here:
 * https://gist.github.com/westc/3a90b6ad29bbd96c98603048255f8fcc
 * @param {Date|string} date
 *   The date object or the string that should be formatted as a date string.
 * @param {string=} opt_format
 *   Optional. The format of the date as it should be returned.  If not
 *   specified the format will be "yyyy-MM-dd'T'HH:mm:ss'Z'".
 * @param {string=} opt_timeZone
 *   Optional.  Timezone to use to format the date.  Defaults to the
 *   spreadsheet's time zone.
 * @return {string}
 *   The formatted version of date as a string.
 */
function formatDate(date, opt_format, opt_timeZone) {
  return Utilities.formatDate(
    new Date(date),
    opt_timeZone || SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(),
    opt_format || "yyyy-MM-dd'T'HH:mm:ss'Z'"
  );
}

To use this on a value found in a sheet you can just use it as follows:

Logger.log(formatDate(SpreadsheetApp.getActive().getActiveCell().getValue(), "H:mm"));

After doing this I was able to find the time value that is in the currently selected cell.