9

When I change something in my spreadsheet, the onEdit() trigger runs, and I can see all the msgbox's that I put in my code.

My function stops at this line 

MailApp.sendEmail(emailAddress, subject, message);

I never see the message 'Email sent!', and get an error in the EXECUTION TRANSCRIPT:

You do not have permission to call sendEmail

If I run the script directly in the script editor, everything works fine...

Here is my code:

function onEdit() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var sheetname = sheet.getName()
      var AcCellRange = sheet.getActiveCell()
      var AcCol = AcCellRange.getColumn()
      var AcRow = AcCellRange.getRow()

      if (sheetname=="Questions/Réponses") {
        //Browser.msgBox(AcCol+' / '+AcRow)
       //liste d'instructions
        //Boucle si les colonne sont comprise dans le range
        if ((AcCol==3) || ((AcCol==7))){
          //Browser.msgBox(AcCol)
          if (AcRow > 7){
            //Browser.msgBox(AcRow)
            sendEmails()
          }
        }
      }
      else
      {}
    }    
function sendEmails() {
      Browser.msgBox('SendEmails')
      var spreadsheet = SpreadsheetApp.getActive();
      var sheet = spreadsheet.getSheetByName('ListCourriel');
      Browser.msgBox('SendEmails2')
      var sheetDonnee = spreadsheet.getSheetByName('Questions/Réponses');
      var RangeProjet = sheetDonnee.getRange(1, 3)
      var NoProjet = RangeProjet.getValue()
      var RangeProjet = sheetDonnee.getRange(4, 3)
      var ProjName = RangeProjet.getValue()
      Browser.msgBox('SendEmails3')
      var startRow = 2;  // First row of data to process
      var LastRows = sheet.getRange(1,4)
      var numRows = LastRows.getValue();   // Number of rows to process
      // Fetch the range of cells A2:B3
      var dataRange = sheet.getRange(startRow, 1, numRows, 2)
      // Fetch values for each row in the Range.
      var data = dataRange.getValues();
      Browser.msgBox('SendEmails4')
      //Permet d'aller cherche les info de la ligne active
      var ActiveCellRange = sheetDonnee.getActiveCell()
      var ActiveRows = ActiveCellRange.getRow()
      var NoLigne = sheetDonnee.getRange(ActiveRows,1)
      var sDep = sheetDonnee.getRange(ActiveRows,2)
      var sDate = sheetDonnee.getRange(ActiveRows,4)
      var sInitiale = sheetDonnee.getRange(ActiveRows,5)
      var sQuestion = sheetDonnee.getRange(ActiveRows,3)
      Browser.msgBox('SendEmails5')
      var rDate = sheetDonnee.getRange(ActiveRows,9)
      var rInitiale = sheetDonnee.getRange(ActiveRows,10)
      var rReponse = sheetDonnee.getRange(ActiveRows,7)

      Browser.msgBox('SendEmails6')
      var subject = 'Modif. Question/Réponse - Projet: ('+NoProjet+') '+ProjName;
      var message = "No Ligne : "+NoLigne.getValue()+String.fromCharCode(10)+String.fromCharCode(10)+"Reponsable : "+sInitiale.getValue()+String.fromCharCode(10)+"Date : "+sDate.getValue()+String.fromCharCode(10)+"Question : "+String.fromCharCode(10)+sQuestion.getValue()+String.fromCharCode(10)+String.fromCharCode(10)+"************************************"+String.fromCharCode(10)+String.fromCharCode(10)+"Reponsable : "+rInitiale.getValue()+String.fromCharCode(10)+"Date : "+rDate.getValue()+String.fromCharCode(10)+"Réponse : "+String.fromCharCode(10)+rReponse.getValue()
      //Browser.msgbox(subject)

      Browser.msgBox('SendEmails7')
      for (i in data) {
        Browser.msgBox('SendEmails8')
        var row = data[i];
        var emailAddress = row[0];  // First column
        Browser.msgBox('SendEmails9')
        MailApp.sendEmail(emailAddress, subject, message);
        Browser.msgBox('Email sent')
      }
    }
Rubén
  • 34,714
  • 9
  • 70
  • 166
Maxime Beaudoin
  • 91
  • 1
  • 1
  • 2
  • If you are using `Browser.msgBox()` for debugging purposes, there is another option. You can use 'Logger.log()`, run the code, then VIEW the LOGS. – Alan Wells Mar 14 '15 at 15:04
  • Have you VIEWed the EXECUTION TRANSCRIPT? What does it state at the end? – Alan Wells Mar 14 '15 at 15:08
  • Use the debugger, and set a break point on the `MailApp.sendEmail()` line, then run the debugger, and when the code pauses, view the values for `emailAddress, subject, message`. What are they? [Google Documentation - Break Points](https://developers.google.com/apps-script/troubleshooting#using_the_debugger_and_breakpoints) – Alan Wells Mar 14 '15 at 15:14
  • Ok thank you for the tips!! If I look at the EXECUTION TRANSCRIPT I can see this error message : [15-03-16 08:48:34:543 EDT] Execution failed: You do not have permission to call sendEmail (line 44, file "Code") [0.139 seconds total runtime] – Maxime Beaudoin Mar 16 '15 at 12:49
  • 2
    OK I found it, I have to instal a trigger, for those who have the same problem check this : https://developers.google.com/apps-script/guides/triggers/installable – Maxime Beaudoin Mar 16 '15 at 14:42

1 Answers1

15

The permissions are different when running a SIMPLE onEdit() trigger. By contrast, there is an INSTALLABLE trigger. Here is the documentation for simple trigger restrictions:

Google Documentation - Triggers - Restrictions

The documentation states:

They (a SIMPLE trigger) cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization

You need to set up an installable trigger to be able to send the email.

In the EDIT menu, choose, CURRENT PROJECTS TRIGGERS.

Name your function something different than onEdit.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • I have an other probllem, I have a File name 'MASTER', I put my code in this file, and each time we start a new projet, someone take the file 'MASTER', rename it and use the new file. My problem is that it seem that each time we copy the file 'MASTER' I need to redo all the processe to add my trigger to the project?? Is there a way that I only have to do it in the MASTER file?? Thank you! – Maxime Beaudoin Mar 18 '15 at 18:04
  • How I solved it: I have already installed the trigger. However, as I added another service in my function, I needed to save the trigger again so that it will ask for my permission for other services. – CCSJ Jun 14 '21 at 11:26