Google Apps 腳本：將格式規則應用於特定工作表 (Google Apps Script: Apply formatting rule to a spesific sheet)

問題描述

Google Apps 腳本：將格式規則應用於特定工作表 (Google Apps Script: Apply formatting rule to a spesific sheet)

根據文檔：https://developers .google.com/apps‑script/reference/spreadsheet/conditional‑format‑rule‑builder

var sheet = SpreadsheetApp.getActiveSheet();

但我只希望它出現在名為 Resultat 的特定工作表中。

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("resultat");

但是有了這個 sheetByName，我得到：

TypeError: ThisSheet.newConditionalFormatRule is not a function

調用代碼：

 var ThisSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("resultat");

var range = ThisSheet.getRange("C:D") // only want it to select C element.

 ThisSheet.clearConditionalFormatRules();

  var rule1 = ThisSheet.newConditionalFormatRule()

      .whenTextContains("NO")

      .setBackground("#b3cfb0")

      .setRanges([range])

      .build();

  var rules = ThisSheet.getConditionalFormatRules();

  rules.push(rule1);

  ThisSheet.setConditionalFormatRules(rules);

</code></pre>





參考解法
方法 1:
 
Modification points:


From your script, I cannot understand about ThisSheet. If ThisSheet is sheet of var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("resultat");, newConditionalFormatRule() is the method of Class SpreadsheetApp. By this, the error occurs.

When ThisSheet is sheet of var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("resultat");, how about the following modification?
Modified script:
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("resultat");
var range = sheet.getRange("C:D") // only want it to select C element.
sheet.clearConditionalFormatRules();
var rule1 = SpreadsheetApp.newConditionalFormatRule()
  .whenTextContains("NO")
  .setBackground("#b3cfb0")
  .setRanges([range])
  .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule1);
sheet.setConditionalFormatRules(rules);


By above modified script, the conditional format rule is set to the range of "C:D".
If you want to set the conditional format rule to the column "C", please modify var range = sheet.getRange("C:D") to var range = sheet.getRange("C:C").

Reference:

newConditionalFormatRule()
(by maria、Tanaike)
參考文件


Google Apps Script: Apply formatting rule to a spesific sheet (CC BY‑SA 2.5/3.0/4.0)