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

User · Accepted Answer

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  lt  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  lt  range getLastColumn    i          if  range offset 0  i  1  1  getValue       Status           return i

User · Answer

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

User · Answer

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  gt   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

User · Answer

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  amp  amp  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

User · Answer

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 lt  gt    search  Complete   S1  gt 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

[google-apps-script] Script to Change Row Color when a cell changes text

Examples related to google-apps-script

Examples related to google-sheets

Examples related to gs-conditional-formatting