2

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

2 Answers 2

5

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.

6
  • I think e.value === undefined && e.oldValue === undefined is a key element in your answer and, considering the OP's previous questions and comments, it might be worthy that you highlight it and to add the limitations (only works for using the Paint Paste on single cells)
    – Rubén
    Oct 30, 2021 at 17:37
  • The pattern will work with ranges of any size, not just ranges of a single cell. Oct 30, 2021 at 22:51
  • Are you aware that e.value and e.oldValue are always undefined when e.range has more than one cell?
    – Rubén
    Oct 30, 2021 at 23:14
  • Yes. The purpose of the e.value test is to improve spreadsheet performance. The insertTimestampOnFormatChange(e) already includes the required additional tests. Oct 31, 2021 at 7:24
  • Sorry for being late @doubleunary I have tried this function along with previous one but still when i use paint format it does not work. Doesn't paste Timestamp
    – ShRa
    Nov 2, 2021 at 10:42
1

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

8
  • Thank you for taking the interest in it and posted the answer. I have included your piece of script and it gives this message in execution imgur.com/BvYvhcy and imgur.com/R0Ly49W
    – ShRa
    Oct 29, 2021 at 15:53
  • Thanks. There was a typo (the second screenshot error message shows that). Answer fixed.
    – Rubén
    Oct 29, 2021 at 16:03
  • I am unable to get that one thing why Format Paint is unable to write Timestamp whereas scripts works for Fill Color
    – ShRa
    Oct 29, 2021 at 16:11
  • Do you already understand what is a JavaScript object and why this concept is relevant in Google Apps Script, what is a Google Apps Script trigger, and what is a Google Apps Script trigger event object?
    – Rubén
    Oct 29, 2021 at 16:14
  • Yes i tried to understand the JS objects and triggers from Dev.Mozilla.org. But its really hard to get all the concepts in single day. and i want to ask that On change event does not support the Paint Format but supports to fill Color. is there any possibility to add as well changeType for Edit
    – ShRa
    Oct 29, 2021 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.