I am trying to show a pop-up when a specific mentioned user opens the google sheet. The main idea is to check a specific column for an email and compare it with the email of the person who opened the google sheet. If the email matches, the pop-up should show on the screen.

function onOpen() 
{
  var result = ?
  var email = Session.getEffectiveUser().getEmail();
  var ui = SpreadsheetApp.getUi();
  if(result = email) {
    ui.alert('Important Notice');
  }
}

I am here so far, I need to get the email from a specific column in the result variable. Let's say column A has the email addresses and when the email address matches the user who opened the sheet, the pop up will be shown to that person.

CodePudding user response：

If the question is how to get the contents of all cells in the spreadsheet:

• You can get all the column contents as an 1-D array by first using the method getValues() and then flat().
• You can check either user's email is contained in this array with indexOf().

Sample (please refer to the documentation for a better understanding othe methods used - if necessary):

function bindMeToAnInstallableOnOpenTrigger() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Name of the Sheet containing emails");
  var emails = sheet.getRange("A2:A").getValues().flat();
  var email = Session.getEffectiveUser().getEmail();
  var ui = SpreadsheetApp.getUi();
  if(emails.indexOf(email) != -1) {
    ui.alert('Important Notice');
  }
}

• Note that getEffectiveUser() is a call that requires authorization and thus will not work on a simple onOpen trigger.
• Instead, you need to rename your function and bind an installable onOpen trigger to it.

CodePudding user response：

This code is working for me but the problem is unless and until the user doesn't give permission to it by running the code once, it will not work. So, every user who is added to the sheet needs to run the Appscript once and give it permission. As this person said that it needs authorization, so I understand the problem is with my code.

function onOpen() 
{
  var range = SpreadsheetApp.getActiveSheet().getRange('A1:A15');
  var value = range.getValue();
  var email = Session.getEffectiveUser().getEmail();
  var ui = SpreadsheetApp.getUi();
  if(value = email) {
    ui.alert('Important Notice');
  }
}