0

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

2 Answers 2

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.

12
  • thank you for posting an answer. But its not working. docs.google.com/spreadsheets/d/…
    – ShRa
    Oct 28, 2021 at 17:30
  • 1
    Hmm... try debugging the "paint roller" tool issue by inserting console.log(JSON.stringify(e)); as the first line in the function, use the paint roller tool, and report what script's executions view tells when you click the most recent execution. Oct 28, 2021 at 19:17
  • 1
    Use my code, insert the console.log(JSON.stringify(e)); as instructed, use the paint roller tool, and tell what your My Executions view tells about the most recent execution. Oct 29, 2021 at 9:48
  • 1
    This message appear in recent Execution view Oct 29, 2021, 8:15:33 AM Debug {"authMode":"FULL","changeType":"EDIT","source":{},"triggerUid":"8886766","user":{"email":"kimjo@gmail.com","nickname":"kimjo"}} @doubleunary
    – ShRa
    Oct 29, 2021 at 12:16
  • 1
    I have posted new question please look onto it. @doubleunary webapps.stackexchange.com/questions/159882/…
    – ShRa
    Oct 29, 2021 at 14:36
1

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

  }
}
4
  • 1
    Hey @Tedinoz, the code example is very illustrative, helps understand many concepts, and aids in debugging, but there are 20 API calls before the first conditional, and another 12 API calls before e.changeType is tested. That is quite wasteful and will slow the spreadsheet down. How about at least doing if (e.changeType === "FORMAT") { return; } as the very first thing? Otherwise, a single busy spreadsheet will make the user exceed their trigger total runtime quota limit. Oct 29, 2021 at 6:49
  • Thank you its working fine. But there is a problem it writes date and time only when we select the white default color from Fill Color Option it does not work when we use Paint Format is there any possibility it can fill when we use Paint Format
    – ShRa
    Oct 29, 2021 at 9:44
  • @ShRa "does not work when we use Paint Format" - I'm aware of this. However using the Paint Roller is a changeType:EDIT and your question/script was very specific about changeType:FORMAT. Also, you didn't mention this in your question. I'm aware that you have opened a new question about this and I'll add my contribution if appropriate.
    – Tedinoz
    Oct 31, 2021 at 3:28
  • @doubleunary That's an excellent observation and suggestion; I'll edit my answer accordingly.
    – Tedinoz
    Oct 31, 2021 at 3:29

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.