I tried to create conditional formatting by using script ( because when someone copy and paste a cell, the rule of this cell is pasted too and rules became wtf quickly)
Here the list of the six rules, the thing is they all work well if i create them manually.
As you can see on the next picture, only the 6th rules work if i use the script and i don't know why only the 6th and not the others.
For 1st to 5th rules, i have to do a modification on the range of the rule, like, for the first rule, "A:A1000" to "A:A100", wait for the sheet calculation ( rule now work ) then modify the range again to "A:A1000" to apply it for all the range.
I've search everywhere on the web but found nothing about this bug, am i the only one :O ?!
Here a sample of my script :
function FormatSheet() { var sheet = SpreadsheetApp.getActive().getSheetByName("Test"); var rangea = sheet.getRange("A:A") var rangeb = sheet.getRange("B:B") var rangec = sheet.getRange("C:C") var ranged = sheet.getRange("D:D") var rangee = sheet.getRange("E:E") var rangef = sheet.getRange("F:F") var rangeg = sheet.getRange("G:G") var rangeh = sheet.getRange("H:H") var rangei = sheet.getRange("I:I") var rangej = sheet.getRange("J:J") var rangek = sheet.getRange("K:K") var rangel = sheet.getRange("L:L") var rangem = sheet.getRange("M:M") var rangen = sheet.getRange("N:N") var rangeo = sheet.getRange("O:O") var rangep = sheet.getRange("P:P") var rangeq = sheet.getRange("Q:Q") var ranger = sheet.getRange("R:R") var ranges = sheet.getRange("S:S") var ranget = sheet.getRange("T:T") var rangeu = sheet.getRange("U:U") var rangev = sheet.getRange("V:V") var rangew = sheet.getRange("W:W") var rangex = sheet.getRange("X:X") var rangey = sheet.getRange("Y:Y") var rangez = sheet.getRange("Z:Z") //var range3 = sheet.getRange("A1:T1"); sheet.clearConditionalFormatRules(); var rule2 = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied('=ET(REGEXMATCH($M1;"ABC*");NBCAR($M1)=8)=VRAI') .setBackground("#c9daf8") .setRanges([rangea,rangeb]) .build(); var rules2 = sheet.getConditionalFormatRules(); rules2.push(rule2); sheet.setConditionalFormatRules(rules2); var rule22 = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied('=ET(REGEXMATCH($M1;"ABC*");NBCAR($M1)=8)=VRAI') .setBackground("#c9daf8") .setRanges([rangec]) .build(); var rules22 = sheet.getConditionalFormatRules(); rules22.push(rule22); sheet.setConditionalFormatRules(rules22); var rule11 = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied('=ET(B1="";C1="")=VRAI') .setBackground("#b7b7b7") .setRanges([rangea,rangeb,rangec,ranged,rangee,rangef,rangeg,rangeh,rangei,rangej,rangek,rangel,rangem,rangen,rangeo,rangep,rangeq]) .build(); var rules11 = sheet.getConditionalFormatRules(); rules11.push(rule11); sheet.setConditionalFormatRules(rules11); var rule3 = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied('=R:R=AUJOURDHUI()') .setBackground("#ff9900") .setRanges([rangea]) .build(); var rules3 = sheet.getConditionalFormatRules(); rules3.push(rule3); sheet.setConditionalFormatRules(rules3); var rule4 = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied('=J1<>""') .setBackground("#4b88e4") .setRanges([rangej,rangek]) .build(); var rules4 = sheet.getConditionalFormatRules(); rules4.push(rule4); sheet.setConditionalFormatRules(rules4); } Thank you for your help
SpreadsheetApp.flush()after each set conditional format rule statement? Changing format of lots of cells using script takes a lot of time so the script may have to apply the format first and wait before creating the next rule.