Limiting OAuth scope for Google App Script

Question

I am trying to limit my OAuth scopes in my script that sends an email after a form has been submitted. I want to limit it so that it has the least permission needed. If I click run, it tries to authorize the correct permissions. If I set up the on form submit trigger, it wants to authorize read, change, delete on all spreadsheets and change on all forms.

If I give the script full access to sheets and forms, it runs as intended. I just want to reduce some of the permissions. The screenshot shows that it is asking for more permission than what is specified in the appsscript.json file.

This script is attached to the responses sheet generated from my form.

From my appsscript.json:

"oauthScopes": [
    "https://www.googleapis.com/auth/gmail.readonly",
    "https://www.googleapis.com/auth/gmail.send",
    "https://www.googleapis.com/auth/drive.file",
    "https://www.googleapis.com/auth/forms.currentonly",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]

The code:

/**
 * @OnlyCurrentDoc
 */

function onFormSubmit(e) {
  var values = e.namedValues;

  var htmlBody = 'Hey ' + values['Name of Recipient'] + "!<br>";
  htmlBody += values['Name of Sender'] + " thinks you deserve a shoutout! Thank you for being so awesome!";
  htmlBody += '<br> <em>' + values['Shoutout'] + " - " + values['Name of Sender'] + "</em>";
  htmlBody += '<br><br>';

  GmailApp.sendEmail(values['Recipient Email'],'SHOUT OUT!!!!!!','',
  {from:'[email protected]',
  htmlBody:htmlBody});
}

Google Form/Sheet Questions/Columns

• Timestamp

• Name of Sender

• Name of Recipient

• Name of Recipient's Boss

• Shoutout

• Recipient Email

• Recipient's Boss Email

OAuth Permissions Screenshot:

Project Details OAuth Scopes:

• View your email messages and settings https://www.googleapis.com/auth/gmail.readonly
• Send email on your behalf https://www.googleapis.com/auth/gmail.send
• See, edit, create, and delete only the specific Google Drive files you use with this app https://www.googleapis.com/auth/drive.file
• View and manage forms that this application has been installed in https://www.googleapis.com/auth/forms.currentonly
• View and manage spreadsheets that this application has been installed in https://www.googleapis.com/auth/spreadsheets.currentonly

Answer 1

By default, See, edit, create, and delete all your Google Sheets spreadsheets is a required scope if you added an Installable Trigger that has event source of From Spreadsheet and View and manage your forms in Google Drive is also added if the event type is On form submit. This is to give script the access to the changes that may happen in the spreadsheet caused by submitting response. As a result, it will return the user an Event Object containing information about the context that caused the trigger to fire.

The script will also work if you manually press Run but there is no Event Object that will be passed to the function parameter.

You can try using Time-driven as event source and it will show the same scope as you declared in appsscript.json since the trigger doesn't need to access the spreadsheet to execute the trigger.

Example:

Time Driven:

From spreadsheet and On Open:

From spreadsheet and On form submit:

References:

• Event Object
• Installable Triggers