Parsing timestamp in Google Sheets 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 seven 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.

Answer 3

The simplest way may be with Conditional formatting (which need not preclude applying GAS for other purposes). Assuming the time stamps are Date/Time in Number format (Text would require some adaptation to the formula below) and that these are in ColumnA, select ColumnA and apply a Custom formula is of:

=mod(A1,1)>0.7542

with formatting of choice and Done.

MOD

.7542 is an approximation for 6:07 PM, but well within one second.