Google Script App change color cells when a cell have a specific value

Question

I've been trying to look for what I need to do but I couldn't figure it out how to do this from the previous posts I found.

I have this Google Spreadsheet here

Want I' love to do is to analize the data in the sheet called "actual" and set the background color of the cells when it meets a condition. So basically, I'm looking for a script to do this:

get the data from the sheet called 'actual'
if a cell is error (equals to "#N/A") then set the color font to white
if a cell equals to "wnd" then set the background color to "red"
if the cell equals to "otc", then set the background color to "green"
etc..

It's going to have around 50 conditions and that is why I would love to do this with code instead of regular Conditional Formatting.

Thank you in advance.

Tulio Troche · Accepted Answer · 2016-09-25 12:39:30Z

2

Change your code to minimize the use of get calls. Those are slow calls. So you want to take one big one. Then work with all the data. Here's some example code:

Note This function checks for values of 3 and sets them red background with white text. The current range its working with is defined in var range. Set that up yourself to whatever you need.

function setCellColors() {
  //Get the sheet you want to work with. 
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
  //Grab the entire Range, and grab whatever values you need from it. EX: rangevalues
 var range = sheet.getRange("A1:E17");
 var rangevalues = range.getValues();
  //Loops through range results
 for (var i in rangevalues) {
  for (var j in rangevalues) {
   //Get the x,y location of the current cell.
      var x = parseInt(j, 10) + 1;
      var y = parseInt(i, 10) + 1;
   //Set the rules logic
     if (rangevalues[i][j] == 3) {
        //Set the cell background
        sheet.getRange(y,x).setBackground("red");
        sheet.getRange(y,x).setFontColor("white");
     }
   }
  }
}

answered Sep 25, 2016 at 12:39

Tulio Troche

1301 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Álvaro Figuero Over a year ago

This looks really good. Can you please explain me why you are using 10 as your second argument in these vars: var x = parseInt(j, 10) + 1; var y = parseInt(i, 10) + 1;

Álvaro Figuero Over a year ago

I've used your code to do this and I have updated the var range to do what I need. However, I only get the result until column O. I have updated the google spreadsheet that using as an example with this code so you can take a look at it. I just can't figure it out why is only working until column O. Thank you in advance.

Tulio Troche Over a year ago

10 is the base of the numbers you're parsing to. if you wanted binary it would be different, or hexadecimal numbers, ect. To update the spreadsheet to include more columns, change the range variable. You can either have this display dynamically, or statically. If you want it static, just pick the values like i did. If you want it dynamically, then you'll have to write it in. (please remember to choose an answer if it helped, this way future searchers can find this more easily)

Álvaro Figuero Over a year ago

Thanks for the clarification! Having it statically works for me. The thing is that I've picked the values that I need for my range, but for some reason that I just can't get it, it doesn't work within the whole range, it stops at column O (although I have set the variable like this: var range = sheet.getRange("A1:DP15");

Tulio Troche Over a year ago

Do you have empty cells randomly in your sheet? Try rangevalues.length during the i and j loops and see what happens.

|

Jason Allshorn · Accepted Answer · 2016-09-25 01:05:42Z

0

Hope this helps.

function setCellColors() {
//Get the sheet you want to work with. 
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
    // determine how how big your data range will be
var lastColumn = sheet.getLastColumn() + 1
var lastRow = sheet.getLastRow() + 1
    // iterate through the data. 
for (var column = 1; column < lastColumn; column++) {
    for (var row = 1; row < lastRow; row++) {
        var cellget = sheet.getRange(row, column).getValue();
        var cellset = sheet.getRange(row, column);
        //Set the rules logic
        if (cellget === "#N/A") {
            //Set the cell background
            cellset.setBackground("red");
            cellset.setFontColor("white");


        }
    }
}
}

answered Sep 25, 2016 at 1:05

Jason Allshorn

1,6451 gold badge18 silver badges28 bronze badges

3 Comments

Álvaro Figuero Over a year ago

It does help! Thanks you very much.

Álvaro Figuero Over a year ago

When running the code, I get this execution hint: Method Range.getValue is heavily used by the script.Collapse File: Code Line: 11 The script uses a method which is considered expensive. Each invocation generates a time consuming call to a remote server. That may have critical impact on the execution time of the script, especially on large data. If performance is an issue for the script, you should consider using another method, e.g. Range.getValues(). I'm not sure how to use the Range.getValues() method instead.

Álvaro Figuero Over a year ago

When the number of rows to evaluate grows, I'm getting this message: Exceeded maximum execution time.

Collectives™ on Stack Overflow

Google Script App change color cells when a cell have a specific value

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related