Google App Scripts onOpen() trigger doesn't work on sheets

Question

I am unable to get my script to run on opening the spreadsheet. I've set the trigger manually see. Authorization scope has been set too.

The code is intended to take some contact information from a table in a sheet, create a Contact and then add that Contact to an email list specified in the table. There is a function to check if this email already exists and prevent duplication. The code works fine if I run it from the script editor. I'm not sure why I am unable to run it with an onOpen(e) trigger.

I thought the issue is related to this but the bare minimum code works for me and creates a second sheet on the open trigger.

Any help appreciated as I'm pretty stuck - it must be something with my code.

Code:

function onOpen(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  

  // Iterate through all the data minus the header
  for (var i=1; i<data.length; i++){
    currApplicant = data[i]
    applicantFirstName = currApplicant[1]
    applicantLastName = currApplicant[2]
    applicantEmail = currApplicant[3]
    emailGroup = currApplicant[13]
    addToEmailBool = currApplicant[12]    //do you want to add them to the email list?
    var numDuplicates = 0;

    Logger.log(applicantEmail); 

    if ((addToEmailBool == 1) && (emailGroup != "")) {
        var duplicateCounter = 0; 
        var numDuplicates = checkForDuplicates(emailGroup, applicantEmail);
      
      if (numDuplicates==0){
        var contact = ContactsApp.createContact(applicantFirstName, applicantLastName, applicantEmail);
        var members = ContactsApp.getContactGroup(emailGroup);
        members.addContact(contact);
        Logger.log("Adding:", applicantEmail)
        Browser.msgBox("Added new contact");
      }
    }
  }
  
}

function checkForDuplicates(emailGroup, applicantEmail) { 
  var duplicateCounter = 0; 
  var groupContacts = ContactsApp.getContactGroup(emailGroup).getContacts()
  
  //go thru all the contacts in this group and check if their emails == applicantEmail
  for (var i in groupContacts) {
    var emails = groupContacts[i].getEmails();   
    for (var e in emails) {
      if (emails[e].getAddress() == applicantEmail){
        duplicateCounter += 1;
        Logger.log("Duplicate found:", applicantEmail);       
      }
    }
  }
  return duplicateCounter;
}

You should really use the var in declaring variables to insure that they have local scope. — Cooper

Cooper Cooper · Accepted Answer · 2019-01-17T03:35:41

Simple Triggers cannot be used for processes that require authorization.

Reference Check the 4th bullet point.

Reading Down a little further the documentation states:

G Suite application triggers

Installable triggers for G Suite applications are conceptually similar to simple triggers like onOpen(), but they can respond to additional events, and they behave differently.

For example, the installable open trigger for Google Sheets activates whenever the spreadsheet is opened by any user who has edit access, just like the simple onOpen() trigger. However, the installable version can call services that require authorization. The installable version runs with the authorization of the user who created the trigger, even if another user with edit access opens the spreadsheet.

Google App Scripts onOpen() trigger doesn't work on sheets

1 Answers