Newbie needs help: Sheets Script to Send Email to Address ON SINGLE ROW when something on that row clicked

[Kate Isabelli]

I am very much an Apps Script beginner and have only done a couple of projects.  They have all been projects that did something with ALL of the data in a sheet.  I am trying to figure out how to trigger what I want on a single row.

• Here is a sample sheet.  
• Column A has email addresses.  
• Column B has some info that I will include in the email. 
• Column C is where I want someone to be able to in some way click to trigger sending an email to the address in column A for CLICKED ROW ONLY to the email address on that row.

I have a script installed in that sheet (code below) that will send the email and desired info ALL of the rows in the spreadsheet appropriately.  But how do I do the trigger and send only one at a time.  (To clarify the task, the data in the sheet is meetings that the person who entered their email address needs someone else to supervise.  So if I can take one of these events, I want to trigger an email to that individual person about that individual meeting.)

function sendReplies() {

 // get data from sheet

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = ss.getSheetByName('Sheet1');

  const allRange = sheet.getDataRange();

  const allData = allRange.getValues();

  // remove the header row

  allData.shift();

  // loop over all the rows

  allData.forEach(row => {

  // get data

    const emailAddr = row[0]

    const eventRequested = row[1]

    // send an email for each row

    sendEmail(emailAddr,eventRequested)

    console.log(emailAddr);

    console.log(eventRequested);

  })

}

function sendEmail(email,eventReq) {

 // create subject line

 const subjectLine = 'Re your request for coverage for an assigned duty'

 // create email body

 const htmlBody = 'Coverage requested for ' + eventReq

 // send email

 GmailApp.sendEmail(email,subjectLine,'',{htmlBody: htmlBody});

}

[CBMServices Web]

Hi Kate,

What you need to do is:

1- create a function that you will be calling from your event trigger

2- function will use the event object to check:

 a- if column changed is your chosen column

 b- if new value is true (means send email)

 c- if both conditions are true, then call your send email function.

3- create a new trigger to monitor for edits to spreadsheet and have it call your trigger function that you created in step 1 above.

Make your column C in sheet to be a checkbox. This way when someone clicks on it, it will change value to either true or false. Your trigger will send an email when checkbox is changed to true. This does mean it can send that same email several times if the checkbox is cycled through true-false-true..

Here is some quick code which you will have to check, have not checked it myself:

function triggerOnChange(event) {

 var column = event.range.getColumn();

 var row = event.range.getRow();

 var newValue = event.value;

 if (column == 3 && newValue == true)

{

  // call send email function here. 

  // row parameter is what row in sheet was changed.

  sendEmail(row);

 }

}


Hope this helps.

Pursuant to Illinois’ public records law, this electronic communication may constitute a public record. Please be advised that this email may be subject to monitoring and disclosure to third parties.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b483a578-61ba-40fd-97a3-b2b64adf53c3n%40googlegroups.com.

[Kate Isabelli]

Thank you for the quick response.  I see where you are going with this and will do my best to integrate it into my code and give it a try!  

[Kate Isabelli]

I feel like I'm closer, but not I'm definitely not there.  I've modified my sheet so that I have the checkboxes in column C and I managed to figure out how to set my trigger and it IS getting triggered, because I can find the Executions.  But there are no logs to give me any feedback, and I do not seem to see any effort to the send email.  Any ideas on what I'm missing.

Same sheet as linked previously, and here's my code, for convenience:

function triggerOnChange(event) {

  var colChanged = event.range.getColumn();

  var rowChanged = event.range.getRow();

  var newValue = event.value;

  if (colChanged == 3 && newValue == true)

  {

    sendtheEmail(rowChanged)

  }

 }

function sendtheEmail(_rowChanged) {

 // get data

    const emailAddr = row[0]

    const eventRequested = row[1]

 // create subject line

 const subjectLine = 'Re your request for coverage for an assigned duty'

 // create email body

 const htmlBody = 'Coverage requested for ' + eventRequested

 // send email

GmailApp.sendEmail(emailAddr,subjectLine,'',{htmlBody: htmlBody});

}

[Kate Isabelli]

I am still plugging away at this.  Through some playing around, I've determined that the issue is that  my emailAddr and eventRequested value setting statements are the problem.  I logs tell me "row is not defined" in reference to those statements.

Any suggestions for me?

[Jon Couch]

Kate, I thought someone more skilled than I would have responded but the question I have is about how you're passing the value for the row. It looks like you declare that you are passing the array data to _rowChanged but you are declaring your constants as values from an array "row" instead of "_rowChanged" that may be why you are getting the error "row is not defined"  I hope that helps, Jon

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/466d14fd-2fe2-47ec-ab46-ac430528be55n%40googlegroups.com.

[Kate Isabelli]

I completely agree, but I'm still stumped.  I went back and tested my basic logic using hard-coding and worked my way back.  If I set the variable emailAddr to a literal string like 'micke...@gmail.com" I can get the email to go through with my subject and body.  So the basic structure works.

So then, I thought I would try to directly reference a specific CELL in my sheet that contains an email address, such as cell A2.  But I can't even get that notation to work.  (I'm pretty sure I've just confused myself at this point, I've been playing with variations for so long!)  What code statement would specifically set emailAddr to the contents of cell A2 in my sample sheet?

[CBMServices Web]

Hi Kate,

 Looked through your script and made some changes to make it work. Try this:

function onEdit(event) {

  var colChanged = event.range.getColumn();

  var rowChanged = event.range.getRow();

  var newValue = event.value;

  Logger.log("Row: " + rowChanged + " Col: " + colChanged + " Value: " + newValue);

  if (colChanged == 3 && newValue == "TRUE")

  {

    sendtheEmail(rowChanged)

  }

 }

function sendtheEmail(_rowChanged) {

 // get data

  var sheet = SpreadsheetApp.getActiveSheet();

  var lastCol = sheet.getLastColumn();

  var rowData = sheet.getRange(_rowChanged,1,1,lastCol).getValues()[0];

    const emailAddr = rowData[0]

    const eventRequested = rowData[1]

 // create subject line

 const subjectLine = 'Re your request for coverage for an assigned duty'

 // create email body

 const htmlBody = 'Coverage requested for ' + eventRequested

  Logger.log("Called sendEmail." + htmlBody);

 // send email

GmailApp.sendEmail(emailAddr,subjectLine,'',{htmlBody: htmlBody});

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f8802dcc-ec06-4ce4-a623-d30fe590cef7n%40googlegroups.com.

[Jon Couch]

I was just testing an onEdit version. That should solve the problem.

Jon

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3XKqnszatXkQrjKNBdamqrHqnEgTXRJR-qnEo_BzgnKGg%40mail.gmail.com.

[cbmserv...@gmail.com]

Yeah sorry, onEdit would not work as there is a call to sendEmail. Kate needs to keep the function name the same as before. I had commented out the GmailApp.sendEmail so I could do a quick test on it via onEdit.

 

Her solution needs to use a manually added trigger and she should not use the restricted function name onEdit for this to work for her situation.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADjEj86Tzj4ZpJWaZp5dUiQE7u4oLrkACF_9NWwFtc4%2Be5W0%3Dg%40mail.gmail.com.

[Kate Isabelli]

Just wanted to post a huge "thank you"!  I appreciate ALL of the contributions on this thread because you both helped me to get to a working answer.  And I learned a lot in the process, which is always a win.  So the explanations are appreciated too.  Thank you for your time!

I have one more problem to solve on this one, which I can live w/o if I don't fix it.  But if I don't figure that one out myself I'll put it on a different thread.

[Keith Andersen]

katieCan you post the script that worked for others benefit?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9ad25355-cb91-4de8-8962-a4f31681dcben%40googlegroups.com.

[Kate Isabelli]

Thank you for the reminder!  I started to do that, and then got distracted.  (Occupational hazard!)  Here is the code, and of course, I also had to attach the  triggerOnChange function to the OnEdit event.

function triggerOnChange(event) {

  var colChanged = event.range.getColumn();

  var rowChanged = event.range.getRow();

  var newValue = event.value;

  if (colChanged == 3 && newValue == "TRUE")

  {

    sendtheEmail(rowChanged)

  }

}

function sendtheEmail(_rowChanged) {

 // get data

  var sheet = SpreadsheetApp.getActiveSheet();

  var lastCol = sheet.getLastColumn();

  var rowData = sheet.getRange(_rowChanged,1,1,lastCol).getValues()[0];

  const emailAddr = rowData[0]

  const eventRequested = rowData[1]

 

 // create subject line

 const subjectLine = 'Re your request for coverage for an assigned duty'

 // create email body

 const htmlBody = '<br>' + 'You requested coverage for the following duty: ' + eventRequested + '<br>'

 // send email

GmailApp.sendEmail(emailAddr,subjectLine,'',{htmlBody: htmlBody});

}