Cannot get date from google sheet filled by google forms

Question

Purpose: To extract date and time from a sheet (created by filled google forms) and use them in the body of email to be set automatically using the script. Script editor code:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 16;  // First row of data to process
  var numRows = 1;   // Number of rows to process

  for( var j=startRow; j<startRow + numRows; j++)
  {
    var color = sheet.getRange(j, 10).getBackground();
    if("#00ff00"==color)
    {
      // Fetch the range of cells A2:B3
      var row = sheet.getSheetValues(j, 1, 1, 14); //extract j th row till 14/Nth col
      var nameStudent = row[0][0] +" "+row[0][1];
      var time = row[0][6]+" on ";// +row[0][7]+" at "+row[0][8];
      var nameRecipient="Dear "+row[0][9]+",";
      var emailRecipient=row[0][13];
      var subject= "Date and time  "+row[0][5]+" at "+row[0][6];

      var message=nameRecipient+sheet.getRange("L11").getValue()+nameStudent+sheet.getRange("M11").getValue()+time+nameStudent+sheet.getRange("N11").getValue();

      MailApp.sendEmail(emailRecipient, subject, message);
      sheet.getRange(j,10).setBackground("#ff9900");
    }
  }
}

In output (i.e. email):

The date is entered by person filling google form. I'm accessing the google sheet created using the forms. Screenshot of the sheet: https://drive.google.com/file/d/0B2hiq6P5ceD-aUZQTDBVUXJYbEk/edit?usp=sharing The cell (when double clicked) opens a calender shown in the above linked screenshot. I use the following code (Using Google Apps Script) to get a row from the sheet:

var row = sheet.getSheetValues(j, 1, 1, 14); //extract j th row till 14/Nth col

In the debugger the date looks like:

[["Gaaav", "Budapest","[email protected]", 9823527827, "San Francisco International Airport(SFO)", 41855, 0.5277777777810115,"Si016", "Al02", "Yaar", "pr", "3112", 94984802569, "[email protected]"]]

In the above statement the number 41855 is the date. It actually is 8/4/2014 . But I can't read it . Neither can I read the time, which in the above debug o/p, is 0.5277777777810115.

I'm attaching a screenshot of the debug screen:

https://drive.google.com/file/d/0B2hiq6P5ceD-aFlDd2g4SlM5MW8/edit?usp=sharing

Answer 1

The values you see in your spreadsheet are dates without formating, you can change that easily in the spreadsheet UI itself by choosing the appropriate format: date for column with 41855 and it will become 4-Aug-2014 and time in the column with 0.5277777777810115 that will show 12:40:00 .

These number are the native values of dates in spreadsheet. days are counted from december 30 1899, if you add 41855 days to this reference you'll get the 4 of august 2014 ! as simple as that ;-)

Time is a decimal representation of the hour-minute-sec (percentage of a 24 hours day which is here 12.666666667 hours in decimal, which corresponds indeed to 12:40:00 :-) so that it can be added to the integer part and give a complete date information.

Once you'll have the format set correctly in the SS the script will read it as date objects., I'd suggest you combine both in a single cell to make the script simpler : create a new column with =C2+D2 if C and D are the columns for example.

see illustration below :

---

EDIT

Following your comment, I ran a test on a copy of your SS using this code :

function getDatesAndTime() {
  var sh = SpreadsheetApp.getActive().getSheets()[1];
  var dates = sh.getRange('F2:F').getValues();
  var hours = sh.getRange('G2:G').getValues();
  var complete = sh.getRange('H2:H').getValues();
  Logger.log(dates[0][0]);
  Logger.log(hours[0][0]);
  Logger.log(complete[0][0]);
}

I set the display format of the SS to date for column F, Time for column G and date+time for column H in which I used a formula =F2+G2

This can be done with script as well like this (insert right before the getValues()):

  sh.getRange('F2:F').setNumberFormat('MM dd yyyy');
  sh.getRange('G2:G').setNumberFormat('HH:mm');
  sh.getRange('H2:H').setNumberFormat('MM dd yyyy HH:mm');

the sheet with modification is here (view only, make a copy to edit) and below is the logger result

Note :

be sure to check that Time zone settings are the same in spreadsheet AND in script (pacific time in your test sheet and in mine !) The hour difference you see in the logger comes from a problem with daylight savings... it is fully explained in this other post

Answer 2

Assuming your time submission is row 14 in your spreadsheet.

convert both the date and time values in to Javascript date objects, then update the date object from the form with the hours and minutes from the time object from the form.

 function sendEmails() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var startRow = 16;  // First row of data to process
   var numRows = 1;   // Number of rows to process

   for( var j=startRow; j<startRow + numRows; j++)
   {
     var color = sheet.getRange(j, 10).getBackground();
     if("#00ff00"==color)
     {
       // Fetch the range of cells A2:B3
       var row = sheet.getSheetValues(j, 1, 1, 14); //extract j th row till 14/Nth col
       var nameStudent = row[0][0] +" "+row[0][1];
       var time = row[0][6]+" on ";// +row[0][7]+" at "+row[0][8];
       var nameRecipient="Dear "+row[0][9]+",";
       var emailRecipient=row[0][13];

       // build out the date time object
        var dateInput = new Date(row[0][5]);
       var timeInput = new Date(row[0][6]);
       dateInput.setHours(timeInput.getHours())
       dateInput.setMinutes(timeInput.getMinutes());
       // format the date/time object into a string
       var dateString = Utilities.formatDate(dateInput, Session.getScriptTimeZone(), "MM/dd/yyyy hh:mm a");

  var subject= "Date and time  "+ dateString

  var message=nameRecipient+sheet.getRange("L11").getValue()+nameStudent+sheet.getRange("M11").getValue()+time+nameStudent+sheet.getRange("N11").getValue();

  MailApp.sendEmail(emailRecipient, subject, message);
  sheet.getRange(j,10).setBackground("#ff9900");
}

} }