Google Apps script to conditional format cell border

Question

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();

}

Answer 1

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]);
}