Insert timestamp when cell background color is changed

Question

I have been using this below code which was working perfectly:

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"));
  }
}

I changed this getActiveRange() line to getRange("C1:C1000") to specify the range to stop it working on whole sheet.

I want to make it work like if changes made between single column then code should be run.

function colorChangeCheck(e) { 
  var sh = SpreadsheetApp.getActive().getSheetByName('Sheet1')
  var color = sh.getRange("C1:C1000").getBackground();
  if(e.changeType == "FORMAT" && color  == "#ffffff"){
    sh.getRange("L"+sh.getRange("C1:C1000")).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM-dd-yyyy HH:mm:ss"));
  }
}

Answer 1

You are apparently running the code on an installable "on change" trigger. The event object contains little information with these events, so to detect which column was re-formatted and only insert a timestamp when it is column C, you will have to use something like this:

/**
* Inserts a timestamp when a cell is formatted by choosing a fill color.
* Runs on an installable 'on change' trigger.
* https://developers.google.com/apps-script/guides/triggers/installable
*
* @param {Object} e The 'on change' event object.
*/
function insertTimestampOnFormatChange(e) {
  // version 1.0, written by --Hyde, 28 October 2021
  const settings = {
    sheetsToWatch: /^(Sheet1|Sheet2|Sheet3)$/i,
    colorsToWatch: /^(#ffffff)$/i,
    columnsToWatch: /^(3)$/i, // column C
    timestampColumn: 12, // column L
    timestampFormat: 'MM-dd-yyyy HH:mm:ss',
  };
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.');
  }
  if (e.changeType !== 'FORMAT') {
    return;
  }
  const sheet = SpreadsheetApp.getActiveSheet();
  if (!sheet.getName().match(settings.sheetsToWatch)) {
    return;
  }
  const range = sheet.getActiveRange();
  if (!String(range.getColumn()).match(settings.columnsToWatch)) {
    return;
  }
  const now = new Date();
  const rowStart = range.getRow();
  const colors = range.offset(0, 0, range.getHeight(), 1).getBackgrounds().flat();
  colors.forEach((color, rowIndex) => {
    if (color.match(settings.colorsToWatch)) {
      sheet.getRange(rowStart + rowIndex, settings.timestampColumn)
        .setValue(now)
        .setNumberFormat(settings.timestampFormat);
    }
  });
}

The first three parameters in settings are regular expressions. You can make the script work with multiple sheets, colors and columns by editing those parameters.

Answer 2

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")

  }
}