How can I detect the user email address and paste it in a cell (in Google Sheet)?

Question

I need to detect the current spreadSheet user email address in a Google Sheet, then I will compare it to my limited users list to prevent my sheet from getting shared with everybody.

Answer 1

There are a couple of things that can be helpful:

• To get the active user in the Google Sheet, you can use the following script and add a trigger to run that function when a user opens the sheet. Then you can compare the result with your users list.

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("A2").setValue(Session.getActiveUser());
}

Note: This option has some limitations, according to getActiveUser() documentation , due to security restrictions this will work if users are within the same Google Workspace domain.

• If your file is shared with anyone with the link, the best is to share it only with the users you want, then you can prevent those users from sharing your files:

  1. Open the file in Google Sheets.
  2. Click "Share".
  3. At the top, click "Settings".
  4. Uncheck "Editors can change permissions and share".

• With Apps Script you can get the list of users that have access to your file, compare it with your limited users list and then remove the users that are not in your list. You can try the following sample:

function permissions() {
  var driveFileID = "fileID"
  var file = DriveApp.getFileById(driveFileID);
  var users = Drive.Permissions.list(driveFileID).items;
  var limitedUsers = ["admin@gmail.com", "example1@gmail.com", "example2@gmail.com"];
  var listEmails = [];

  for (var i = 0; i< users.length; i++){
    let email = users[i].emailAddress;
    listEmails.push(email)
  }

  var notFound = listEmails.filter(x=> !limitedUsers.includes(x) & x != null);

  for (var a = 0; a < notFound.length; a++){
    file.revokePermissions(notFound[a])
    Logger.log("Removed user: "+ notFound[a])
  }
}

Answer 2

Use the Share Spreadsheet feature to add specific users to your sheet.

You can then limit whether the users granted access are able to share with others by following these steps (see the “Limit how your files are shared” > “Prevent others from sharing your files” section)