Google Script - Shade a Sheet Row based on Comparison of 2 data points
Hence, a short little script that will compare 2 data points in a row, then shade the row a particular color if the comparison is true.
What the Script Does:
It compares the values from two different columns to see if the first one is part of the second one - in this specific case it is checking the sending district against the receiving district. If it is, then the row containing this record is shaded.
The Script
Here is the script I created to accomplish this -
function setRowColor() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
for (var i = range.getRow(); i < range.getLastRow(); i++) {
var sendDistrict = sheet.getRange(i,4).getValue(); //Set 1st data point
var receiveDistrict = sheet.getRange(i,6).getValue(); //Set 2nd data point
row = range.offset(i-1, 0, 1); //reference current row evaluating
if (receiveDistrict.indexOf(sendDistrict)>-1) { //compare 2 data points, in this case 1st is part of 2nd
row.setBackgroundRGB(230,230,250); //set background color of entire row
}
}
}
Breaking it down and using it:
1. This can be used on any spreadsheet - even one being populated by a form. When in the spreadsheet, go to the Tools Menu and select Script Editor -
2. Copy the script above (everything highlighted gray) and paste it into the script editor window that opens. Make sure to delete out anything already there!
3. Modify the script as desired
Some of the script you may need to modify -
var sendDistrict = sheet.getRange(i,4).getValue(); //Set 1st data point var receiveDistrict = sheet.getRange(i,6).getValue(); //Set 2nd data point
- the numbers (4 & 6 above) tell it which columns to get the data points from. Change these to reflect the data points you wish to use
row = range.offset(i-1, 0, 1); //reference current row evaluating
If you want to color a DIFFERENT row than what the data is in, change the references here. Note that i-1 gets the row with data being compared - to change the row use that as a reference point. The 0 references where to start with the columns. The 1 designates that only 1 row should be colored. You can add a 4th reference if you want a few columns worth to be colored. (For instance, if I just wanted the 4 cells containing my data points, the cell in between, and the district building I would use (i-1, 3, 1, 4) - this would highlight ONLY cells that are in columns D, E, F, G when conditions are met.)
(receiveDistrict.indexOf(sendDistrict)>-1)
This is my comparison - because the receivingDistrict data has more information than the sendDistrict, I need to know if sendDistrict is part of it. This can be simplified if you are looking for either a specific entry, or if the 2 entries would be identical (no need to use an indexOf).
setBackgroundRGB(230,230,250)
You can change these to set the color you wish to use. Also, you can use setBackground('color') for HEX colors and the standard colors (ie. blue, red, yellow).
After you are done modifying the script, you will need to save it (save icon in script editor). You may also want to run it to make sure there are no errors. Remember that during this process you will need to give it permission to run.
That's it! A fairly easy to use and modify script, and we have covered a couple useful pieces: how to compare 2 text values where one is a part of the other, and how to set the color of a spreadsheet row.
