Search code examples
google-apps-scriptgoogle-sheetstriggersgoogle-oauthgoogle-workspace

Installable trigger needs to grab email of user who edited, but is only accessing mine

So I've got a spreadsheet that lists work to be done via an imported range from another file. Users have a dropdown of validated data that when they select the status of this work it searches the other sheet for the work they've selected and updates the status of this work. It's super simple, and works for everyone I've given editing privileges to.

I'd like alter it to also log the email of the user who edited the work status and therefore ran the script (we are all part of the same workspace domain). I've gotten it to pull my email and place it where required with repetition, but I cannot get it to access anyone else's. I tried deploying it, although I'm not entirely sure I understand how that works. I've looked into authorizing it, but the only place I can find to alter authorization is via the appscript.json in the editor, but that isn't showing the permissions that the documentation says to edit/add so I'm a little lost as to how to authorize this.

Not sure if it matters, but this script is attached to the sheet it picks up the edit from. I don't know if that means the sheet permissions need to change or what.

Here is the entirety of the code, minus identifying URL's/ID's:

function onEdit(e) {



var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //shortens calling to the current sheet

  var dataSheet = SpreadsheetApp.openById("datafileID").getSheetByName("Data"); //shortens calling to the data file

  const status = e.value; //validated user input

  var erange = e.range; //range of edited cell
  var ecolumn = erange.getColumn(); //range column of edited cell
  var erow = erange.getRow(); //range row of edited cell

  var snRow = erow; //identifies what row to look for the store number
  var snColumn = ecolumn-2; //identifies what column to look for the store number
  var sn = sheet.getRange(snRow, snColumn).getValue(); //declares the store number as variable

  var user = e.user; //declares user as variable

  if (!e.range.isBlank()) { //searches data sheet for store and updates status and user

    var column = dataSheet.getRange("G:G").getValues();
    var uRow;
      for (var i = 0; i < column.length; i++){
        if (column[i][0] === sn) {
        uRow = i+1;
        break;
      }
    }

    dataSheet.getRange(uRow,6).setValue(status)
    
    dataSheet.getRange(uRow,5).setValue(user)


  }

 

  sheet.getActiveCell().clearContent();
}
Solution

  • onEdit is a reserved name for simple triggers, you should not use call this function from an edit / change installable trigger because there will be two executions running in parallel.

    When using an simple or installable trigger with Google Workspace accounts from the same domain, e.user should return the User object representing de active user.

    As the script is working for your account there is no need of additional permissions.

    As the script is not working, try the following:

    1. Delete the installable trigger
    2. Change the name of the function (i.e. respondToEdit)
    3. Create the installable trigger again pointing to the new function name.
    4. Double check that the spreadsheet sharing permissions are set to editors from your Google Workspace domain only.

    References