Google-sheets – Trigger onOpen() only for first instance of the sheet being opened for a user

google sheetsgoogle-appsgoogle-apps-script

I have a sheet that I want multiple users in my organisation to use as a dashboard, with the data displayed changing depending on the logged-in user. Everything works fine, and I have a simple script triggered onOpen that populates the current user's email address into the sheet and all data filtering is done based on it:

function getEmail () {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  activeSheet.getRange(3, 3).setValue(Session.getActiveUser());
  Logger.log(Session.getActiveUser());
}

The problem is that because the trigger is set to onOpen, if one user opens the sheet and, while the sheet is still open for the first user, a second user then opens the sheet, the onOpen trigger runs not just for the second user who just opened the sheet, but also for the first user, who's data now changes to display the data only meant for the second user.

In other words, the onOpen trigger is executed each time the sheet is opened for all users with the sheet currently open, not just for the user who just opened the sheet.

Is there a way to ensure the onOpen trigger is only executed for a user the first time that user opens the sheet – not any user? If not – can anyone think of a solution to this other than having a different sheet for each user?

Best Answer

As mentioned in the comments while onOpen is run from a the users session the changes made to cell values by user B will be visible to user A whether they are made by manual entry or an onEdit() trigger. (Imagine the user session as a window onto a garden and the cell values as the plots in the garden. If User B has the gardener pick the oranges from the tree in the second plot, those oranges will be gone from the tree no matter what window you look at the garden from)

If you are trying to prevent user A from seeing the user B data for a security reason the method you are trying to implement has a number of flaws beyond the ones that make it not work.

The only way to securely filter the data is with a Spreadsheet for each user using =importrange() functions and the user only having view rights (either at the sharing level or the cell/sheet protection level).

If your goal is to just have a convenience filtering for each user and want it to be automated you could create a separate sheet for each user with their range filtering/formatting and have the onOpen take them to their sheet with a SpreadsheetApp.setActiveSheet(sheet) which is one of the few things you can change about a user session. They would still be able to other peoples sheets by manual clicking on them. (While you can hide and protect a sheet to prevent it from being looked at, hiding is a sheet level change not user session change so if user B can see their sheet so can user A)

As mentioned in the comments a filter view may be a better solution, however i have found them a bit clunky and the method i describe above allows a bit more freedom in how you could format the data.

Related Topic