Google Spreadsheet How to allow shared users to execute script

Question

I am having a problem/ question with my Google spreadsheet. Scripts run fine when I execute them in the spreadsheet, but when I share the sheet with people within my domain, the full script does not execute.

The script is suppose to run when a value is change in the column N, then prompts "Do you want to send this email". If yes, email is sent.

Now when I share the spreadsheet with edit rights to my other colleagues. They can open the spreadsheet, change the value, and receive the script prompt "Do you want to send this email". They click yes but nothing happens. No email is sent. When a email is sent, they are suppose to receive a toast prompt at the bottom right of the spreadsheet. This does not pop-up either.

How do allow others to be able execute the script when the spreadsheet is shared? Could it be something in the code? Any ideas, help, or code correction would be appreciated.

Here is the spreadsheet for testing: https://docs.google.com/spreadsheet/ccc?key=0Ag8NytPhOo00dEt6OUFNSlRUUFEwWmVuNm5RbVZPNHc&usp=sharing

Edit 2

Hi Serge, I saw the edits you made to the script. but the issue still occurring with my colleagues. We went in and authorize the scripts in the spreadsheet. Set-up the triggers and save it.

When they changed the value in column N. The confirmation box appears, click yes and nothing happens. I am able to complete the script with all my other gmail accounts outside of the company's domain. Could it have anything to do with the company's domain or restrictions set by the administrator on the domain or executing shared scripts?

[email protected] Execution transcript and Logging output

Logging output

[14-03-21 02:08:00:906 EDT] sendEmailNotifications2()
[14-03-21 02:08:01:572 EDT] getName: Active Discs
[14-03-21 02:08:01:573 EDT] columnStart: 14
[14-03-21 02:08:01:573 EDT] e.value: In Progress
[14-03-21 02:08:01:573 EDT] In Progress

See below Execution transcript

[14-03-20 23:30:19:494 EDT] SpreadsheetApp.getActiveRange() [0 seconds]
[14-03-20 23:30:19:495 EDT] Range.getRow() [0 seconds]
[14-03-20 23:30:19:495 EDT] Range.getLastRow() [0 seconds]
[14-03-20 23:30:19:495 EDT] Range.getColumn() [0 seconds]
[14-03-20 23:30:19:495 EDT] Range.getLastColumn() [0 seconds]
[14-03-20 23:30:19:502 EDT] Session.getActiveUser() [0.006 seconds]
[14-03-20 23:30:19:502 EDT] User.getEmail() [0 seconds]
[14-03-20 23:30:19:502 EDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[14-03-20 23:30:19:649 EDT] Starting execution
[14-03-20 23:30:19:661 EDT] Session.getEffectiveUser() [0 seconds]
[14-03-20 23:30:19:662 EDT] Logger.log([sendEmailNotifications2(), []]) [0 seconds]
[14-03-20 23:30:19:662 EDT] Spreadsheet.getActiveSheet() [0 seconds]
[14-03-20 23:30:19:674 EDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[14-03-20 23:30:19:945 EDT] Sheet.getName() [0.271 seconds]
[14-03-20 23:30:20:340 EDT] Sheet.getName() [0.394 seconds]
[14-03-20 23:30:20:340 EDT] Logger.log([getName: Active Discs, []]) [0 seconds]
[14-03-20 23:30:20:340 EDT] Logger.log([columnStart: 14, []]) [0 seconds]
[14-03-20 23:30:20:341 EDT] Logger.log([e.value: In Progress, []]) [0 seconds]
[14-03-20 23:30:20:341 EDT] Logger.log([In Progress, []]) [0 seconds]
[14-03-20 23:30:20:346 EDT] Browser.msgBox([Colombo Team Email, Email will be sent to the Venue Colombo Team. Do you want to sent this email?, YES_NO]) [0 seconds]
[14-03-20 23:30:20:451 EDT] Browser.hashCode() [0 seconds]
[14-03-20 23:30:20:530 EDT] CacheService.hashCode() [0 seconds]
[14-03-20 23:30:20:531 EDT] CalendarApp.hashCode() [0 seconds]
[14-03-20 23:30:20:532 EDT] Charts.hashCode() [0 seconds]
[14-03-20 23:30:20:534 EDT] ContactsApp.hashCode() [0 seconds]
[14-03-20 23:30:20:535 EDT] ContentService.hashCode() [0 seconds]
[14-03-20 23:30:20:536 EDT] DocsList.hashCode() [0 seconds]
[14-03-20 23:30:20:537 EDT] DocumentApp.hashCode() [0 seconds]
[14-03-20 23:30:20:540 EDT] DriveApp.hashCode() [0 seconds]
[14-03-20 23:30:20:541 EDT] FinanceApp.hashCode() [0 seconds]
[14-03-20 23:30:20:542 EDT] FormApp.hashCode() [0 seconds]
[14-03-20 23:30:20:544 EDT] GmailApp.hashCode() [0 seconds]
[14-03-20 23:30:20:545 EDT] GroupsApp.hashCode() [0 seconds]
[14-03-20 23:30:20:547 EDT] GroupsManager.hashCode() [0 seconds]
[14-03-20 23:30:20:548 EDT] HtmlService.hashCode() [0 seconds]
[14-03-20 23:30:20:549 EDT] Jdbc.hashCode() [0 seconds]
[14-03-20 23:30:20:551 EDT] LanguageApp.hashCode() [0 seconds]
[14-03-20 23:30:20:551 EDT] LinearOptimizationService.hashCode() [0 seconds]
[14-03-20 23:30:20:552 EDT] LockService.hashCode() [0 seconds]
[14-03-20 23:30:20:553 EDT] Logger.hashCode() [0 seconds]
[14-03-20 23:30:20:559 EDT] MailApp.hashCode() [0 seconds]
[14-03-20 23:30:20:560 EDT] Maps.hashCode() [0 seconds]
[14-03-20 23:30:20:561 EDT] NicknameManager.hashCode() [0 seconds]
[14-03-20 23:30:20:561 EDT] PropertiesService.hashCode() [0 seconds]
[14-03-20 23:30:20:562 EDT] ScriptApp.hashCode() [0 seconds]
[14-03-20 23:30:20:564 EDT] ScriptDb.hashCode() [0 seconds]
[14-03-20 23:30:20:565 EDT] ScriptProperties.hashCode() [0 seconds]
[14-03-20 23:30:20:565 EDT] Session.hashCode() [0 seconds]
[14-03-20 23:30:20:566 EDT] SitesApp.hashCode() [0 seconds]
[14-03-20 23:30:20:567 EDT] SoapService.hashCode() [0 seconds]
[14-03-20 23:30:20:568 EDT] SpreadsheetApp.hashCode() [0 seconds]
[14-03-20 23:30:20:570 EDT] UiApp.hashCode() [0 seconds]
[14-03-20 23:30:20:573 EDT] UrlFetchApp.hashCode() [0 seconds]
[14-03-20 23:30:20:574 EDT] UserManager.hashCode() [0 seconds]
[14-03-20 23:30:20:574 EDT] UserProperties.hashCode() [0 seconds]
[14-03-20 23:30:20:575 EDT] Utilities.hashCode() [0 seconds]
[14-03-20 23:30:20:576 EDT] Xml.hashCode() [0 seconds]
[14-03-20 23:30:20:576 EDT] XmlService.hashCode() [0 seconds]
[14-03-20 23:30:20:579 EDT] User.hashCode() [0 seconds]
[14-03-20 23:30:20:580 EDT] Spreadsheet.hashCode() [0 seconds]
[14-03-20 23:30:20:580 EDT] Range.hashCode() [0 seconds]
[14-03-20 23:30:20:580 EDT] Range.hashCode() [0 seconds]
[14-03-20 23:30:20:581 EDT] User.hashCode() [0 seconds]
[14-03-20 23:30:20:581 EDT] User.hashCode() [0 seconds]
[14-03-20 23:30:20:581 EDT] Sheet.hashCode() [0 seconds]
[14-03-20 23:30:20:581 EDT] Spreadsheet.hashCode() [0 seconds]

Answer 1

Other users need to authorize the script in order to be able to send mails. The onEdit you use works silently and doesn't trigger the authorization process and fails (silently as well).

You should create a simple function that executes from the menu that will ask users for explicit authorization as a sort of 'install process' happening once.

You can ask them to do so with a onOpen popup showing a message that would show up only if user has not yet do so.

(note that you know that it works for other users since I used your script xD )

---

EDIT

After a few tests, I've narrowed down the issue, it comes obviously from the getActiveUser call.

If you replace that with a valid email as a string then everything is ok.

Could you check in the admin settings of your domain if you're allowed to share apps and documents with someone outside of your domain ? I guess it is not the case and that would be the reason why you get this error. Please update here to confirm (or not)

Answer 2

Don't use the Browser.msgBox() function because now (new Google Docs) it is expecting the owner to make a choice. Since any other user is not an owner, the script just collapses after Browser.msgBox().

To make it clear: if you're the owner – everything will work as it should. Things change when you're the user – the window will appear letting you press the button, and then script is down. Try using another method.

To Serge: Google scripts are meant to work only under the owner's rules. No users are allowed to change them. Otherwise you'll totaly mess up the scripts. Most of the users don't even have to know what is a script and all this tech.