3

I have a google apps script on a particular spreadsheet to which I have given permission to write an email from my account. A few others have 'edit' access to the spreadsheet as well. I was wondering if there is any way for me to prevent the people with 'edit' access from modifying my script and sending rouge emails from account?

I explored options such as:

  1. Creating a library on a personal sheet and reading the code which is being accessed by other as well. This only hides the code from others, but they can still write an email because the script area would already have had the permission to write emails.
  2. Creating add-ons which is what I am currently investigating.

Below you can see an example of my script which is triggered on edits inside the spreadsheet.

function onEditTrigger(e) 
{
  var row = e.range.rowStart;
  var column=e.range.columnStart;
  
  if (row==1 && column ==1)
  {
  GmailApp.sendEmail("xxx@gmail.com","Subject","Email content");
  }
  
  }

What I want to do is to send an email from my account when anyone edits the A1 cell. But I want control over what is sent from my mailbox. Currently anyone who can edit the sheet can edit the email that is sent from mailbox as well, which is what I do not like.

Can I restrict people so that they can not edit the email content from my account by exploiting the email permissions which I had originally given it.

EDIT:235325 Just to be clear, I am not worried about normal functioning of code. I just want a way of securing the script so that someone rogue with 'edit' permissions to the spreadsheet should not be able to edit my script to send any email that he/she wants .

Vikramaditya Gaonkar
  • 717
  • 10
  • 23
  • I am not sure how can others send email as you? Could you share the code for clarity? Also little more explanation on what this script does would help to come up with a alternate solution. – Jack Brown Apr 28 '17 at 13:52
  • Agreed. Seeing your script would help answer the question. You can execute scripts as the effective user, meaning it would find *their* email address, not yours. – Brian Apr 28 '17 at 14:00
  • Thanks for the input guys. I have included more details. Please do let me know if you think the question is still lacking clarity – Vikramaditya Gaonkar Apr 28 '17 at 14:35

2 Answers2

3

Isolate the code by creating an unbound script and calling the following global variables:

var ss = SpreadsheetApp.openById("yourSheetIdHere");
var sheet = ss.getSheetByName("sheetName");
/* do more stuff */

This way, you're the only user running the script to send the email. When the script is unbound, it always runs as you and cannot be opened by someone without edit rights. You'll need to create a trigger which watches the sheet, though. From the Google Apps docs:

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myFunction')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

Then, to be super-sure no one is messing with your emails, you could take one of a couple routes, both using Session.getActiveUser().

Method One - Send email as the active user.

This is simple - set an email variable with Session.getActiveUser().getEmail() and pass that into your GmailApp method. This will set the email address to send from the sheet editor.

Method Two - Validate the user trying to run the function.

You could also store authenticated users in an array in your script. Use Session.getActiveUser().getEmail() and check that the array contains the email address. If so, return true from the authenticator function and continue. If not, pass an error message.

Here's the Google Documentation on the method.

Brian
  • 4,274
  • 2
  • 27
  • 55
  • Hey Brian. Thanks for the answer. Someone rogue can still access the script, edit it and send emails from the GMailApp from my original authorization is what I am worried about. – Vikramaditya Gaonkar Apr 28 '17 at 15:45
  • The only way to prevent that is to A) be really careful of who you give edit rights to, or B) use the method above and create an unbound that only you have access to which opens the spreadsheet and runs. Updated answer above. – Brian Apr 28 '17 at 16:26
0

There is one more way to protect your code.

  1. Create a standalone script to receive email parameter data and send an email which will run on behalf of your account and will be only shared with you.
  2. Send data via GET or POST using UrlFetchApp from your bounded script.

This way you can have control over your code and email contents.

Parag Jadhav
  • 1,853
  • 2
  • 24
  • 41