Pop up to only show for the doc owner

问题:

I have the following script for a simple pop up on opening the doc. Is there any way for this pop up to only show to the sheet owner and not all the other people the doc is shared with?

function onOpen() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var ui = SpreadsheetApp.getUi();
var Alert = ui.alert("TEXT HERE");
}

My use case for this, is a message visible to only me as the doc owner, which I don't want anybody else with sharing rights, ie clients, to see.

回答1:

• You want to open the dialog only when owner opens the Spreadsheet.
• When the shared users who are not owner open the Spreadsheet, you don't want to open the dialog.

If my understanding is correct, how about this modification?

Modification points:

In this modification, it knows whether owner opened the Spreadsheet by retrieving the email of active user.

• When owner opens the Spreadsheet, the value of Session.getActiveUser().getEmail() is owner's email.
• When users, who are not owner, open the Spreadsheet, no value of Session.getActiveUser().getEmail() is retrieved.

This is used.

Modified script:

function onOpen() {
  var email = Session.getActiveUser().getEmail();
  if (email) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ui = SpreadsheetApp.getUi();
    var Alert = ui.alert("TEXT HERE");
  }
}

Note:

• In the case of your script, it is not required to install the function as the installable trigger of OnOpen.
• Even if the installable trigger of OnOpen is installed, when users open the Spreadsheet, no value of Session.getActiveUser().getEmail() is retrieved. So I think that this can be used. At that time, Session.getActiveUser().getEmail() has owner's email.

Reference:

• getActiveUser()

If I misunderstood your question and this was not the result you want, I apologize.

Edit 1:

If all users install the trigger, Session.getEffectiveUser().getEmail() returns the value. So the value is required to be compared with that of the registered users. In this sample script, it supposes as follows.

• Users of "email1" and "email2" install the installable trigger of OnOpen by each user.

Sample script:

var users = ["email1", "email2",,,]; // Please set emails of users who can open the dialog.
var email = Session.getEffectiveUser().getEmail();
if (users.indexOf(email) > -1) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  var Alert = ui.alert("TEXT HERE");
}

Edit 2:

When I tested this situation by 3 users just now, I noticed new issue.

At first, it supposes as follows.

• Spreadsheet is shared and used by 3 users who are owner, user1 and user2.
• Function is run onOpen_sample(){} when the Spreadsheet is opened.
• When owner and user1 can run the script and open a dialog ui.alert("TEXT HERE").

In this situation, the following results are obtained.

1. Owner installed onOpen_sample() as a trigger of OnOpen.
   • Session.getActiveUser().getEmail() returns owner's email.
   • Session.getEffectiveUser().getEmail() returns owner's email.
2. User1 installed onOpen_sample() as a trigger of OnOpen.
   • Session.getActiveUser().getEmail() returns no value.
   • Session.getEffectiveUser().getEmail() returns user1's email.

Here,

1. User2 doesn't install onOpen_sample() as a trigger of OnOpen.
   • Session.getActiveUser().getEmail() returns no value.
   • Session.getEffectiveUser().getEmail() returns user1's email.
     • This is not owner's email.

And furthermore,

1. User2 installed onOpen_sample() as a trigger of OnOpen.
   • Session.getActiveUser().getEmail() returns no value.
   • Session.getEffectiveUser().getEmail() returns owner's email.
     • This is not user2's email.

And also, when I installed the trigger of OnOpen several times, even if user1 and user2 install the trigger, Session.getEffectiveUser().getEmail() got to return only owner's email.

By these, all users can run the script and open the dialog. I thought that your issue might be this. I think that this might be a bug. So I would like to report this to the issue tracker.

From this situation, in the current stage, it was found the following result.

1. Only owner can run the script and open the dialog.
2. Specific users cannot run the script and open the dialog.

I apologize that your issue was not resolved. If I found the workaround, I would like to report here.