Home > Enterprise >  IF Row color Changes from Purple to White Default Color then write
IF Row color Changes from Purple to White Default Color then write

Time:10-29

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.

enter image description here

  • 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.

  • Related