google-apps-scriptgoogle-sheetsgs-conditional-formatting

Google Spreadsheet conditional formatting script


I am trying to figure out how to use conditional formatting on a google spreadsheet similar to what you can do in excel via a formula.

I want cell A2 to change to Green if cell O2 has a value of "X" and this will be done on both columns all the way down. I know this will require a script.

I ran across a link that is similar but i do not know how to adjust it to meet my needs. Is this something that can be done?

Link: https://webapps.stackexchange.com/questions/16745/google-spreadsheets-conditional-formatting


Solution

  • Here's a script you could use to do what you described:

    function formatting() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
      var columnO = sheet.getRange(2, 15, sheet.getLastRow()-1, 1);
      var oValues = columnO.getValues();
    
      for (var i = 0; i < oValues.length; i++) {
        if (oValues[i][0] == 'X') {
          sheet.getRange(i + 2, 1, 1, 1).setBackgroundColor('green');
        }
      }
    }