Combine a Date & a Time Column in Google Sheets for a createEvent() function

Question

I need to combine two columns into one dateTime column that can be reading by a createEvent function for startDate.

Column F is the date (mm-dd-yyyy) & Column G is the time (HH:mm PM/AM). I am currently combine them in Column H with the following code:

function conCat() {

  var sheet = SpreadsheetApp.getActiveSheet();
  var numberOfRows = sheet.getLastRow().toString();
  var range = sheet.getRange(2,1,numberOfRows,12);
  var values = range.getValues();
  var consultedAlreadyFlag = sheet.getRange(2,10,numberOfRows,12);
  var sheetName = sheet.getSheetName();


  //show updating message
   consultedAlreadyFlag.setFontColor('red');

   var numValues = 0;
   for (var row = 2; row < values.length.toString(); row++) {

   //check to see if name and type are filled out - date is left off because length is "undefined"
  if (values[row][3].length > 0) {
   currentStatus = values[row][1];
  //check if it's been entered before          
  if (values[row][9] != 'EMAIL_SENT'){

      sheet.getRange(row+2,8,1,1).setValue('=F' +(row+2)+ '+G' +(row+2));

      }
      else{
        //sheet.getRange(row+2,10,1,1).setValue('EMAIL_SENT');
      }
     numValues++;
  }   

  //hide updating message
  consultedAlreadyFlag.setFontColor('green');
  } 
}

This code isn't working because when someone submits the form, and the code combines the columns, I cannot get the format to come out as "mm-dd-yyyy HH:mm:ss" which I feel I need in order for my createEvent function to work.

How can I get it to combine the two columns to get the format I need?

Answer 1

I wouldn't bother trying to combine the two columns. There's no point in adding another column, I don't think. You can use JavaScript methods like setHours():

function fncAddToCalender() {
  var theEventDate = //To Do - Get the Date
  var startMin = value from sheet cell

  theEventDate.setHours(15);  //Sets event date to 3pm
  theEventDate.setMinutes(startMin);


  var cal = CalendarApp.getDefaultCalendar();

  var event = cal.createEvent(calndrTitle, theEventDate, endDate, {
    description : theDescrptn,
    location : theLocation,
    guests : guestToInvite,
    sendInvites : true
  });
};

The format in the spreadsheet is probably set to "date". And getting the values with Apps Script will probably return a value that's already in a date format. If you have the columns formatted as text, you'd need to change the values to a date.