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

Answer 2

I believe your goal is as follows.

• From your question, you want to restrict the scopes for your showing script of The code: as follows.

    /**
     * @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});
    }
  

It seems that when GmailApp.sendEmail of Gmail Service is used, the scope is constant as https://mail.google.com/. It seems that this is the current specification. So when you want to restrict the scopes, I think that you can achieve it using Gmail API. When your script is converted using Gmail API, it becomes as follows.

Modified script:

Before you use this script, please enable Gmail API at Advanced Google services.

/**
 * @OnlyCurrentDoc
 */

// This is from https://stackoverflow.com/a/66088350
function convert_(toEmail, fromEmail, subject, textBody, htmlBody) {
  const boundary = "boundaryboundary";
  const mailData = [
    `MIME-Version: 1.0`,
    `To: ${toEmail}`,
    `From: ${fromEmail}`,
    `Subject: =?UTF-8?B?${Utilities.base64Encode(subject, Utilities.Charset.UTF_8)}?=`,
    `Content-Type: multipart/alternative; boundary=${boundary}`,
    ``,
    `--${boundary}`,
    `Content-Type: text/plain; charset=UTF-8`,
    ``,
    textBody,
    ``,
    `--${boundary}`,
    `Content-Type: text/html; charset=UTF-8`,
    `Content-Transfer-Encoding: base64`,
    ``,
    Utilities.base64Encode(htmlBody, Utilities.Charset.UTF_8),
    ``,
    `--${boundary}--`,
  ].join("\r\n");
  return Utilities.base64EncodeWebSafe(mailData);
}

function onFormSubmit(e) {
  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>';

  var raw = convert_(values['Recipient Email'], '[email protected]', 'SHOUT OUT!!!!!!', "", htmlBody);
  Gmail.Users.Messages.send({raw: raw}, "me");
}

• In this script, only tne scope of https://www.googleapis.com/auth/gmail.send can be used.

• In this sample script, it supposes that e is the correct value you want to use. Please be careful about this.

Note:

• About other scopes of "https://www.googleapis.com/auth/gmail.readonly", "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/forms.currentonly", "https://www.googleapis.com/auth/spreadsheets.currentonly", when I saw your script, those scopes are not required to be used. But, when Google Form is used, https://www.googleapis.com/auth/forms.currentonly might be required to be used. Please be careful this.

• For example, when you want to use other methods, please add the scopes for them. My answer is for your showing script in your question. Please be careful about this.

Reference:

• Method: users.messages.send