How to send email based on cell contents in Google Sheets using custom function

Question

I have a spreadsheet where data will be continuously inputted (through Google Forms and user input), and I would like to have code that will send out a single email for a single row, which I can call multiple times as rows get completed. Currently, I created a custom function that is supposed to do this but when I run it I get an error which reads "You do not have permission to call MailApp.sendEmail. Required permissions: https://www.googleapis.com/auth/script.send_mail (line 6)."

function sendDirectiveResponse(name, message, response, emailAddress) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var emailSent = "Email Sent"; 
  var message = "Received: " + message + "\n\n Response: " + response;   
  var subject = "Message Response";
  MailApp.sendEmail(emailAddress, subject, message);
  return emailSent
}

I expect an email to be sent out and the cell to show "Email Sent" but, instead, it says "#ERROR" and no email is sent.

Answer 1

Sending emails based upon cell contents

function sendMyEmails() {
  var ss=SpreadsheetApp.getActive();
  //the next few commands create a newsheet and load it with sample data so that you do not have to.  You  will want to remove this and use a spreadsheet of your own choosing
  var sh=ss.insertSheet();//setup
  var init=[['Email','Subject','Message','Status'],['sample1@gmail.com','Email Testing','Have a great day.',''],['sample2@gmail.com','Email Testing','Have a great day.',''],['sample3@gmail.com','Email Testing','Have a great day.',''],['sample4@gmail.com','Email Testing','Have a great day.','']];//setup
  sh.getRange(1,1,init.length,init[0].length).setValues(init);//setting up data
  var rg=sh.getDataRange();//get data
  var vA=rg.getValues();
  var hObj=[];
  var html='';
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      hObj[vA[0][j]]=vA[i][j];//this loads the object with all of the data for this row. And you can now refer to it with hObj.headertitle
    }
    if(!hObj.Status) {//When you supply your own data this will prevent function from sending emails more than once
      html+=Utilities.formatString('<br />Email: <strong>%s</strong> Subject: <strong>%s</strong> Message: <strong>%s</strong>', hObj.Email,hObj.Subject,hObj.Message)
      sh.getRange(i+1,vA[0].indexOf('Status') + 1).setValue('Done')
      //MailApp.sendEmail(hObj.Email, hObj.Subject, hObj.Message);//removed for testing you will have to uncomment this line to actually send email
    }    
  }
  var ui=HtmlService.createHtmlOutput(html).setWidth(1000);
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Email Testing');//this provides a dialog to show you what would have been sent if everything were enabled.
}