Script to Change Row Color when a cell changes text

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

More Related Contents:

  1. Determining the last row in a single column
  2. Moving google apps script to v8 file upload stopped working from sidebar
  3. How can I be getting multiple unwanted event blocks from the same onFormSubmit Trigger?
  4. ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?
  5. Google Sheets OnEdit() – You do not have permission to call a service
  6. Detect user inserting row or column in a google spreadsheet and reacting in a script
  7. How to define global variable in Google Apps Script
  8. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  9. Format a Google Sheets cell in plaintext via Apps Script
  10. UiApp has been deprecated. Please use HtmlService instead
  11. Google app scripts: email a spreadsheet as excel
  12. Is there a way to keep two sheets synchronized?
  13. Selecting the last value of a column
  14. Moving Files In Google Drive Using Google Script
  15. onEdit simple trigger never seems to be triggered
  16. How to get filtered values from Filter after using setColumnFilterCriteria?
  17. Protect ranges with google apps script
  18. GAS “On edit” trigger not running when edited from API
  19. How to access new ‘in-cell-image’ from google apps script?
  20. How to convert a Google Sheets-File to an Excel-File (XLSX)
  21. Difference between getValue() and getDisplayValue() on google app script
  22. How to allow onEdit function to affect protected cell in a Google Sheet?
  23. Creating and Sending sheets in the same function
  24. How do I know if spreadsheet cells are merged using google apps script
  25. How to use a formula written as a string in another cell [evaluate for Google Spreadsheet] [duplicate]
  26. Google apps script error: “You do not have permission to call protect” [duplicate]
  27. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  28. .setFormula() and received error missing ) argument list
  29. How can I merge multiple tabs in a Google Spreadsheet using Google App Script?
  30. Passing cell references to spreadsheet functions

Leave a Comment