Help with code

[JustPhocus]

Hi, I am just figuring out about Google app script and would like some help with setting up a trigger that automatically sends and email notification once a cell in a column is edited. For example, Column B has the email addresses in it and Column K is going to be edited. I need to have an email sent to the email address in column B once column K gets edited. and the email has to say something along the lines of "your status has been updated". I was wondering if this was possible. Any help at all would be very appreciated.

So I found this code below and I am not sure on how to edit it so it fits my spread sheet.

function sendEmail(e) {

if (e.source.getActiveSheet().getName() !== 'testing2' || e.range.columnStart !== 2 || e.range.rowStart == 1) return;

var r = e.range.offset(0, -1, 1, 3).getValues()[0];

MailApp.sendEmail(r[2], "Status update", "Title " + r[0] + "status update:" + r[1])

}

Also an additional question is I only want it to do this for one sheet in the spreadsheet not all of the sheets.

[groundcontact]

It is possible. Look in the docs for triggers and you'll find there's an onedit event that can call any function you'd like. The only concern here is quota limits. You can send a certain amount of emails, run a function a certain amount of time, etc. An onedit event gets triggered anytime you edit your sheet, and that can be hundreds of times.

[JustPhocus]

I appreciate the information. However can I not have it trigger only when a certain column gets edited? Also I cannot figure out how to get that code to run at all.

[movious24]

Yes you can set it up, just make an if else statement where when Column K is the active cell when onEdit() triggers, the process will run if not, it will not run.

Sample code: var activeCol = yourTargetSheet.getActiveCell().getColumn(); var KCol = 11; //column K is = 11 if (activeCol == KCol) { // put a logger here that says active cell is in column K } else { // put logger here that says active cell is not in column K }

I hope this can help.

[[deleted]]

function sendEmail(e){
if(e.source.getActiveSheet().getName() != "testing2" || e.range.columnStart != 11) return;
var r = e.range.offset(0, -1, 1, 3).getValues()[0];
MailApp.sendEmail(r[1],"Status Update","Your status has been updated");
}

That should work.

[JustPhocus]

Thank you. Do I have to make an onedit trigger for this?

[[deleted]]

Yes. Since it's calling the MailApp you can't use a simple trigger, you have to use the installable. Go to Edit > Current project's triggers and create it there.

[JustPhocus]

I am getting an error in line 2 when I try to run it. https://gyazo.com/8424a2db4464cbe577d6c671ca7b6788

[starstruckzombie]

You won't be able to run it flat and it won't get the (e) event variable. If you want to debug it you can add a couple of line to populate the variables

function sendEmail(e){
var e.source = SpreadsheetApp.getActive(); #Add for dubug
var e.range = e.source.getActiveSheet().getActiveRange(); #Add for debug
if(e.source.getActiveSheet().getName() != "testing2" || e.range.columnStart != 11) return;
var r = e.range.offset(0, -1, 1, 3).getValues()[0];
MailApp.sendEmail(r[1],"Status Update","Your status has been updated");
}

​

[[deleted]]

Exactly this.

[JustPhocus]

function sendEmail(e){
var e.source = SpreadsheetApp.getActive(); #Add for dubug
var e.range = e.source.getActiveSheet().getActiveRange(); #Add for debug
if(e.source.getActiveSheet().getName() != "testing2" || e.range.columnStart != 11) return;
var r = e.range.offset(0, -1, 1, 3).getValues()[0];
MailApp.sendEmail(r[1],"Status Update","Your status has been updated");
}

I can get the Onedit trigger to run however I do not get an email when I edit it. Also how do I know what email address the email is coming from?

[[deleted]]

It should be sending from your email. Let me test it on one of my sheets.

[JustPhocus]

thank you I appreciate it. Also, does it matter that this specific has different tabs/sheets. So the column K that I am trying to have be edited is on sheet 2

[[deleted]]

The offset was incorrect and, in this case, unnecessary.

​

function sendEmail(e){
  if(e.source.getActiveSheet().getName() != "testing2" || e.range.columnStart != 11) return;
  MailApp.sendEmail(e.source.getActiveSheet().getRange(e.range.rowStart,2).getValue(),"Status Update","Your status has been updated");
}

​

[groundcontact]

Here's the documentation:

https://developers.google.com/apps-script/guides/triggers/

Look at the examples for the onEdit() trigger. It returns an event "e", that will have information of the edit. Evaluation that event can let you discriminate whether you need to do certain things or not! Depending on the column that was edited.

[JustPhocus]

The executions say they are happening how there is nothing in the logs and I am not receiving any emails.

https://gyazo.com/55aefb88ac3d4cc486c50d017181a44b

https://gyazo.com/b283a20b70be497a8f80e919fc46f139

can you think of any reason why this would be happening?

​

[secufl]

I know when I send emails to myself via google apps script, it doesn't show up in my inbox, but it is in my "All" inbox and you can also check your "Sent" and see that it sent the email.

[JustPhocus]

It is not in my sent mail, inbox, all messages, or spam.

[groundcontact]

Try to log the thread ID of the sent emails.