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?

score 1 · Answer 1 · answered Jun 09 '15 at 01:47

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.

answered Jun 09 '15 at 01:47

Alan Wells

30,746
15
104
152

Thanks! That gave me a great starting point. One follow up: Column G for time is populated by a form that is filled out by users. The times they can select are limited, but always are in the format of HH:mm AM/PM. How does the setHours() and setMinutes() to understand the value of this string? – WhoLsJohnGalt Jun 09 '15 at 03:36
`setHours()` and `setMinutes()` will only take numbers. If the value in the spreadsheet cell is text, you could use a string method `slice()` to extract part of the string. You could extract both the time, and the "AM", "PM", and if it's "PM" add 12 to the number. Or concatenate the date and time, and use `new Date()` to create a new date type variable. Do a search on "W3" and "JavaScript". w3 javascript to get some information on dates and string methods. – Alan Wells Jun 09 '15 at 04:29
For reference commented from @Andres Duarte answer here: https://stackoverflow.com/a/57285206/10789707 – Lod Jul 19 '23 at 13:16

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

1 Answers1