CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
David Ahn
David Ahn

Reputation: 37

Google Appscript - how to set conditional format with multiple ranges

I'm trying to figure out how to fix this problem. I tried to use 'for loop' to set multiple ranges with one function but got error message.

'Exception: The parameters (String) don't match the method signature for SpreadsheetApp.ConditionalFormatRuleBuilder.setRanges.'

Here's my code.

function myFunction() {

let columnStart = ['d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','aa','ab','ac'] 

let columnEnd = ['d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','aa','ab','ac','ad','ae','af','ag','ah','ai','ai','ai','ai','ai','ai','ai','ai','ai']

let range='';
let formula ='';
let rule = '';

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetname')

for(let z=0; z<columnStart.length;z++){

formula = '=sum($'+columnStart[z]+'6:$'+columnEnd[z+6]+'6)>=40'
range += '[';

  for(let i=3; i<25; i++){
    if(i!=24){
      range += "sheet.getRange('"+columnStart[z]+2*i+':'+columnEnd[z+6]+2*i+"'),"
    }else{
      range += "sheet.getRange('"+columnStart[z]+2*i+':'+columnEnd[z+6]+2*i+"')"
    }
  }

  range += "]"

  Logger.log(range)

  rule = SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied(formula).setBackground('#f4cccc').setRanges(range).build();


  rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);

  range ="";
  formula='';
}

}

Firstly, i thought maybe the range got from the for loop has the problem. but when copied the range from the Logger.log() and pasted that range to the setRange's range, it worked.

Here's the code I tried.

enter image description here

Could you help me figure this out? Any advice would be appreciated.

Upvotes: 1

Views: 760

Answers (1)

Tanaike
Tanaike

Reputation: 201503

I believe your goal as follows.

  • You want to set the conditional format rules for each range with each formula.
  • The range is created by the for loop of for (let i = 3; i < 25; i++) {,,,}.
  • The formula is created by const formula = '=sum($' + columnStart[z] + '6:$' + columnEnd[z + 6] + '6)>=40';.

Modification points:

  • When I saw your script, it seems that range of Logger.log(range) is the string value. But when setRanges(range) is used, range is required to be the Range object. I think that this might be the reason of your issue.
  • And, in your situation, how about the following flow?
    1. Put each range to an array of rules of rules = sheet.getConditionalFormatRules().
      • For this, in my answer, I used RangeList.
    2. Use rules with sheet.setConditionalFormatRules(rules) put outside of the loop.

When above points are reflected to your script, it becomes as follows.

Modified script:

function myFunction() {
  let columnStart = ['d', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'aa', 'ab', 'ac'];
  let columnEnd = ['d', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'aa', 'ab', 'ac', 'ad', 'ae', 'af', 'ag', 'ah', 'ai', 'ai', 'ai', 'ai', 'ai', 'ai', 'ai', 'ai', 'ai'];
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetname');
  const rules = []; // sheet.getConditionalFormatRules();
  for (let z = 0; z < columnStart.length; z++) {
    const formula = '=sum($' + columnStart[z] + '6:$' + columnEnd[z + 6] + '6)>=40';
    const rangeList = [];
    for (let i = 3; i < 25; i++) {
      rangeList.push(columnStart[z] + 2 * i + ':' + columnEnd[z + 6] + 2 * i);
    }
    const ranges = sheet.getRangeList(rangeList).getRanges();
    const rule = SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied(formula).setBackground('#f4cccc').setRanges(ranges).build();
    rules.push(rule);
  }
  sheet.setConditionalFormatRules(rules);
}

Note:

  • In your script, rules = sheet.getConditionalFormatRules(); is used. In this case, the rules are added every run. In this case, I thought that const rules = []; might be suitable instead of rules = sheet.getConditionalFormatRules();. In this answer, const rules = []; is used. If you want to use rules = sheet.getConditionalFormatRules();, please modify above script.

  • In this answer, I used the range and the formula from your script. If your actual situation is different from them, please modify above script. Please be careful this.

References:

Upvotes: 3

Related Questions