I am using below Script which i want to work like whenever Sheet row Color changes from Purple
to White Default Color
then it should write Date
and time
to column L
.
But it is not working accordingly. It should not write Date to column L
when cell is being edited row is being added. It should work just when Row color is change from Purple
to White Default Color
Any help will be appreciated.
- Then, you can modify your code as following to perform an action only when the change event was a format change and when the new color of the range is white:
function colorChangeCheck(e) {
var sh = SpreadsheetApp.getActive().getActiveSheet();
var color = sh.getActiveRange().getBackground();
if(e.changeType == "FORMAT" && color == "#ffffff"){
sh.getRange("L" sh.getActiveRange().getRow()).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd-yyyy HH:mm:ss"));
}
}
Important:
This snippet does not allow you to know what the previous color was and either the change event was a color change or another formatting change (e.g. making the text bold).
The implementation of this functionality would be much more complicated, since you would need to store the background color of all cells of your sheet within the script properties, so you can compare the old color to the new color.