google spreadsheet set row background color based on note

Question

I have a Google spreadsheet that has client contact information in it.

I am trying to find a way to highlight edits users make automatically without them having to change the row color to say that they edited it.

What I was thinking of doing is when a user edits a cell is to add a note to that cell with the users email address. Then have it check to see if the note that was added to that cell equals a certain email address to set the background color of the row to whatever I specify.

The below adds a note of the users email address to the edited cell and sets the background color of the edited cell to red but not the entire row.

Any ideas on how to do this and if there is a better way to accomplish what I am trying to do.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var cell = ss.getActiveCell();
  var range = ss.getActiveRange();
  var note = cell.getNote();
  var user = Session.getUser();

  note = user;
  cell.setNote(note);



  if(note = "user@email.com") {
    range.setBackgroundRGB(255, 0, 0);
  }

};

Answer 1

To get the entire row, assume it starts at 1, and use the Range.getLastColumn() method to find the right-extent of the row. (If your rows are staggered, this may highlight empty cells at either end - checking for that, though, will slow down the function further.)

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();

  var cell = ss.getActiveCell();
  var range = sheet.getRange(cell.getRow(),1,1,sheet.getDataRange().getLastColumn());
  var note = cell.getNote();
  var user = Session.getUser();

  note = user;
  cell.setNote(note);

  if(note = "user@email.com") {
    range.setBackgroundRGB(255, 0, 0);
  }
};

You also asked about a "better way". Here's a variation with a few 'improvements'.

• It uses the trigger Event, rather than relying on being a container-bound script. (See Understanding Events.) That makes this more portable, sure, but it also means that you can eliminate some (slow) calls to Spreadsheet services, since you're handed information for free.

  To test this function, use the technique described in How can I test a trigger function in GAS?.

• Your original script contained an if-then to check which user had made a change, the beginning of a code block that would grow as you added more users. This one takes a data-driven approach, with const userColors. Then, we can check whether the user is known by an in comparison, and act on that.

  You could further improve it by managing the user colors in the spreadsheet, or elsewhere - and even having it dynamically learn new users and assign them their own colors. At any rate, this allows you to easily add more user & color combinations, without needing to change the logic in the function.

• In onEdit() functions, you should be very concerned about performance. (This example is so short, it's not really an issue, but still a good habit.) This version has a couple of changes to address that. First, there is a check to see if we need to set color at all - if the cell being edited was last edited by the same user, there's no need to do anything else. Second, a number of operations that invoke Spreadsheet Services have been moved so they only execute when needed.

Updated script:

function onEdit(event) {
  const userColors = {
    'user1@email.com' : 'red',
    'user2@email.com' : 'blue',
    'user3@email.com' : '#ff00ff'
  };

  var note = event.range.getNote();
  var user = new String(Session.getUser());

  // update note and color if new editor
  if (note != user) {
    var sheet = event.range.getSheet();
    var range = sheet.getRange(event.range.getRow(),1,1,sheet.getLastColumn());

    event.range.setNote(user);
    
    if (user in userColors) range.setBackground(userColors[user]);
    // don't color unknown users
    else range.setBackground('white');
  }
};

Answer 2

I've also taken a look. The getLastColumn will give you the last column which contains data on the entire sheet, ignoring empty rows beyond that, see here. If you want the entire row, you should use getMaxColumns(), see here

Checking for the value of the note after running setNote is redundant. eg

var i = 1;
if(i === 1) { dosomething(i) }

There are a few things that came to mind when thinking about this feature is a spreadsheet.

• Different users having different colors.
  • I would have a color selection option and store that in ScriptDB
• Clearing the colors on "approval" or some other condition
  • I would use a menu option that is visible only to the correct users
• Keeping track of users notes
  • I would have a key which is appended to the end of any existing note (and overwritten if the key already exists). Then, I would use regex note.search(/editor:.*?$/i) to check for the key later and overwrite it.

---

Here's my quick version of the onEdit function.

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = SpreadsheetApp.getActiveRange();

  var rowNum = cell.getRow();
  var row = sheet.getRange(rowNum, 1, 1, sheet.getMaxColumns());

  var user = Session.getActiveUser();

  cell.setNote("Edited by: " + user.getEmail());
  row.setBackground("red");
}