Google Apps Script – change color – part 027. – free-tutorials.org news free tutorials source code development scripts

Open a new spreadsheet with a Sheet named Sheet3, then a new script.
This will require a new project.
Also, you can run it with an old project.
Use this function to add a blue color on each empty cell selected by your mouse in the Sheet3.
You can see the rule I used with the newConditionalFormatRule:

function AddFormatRule(){
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = s.getSheetByName("Sheet3");
  var numRows = sheet.getLastRow();
  var numCols = sheet.getLastColumn();
  var data_range = sheet.getRange(1,1, numRows, numCols);
  Logger.log(numRows + "|" + numCols);

  var rule = SpreadsheetApp.newConditionalFormatRule()
      .whenCellEmpty()
      .setBackground("blue")
      .setRanges([data_range])
      .build();        
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

function AddFormatRule(){

var s = SpreadsheetApp.getActiveSpreadsheet();

var sheet = s.getSheetByName("Sheet3");

var numRows = sheet.getLastRow();

var numCols = sheet.getLastColumn();

var data_range = sheet.getRange(1,1, numRows, numCols);

Logger.log(numRows + "|" + numCols);

var rule = SpreadsheetApp.newConditionalFormatRule()

.whenCellEmpty()

.setBackground("blue")

.setRanges([data_range])

.build();

var rules = sheet.getConditionalFormatRules();

rules.push(rule);

sheet.setConditionalFormatRules(rules);

}

Leave a Reply Cancel reply