Skip to content

Change Row Colour in Google Docs

I am currently in the progress of working on something for Vex Studios and I’m using Google Docs so that we can collaborate on the list of features for the new project. The problem is I wanted to dynamically change the row colour based on the contents of a cell.

The sheet is set up with validation for the status column and as you change the status the entire row will change colour to help make things more organised.

To set this up in your Google Doc click on Tools > Script Editor and paste in the below making sure you change the status options, Sheet Name, Column Length and Column Choice to match your sheet.

function onEdit(e) {
  if (e) { 
    var as = e.source.getActiveSheet();
    var ar = e.source.getActiveRange();
    var sheetName = "Features"; // Change this to the name of your sheet
    var colLength = 5; // How many columns are in the selection
    var colChoice = 5; // In your sheet what is the column number that contains the choice

    if (ar.getRow() != 1 && as.getName() == sheetName) {

      status = as.getRange(ar.getRow(), colChoice).getValue();

      rowRange = as.getRange(ar.getRow(),1,1,colLength);

      // The status what we are basing the colours on
      if (status == 'In Progress') {
        rowRange.setBackground("#ffa500");
      } else if (status == 'Complete'){
        rowRange.setBackground("#66CD00");
      } else if (status == 'Rejected') {
        rowRange.setBackground("#FF0000");
        // Default and Planned Status
      } else if (status == '' || status == 'Planned') { 
        rowRange.setBackground("#FFFFFF");

      }   
    }
  }
}

 

 

 

Published inBlogCode

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Link your latest blog post?