How to change a google spreadsheet row color, when

Question

I have already tried this: Google Spreadsheet: Script to Change Row Color when a cell changes text;

But it cant get it to work. The color of the row does not change to #000000

This is what i have so far:

function onEdit(event)
{
  var ss = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  var currentValue = r.getValue();

  if(currentValue == "dags dato")
  {
    var dd = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
    r.setValue(dd);
  }
  else if(currentValue == "dialog")
  {
    setRowColor("yellow");
  }
  else if(currentValue == "besvaret")
  {
    setRowColor("yellow");
  }
  else if(currentValue == "afvist")
  {
    setRowColor("red");
  }
}

function setRowColor(color)
{
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1);
    status = rowRange.offset(0, statusColumnOffset).getValue();

    rowRange.setBackgroundColor("#000000");

}


//Returns the offset value of the column titled "Status"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getStatusColumnOffset() {

  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Status") {
      return i;
    } 
  }
}

Answer 1

I wrote way faster and cleaner method for myself and I wanted to share it.

function onEdit(e) {
    if (e) { 
        var ss = e.source.getActiveSheet();
        var r = e.source.getActiveRange(); 

        // If you want to be specific
        // do not work in first row
        // do not work in other sheets except "MySheet"
        if (r.getRow() != 1 && ss.getName() == "MySheet") {

            // E.g. status column is 2nd (B)
            status = ss.getRange(r.getRow(), 2).getValue();

            // Specify the range with which You want to highlight
            // with some reading of API you can easily modify the range selection properties
            // (e.g. to automatically select all columns)
            rowRange = ss.getRange(r.getRow(),1,1,19);

            // This changes font color
            if (status == 'YES') {
                rowRange.setFontColor("#999999");
            } else if (status == 'N/A') {
                rowRange.setFontColor("#999999");
            // DEFAULT
            } else if (status == '') { 
                rowRange.setFontColor("#000000");
            }   
        }
    }
}

Answer 2

You could try to check your code for any errors or issues by using the Logger class like so:

try {
    //your code
}
catch(e) {
    Logger.log(e);
}

Then you can go to View -> Logs from the Script Editor to see if each line of code performs as expected. Also the Execution transcript might be useful to see if the code breaks at one particular line of code. You can view more details about how each troubleshooting method works.