1

I'm very new to scripts so please bear with me. I want to make a condition where if a cell color is changed, a new timestamp is created. So a cell will start from white (unused) and change to ~3 different colors depending on status (unread, in progress, complete, etc.) I looked at the forums and a user doubleunary wrote something awhile back that worked with the cell background fill function (which is what I need), but it doesn't seem to work with my sheet/script. Can anyone point out what I'm doing wrong? Script below:

/**
* 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: /^(1)$/i, // column A
    timestampColumn: 2, // column B
    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);
    }
  });
}

Link to Google sheet here:

https://docs.google.com/spreadsheets/d/1icX4qQep4mzw4QmMIWcDbS_sBEhREu2LFuwuS4FPhPY/edit?usp=sharing

I want it to update whenever the color changes from white to a color, and subsequently if/when the color is changed again, I want the time stamp to update. Do I have to define each color for it to watch for?

1

1 Answer 1

1

The execution log of the script project contains this error:

Error: Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.

The error message means that you should set up an installable trigger to automatically run the code when column A is modified, as specified in Insert timestamp when cell background color is changed.

Do I have to define each color for it to watch for?

Yes. Modify the colorsToWatch parameter. It is a regular expression. To match any color except white, use /[^f]/i. To match any color change, including to white, use /./.

2
  • Thanks @doubleunary, I'm not sure if I'm understanding, are you saying that the script looks right and theoretically when I hand edit my sheet it should do the time stamp procedure? Because it isn't doing so at the moment, nothing is changing. Or are you saying my 'e' trigger isn't set up properly?
    – David Yang
    Sep 28, 2023 at 17:02
  • You must set up an installable "on change" trigger. The code will not run without the trigger. You can verify whether it runs by looking at the execution log. Sep 28, 2023 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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