Share form bound script, have it run as the user using the form

Question

I created a Form bound script that takes content of the form, generates a PDF and HTML email, and ultimately sends all the content in an email.

This will be used by co-workers. Currently after I shared the scripts, the emails sent are under Google account, since I am the owner of the script.

How can I enforce that the script runs as the user using the form?

NOTE: Since I shared the sheet with multiple users, I planned on having them open the edit sheet, then click "view live form" and fill it out from there.

I have not dealt with sharing scripts before. I have a feeling I need to make the script a standalone script, share it as a library and share the form. Then an employee can add the library to the shared form and run it under their own account instead of mine? Hopefully there is an easier process than this.

Answer 1

Currently after I shared the scripts, the emails sent are under (my) google account, since I am the owner of the script.

That's due to the behaviour described here, The installable [trigger] version runs with the authorization of the user who created the trigger, even if another user with edit access opens the spreadsheet.

The solution, as you've determined, is to have each user install a trigger themselves. However, rather than force them to touch the Google Apps Script editor, you have three options to provide a user interface that lets them manage their own triggers, within the shared Spreadsheet.

Form Submission Trigger

Here's a simple trigger function that will send an email with their responses to the user who programmed the trigger, and only for their own responses.

function mailMeMyResponse(e) {
  // This only works in forms collecting user names.
  if (!e.namedValues.hasOwnProperty("Username")) {
    //throw new Error( "Form not collecting user names." );
    return;
  }

  var me = Session.getActiveUser().getEmail();

  // If the current form response is mine, email it to me
  if (me == e.namedValues["Username"]) {
    var body = "Your responses were:\n\n";
    for (var resp in e.namedValues) {
      body += resp + ": " + e.namedValues[resp] + "\n";
    }
    MailApp.sendEmail(me, "Your form responses", body);
  }
}

UI Option 1: Custom Menu

This set of functions provides a custom menu, labeled "MailMe", for the spreadsheet. The menu has a single item in it, which changes depending on the current state of the user's notification trigger.

The first time a user with editor privileges opens the spreadsheet, the menu option will be "Enable response notification". By selecting that, they will trigger authorization of the script, and set up their trigger function to begin monitoring responses in the spreadsheet. (That's all there is to it!) After that, they can always disable notifications via the same menu.

The ID of the trigger is saved in the User's properties, and is used to subsequently manage the menu and trigger state.

function onOpen(e) {
  // Set up custom menu      
  updateMenu(e);
}

function updateMenu() {
  var menu = SpreadsheetApp.getUi().createMenu("MailMe");

  var props = PropertiesService.getUserProperties();

  // Check whether a trigger function is defined for this user
  var triggerId = props.getProperty("triggerId");
  if (triggerId) {
    menu.addItem('Disable response notification', 'deleteFormTrigger');
  } else {
    menu.addItem('Enable response notification', 'createFormTrigger');
  }
  menu.addToUi();
}

function createFormTrigger() {
  var props = PropertiesService.getUserProperties();

  // Check whether a trigger function is defined for this user
  var triggerId = props.getProperty("triggerId");
  if (triggerId == null) {  
    // Set up form submission trigger for this user
    var ss = SpreadsheetApp.getActive();
    var triggerId = ScriptApp.newTrigger("mailMeMyResponse")
                             .forSpreadsheet(ss)
                             .onFormSubmit()
                             .create()
                             .getUniqueId();
    // Remember triggerId
    props.setProperty("triggerId", triggerId);
  }

  // Update menu
  updateMenu();
}

function deleteFormTrigger() {
  var props = PropertiesService.getUserProperties();

  // Check whether a trigger function is defined for this user
  var triggerId = props.getProperty("triggerId");
  if (triggerId !== null) {
    if (deleteTriggerById( triggerId )) {
      // Trigger was deleted, delete property
      props.deleteProperty("triggerId");

      // Update menu
      updateMenu();
    }
  }
}

function deleteTriggerById(triggerId) {      
  if (triggerId !== null) {
    // Search all triggers
    var triggers = ScriptApp.getProjectTriggers();
    for (var i=0; i<triggers.length; i++) {
      if (triggers[i].getUniqueId() == triggerId) {
        // Found our trigger, delete it.
        ScriptApp.deleteTrigger(triggers[i]);
        return true;
      }
    }
    return false; // Didn't find trigger
  }
  return true;  // Nothing to do
}

UI Option 2: Add-on

The advantage of approach over the Custom Menu script is that it's not tied to any specific spreadsheet file. It could be used on any spreadsheet within your domain, without requiring copying of the script.

The code for the add-on is more complex than for the custom menu, as it needs to handle installation from the store and an authorization flow. However, the structure of the code is largely the same, with minor tweaks to accommodate the broader requirements. (Look for event objects being used to drive the refresh of the menu, for example.)

The functions dealing with creation & deletion of the trigger function are identical to Option 1.

function onInstall(e) {
  onOpen(e);
}

function onOpen(e) {  
  if (e && e.authMode == ScriptApp.AuthMode.NONE) {
    // Add a normal menu item (works in all authorization modes).
    updateMenu(e);
  } else {
    // Privileged setup, based on properties (doesn't work in AuthMode.NONE).
    createFormTrigger();
  }
}

function updateMenu(e) {
  var menu = SpreadsheetApp.getUi().createAddonMenu();
  if (e && e.authMode == ScriptApp.AuthMode.NONE) {
    // Add a normal menu item (works in all authorization modes).
    menu.addItem('Authorize & enable response notification', 'createFormTrigger');
  } else {
    // Add a menu item based on properties (doesn't work in AuthMode.NONE).
    var props = PropertiesService.getUserProperties();

    // Check whether a trigger function is defined for this user
    var triggerId = props.getProperty("triggerId");
    if (triggerId) {
      menu.addItem('Disable response notification', 'deleteFormTrigger');
    } else {
      menu.addItem('Enable response notification', 'createFormTrigger');
    }
  }
  menu.addToUi();
}

function deleteFormTrigger() {
  var props = PropertiesService.getUserProperties();

  // Check whether a trigger function is defined for this user
  var triggerId = props.getProperty("triggerId");
  if (triggerId !== null) {
    if (deleteTriggerById( triggerId )) {
      // Trigger was deleted, delete property
      props.deleteProperty("triggerId");

      // Update menu, using fake event
      updateMenu({authMode:ScriptApp.AuthMode.FULL});
    }
  }
}

function deleteTriggerById(triggerId) {      
  if (triggerId !== null) {
    // Search all triggers
    var triggers = ScriptApp.getProjectTriggers();
    for (var i=0; i<triggers.length; i++) {
      if (triggers[i].getUniqueId() == triggerId) {
        // Found our trigger, delete it.
        ScriptApp.deleteTrigger(triggers[i]);
        return true;
      }
    }
    return false; // Didn't find trigger
  }
  return true;  // Nothing to do
}

function createFormTrigger() {
  var props = PropertiesService.getUserProperties();

  // Check whether a trigger function is defined for this user
  var triggerId = props.getProperty("triggerId");
  if (triggerId == null) {  
    // Set up form submission trigger for this user
    var ss = SpreadsheetApp.getActive();
    var triggerId = ScriptApp.newTrigger("mailMeMyResponse")
                             .forSpreadsheet(ss)
                             .onFormSubmit()
                             .create()
                             .getUniqueId();
    // Remember triggerId
    props.setProperty("triggerId", triggerId);
  }

  // Update menu, using fake event
  updateMenu({authMode:ScriptApp.AuthMode.FULL});
}

UI Option 3: Webapp to enable notification

Another possible approach that would eliminate the need for users to open the spreadsheet at all is to adapt the earlier createFormTrigger() function to be a stand-alone script Web Application, shared and set to run as the individual user, possibly with its own simple UI to switch notification on and off.