What trigger to use to update cells when inputted data is copied and pasted into spreadsheet? (Google Sheets/ apps script)

Question

So i have an OnEdit function that when the user chooses a color from the list, the cells background color becomes that selected value.

This works fine, but I am trying to get the function to update the cells background color immediately when data is copied and pasted into the spreadsheet as well as when a user edits a cell. Currently when you copy and paste data into the speadsheet, the code i wrote to change the cells background colour does not trigger as nothing has been "edited". Im trying to get the code to work for OnEdit and when data is copied and pasted into the sheet. Any ideas would be greatly appreciated! Thanks!

This is what I have so far..

// Validates the colours using the above map and changes the cell background to the input colour..
function onEdit(e) {
  try {
    var range = e.range;
    var sheet = range.getSheet();
    var tabColumnData = getColumnData(sheet);
    if(!tabColumnData.hasOwnProperty('Color')) return;
    var col = range.getColumn();
    
    if (col == tabColumnData['Color'] + 1) {
      var color = e.value;
      if(validateCellValue(color) || COLOR_TO_HEX_MAP.hasOwnProperty(color)) {
        range.setBackground(color);
      } else {
        range.clear();
        SpreadsheetApp.getUi().alert('Please select a color from the list or a hex color code.');
      }
    }
    
    if (col == tabColumnData['Name'] + 1) {
      setDataValidation(createColorList());
    }
  } catch(e) {
    Logger.log(e);
  }
}


// Sets the data validation when the spreadsheet is opened.
function onOpen(e){
   setDataValidation(createColorList());
}

You have to assume that e.range is more than one cell and then iterate through each row and column in the range. e.range.rowStart is the top row, e.range.rowEnd is the bottom row. e.range.columnStart is the left most column e.range.columnEnd is the rightmost column. It is a range so getWidth() and getHeight() methods apply. And e.value only applies to the upper left corner cell. — Cooper

Jacques-Guzel Heron Jacques-Guzel Heron · Accepted Answer · 2019-12-16T09:06:28

After reading your question, I assume the following:

You want to detect when a group of cells are edited.
When the modification is detected, you want to change the colour of each cell to match its content.

If my assumptions are correct, you can use the following example to fulfill your requests:

CODE

function onEdit(e) {
  var firstRow = e.range.getLastRow() - e.range.getHeight() + 1;
  var firstColumn = e.range.getLastColumn() - e.range.getWidth() + 1;
  var values = e.range.getValues();
  for (var i = 0; i < e.range.getHeight(); i++) {
    for (var j = 0; j < e.range.getWidth(); j++) {
      try {
        e.source.getActiveSheet().getRange(firstRow + i, firstColumn + j)
          .setBackground(values[i][j]);
      } catch (e) {
        // You might want some error handling or logging here
      }
    }
  }
}

BEHAVIOUR

The code will run each time that a cell or group of cells are edited. When the edition is detected, the code will iterate each cell and try to change its background colour to the content of the cell.

OBSERVATIONS

This code works for every sheet of the spreadsheet. To make it exclusive to a sheet, you should use getSheet methods [as getSheetByName()].
Due to onEdit trigger limitations, this code won't run if the modification is made by a script (in opposition to a user edit).
The background colours are limited until the ones defined on CSS Colour 3. You can check the full list here.

ALLUSIONS

Please, don't hesitate to write me back any additional doubts or request me further clarifications.

UPDATE

The previous code will iterate only over edited cells, not over all the sheet. This behaviour will occur on all the spreadsheet. If you want to reduce your effects over a specific range (like the categorycolor column) you need a conditional statement to check if the modified cells are in the desired range.

The first step to solve this situation is to create a named range as described here. In this example I used the name ColourRange, but you can change the code to set up your own named range. Take this as one of the possible solutions:

function onEdit(e) {
  // Colour range
  var colourRange = SpreadsheetApp.getActiveSheet().getRange('ColourRange');
  var firstColourRow = colourRange.getRow();
  var lastColourRow = colourRange.getLastRow();
  var firstColourColumn = colourRange.getColumn();
  var lastColourColumn = colourRange.getLastColumn();

  // Modified range
  var firstRow = e.range.getLastRow() - e.range.getHeight() + 1;
  var firstColumn = e.range.getLastColumn() - e.range.getWidth() + 1;
  var values = e.range.getValues();

  for (var i = 0; i < e.range.getHeight(); i++) {
    for (var j = 0; j < e.range.getWidth(); j++) {
      var modifiedCell = e.source.getActiveSheet().getRange(firstRow + i,
        firstColumn + j);
      if (modifiedCell.getRow() >= firstColourRow && modifiedCell
      .getLastRow() <= lastColourRow && modifiedCell.getColumn() >=
        firstColourColumn && modifiedCell.getLastColumn() <= lastColourColumn) {
        try {
          e.source.getActiveSheet().getRange(firstRow + i, firstColumn + j)
            .setBackground(values[i][j]);
        } catch (e) {
          // You might want some error handling or logging here
        }
      }
    }
  }
}