Adding ui.alert and ui.Button to @--Hyde 's amazing code!

[Ashley Griffith]

Hello!! I'm really hoping @--Hyde is able to help me on this as they were the original creator of this code. But I'm open to anyone helping out! :)

I have a large workbook with 20+ sheets where clients fill out data and then use a checkbox to let me know they are finished with that questionnaire. Then I receive an email letting me know a specific questionnaire has been completed and by which client. (I'm a wedding planner, my clients are represented by their first names, i.e. Ashley + Ross).v

Here is my sample workbook with just one sheet on it:

https://docs.google.com/spreadsheets/d/14gfIbQA3UjDPCwlunMu5Xf-9FpvsrDdRwUSYVOSlgpQ/edit?usp=sharing

On a Google Apps Script forum I found this code by @Michael Campbell 5261 (my info is added in):

function onEditTrigger(e) {

Logger.log(e.value)

if(e.value=="TRUE"){

var ui = SpreadsheetApp.getUi()

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var result = ui.alert('Want to send your completed questionnaire to Ashley?','Yes or no',ui.ButtonSet.YES_NO)

if(result==ui.Button.YES){

var rp = sheet.getRange("N"+e.range.getRow()).getValues();

Logger.log("Emailing "+rp[0])

sheet.getRange("K"+e.range.getRow()).setValue("Questionnaire Submitted!");

MailApp.sendEmail({

to: rp.toString(),

subject: "SUBMITTED: " + e.source.getRange('M'+ e.range.getRow()).getValue(),

body: "Venue Questionnaire",

})

Logger.log("sent")

}

Logger.log("TRUE"+e.value+"complete")

}

}

This worked just fine until I was on a different sheet - not a questionnaire sheet - and when I clicked a random checkbox, it asked me if I wanted to submit my questionnaire. That's not what I need. 

So, I found this fantastic code by --Hyde (listed below) and it solved my issue of random checkboxes triggering popup boxes. AND it added this cute little "To open the spreadsheet, click this link:\n" and it takes me DIRECTLY TO my worksheet! 

HOWEVER, I really like two features from the original code: the ui.alert and ui.Button along with the part about  

sheet.getRange("K"+e.range.getRow()).setValue("Questionnaire Submitted!");​ 

which gives my users a little affirmation that their questionnaire will be seen by someone.

--Hyde, I'm hoping you can help me add these two little features to your otherwise fantastic code. I tried, but I'm failing.  Here is the original --Hyde code with my info added in:

​function sendEmailOnCheckboxClick(e) {

// version 1.0, written by --Hyde, 22 October 2021

// - see https://support.google.com/docs/thread/131898237

if (!e) {

throw new Error('Please do not run this function directly but set up a trigger. See the instructions in the script.');

}

if (e.value !== 'TRUE' || e.range.getA1Notation() !== 'J2') {

return;

}

const ss = e.source;

const sheet = e.range.getSheet();

const sheetName = sheet.getName();

const userName = sheet.getRange('M2').getDisplayValue();

const emailAddress = 'ash...@fakeemail.com';

const emailSubject = 'SUBMITTED: ' + sheetName + ' by ' + userName;

let emailContents =

'A new questionnaire has been submitted!\n\n'

+ "Spreadsheet: '" + ss.getName() + "'\n"

+ "Sheet: '" + sheetName + "'\n"

+ 'Submitted by: ' + userName + '\n\n'

+ 'To open the spreadsheet, click this link:\n'

+ ss.getUrl() + '#gid=' + sheet.getSheetId();

MailApp.sendEmail(emailAddress, emailSubject, emailContents);

}

​​

Thank you, in advance, for your help!

Ashley