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:
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 .
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With