Google script to color a cell based on another cells value

1
votes

I'm trying to change the color of a cell in a column based on if it bigger or not than another cell (in the same row)

I have set this up if conditional formatting, but formulas get messed around as I copypaste in the sheet, hence I'm looking to do the same with gs.

I have found that I need to use onEdit() (https://developers.google.com/apps-script/guides/triggers/#Simple) to get the changes updated on every cell change.

And got some code from another questions here, but I'm getting an error in the getRange. 

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Programar');
  var values = sheet.getRange(2, 16, 3).getValues();
  var values_now = sheet.getRange(2, 5, 3).getValues();
  var color = '';  
  var current_row = 2
  for (var i = 0; i < values.length; i++)
  {

    if (values_now[i][0] > values[i][0])
    {
            SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
    .alert(values_now[i][0] + ' ' + values[i][0]);
      color='#FFDD88'
    }
    else if (values_now[i][0] < values[i][0])
    {
      color = '#CC6666'
    }
    sheet.getRange(current_row, 5).setBackgroundColor(color);
    current_row++
  }

}

The range I select in values is P2:P243 and in values_now E2:E243, this last range is the one i like to color the cell.

  • So if P2 is Bigger than E2, E2 should be in Blue
  • If P32 is smaller than E32, E32 should be in Red. an so on...
1
google-apps-scriptgoogle-sheetsgs-conditional-formatting

1 Answers

1
votes

How about this modification? The flow of this modified script is as follows. Please think of this as just one of several answers.

  1. Retrieve values from the cells.
  2. Put the coordinates of red and blue to an array.
  3. Apply the retrieved coordinates to the cells.

Modified script:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Programar');
  var values = sheet.getRange("E2:P243").getValues();
  var colorToRed = [];
  var colorToBlue = [];
  for (var i = 0; i < values.length; i++) {
    var e = values[i][0];
    var p = values[i][11];
    if (e > p) {
      colorToRed.push("E" + (i + 2));
    } else if (e < p) {
      colorToBlue.push("E" + (i + 2));
    }
  }
  sheet.getRangeList(colorToRed).setBackground("#FFDD88"); // #FFDD88 is used as red?
  sheet.getRangeList(colorToBlue).setBackground("#CC6666"); // #CC6666 is used as blue?
}

Note:

  • In this modified script, when the values of column "E" and "P" are the same. The background color is not changed.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

As other sample script, when setBackgrounds() is used, the script is as follows.

Sample script:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Programar');
  var values = sheet.getRange("E2:P243").getValues();
  var colors = [];
  for (var i = 0; i < values.length; i++) {
    var e = values[i][0];
    var p = values[i][11];
    if (e > p) {
      colors.push(["#FFDD88"]); // #FFDD88 is used as red?
    } else if (e < p) {
      colors.push(["#CC6666"]); // #CC6666 is used as blue?
    } else {
      colors.push([""]);
    }
  }
  sheet.getRange(2, 5, colors.length, 1).setBackgrounds(colors);
}