I have a set of date time data that is drawn from a database. the problem with it is that the data is separated into two columns, one for date and one for time.
I am trying to write a google apps script that can combine the two of them so I can do some meaningful analysis but to no avail.
I tried the common getDate(), getYear(), getMonth() or concatetating them but it doesn't work.
Here is the link to the spreadsheet.
https://docs.google.com/spreadsheets/d/1VjhT7Hu8rl1wZgFxItf3unU8jN9Gw3905dNgQ9qICyI/edit#gid=0
If any one knows how to make this data more manageable please do let me know?
Thanks!
You could use parse in JavaScript to get the number of milliseconds for the date, then add the milliseconds in time to it:
First create a new column to convert the time to a different value:
=TIMEVALUE(C2)
4:00:00 PM 0.6666666667
11:44:00 PM 0.9888888889
4:51:00 AM 0.2020833333
8:42:04 AM 0.3625462963
7:15:00 AM 0.3020833333
11:46:33 AM 0.4906597222
12:23:00 PM 0.5159722222
5:28:00 PM 0.7277777778
5:37:00 PM 0.7340277778
12:52:00 AM 0.03611111111
6:00:00 AM 0.25
11:49:00 AM 0.4923611111
Then run a script to convert the date to milliseconds
var milliSkonds = Date.parse("3/21/2012");
Logger.log('milliSkonds :' + milliSkonds);
//This returns 1332302400000
Get the decimal values of the column with the time values. then add the milliseconds and time values together.
1332302400000.6666666667
That represents 4PM on 3/21/2012
I'm not showing how to retrieve the data out of the spreadsheet with Apps Script, or looping through each row. This is just a strategy I'm suggesting.
The other answer is not correct. It mixes 2 different things.
In Javascript date and time are the same objects, there is no time object that has no date and no date without time : their native values are milliseconds counted from January 1 1970 at midnight (which is an integer).
In spreadsheets, dates have a native value of an integer representing the number of days since december 31 1899 and time is a decimal value which is the fraction of a day ( 6 hours = 1/4 of a day for example , 0.25 day).
So when you add DATE+TIME (integer+decimal) in a spreadsheet you get a full date with time .
But this is not the case in JavaScript of course and you cannot (obviously) add milliseconds and decimal values to get a valid result as there are no decimal values in JS dates.
For full details about dates I'd suggest to search the web and have a look at the MDN date page
Concerning your specific issue, you can run a small function like below to force the format in your spreadsheet (although you could make it also manually using the 123 menu)
function myFunction() {
var sh = SpreadsheetApp.getActive().getActiveSheet();
sh.getRange('A2:A').setNumberFormat('MMM/dd/yyyy');
sh.getRange('B2:B').setNumberFormat('hh:mm:ss');
}
and use a third column in your SS to simply add column A and B, you will then get a complete date value (with time, the display format should be automatically right)
If you can't use that simple solution then the combination of date and time will be a bit more complex, have a look at this recent post for some suggestions.
Below is a code that converts all your date and time to JS date objects.
function getFullDates(){
var sh = SpreadsheetApp.getActive().getActiveSheet();
var data = sh.getDataRange().getValues();
data.shift();
for(var n in data){
var time = new Date(data[n][1]);
var date = new Date(data[n][0]);
var hrs = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'HH'));
var min = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'mm'));
var sec = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'ss'));
Logger.log('date = '+Utilities.formatDate(date, Session.getScriptTimeZone() ,'dd-MM-yyyy HH:mm:ss'));
Logger.log('time = '+hrs+':'+min+':'+sec);
var dateAndTime = new Date(date).setHours(hrs,min,sec,0);
Logger.log('full date object = '+Utilities.formatDate(new Date(dateAndTime), Session.getScriptTimeZone() ,'dd-MM-yyyy HH:mm:ss'))
}
}
NOTE : be sure to check your SS TimeZone and script TimeZone as well, it seems that you are in GMT+8 China, I tested with these values.
Note 2 : I had to use a small trick in the code above to get hours/minutes and seconds because the normal getHours(), getMinutes() getSeconds() return wrong results. This is a problem that has already been discussed here but I can't remember exactly when and I didn't find the post reference right now...
I show the code for info only, feel free to test to see the issue ;-).
function getFullDatesBug(){
var sh = SpreadsheetApp.getActive().getActiveSheet();
var data = sh.getDataRange().getValues();
data.shift();
for(var n in data){
var time = new Date(data[n][1]);
var date = new Date(data[n][0]);
var hrs = time.getHours();
var min = time.getMinutes();
var sec = time.getSeconds();
Logger.log('date = '+Utilities.formatDate(date, Session.getScriptTimeZone() ,'dd-MM-yyyy HH:mm:ss'));
Logger.log('time = '+hrs+':'+min+':'+sec);
var dateAndTime = new Date(date).setHours(hrs,min,sec,0);
Logger.log('full date object = '+Utilities.formatDate(new Date(dateAndTime), Session.getScriptTimeZone() ,'dd-MM-yyyy HH:mm:ss'))
}
}
This might be an old question, but maybe my workaround might be usefull for somebody nevertheless.
I had similar; trying to compose a startTime and an endTime on one single date in order to populate options in a form.
Getting data from an active selection in a spreadsheet:
var firstRowNumber = sheet.getActiveRange().getRow();
var numRows = sheet.getActiveRange().getNumRows();
var sourceData= sheet.getRange(firstRowNumber, 1, numRows, sheet.getLastColumn()).getValues();
Then, iterating through the (two) time-values in this dataset and replace the value like:
Sat Dec 30 1899 10:00:00 GMT+0100 (CET)
into the desired representation of time like:
0:00
for(var i = 0; i < sourceData.length; i++){
for(var j = 8; j < 10; j++){
sourceData[i][j] = Utilities.formatDate(new Date(sourceData[i][j]), "GMT-8", "HH:mm");
}
}
In order to find out the exact deviation of GMT, I used some trial-and-error values to GMT+. This might cause a problem in different timezones, or with changes of daylight saving time.
On three active rows, this results in usable start- and ending date/time-values:
Fri Dec 30 2016 09:00:00 GMT+0100 (CET) 00:00 Fri Dec 30 2016 09:00:00 GMT+0100 (CET) 01:00
Sat Dec 31 2016 09:00:00 GMT+0100 (CET) 01:30 Sat Dec 31 2016 09:00:00 GMT+0100 (CET) 02:15
Thu Dec 29 2016 09:00:00 GMT+0100 (CET) 01:30 Thu Dec 29 2016 09:00:00 GMT+0100 (CET) 02:15
with which I can populate the options in my form.
