Parsing timestamp in Google spreadsheet with script

Question

I have a spreadsheet with a form that timestamps every time the form is submitted. I'm writing a script to change the background color of the timestamped cell if it is more than 7 minutes after a certain time. Is there a way to compare time without comparing date in the API? Should I convert the timestamp to another format (a string or something) to compare it? I need to highlight the cell if it's after 6:07pm on any given day.

Answer 1

The following piece of code will set the background color of the entire row:

function onFormSubmit(e) {
  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get the minutes and hours of the timestamp (time[0]) 
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    e.range.setBackgroundColor('yellow');
  } else {
    e.range.setBackgroundColor('red'); 
  }  
}

UPDATE
This code will set only the back ground color of the timestamp:

function onFormSubmit(e) {
  // get active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get row index
  var row = e.range.getRowIndex();

  // get the minutes and hours  
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // set range to timestamp
  var range = sh.setActiveCell("A"+row);

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    range.setBackgroundColor('yellow');
  } else {
    range.setBackgroundColor('red'); 
  }  
}

Add this script, via the script editor, to your spreadsheet and set the trigger:

Here's the form: Test For Web Applications and here's the sheet

Answer 2

Not quite an exact solution form you, but..

You could use a cell formula like this =IF(HOUR(A2)*60+MINUTE(A2) > 1087,"Late","On time"), then right-click on the cell and use Conditional formatting... to color the cells.

• A2 in the formula is the cell where the timestamp is
• 1087 is the value for 18:07 (6:07 pm in 24 hour time) that you get from the above formula of hour * 60 (to give minutes) + minutes.