CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptconditional-statementsconditional-formatting
Jeewaka Dayarathne
Jeewaka Dayarathne

Reputation: 15

Google Apps script to conditional format cell border

I have a google sheet with 26 columns and 96 raws. I want to change the cell border thickness if cell contain text which is same to Z6 cell value. Is there a way to write a google app script code for this.

I tried this code but failed

function formatting() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Chart');
 var range = sheet.getRange("A1:Z100")
 var ruleRed = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied("=$Z$6")
.setBackground("green")
.build();

}

Upvotes: 0

Views: 762

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

Several things:

  • The correct formula for conditional formatting would be: "=A1=$Z$6"
  • The correspoding request in Apps Script including the necessary setting of ranges would be: var ruleRed = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied("=A1=$Z$6") .setBackground("green") .setRanges([range]) .build();
  • After creating a conditional rule, you need to apply to the sheet, e.g. sheet.setConditionalFormatRules([ruleRed]);

However

  • Borders cannot be formatted in scope of conditional formatting, you need to take another approach
  • You can use the Apps Script method setBorder()
  • To set borders as required in Apps Script, you need to
    • retrieve the value of Z6
    • Loop through your range comparing all values to Z6
    • Assign the border to each cell fulfilling the condition

Sample for border formatting and conditional formatting with Apps Script:

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Chart');
  var range = sheet.getRange("A1:Z100")
  //border formatting 
  var value = sheet.getRange("Z6").getValue();
  var values = range.getValues();
  for (var i = 0; i < values.length; i++){
    for (var j = 0; j < values.length; j++){
      if (values[i][j] == value){
        range.getCell(i+1, j+1).setBorder(true, true, true, true, false, false);
      }
    }
  }
  //conditional formatting 
  var ruleRed = SpreadsheetApp.newConditionalFormatRule()
  .whenFormulaSatisfied("=A1=$Z$6")
  .setBackground("green")
  .setRanges([range])
  .build();
  sheet.setConditionalFormatRules([ruleRed]);
}

Upvotes: 0

Related Questions