我试图比较来自两个不同列的数据,当它们不匹配时,我希望第6列中的数据变为红色,如果变回匹配,则变回黑色。
我不想使用菜单中的条件格式,因为我不希望人们能够关闭它。
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var activeCell = sheet.getActiveCell();
var col = activeCell.getColumn();
var row = activeCell.getRow();
var col6 = activeCell.getColumn( col == 6 && row >=5 && row <=55 && sheet);
var col14 = activeCell.getColumn( col == 14 && row >=5 && row <=55 && sheet);
var first = get.Value(col6);
var second = get.Value(col14);
//Start Time Function
if (col == 1 && row>=5 && row<=55 && sheet.getRange(row,col).getValue() == ""){
sheet.getRange(row,1,1,sheet.getMaxColumns()).clearContent();
}
else if (col == 1){
sheet.getRange(row,col+11).setValue(new Date()).setNumberFormat('hh:mm:ss');
sheet.getRange(row,col+13).setValue(new Date()).setNumberFormat('hhmm');
sheet.getRange(row,col+5).setValue(new Date()).setNumberFormat('hhmm');
sheet.getRange(row,col+3).setValue(new Date()).setNumberFormat();
}
//End Time Function
if (col == 8 && row>=5 && row<=55 && sheet.getRange(row,col).getValue() == ""){
sheet.getRange(row,7,1).clearContent() && sheet.getRange(row,13,1).clearContent() && sheet.getRange(row,15,1).clearContent();
}
else if (col == 8) {
sheet.getRange(row,col+5).setValue(new Date()).setNumberFormat('hh:mm:ss');
sheet.getRange(row,col+7).setValue(new Date()).setNumberFormat('hhmm');
sheet.getRange(row,col-1).setValue(new Date()).setNumberFormat('hhmm');
}
// Data Valadation Start Time
// Confirm data in column 6 matches column 14, if different set column 6 font to red.
if(first != second){
var cell = sheet.getRange(first);
cell.setFontColor("red");
}
else if(first == second){
var cell = sheet.sheet.getRange(first);
cell.setFontColor("black");
}
}
工作代码。
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var activeCell = sheet.getActiveCell();
var col = activeCell.getColumn();
var row = activeCell.getRow();
//Start Time Function
if (col == 1 && row>=5 && row<=55 && sheet.getRange(row,col).getValue() == ""){
sheet.getRange(row,1,1,sheet.getMaxColumns()).clearContent();
}
else if (col == 1){
sheet.getRange(row,12,1).setValue(new Date()).setNumberFormat('hh:mm:ss'); //real time
sheet.getRange(row,14,1).setValue(new Date()).setNumberFormat('hhmm').setNumberFormat("@");
sheet.getRange(row,6,1).setValue(new Date()).setNumberFormat('hhmm').setNumberFormat("@");
sheet.getRange(row,4,1).setValue(new Date()).setNumberFormat(); //Date Row
} // END - Start Time Function
//End Time Function
if (col == 8 && row>=5 && row<=55 && sheet.getRange(row,col).getValue() == ""){
sheet.getRange(row,7,1).clearContent() && sheet.getRange(row,13,1).clearContent() && sheet.getRange(row,15,1).clearContent();
}
else if (col == 8) {
sheet.getRange(row,13,1).setValue(new Date()).setNumberFormat('hh:mm:ss'); // real time
sheet.getRange(row,15,1).setValue(new Date()).setNumberFormat('hhmm').setNumberFormat("@");
sheet.getRange(row,7,1).setValue(new Date()).setNumberFormat('hhmm').setNumberFormat("@");
} // END - End Time Function
// Data Valadation - Start Time
var firstF = sheet.getRange(row,6,1);
var secondN = sheet.getRange(row,14,1);
if(firstF.getValue() != secondN.getValue()){
var cell = firstF;
cell.setFontColor("red");
}
else {
var cell = firstF;
cell.setFontColor("black");
} // END - Data Valadation - Start Time
// Data Valadation - End Time
var firstG = sheet.getRange(row,7,1);
var secondO = sheet.getRange(row,15,1);
if(firstG.getValue() != secondO.getValue()){
var cell = firstG;
cell.setFontColor("red");
}
else {
var cell = firstG;
cell.setFontColor("black");
} // END - Data Valadation - End Time
} // END - function onEdit
- 注意不要混淆范围和值
- 此外,您似乎使用了应用程序脚本中不存在的方法方法/不能与参数一起使用
- 请查看getValues((和getColumn((的文档
基于您的代码修改的工作样本:
function onEdit(e) {
var activeCell = e.range;
var sheet = e.range.getSheet();
var col = activeCell.getColumn();
var row = activeCell.getRow();
var first = sheet.getRange(row, 6);
var second = sheet.getRange(row, 14);
if(first.getValue() != second.getValue()){
var cell = first;
cell.setFontColor("red");
}
else {
var cell = first;
cell.setFontColor("black");
}
}