Conditional formatting using other cells in Google Spreadsheet -

i'm trying produce script changes cell colour depending on contents of other cells in row.

basically, i'd change cell (col 6) red if date (col 1) of entry further 2 days prior , number cell less 1. if third column (col 5) changes y, format cell green.

i'm having trouble getrange on line 5 returning null, wanted check doing correctly. thanks!

function formatting() {   var  = new date().gettime();   var twodaysinmilliseconds = 172800000;    var sheet = spreadsheetapp.getactivespreadsheet().getsheetbyname('enquiry tracking');   var columnbooked = sheet.getrange(2, 5, sheet.getlastrow()-1, 1);   var bvalues = columnbooked.getvalues();    (var = 0; < bvalues.length; i++) {     var columnfu = sheet.getrange(i + 2, 6, 1, 1);     if (bvalues[i][0] != 'y')  {       var rowdate = new date(sheet.getrange(i + 2, 1, 1, 1).getvalue()).gettime();        if (now - rowdate > twodaysinmilliseconds) , (columnfu.getvalue() < 1)       {           columnfu.setbackgroundcolor('red');       }      }     else {       columnfu.setbackgroundcolor('green');     }   } } 

you're on track. error you're getting .getrange() due invalid values rows or columns. avoid sort of problem, instead read entire spreadsheet's contents array, relying on .getdatarange().getvalues(). once that's done, can access values column within resulting array.

some other comments:

• spreadsheet rows & columns start numbering @ 1, while javascript arrays start @ 0. tricky, it's best avoid using numbers in code as possible. instead, use variables. in example, array indices calculated subtracting 1 spreadsheet column numbers.
• when read spreadsheet using getvalue() or getvalues(), cells contain dates appear javascript date objects, e.g. "1/1/2013 16:54:00" (new date(1357077240000)), if check debugger. therefore, don't need mess around new date(...) in code.
• the and operator in javascript &&. (or ||.)
• setbackgroundcolor() has been deprecated, use setbackground(color) instead. if have range of colors set (as do), script more efficient using setbackgrounds() once, accepts two-dimensional array of colors.
• another efficiency improvement can made re-arranging various if statements. (the idea reduce computational investment. in case of slow operations many of google api calls, thinking can save script timeouts. in example, .gettime() function quick, it's not necessary refactor code.)

updated code

function formatting() {   var  = new date().gettime();   var twodaysinmilliseconds = 2 * 24 * 60 * 60 * 1000; //172800000;   var headers = 1; // # rows of headers skip    var sheet = spreadsheetapp.getactivespreadsheet().getsheetbyname('enquiry tracking');   var data = sheet.getdatarange().getvalues().splice(headers); // data, skipping headers   var colbooked = 5-1;  // column numbers -1 map 0-based array   var coldate = 1-1;   var colfu = 6-1;   var colors = [];    (var row = 0; row < data.length; row++) {     colors[row] = ['transparent']; // default - no color     if (data[row][colbooked] === 'y')  {       colors[row][0] = 'lawngreen';     }     else {       if (data[row][colfu] < 1) {         var rowdate = (data[row][coldate]).gettime();          if (now - rowdate > twodaysinmilliseconds)         {             colors[row][0] = 'tomato';         }       }     }   }   // set colors data cells in colfu   sheet.getrange(1+headers,colfu+1,colors.length,colors[0].length).setbackgrounds(colors); }