How to have simple google apps script send mails from Sheets from owner account regardless of who's accessing file

Question

I've clicked around for the past few days trying to find an answer but can't seem to find one that makes sense to me (forgive me, I'm fairly new to GAS). I am trying to set up a Fantasy Golf Draft sheet to be used by about 12 users, but over half of which don't have/aren't willing to use a Gmail address. Getting access to the file is no problem, where I am running into an issue is trying to run a script, where when a Button/Shape is clicked, it sends an automated email to the next person who's turn it is to pick. The functionality of the script is working, when it comes from myself or someone with a Google account who can authorize the script etc. I run into troubles when it's someone without a Google account.

My question - how can I set the script to ONLY send from my email, or the Sheet/Script Owner's email - regardless of who is modifying/clicking the button? I see links about creating the script as a webapp to do this, but I get lost quickly.

Here's a link to my sheet: [https://docs.google.com/spreadsheets/d/16AppcmrcuhatnzcEs7eIQyD_p1swbRimRZZ4FdbhBKI/edit?usp=sharing][1]

And here is my send mail code:

function sendAlertEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("Send Mails"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange("A2:f2");
  var data = dataRange.getValues();
  for (i in data) {
    var rowData = data[i];
    var emailAddress = rowData[1];
    var recipient = rowData[0];
    var message1 = rowData[2];
    var message2 = rowData[3];
      var message3 = rowData[4];
          var message4 = rowData[5];
       var message = 'Hey ' + recipient + ',\n\n' + message1 + '\n\n' + ' The last player picked was ' + message2 + '\n\n' + message3 +'\n\n' + message4;
    var subject = '*GOLF DRAFT 2018* - YOU ARE ON THE CLOCK';
    MailApp.sendEmail(emailAddress, subject, message);
    var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("DRAFT"));
  }
   
}

Any help would be greatly appreciated!

Answer 1

I felt interested in this issue and worked a bit more on it. I changed from it being a get request to being a post request.

Here is what I have in the Google sheet.

    function sendAlertEmails() {

      var ss = SpreadsheetApp.getActiveSpreadsheet();
      ss.setActiveSheet(ss.getSheetByName("Send Mails"));
      var sheet = SpreadsheetApp.getActiveSheet();
      var dataRange = sheet.getRange("A2:f2");
      var data = dataRange.getValues();
      for (i in data) {
        var rowData = data[i];
        var emailAddress = rowData[1];
        var recipient = rowData[0];
        var message1 = rowData[2];
        var message2 = rowData[3];
        var message3 = rowData[4];
        var message4 = rowData[5];
        var message = 'Hey ' + recipient + ',\n\n' + message1 + '\n\n' + ' The last player picked was ' + message2 + '\n\n' + message3 +'\n\n' + message4;
        var subject = '*GOLF DRAFT 2018* - YOU ARE ON THE CLOCK';    
        var data = {
         'name': 'Bob Smith',
         'email': 'a@b.com',
         'message': message,
         'subject': subject, 
        };
        var options = {
          'method' : 'post',
          'contentType': 'application/json',
          'payload' : data
        };

        var secondScriptID = 'STANDALONE_SCRIPT_ID'
        var response = UrlFetchApp.fetch("https://script.google.com/macros/s/" + secondScriptID + "/exec", options);
        Logger.log(response) // Expected to see sent data sent back
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        ss.setActiveSheet(ss.getSheetByName("DRAFT"));
    //    Browser.msgbox("Your Pick Has Been Made");
      } 
    }

Below is what I have in the standalone script. There are some provisos on the standalone script working:

• It needs to be published under "Deploy as a webapp"
• Access should be set to 'Anyone, even anonymous'
• Every time you make a change to the standalone script publish again and change the Project version to new. This is so the call from the first sheet calls to the latest code.

Standalone Script

function convertURItoObject(url){  
      url = url.replace(/\+/g,' ')
      url = decodeURIComponent(url)
      var parts = url.split("&");
      var paramsObj = {};
      parts.forEach(function(item){
         var keyAndValue = item.split("=");
         paramsObj[keyAndValue[0]] = keyAndValue[1]
      })  
     return paramsObj; // here's your object
    }


function doPost(e) { 

  var data = e.postData.contents;
  data = convertURItoObject(data)
  var recipient = data.email;
  var body = data.message;
  var subject = data.subject;
  try {
   MailApp.sendEmail(recipient, subject, body)
  }
  catch(e){
   Logger.log(e)
  } 
  return ContentService.createTextOutput(JSON.stringify(e));

}