Join Date and time for events from sheet to calendar [duplicate]

Question

With this code I can send the events directly to google calendar from google sheets. The start date and end date are on two different columns (col D and col F), the problem is that I need also the time that are on others two columns (col E and col G). How could I join startDate with date+time and endDate with date+time?

function sendToCalendar() {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('XCALENDAR')
  var calendarID = spreadsheet.getRange("O1").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarID);
  var signups = spreadsheet.getRange("A5:P5").getValues(); 
  
  for (x=0; x<signups.length;x++)
  {
    var shift = signups[x];
    var startTime = shift[3];
    var endTime = shift[5];
    var nameevent= shift[1];
    var desc= shift[13];
    var color = shift[15];
    var event = eventCal.createEvent(nameevent, startTime, endTime,{description:desc});

    if(color){
      event.setColor(CalendarApp.EventColor[color]);
    }
  }
}

Answer 1

To concatenate date (d) and time (t), you can do it this way

new Date(d.getFullYear(), d.getMonth(), d.getDate(), t.getHours(), t.getMinutes(), t.getSeconds())

try with

function createEvent() { 
  // A = title, B = description, C = location, D = begin .. E = at, F = end ... G = at
  const myCalend = CalendarApp.getCalendarById("[email protected]");
  const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  const [headers, ...data] = sh.getRange('A1:H' + sh.getLastRow()).getValues()
  const colID = 8; // H
  data.forEach((r, index) => {
    if (r[colID - 1] == '') {
      let [title, desc, loc] = [r[0], r[1], r[2]]
      let [bd, bh, ed, eh] = [r[3], r[4], r[5], r[6]]
      let id = (myCalend.createEvent(
        title,
        new Date(bd.getFullYear(), bd.getMonth(), bd.getDate(), bh.getHours(), bh.getMinutes(), bh.getSeconds()),
        new Date(ed.getFullYear(), ed.getMonth(), ed.getDate(), eh.getHours(), eh.getMinutes(), eh.getSeconds()),
        {
          description: desc,
          location: loc
        }).getId())
      sh.getRange(index + 2, colID).setValue(id)
    }
  })
}

edit

according to your spreadsheet, and assuming that dates are as dd/MM/yyyy, you can use (the eventID will be stored in column M=13 and prevent duplicating event)

function sendToCal() {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('Foglio1')
  let eventCal = CalendarApp.getCalendarById("[email protected]");
  var signups = spreadsheet.getRange("A2:M" + spreadsheet.getLastRow()).getDisplayValues();
  var col = 13; // M
  for (x = 0; x < signups.length; x++) {
    var shift = signups[x];
    if (shift[(col - 1)] == '') {
      let [d, e, f, g] = [shift[3].split("/"), shift[4].split(":"), shift[5].split("/"), shift[6].split(":")]
      let [nameevent, desc, color] = [shift[1], shift[11], shift[12]]
      var startTime = new Date(parseInt(d[2]), parseInt(d[1])-1, parseInt(d[0]), parseInt(e[0]), parseInt(e[1]), 0)
      var endTime = new Date(parseInt(f[2]), parseInt(f[1])-1, parseInt(f[0]), parseInt(g[0]), parseInt(g[1]), 0)
      var event = eventCal.createEvent(nameevent, startTime, endTime, { description: desc });
      spreadsheet.getRange(+x + 2, col).setValue(event.getId())
      if (color) {
        event.setColor(CalendarApp.EventColor[color]);
      }
    }
  }
}

Answer 2

Current Date and Time

function currentdatetime() {
  const second = 1000;
  const minute = 60 * second;
  const hour = minute * 60;
  const day = hour * 24;
  const dt = new Date();
  const dtv = dt.valueOf();
  const td = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate())
  const dv = td.valueOf();
  const d = dtv - dv;
  let hours = Math.floor(d % day / hour);
  let minutes = Math.floor(d % day % hour / minute);
  let seconds = Math.floor(d % day % hour % minute / second);
  Logger.log(`Date: ${td.getMonth() + 1}/${td.getDate()}/${td.getFullYear()} - Time:${hours}:${minutes}:${seconds}`)
}

Execution log
9:05:08 AM  Notice  Execution started
9:05:09 AM  Info    Date: 5/4/2022 - Time:9:5:9
9:05:09 AM  Notice  Execution completed

A simpler way

function currentdatetime() {
  const dt = new Date();
  Logger.log(`Date: ${dt.getMonth() + 1}/${dt.getDate()}/${dt.getFullYear()} - Time:${dt.getHours()}:${dt.getMinutes()}:${dt.getSeconds()}`)
}

Execution log
9:11:19 AM  Notice  Execution started
9:11:20 AM  Info    Date: 5/4/2022 - Time:9:11:20
9:11:20 AM  Notice  Execution completed

Super simple way

function currentdatetime() {
  Logger.log(Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy - HH:mm:ss"))
}

Execution log
9:16:22 AM  Notice  Execution started
9:16:23 AM  Info    05/04/2022 - 09:16:23
9:16:23 AM  Notice  Execution completed

Joining date and time

function currentdatetime(M=5,d=4,y=2022,H=9,m=28,s=0) {
  const dt = new Date(y,M-1,d,H,m,s);
  Logger.log(Utilities.formatDate(dt,Session.getScriptTimeZone(),"MM/dd/yyyy HH:mm:ss"));
  return dt;
}

Execution log
9:29:38 AM  Notice  Execution started
9:29:39 AM  Info    05/04/2022 09:28:00
9:29:39 AM  Notice  Execution completed