0

I am trying to figure out how to use conditional formatting via script on a google spreadsheet similar to what you can do with the conditional formatting feature.

I have two columns labeled 'Country' and 'State.' If the value in one cell is 'United States,' I want the script to check the adjacent state column to make sure it's not blank. If it is, I want the cell background to change to red.

Is there any way to do this in a script? I don't want to use the built in feature as it doesn't copy over to newly create sheets within the spreadsheet as I'll be having other users creating new sheets.

I found some references, but I'm having trouble tailoring them to my needs.

Links: Google Spreadsheet conditional formatting script

https://webapps.stackexchange.com/questions/16745/google-spreadsheets-conditional-formatting-based-on-another-cells-content

Community
  • 1
  • 1
Anna Thpvng
  • 17
  • 1
  • 6

2 Answers2

0

Let's assume that Country is placed at (1,1) and State is placed at (1,2) where (i,j) indicates the ith row and jth column on the Spreadsheet. Google Spreadsheets is 1-indexed meaning indices start at 1.

var activeSheet = SpreadsheetApp.getActiveSheet();

for (var a = 2; a < activeSheet.getLastRow(); a++) {
   if (String(activeSheet.getRange(a,1).getCell(1,1)) === "United States") { 
       if (String(activeSheet.getRange(a,2).getCell(1,1)) === null) { 
          activeSheet.getRange(a, 2, 1, 1).setBackgroundColor('red'); 
       } 
   } 
}
hologram
  • 533
  • 2
  • 5
  • 21
  • I'm trying to test this to see how it works so I can understand it.. at (1,1), That would be cell A1, correct? – Anna Thpvng Mar 03 '15 at 01:35
  • I think I may be missing something, I've been playing around with and tinkering a bit without any result (not even some error). Any help? – Anna Thpvng Mar 03 '15 at 23:48
  • Yes, sorry for the late response, Google Sheets has cell A1 (first cell) at (1,1). What do you need help with? – hologram Mar 09 '15 at 22:59
  • I'm just trying it in google sheets and it's not working even if I open up a new sheet. – Anna Thpvng Mar 19 '15 at 20:57
0

Try copy and pasting this into a blank script file. This depends on column A being Country, and column B being State, but you can make as many sheets as you want, and this will work automatically.

Let me know if you want an explanation of a specific part, as I'm not sure what your scripting background is. I've included some comments in the code.

function onEdit(e) {

  var ss = e.source;
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A:B");
  var values = range.getValues();

  //for each row that data is present
  for(var i = 0; i < values.length; i++) {
    var cell = sheet.getRange(i + 1, 2);

    //check if the first value of that row is exactly "United States"
    if(values[i][0] === "United States") {

      //if it is, check for a blank cell.  If so, make it red.
      if(values[i][1] === "") {
        cell.setBackground('red');
      } else {
        cell.setBackground('white');
      }

    } else {

      //In any other case, make it white.
      cell.setBackground('white');
    }
  }  
}
Dan Oswalt
  • 2,201
  • 2
  • 14
  • 24
  • I'm only a beginner, I've only finished the codeacademy course and will doing Team Treehouse soon. I'm just trying to build a data validation script as starter project in GAS. On the function, why do we have the value 'e' ? I just put this into a blank script, and it only picks up some cells and leaves some out, even though the loop looks like it should go through all the rows until there is no data? – Anna Thpvng Mar 04 '15 at 17:52
  • Oops, my logic was wrote in there, I shouldn't be using the 'break' command. I updated the code. e stands for the Event Object, the onEdit function handles an event, editing. You can omit it if you don't use it, but it doesn't hurt to have it in there. In the updated coded I used e.source to identify the active spreadsheet instead of SpreadsheetApp.getActiveSpreadsheet() so you can see one way to use it. – Dan Oswalt Mar 04 '15 at 21:02
  • And instead of using the values = getDataRange, I used values = getRange('A:B') instead, that probably makes more sense than grabbing the dimensions of the data. – Dan Oswalt Mar 04 '15 at 21:29
  • Assuming that I want to move away from just columns A & B, I would just change the range values found in the variable 'range' and then change the 1 in 'values' here to where the column is (i.e. column c,d) and it would look like this? `//check if the first value of that row is exactly "United States" if(values[i][3] === "United States") { //if it is, check for a blank cell. If so, make it red. if(values[i][4] === "") { cell.setBackground('red');` – Anna Thpvng Mar 07 '15 at 00:37
  • Remember arrays start at zero, so column C is actually 2, D is 3. But just to keep it all nice and confusing, take a look at the cell.getRange() definition, you'll need to change this also. This method's parameters are the row and column of the actual sheet, but they are not zero-based. So we add one the i variable, which is being used to iterate over each row in the values array. Column B is actually 2 here. So if you're shifting to the C:D range, this will be `cell.getRange(i + 1, 4)`. It's really easy to make a one-off counting mistake while scripting for spreadsheets! – Dan Oswalt Mar 07 '15 at 01:29
  • @zbnrg this code is ineffective - the runtime is too long – hologram Mar 09 '15 at 22:59