Friday, February 23, 2024
rated 0 times [  186] [ 6]  / answers: 1 / hits: 15544  / 10 Years ago, wed, may 7, 2014, 12:00:00

I have a script that imports events from spreadsheets into calendar:

function caltest1() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 3; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 5);
var data = dataRange.getValues();
var cal = CalendarApp.getDefaultCalendar();
for (i in data) {
var row = data[i];
var title = row[0]; // First column
var desc = row[1]; // Second column
var tstart = row[2];
var tstop = row[3];
var loc = row[4];
//cal.createEvent(title, new Date(March 3, 2010 08:00:00), new Date(March 3, 2010 09:00:00), {description:desc,location:loc});
cal.createEvent(title, tstart, tstop, {description:desc,location:loc});

The script works fine if my spreadsheet contains the data like:

Title      Description    Start               Stop                  Channel
Mr Dear no drinks 5/6/2014 20:55:00 5/6/2014 21:57:00 what ever

But it does not work if I create my own date =CONCATENATE($D4, ,$G4), given that D4 has a date and G4 has time combined into a single cell Date and time. I figured because it senses that concatenate creates a plain text and not a time formatting, but how can I fix it?

More From » google-apps-script


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 .

So the answer (as you noticed it in your comment on the other answer) is logically to ADD both values. That's actually the reason spreadsheets are build like that ! (to make it easy to use date and time)

Use the formula =D2+E2 in a new column and you get a complete date object directly useable in JavaScript.

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).

[#71145] Monday, May 5, 2014, 10 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.

Total Points: 645
Total Questions: 100
Total Answers: 118

Location: Tanzania
Member since Wed, Feb 24, 2021
3 Years ago