javascriptgoogle-sheetsgoogle-apps-script

Changing cell background on user edit automatically


I'm trying to make a script that automatically change the background color of cells depending on which user edits it. I currently have this code but it doesn't do anything. We need to know which user edits or creates a new entry.

I just need the cells in the first column to change background color. This is for a Data base I'm working with 4 other people

function checkEdits() {
    var s = SpreadsheetApp.getActiveSheet();

    //checks that we're on the correct sheet
    if (s.getName() == "Sheet1") { 
        var r = s.getActiveCell();

        //checks the column
        if (r.getColumn() == 1) {
            var email = r.Session.getActiveUser().getEmail();

            if (email == "XXXXX@gmail.com") {
                r.setBackground('#dbbf94');
            }

            if (mail == "YYYYYY@gmail.com") {
                r.setBackground('#94dbab');
            }

            if (mail == "ZZZZZZ@gmail.com") {
                r.setBackground('#a2bfdf');
            }

            if (mail == "MMMMMMMM@gmail.com") {
                r.setBackground('#cf9ce5');
            }

            if (mail == "NNNNNNN@gmail.com") {
                r.setBackground('#dfa2b1');
            }
        }
    }
}

I expect that when a user edit a cell un Column A, the same cell changes its background to the specific user color


Solution

  • You may want to try this.

    function onEdit(e){
      if (e.range.getSheet().getSheetId() == e.source.getSheetByName("Sheet1").getSheetId() && e.range.getColumn() == 1.0){
        switch (e.user){
          case "XXXXX@gmail.com":
            e.range.setBackground('#dbbf94');
            break;
          case "YYYYYY@gmail.com":
            e.range.setBackground('#94dbab');
            break;
          case "ZZZZZZ@gmail.com":
            e.range.setBackground('#a2bfdf');
            break;
          case "MMMMMMMM@gmail.com":
            e.range.setBackground('#cf9ce5');
            break;
          case "NNNNNNN@gmail.com":
            e.range.setBackground('#dfa2b1');
            break;
          default:
            e.range.setBackground('white');
        }
      }
    }