Why is the conditional formatting not working with my on edit function?

189 views
Skip to first unread message

Jake Shaffer

unread,
Oct 26, 2023, 5:24:49 PM10/26/23
to Google Apps Script Community
When running the on edit function the sort data works fine, but my conditional formatting does nothing. I'm also trying to apply the sort data and conditional formatting to "sheet4" independently of "sheet3".  When editting on sheet4 nothing happens. Any advice for why these functions aren't working? I'm very new at this and would greatly appreciate any and all help!


My Code:
function SortData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet3");
var lastRow = sheet.getLastRow();
var myRange = sheet.getRange(2,1,lastRow-1,4)

myRange.sort([{column:4,ascending:true}, {column:3,ascending:true}])
}


function onEdit(e){

var row = e.range.getRow();
var col = e.range.getColumn();
var curSheet = e.source.getActiveSheet().getSheetName();
var value = e.range.getValue();

if(row != 1 && curSheet == "Sheet3" ){
SortData(); addConditionalFormatting();
}
}


function addConditionalFormatting() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet3");
var range = sheet.getRange("A2:C");
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo("TRUE")
.setBackground("#EFEFEF")
.setFontColor("#CCCCCC")
.setStrikethrough(true)
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

var sheet4 = ss.getSheetByName("Sheet4");
var range4 = sheet4.getRange("A2:C");
var rule4 = SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo("TRUE")
.setBackground("#EFEFEF")
.setFontColor("#CCCCCC")
.setStrikethrough(true)
.setRanges([range4])
.build();
var rules4 = sheet4.getConditionalFormatRules();
rules4.push(rule4);
sheet4.setConditionalFormatRules(rules4);
}





Daniel Paes

unread,
Oct 27, 2023, 12:22:42 PM10/27/23
to Google Apps Script Community
the code its correct, but the sheet4 have some protection? because depending of the error, if the script run in the name of the user who is using the script, if the sheet4 still protected, can be denied the acess to change the cells.

Jake Shaffer

unread,
Oct 27, 2023, 3:07:49 PM10/27/23
to Google Apps Script Community
Sheet4 is not protected. I have no idea why none of the functions are working with sheet4. Do you have any suggestions on how to fix it? Do you have any idea why the conditional formatting is not working on sheet3?Any suggestions on how to fix that too? Thank you so much for responding and trying to help!
Reply all
Reply to author
Forward
0 new messages