Google Spreadsheet Set script time driven

Question:

( by Nicole Beggs )

I currently have a script which will look at 2 criteria, it will look at the 'status of one column, and has an array of different 'statuses' it could be. This part of the formula I have working perfectly, it is when I try adding another type of rule where I am having issues.

In the spreadsheet itself I have a column which will work out how many days it has been since contact was last made, this is inputted manually, the script needs to highlight the first 6 cells of the row Red if this number is more than 7. This also works fine until the date ticks over because its going onto the eighth day, caused by the DAYS360 formula, I have been looking around on the google product forums and also on here and can't quite find a solution that meets my requirements. I have also been trying to use time-driven triggers to no avail, I have also tried using an onOpen script however this changed the colour regardless of how many days its been

This is my script I currently have.

function onEdit(e) {
  if (e) {
    var ss = e.source.getActiveSheet();
    var r = e.source.getActiveRange(); 

        status = ss.getRange(r.getRow(), 5).getValue();
        date = ss.getRange(r.getRow(), 4).getValue();


        rowRange = ss.getRange(r.getRow(),1,1);

        // This changes font color
      if (status == 'Completed: Details Confirmed & Processed') {
            rowRange.setBackgroundColor("#2EFE2E");
        } else if (status == 'Outstanding: Contact Made Awaiting Docs') {
            rowRange.setBackgroundColor("#FFFF00");
        } else if (status == 'Outstanding: Contact NOT Made') {
            rowRange.setBackgroundColor("#FF0000");
        } if (date > '7' ) { 
            rowRange.setBackgroundColor("#FF0000");  

        }   
    }
}

Solution:

Note, time driven trigger when executes, it requires sheet's name as there might be no active sheet when the time event occurs.

So along with your code also put the following code in your script:

///////////////////////////////////////

function checkDate() {  
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var values = s.getDataRange().getValues();
  
  for( var i=0;i<values.length;i++)
  {
    if (values[i][4] == 'Completed: Details Confirmed & Processed') 
      s.getRange(i+1,1,1,5).setBackgroundColor("#2EFE2E");
    else if (values[i][4] == 'Outstanding: Contact Made Awaiting Docs') 
      s.getRange(i+1,1,1,5).setBackgroundColor("#FFFF00");
    else if (values[i][4] == 'Outstanding: Contact NOT Made') 
      s.getRange(i+1,1,1,5).setBackgroundColor("#FF0000");
    if(values[i][3]>7)
      s.getRange(i+1,1,1,5).setBackgroundColor("#FF0000");
  }

}

///////////////////////////////////////

put the above code in your script editor, and then set the time driven (may be of daily or whatever you wish) trigger on it.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive