Changing the owner of google spreadsheet for sendi

Question

I have generated the following codes in app script in order to send emails automatically if users changed some values in google spreadsheet, which is working fine.

My question is that right now I am the owner for this google spreadsheet, so every email is sent out from my gmail account. I am just wondering if it is possible to change the sender so that each email will be sent out from other's gmail account. I did test it by changing the owner to one of my colleagues but it is not working. I am still "sending" those emails even though the owner of the google spreadsheet is someone else.

Do I need to change my code? Any advice would be greatly appreciated! Thanks!

function PODTool() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0];

var sheet = spreadsheet.getActiveSheet();
var lastRow = sheet.getLastRow();

var startRow = 2;

var range = sheet.getRange(2,7,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var LinksToPODForm = range.getValues();

var rangeColumnReadyToSendEmail =sheet.getRange(2,9,lastRow-startRow+1,1);
var sendEmailOrNot = rangeColumnReadyToSendEmail.getValues();


var warning_count = 0;
var msg = "";

// Loop over the values 
for (var i = 0; i <= numRows - 1; i++) {
var EachLinkValue = LinksToPODForm[i][0];


if (EachSendEmailOrNotValue=="yes" && sheet.getRange(i+2,10).getValue()=='Email not Sent' &&EachEmailRecipient=="Someone"){
var BillingOfLadingShowingInEmailNotification = Billing_of_lading_number[i][0];


msg = msg +"Requester:       "+EachEmailRecipient+"
warning_count++;
var subject ="Billing of Lading: "+BillingOfLadingShowingInEmailNotification;
  sheet.getRange(i+2,10).setValue('Sent');
var recipient = "[e-mail]";
}

if(warning_count) {
  MailApp.sendEmail(recipient, subject,msg)
}

};

Answer 1

You have to adjust the "Deploy Web App..." settings so that the apps script runs as the "User accessing the web app". This option is under the setting "Execute the app as".

This will require the user to authorize the app to use their account once as Cameron described.

Answer 2

The emails will be sent from the account under who's authority the script is running.

I believe in your case, that would be whichever account created the trigger that runs the script. The owner of the script itself isn't relevant, since any user with edit rights can create a trigger.

Note that when the new user creates the trigger they'll also have to manually trigger the script once so they can Authorize it.