0

I am trying to send an email through a google spreadsheet on click of checkbox. Below is the code I am using

function onEdit(e){
  var ss = e.source.getActiveSheet();
  var ss_name = ss.getName();
  var ui = SpreadsheetApp.getUi();//unused

  if (ss_name == "Shopy"){
    var row = e.range.getRow();
    var col = e.range.getColumn();
    if (col === 5 && row == 2){
      if (ss.getRange("E2").getValue()){
        checkIt(ss);
        ss.getRange("E6").setValue("Report sent");
      }  
    }
  }
}

function checkIt(ss){
  sendEmail(ss,ui);
}

Sheet is working till the UI bottom pops up for YES or No but I guess the function sendEmail(ss) is not executing.

** Answer: ** Triggers won't work but using an image button works awesome

Screenshot of button

Wondo
  • 31
  • 6

1 Answers1

1

onEdit is a simple trigger. Simple triggers cannot perform operations that require permission. Sending emails requires permission.

simple triggers

It's not a good idea to use a UI in a simple trigger because they must finish in 30 seconds.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Hi @Cooper, good to see you again. I tried to add another function `openSender(ss)` so first the trigger fires up the function ```function openSender(ss){sendEmail(ss)};``` but its still not working. Any workaround idea to do this? it's necessary for me to do it only when the checkbox is clicked – Wondo May 28 '21 at 15:12
  • The issue is that simple triggers cannot perform operations that require permission. You must use an installable trigger. – Cooper May 28 '21 at 15:14
  • Removed that UI, still not working – Wondo May 28 '21 at 15:16
  • How about updating your code so that we can tell where you are at. – Cooper May 28 '21 at 15:18
  • Reason why installable triggers can't be useful for me: *Installable triggers always run under the account of the person who created them.* – Wondo May 28 '21 at 15:19
  • Then you can't do things that require permission. No workaround for that. – Cooper May 28 '21 at 15:20
  • Updated my code – Wondo May 28 '21 at 15:21
  • Using an intermediate function does not solve the problem of not being able to perform operations that require permission. – Cooper May 28 '21 at 15:22
  • what if I give permission? But it is not asking me for the permission – Wondo May 28 '21 at 15:22
  • You can try to fight it all you want but in the end simple triggers cannot perform functions that require permission. Read this over and over until you understand it. There is no work around there is no other option you either go with an installable trigger or your wasting your time. – Cooper May 28 '21 at 15:25
  • Thanks, a little more fight brought an option of inserting image to make it happen – Wondo May 28 '21 at 16:14
  • I don't think adding an image is going to allow a simple trigger to perform operations that require permission. But if you want to believe it will go for it. – Cooper May 28 '21 at 17:19
  • lol, @Cooper if I will have a button image instead of checkbox on a sheet that users can see and click on it, why will I go for a trigger. Simply can run a function that can send the email. And yes it's working greater than expected. You still did not get, I just needed any way that can make it possible, didn't mean to get obsessed with triggers. – Wondo May 31 '21 at 18:16
  • Just in case if you wonder I have attached a picture in my question. – Wondo May 31 '21 at 18:25
  • Yes an image button that makes sense now. – Cooper May 31 '21 at 20:08