3

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.

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:
enter image description here

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

  • 1
    Thanks. This was so long ago I hardly remember working on it, but I marked your code as the answer. Better late than never! – Ele Munjeli Jan 8 '13 at 9:30
0

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.
  • I'll try that. I'm kind of considering the script though since in the long run it will allow us to add more fuctionality. – Ele Munjeli Aug 12 '12 at 8:00
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.