CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user2662243
user2662243

Reputation: 1

Conditional formatting using other cells in Google Spreadsheet

I'm trying to produce a script that changes cell colour depending on the contents of other cells in the row.

Basically, I'd like to change the cell (col 6) to red if the date (col 1) of the entry is further than two days prior AND the number that cell is less than 1. But if a third column (col 5) changes to Y, format the cell as green.

I'm having trouble with getRange on line 5 returning null, but wanted to check i was doing this correctly. Thanks!

function formatting() {
  var now  = 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();

  for (var i = 0; i < 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) AND (columnFU.getValue() < 1)
      { 
         columnFU.setBackgroundColor('red');
      }

    }
    else {
      columnFU.setBackgroundColor('green');
    }
  }
}

Upvotes: 0

Views: 2134

Answers (1)

Mogsdad
Mogsdad

Reputation: 45710

You're on track. The error you're getting with .getRange() is probably due to invalid values for rows or columns. To avoid this sort of problem, you could instead read the entire spreadsheet's contents into an array, relying on .getDataRange().getValues(). Once that's done, you can access values for any column within the resulting array.

Some other comments:

  • Spreadsheet rows & columns start numbering at 1, while Javascript arrays start at 0. This is always tricky, so it's best to avoid using the numbers in your code as much as possible. Instead, use variables. In this example, the array indices are calculated by subtracting 1 from the spreadsheet column numbers.
  • When read from a spreadsheet using getValue() or getValues(), cells that contain dates appear as javascript date objects, e.g. "1/1/2013 16:54:00" is (new Date(1357077240000)), if you check the debugger. Therefore, you don't need to mess around with new Date(...) in your code.
  • The AND operator in Javascript is &&. (OR is ||.)
  • setBackgroundColor() has been deprecated, use setBackground(color) instead. If you have a range of colors to set (as you do), your script will be more efficient by using setBackgrounds() just once, which accepts a two-dimensional array of colors.
  • Another efficiency improvement can be made by re-arranging your various if statements. (The idea is to reduce the computational investment. In the case of slow operations like many of the Google API calls, this thinking can save your script from timeouts. In this example, the .getTime() function is quick, so it's not really necessary to refactor the code.)

Updated code

function formatting() {
  var now  = new Date().getTime();
  var twoDaysInMilliseconds = 2 * 24 * 60 * 60 * 1000; //172800000;
  var headers = 1; // # rows of headers to skip

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Enquiry Tracking');
  var data = sheet.getDataRange().getValues().splice(headers); // all data, skipping headers
  var colBooked = 5-1;  // Column numbers -1 to map to 0-based array
  var colDate = 1-1;
  var colFU = 6-1;
  var colors = [];

  for (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 the colors for all data cells in ColFU
  sheet.getRange(1+headers,colFU+1,colors.length,colors[0].length).setBackgrounds(colors);
}

Upvotes: 1

Related Questions