CODEWITHSUNDEEP
google-sheets
Aaron Smith
Aaron Smith

Reputation: 37

Google Sheets: highlight certain users' edits

I want to conditionally format a Google Sheet for specific users. Basically, Teacher#1's edits would appear yellow while Teacher#2's are green. Unfortunately the "conditional formatting" tool in Sheets doesn't have this option. Protecting certain cells doesn't work because of the regularity of changes in editors.. I simply want to see editors, not restrict them. Another suggestion should be to use the "Revision History" option.. but this doesn't seem practical for finding a certain editor's changes over several months using several sheets (that several editors have access to).

So the direction I was thinking of going was having a function with an OnEdit trigger that would grab the email (all users must login) and then highlight the last edit.

Here is the URL for a public Google Sheet I duplicated. There, you'll see one of many Weeks sheets. Then there is a control panel with colors that would be associated with each user (teacher).

I copied some script from another StackOverflow forum to get started.

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

  // GET EMAIL ADDRESS OF ACTIVE USER
  var email = Session.getActiveUser().getEmail();
  Logger.log(email);

  //HIGHLIGHT LAST EDITED CELL BASED ON OF ACTIVE USER'S PREDETERMINED COLOR
  sheet.getRange(here is where I need to locate last edited cell).setBackgroundColor("here is where I need to color it based on the "Control Panel" sheet colors");

Hopefully someday Google will allow this kind of Conditional Formatting, but in the meantime it will help my school in Latin America a ton.

Thank you for considering!

Upvotes: 0

Views: 2485

Answers (1)

Sangbok Lee
Sangbok Lee

Reputation: 2228

The first line of your code should be:

function onEdit() {

And the last lines should be something like this: (edit)

  //HIGHLIGHT LAST EDITED CELL BASED ON OF ACTIVE USER'S PREDETERMINED COLOR
  var teacherName = ss.getRangeByName("TeacherName").getValues().map(function(array) { return array[0]; });
  var teacherColor = ss.getRangeByName("TeacherColor").getValues().map(function(array) { return array[0]; });
  var nameIdx = teacherName.indexOf(email);
  if(nameIdx > -1)
    sheet.getActiveCell().setBackground(teacherColor[nameIdx]);

To get it working, you should first run the code in the Script Editor and authorize it. And TeacherName and TeacherColor should be set to named ranges beforehand. Test sheet is here.

Upvotes: 1

Related Questions