Search code examples
google-apps-scriptgoogle-sheetstriggers

Highlight rows when any edit/change is made to a sheet

I am looking for a Google Apps Script to highlight rows when any edit/change is made to a sheet.

I tried the below code, but it did not work. Does anyone have any ideas?

function onEdit() {



var sheetsToWatch = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5', 'etc.'];

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = sheet.getActiveCell();
var sheetName = sheet.getName();
var matchFound = false;

for (var i = 0; i < sheetsToWatch.length; i++) {
if (sheetName.match(sheetsToWatch[i])) matchFound = true;
  }
if (!matchFound) return;

var rowColLabel = 
sheet.getRange(cell.getRow(),cell.getColumn()).setBackground('#faec15'); // #faec15 
 // set backgorund color in yellow, 
  // you can do any color 

  }
Solution

  • From I am looking for a Google Apps Script to highlight rows when any edit/change is made to a sheet., I believe your goal is as follows.

    • You want to change the background color of the edited row when a cell is edited.

    Modification points:

    • In your showing script, by sheet.getRange(cell.getRow(),cell.getColumn()).setBackground('#faec15'), the background color of only one cell (edited cell) is changed. I guessed that this might be the reason for your current issue of I tried the below code, but it did not work..

    If this modification point is reflected in your script, how about the following modification?

    Modified script:

    function onEdit() {
  var sheetsToWatch = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5', 'etc.'];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var sheetName = sheet.getName();
  var matchFound = false;
  for (var i = 0; i < sheetsToWatch.length; i++) {
    if (sheetName.match(sheetsToWatch[i])) matchFound = true;
  }
  if (!matchFound) return;
  sheet.getRange(cell.getRow(), 1, 1, sheet.getMaxColumns()).setBackground('#faec15'); // Modified
}

    • In this case, when a cell in the sheets sheetsToWatch is edited the background color of the edited row is changed.

    • From your showing script, I guessed that you might have wanted to directly run your script with the script editor while the script is automatically run with the simple trigger. So, I proposed the above modification.

    • But, if you are not required to directly run your script with the script editor, how about using the event object as follows? When the event object is used, the process cost can be reduced a little. Ref (Author: me)

      function onEdit(e) {
  var sheetsToWatch = ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5', 'etc.'];
  var { range } = e;
  var sheet = range.getSheet();
  if (!sheetsToWatch.includes(sheet.getSheetName())) return;
  sheet.getRange(range.rowStart, 1, range.rowEnd - range.rowStart + 1, sheet.getMaxColumns()).setBackground('#faec15');
}
      • In this modification, for example, when multiple cells are edited by the copy and paste, the background color of those rows is also changed.

    • If you want to change the background color of the edited row in the data range, please modify sheet.getMaxColumns() to sheet.getLastColumn()

    References: