Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I prevent people with edit access from modifying my google apps script? [duplicate]

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("[email protected]","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 .

like image 857
Vikramaditya Gaonkar Avatar asked Oct 16 '25 01:10

Vikramaditya Gaonkar


1 Answers

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.

like image 151
Brian Avatar answered Oct 19 '25 10:10

Brian



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!