How to insert timestamp when using either of the Fill Color tool or the Paint Format tool

Question

I have been using the code below to insert a timestamp in column L when a cell fill color in column C is changed to white.

The code is working well, but while it works when I choose the white color with the Fill Color paint bucket tool, it does not work when I use the Paint Format paint roller tool.

How do I make the code work with both of these tools?

doubleunary's answer

Google Sheet

/**
* 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);
    }
  });
}

Answer 1

Try catching paint roller events with this simple trigger:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  // version 1.0, written by --Hyde, 30 October 2021
  if (!e) {
    throw new Error('Please do not run the onEdit(e) function in the script editor window. It runs automatically when you hand edit the spreadsheet.');
  }
  if (e.value === undefined && e.oldValue === undefined) {
    e.changeType = 'FORMAT';
    insertTimestampOnFormatChange(e);
  }
}

The code detects whether the edit is possibly because of a paint roller event, and modifies the event object e before passing it to insertTimestampOnFormatChange(e).

If you only ever use the paint roller tool, and never the fill color paint bucket tool, you can delete the on change trigger you have installed. To support both tools, you will have to keep both the simple trigger and the installable trigger.

Answer 2

The following code lines

  if (e.changeType !== 'FORMAT') {
    return;
  }

make that the execution to stop when the value of the changeType is different than FORMAT. It's very likely that the value of changeType when using the paint format tool is equal to OTHER. You could confirm this by your self by including console.log(JSON.stringify(e,null,' ')) before return. I.E.

  if (e.changeType !== 'FORMAT') {
    console.log(JSON.stringify(e,null,' '));
    return;
  }

NOTE: Comment from the OP to doubleunary's answer

This message appear in recent Execution view Oct 29, 2021, 8:15:33 AM Debug {"authMode":"FULL","changeType":"EDIT","source":{},"triggerUid":"8886766","user":{"email": redacted ,"nickname": redacted}} @doubleunary – ShRa 3 hours ago

Be aware if that you remove the above if statement or if you change the if condition to not stop the script execution when changeType be equal to 'EDIT', the section of the code after it always / when any edit be made respectively, will be executed, not only when the cell color be changed.

One solution might be to keep the cell colors stored somewhere then an 'EDIT' occurs, compare the corresponding stored cell color with the current color of the edited cell / range, then update the stored cell colors in preparation for future script executions. For this you might have a copy of the sheet, use the Properties Service among other alternatives.

Resources

• https://developers.google.com/apps-script/guides/triggers/installable
• https://developers.google.com/apps-script/guides/triggers/events
• https://developers.google.com/apps-script/guides/logging