I'm trying to put together a google apps script that changes the font color of of a cell based on the value of another cell. If cell B2 > D2, change the font color of B2 to red. Problem is that I have no experience with javascript...so the little script I copy pasted and edited together doesn't work.

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var columnO = sheet.getRange(2, 2, sheet.getLastRow()-1, 1);
  var oValues = columnO.getValues();
  var column1 = sheet.getRange(2, 4, sheet.getLastRow()-1, 1);
  var oValues1 = columnO.getValues();

  for (var i = 0; i < oValues.length; i++) {
    if (oValues[i][0] > oValues1[i][0]) {
      sheet.getRange(i, 1, 1, 1).setFontColors('red');
    }
  }
}

I would like to extend this script to be able to do: if B2 > D2 color red and if B2 < C2 color blue, otherwise color green.

I'm trying to put together a google apps script that changes the font color of of a cell based on the value of another cell. If cell B2 > D2, change the font color of B2 to red. Problem is that I have no experience with javascript...so the little script I copy pasted and edited together doesn't work.

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var columnO = sheet.getRange(2, 2, sheet.getLastRow()-1, 1);
  var oValues = columnO.getValues();
  var column1 = sheet.getRange(2, 4, sheet.getLastRow()-1, 1);
  var oValues1 = columnO.getValues();

  for (var i = 0; i < oValues.length; i++) {
    if (oValues[i][0] > oValues1[i][0]) {
      sheet.getRange(i, 1, 1, 1).setFontColors('red');
    }
  }
}

I would like to extend this script to be able to do: if B2 > D2 color red and if B2 < C2 color blue, otherwise color green.

• javascript
• google-apps-script
• google-sheets

1 Answer 1

First thing I see is a mistake/typo on line 6: column0.getValues() should be column1.getValues().

Another error is on the for-loop, where you getRange(i, 1, 1, 1). On your question you wrote you want to change B2 cell color and this statement is changing column A. Also, the row index should start on 2, not 0.

Another important thing to consider on a script like this is to use batch functions, as it will speed it up greatly, e.g.

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var values = sheet.getRange(2, 2, sheet.getLastRow()-1, 3).getValues(); //B, C and D
  var colors = [];  

  for (var i = 0; i < values.length; i++)
    colors.push([values[i][0] > values[i][2] ? 'red' :
                 values[i][0] < values[i][2] ? 'blue' : 'green']);
  sheet.getRange(2, 2, colors.length, 1).setFontColors(colors);
}