Check for cell updates Google Spreadsheet

Question

I'm working on a Google spreadsheet that is edited by multiple people. It's to track various member stats, so they each only update one row. What I am trying to do is check when a cell in the row is updated, and change the background of another cell in that row depending on the date of the update.

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "HT ~ Stats" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 7 ) { //checks for the correct column
     var dateCell = r.offset(0,21);
     //var date = new Date()
     var date = Utilities.formatDate(new Date(), "GMT", "MM-dd-yyyy")

     dateCell.setValue(date); //sets column AB of selected row to the date of the update

 };
}

It's at this point where I'm getting stuck. What I want to happen after this point is for the value of the var date to get compared to the current date. Once it gets compared, if the result is within 3 days of the current date, I want the first cell in the selected row to have it's background changed to green.

This is my first time ever doing anything in Google Scripts, so I'm sure there is any easier or better way to do this. Thanks for any help :)

dev dev · Accepted Answer · 2013-04-14T13:49:57

I haven't tested the below but it's something that might get you on the right track. It basically does what your's does above, but then on the second edit it gets the original date, converts it to a integer, gets a new date integer, works out the difference, and if it's greater than 3 days (in milli seconds) then sets the first cells background colour.

It may need some tweaking but I hope it helps.

 function onEdit() {
  //G ets current spreadsheet
  var s = SpreadsheetApp.getActiveSheet();
  // If on the right spreadsheet
  if( s.getName() == "HT ~ Stats" ) { 
    // Gets the active cell
    var r = s.getActiveCell();
    // If on the right column
    if( r.getColumn() == 7 ) {
      // Sets the AB column
      var dateCell = r.offset(0,21);
      // Retrieves the currentDate
      var currentDate = dateCell.getValue().getTime();
      // Creates a new date
      var date = new Date();
      // Adds the date to the AB cell
      dateCell.setValue(date);
      // Creates a date integer
      var dateInt = date.getTime();
      // Works out the difference of the current date and new date
      var difference = dateInt - currentDate;
      // Creates a 3 day integer
      var threeDays = 259200000;
      // If the difference is greater than 3 days. 
      if(difference >= threeDays)
      {
        //Set the first cell background colour.
        r.offset(0,-6).setBackgroundColor("#D3E375");
      }
    }
  }
}

Check for cell updates Google Spreadsheet

1 Answers