Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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

share|improve this answer
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
add comment

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.
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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