You had a working script, using an onChange
trigger, based on the activesheet and activerange. You changed the script so that it applied to a specific sheet and a specific range but the script didn't run.
You can continue to use the onChange
trigger. However you need to test more scenarios before you can test for "FORMAT/white". The additional tests are for:
- Sheet name - equals "Sheet1"
- Column number - equals Column C AND only Column C
- Row Number - greater than or equal to 1 AND less than or equal to 1000 AND a single cell only
This requires setting/creating a number of variables to assist in the evaluations. In the real world, these could be combined in a single statement but I have broken them out in the answer to highlight the effect of each evaluation. I've also included a number of Logger
statements that assist with troubleshooting. These can deleted or "remarked".
function colorChangeCheck(e) {
if(e.changeType != "FORMAT"){
// the change type isn't FORMAT
// stop processing
Logger.log("DEBUG: the change type is not format")
return
}
Logger.log("DEBUG: The change type is FORMAT, so keep going.")
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheetname = "Sheet1"
var sh = ss.getSheetByName(sheetname)
// get Active sheet
var as = ss.getActiveSheet()
Logger.log("DEBUG: sheet name = "+sh.getName()+", the active sheet = "+as.getName())
// get active range
var activerange = as.getActiveRange()
Logger.log("DEBUG: active range = "+activerange.getA1Notation()+", startcolumn:"+activerange.getColumn()+", endColumn:"+activerange.getLastColumn()+", startrow"+activerange.getRow()+", end row:"+activerange.getLastRow())
// set some variables
var colorrange = sh.getRange("C1:C1000")
var coltowatchstart = colorrange.getColumn()
var coltowatchend = colorrange.getLastColumn()
var rowtoWatchstart = colorrange.getRow()
var rowtowatchend = colorrange.getLastRow()
Logger.log("DEBUG: col2watchstart:"+coltowatchstart+", col2watchend:"+coltowatchend+", row2watchstart"+rowtoWatchstart+", row2watchend"+rowtowatchend)
var colorcode = "#ffffff"
var timestampCol = 12
// test for sheet, column and row
if (as.getName() === sheetname) {
// the sheet name is OK
Logger.log("DEBUG: the sheet name matches")
if (activerange.getColumn() === coltowatchstart && activerange.getLastColumn() === coltowatchend){
// the change is in the right column AND only in one column
Logger.log("DEBUG: the edited column is in the right Column")
if (activerange.getRow()>=rowtoWatchstart && activerange.getLastRow()<= rowtowatchend && activerange.getRow() === activerange.getLastRow()){
// the row is in the right range and is a single cell
Logger.log("DEBUG: the row is in the right range")
// get the color
// define range = row/column
var color = sh.getRange(activerange.getRow(),coltowatchstart).getBackground();
// test for background colour
if(color === colorcode){
// the change is FORMAT
// the new background color is white
// update the timestamp in Column L
sh.getRange(activerange.getRow(),timestampCol).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd-yyyy HH:mm:ss"));
}
else{
// either not format or not white
Logger.log("DEBUG: background is NOT white")
}
}
else{
Logger.log("DEBUG: the row is not in the right range")
}
}
else{
// the column doesn't match
Logger.log("DEBUG: the edited column is NOT in the right Column")
}
}
else{
Logger.log("DEBUG: the sheet name does not match")
}
}