Google Apps Script setValue() AND setBackgroundRGB() for the same cell

Question

I'm trying to use the methods setValue and setBackgroundRGB together for the same cell but it doesn't work. Actually, only setValue works. I tried the 3 different ways of the following if-else condition but always I see only the number without any change at background colour. If I delete the number, only then I see the colour. Is it possible to have both for the same cell?

(Also, is there a way to work in specific columns without using the active cell?)

function onEdit(e) {
  var active_cell = e.range;
  var active_column = active_cell.getColumn();

      if(active_sheet.getName().startsWith("202")){
        var in_row= 5;
        var exp_row= 6;
        var diff_row = 7;
    
        var income = active_sheet.getRange(in_row,active_column).getValue();
        var expenses = active_sheet.getRange(exp_row,active_column).getValue();
        var diff = income - expenses;
    
        if (active_column >= 2){
          if(income > expenses) {
            active_sheet.getRange(diff_row,active_column).setBackgroundRGB(255,0,0).setValue(diff);
          } else if (income == expenses) {
            active_sheet.getRange(diff_row,active_column).setValue(diff).setBackgroundRGB(0,255,0);
          } else {
            active_sheet.getRange(diff_row,active_column).setValue(diff);
            active_sheet.getRange(diff_row,active_column).set.setBackgroundRGB(255,0,0);
          }
        }
     }

}

question from:https://stackoverflow.com/questions/65908685/google-apps-script-setvalue-and-setbackgroundrgb-for-the-same-cell

Answer 1

active_sheet is not defined in your code, Range.setBackgroundRGB() and Range.setValue() are working as expected in either of your combinations.

Updated Code:

function onEdit(e) {
  var active_sheet = e.source.getActiveSheet();
  var active_cell = e.range;
  var active_column = active_cell.getColumn();

      if(active_sheet.getName().startsWith("202")){
        var in_row= 5;
        var exp_row= 6;
        var diff_row = 7;
    
        var income = active_sheet.getRange(in_row,active_column).getValue();
        var expenses = active_sheet.getRange(exp_row,active_column).getValue();
        var diff = income - expenses;
    
        if (active_column >= 2){
          if(income > expenses) {
            active_sheet.getRange(diff_row,active_column).setBackgroundRGB(255,0,0).setValue(diff);
          } else if (income == expenses) {
            active_sheet.getRange(diff_row,active_column).setValue(diff).setBackgroundRGB(0,255,0);
          } else {
            active_sheet.getRange(diff_row,active_column).setValue(diff);
            active_sheet.getRange(diff_row,active_column).setBackgroundRGB(255,0,0);
          }
        }
     }

}

Output:

---

Regarding your other question: Is there a way to work in specific columns without using the active cell?

• Yes, you can select a specific column as your range without using the active cell's column index. You just need to provide a hard-coded column index when selecting a range using Sheet.getRange(row, column).

Example:

var income = active_sheet.getRange(in_row,4).getValue();

• Here, I will access the cell in column D (which is in index 4).