Script to Change Row Color when a cell changes text

Question

I have a Google spreadsheet where I keep a list of bugs and whenever I fix a bug I change the status from "Not Started" to "Complete". I want to write a script for the Google Docs spreadsheet such that whenever I change the status to "Complete" the entire row gets highlighted in a certain color.

I already know that Google spreadsheet already has "change color on text" but that function only changes the color of the cell and does not change the color of the entire row.

Rashida

=sparkline(1,{"charttype","bar";"color1",A1}) – Thrilling

Answer 1

//Sets the row color depending on the value in the "Status" column.
function setRowColors() {
  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();
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#99CC99");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFDD88");    
    } else if (status == 'Not Started') {
      rowRange.setBackgroundColor("#CC6666");          
    }
  }
}

//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;
    } 
  }
}

Zajac

How do you tell Google Docs to execute this function, when the text changes? – Vellicate

@Zajac could you please add some comments to your anwser? Some people might need some guidelines on what to do with the code that you posted. – Goulet

see the response from user2532030 – Erenow

Answer 2

Realise this is an old thread, but after seeing lots of scripts like this I noticed that you can do this just using conditional formatting.

Assuming the "Status" was Column D:

Highlight cells > right click > conditional formatting. Select "Custom Formula Is" and set the formula as

=RegExMatch($D2,"Complete")

or

=OR(RegExMatch($D2,"Complete"),RegExMatch($D2,"complete"))

Edit (thanks to Frederik Schøning)

=RegExMatch($D2,"(?i)Complete") then set the range to cover all the rows e.g. A2:Z10. This is case insensitive, so will match complete, Complete or CoMpLeTe.

You could then add other rules for "Not Started" etc. The $ is very important. It denotes an absolute reference. Without it cell A2 would look at D2, but B2 would look at E2, so you'd get inconsistent formatting on any given row.

Strage

Note: Using the latest version of google docs, user2532030's answer is the easiest answer to use. In my case I wanted to highlight a row based on a cell being "Y". I highlighted the top row, right clicked conditional formatting. Set "Custom Formula Is" = =RegExMatch($G1,"Y"), range =1:1000. This sorted the whole sheet. – Withe

This is a good solution. But instead of using the OR operator to catch both lower-case and Pascal-notation, you should take advantage of regex-support for case-insensivity like this: =RegExMatch($D2,"(?i)complete"). This will match all literal representations of the word "complete", including "cOmPlEtE" et.c.. I've tested this, and Google Apps supports the case-ignore instruction. – Stedfast

That's a great addition Frederik. I've edited my answer to include it. – Strage

You can do it even more simply. Custom Formula: =$D$1:$D$25="Completed". Then set range to be the rows you want to color. For example range: 1:25 – Oyster

@kilianc - I disagree that this should be marked as the correct answer. The answer that was marked was absolutely correct at the time that the question was posed. The Conditional Formatting solution only became available in 2014 with the new version of Sheets. – Brinkman

@Brinkman so you are suggesting it's best to give people outdated info? At this time, this is the correct answer. – Cultured

@Cultured I see your point, but is it fair that the original answerer, who provided something timely and correct should lose the credit for his answer? – Brinkman

Also, it should be pointed out that this solution only works for spreadsheets that were created using Google's "new" format. Legacy spreadsheets (at this time) have not been automatically upgraded, so @genegc's answer is still the correct one for those sheets. – Brinkman

In your edited solution, you should remove the leading OR(. There is no OR, now that you only have 1 regex. Also, 1 closing parenthesis is missing. The correct formula should be =OR(RegExMatch($D2,"(?i)Complete") – Stedfast

In Firze's suggestion, remove row numbers from formula: =$D:$D="Completed". No need to specify row range twice. Off-topic: @Brinkman SO wants to help people and spread knowledge, not grow score e-peen and bolster ego. For an active SO user loosing a few points from an old answer is insignificant compared to all the points they've gained since then. Conversely if they are no longer active then it's irrelevant. The score stuff is primarily for gating access to advanced site features, and the reward is not the score itself (just a number) but access to those features (community empowerment). – Agata

Answer 3

I used GENEGC's script, but I found it quite slow.

It is slow because it scans whole sheet on every edit.

So 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");
            }   
        }
    }
}

Celsacelsius

Answer 4

user2532030's answer is the correct and most simple answer.

I just want to add, that in the case, where the value of the determining cell is not suitable for a RegEx-match, I found the following syntax to work the same, only with numerical values, relations et.c.:

[Custom formula is]
=$B$2:$B = "Complete"
Range: A2:Z1000

If column 2 of any row (row 2 in script, but the leading $ means, this could be any row) textually equals "Complete", do X for the Range of the entire sheet (excluding header row (i.e. starting from A2 instead of A1)).

But obviously, this method allows also for numerical operations (even though this does not apply for op's question), like:

=$B$2:$B > $C$2:$C

So, do stuff, if the value of col B in any row is higher than col C value.

One last thing: Most likely, this applies only to me, but I was stupid enough to repeatedly forget to choose Custom formula is in the drop-down, leaving it at Text contains. Obviously, this won't float...

Doubleness

Answer 5

I think simpler (though without a script) assuming the Status column is ColumnS.

Select ColumnS and clear formatting from it. Select entire range to be formatted and Format, Conditional formatting..., Format cells if... Custom formula is and:

=and($S1<>"",search("Complete",$S1)>0)

with fill of choice and Done.

This is not case sensitive (change search to find for that) and will highlight a row where ColumnS contains the likes of Now complete (though also Not yet complete).

Che