Loop and Trigger on Edit Assistance

kelo...@sewanee.edu

unread,

Sep 20, 2021, 5:20:52 PM9/20/21

to Google Apps Script Community

Hi Everyone,

I have an inelegant workflow in which, HR informs the IT coordinator of a new hire. The coordinator adds the new hire's information into a spreadsheet and then clicks a button to notify the system admin to create the new account. The system admin then goes into the spreadsheet and marks his task complete. When he marks his task complete, this triggers an email to the Google admin to create the email account. The Google admin then goes into the spreadsheet and marks her task complete. This then triggers another email etc.

I am struggling with two things.

1. I want to avoid duplicate emails. The script should skip rows in which particular cells contain the term "Email Sent". For some reason, this is not working. It is also sending emails for blank rows.

2. I can see how to trigger my script to run when the sheet is edited by I am struggling with how to trigger the script to run when the checkbox is checked.

Can anyone offer some advice? Also, if there is a better way to accomplish what I am trying to do, I am open to suggestions.

Here is my script:

function emailtoBannerAdmin(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName ("Preboarding"); //Get Sheet called "Preboarding"

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Preboarding");

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());

var data = dataRange.getValues();

var startRow = 2; //First row of data to process

for (var i=0;i<data.length;i++) {

var rowData = data [i];

var startdate = rowData [0];

var bannerid = rowData [1];

var firstname = rowData [2];

var lastname = rowData [3];

var previousaccountname = rowData [4];

var newaccountname = rowData [5];

var alias = rowData [7];

var emailstatus = rowData [8];

var checkbox1 = rowData [9];

if (emailstatus != "Email Sent"){

var message = "<HTML><BODY>"

+ "Rob,"

+ " "

+ "Please process the following request:"

+ " "

+ "Start Date: " + startdate + " "

+ "Banner ID: " + bannerid + " "

+ "First Name: " + firstname + " "

+ "Last Name: " + lastname + " "

+ "Previous Account Name: " + previousaccountname + " "

+ "New Account Name: " + newaccountname + " "

+ "Alias " + alias + " "

+ " "

+ "Chrissy"

+ " "

+ "Preboarding Spreadsheet" +' \n' + urlofsheet;

+ " "

+ "</HTML></BODY>";

MailApp.sendEmail(email address, "Account Creation", "", {htmlBody: message});

sheet.getRange(i + 2,10).setValue("Email Sent");

}

//Add send emails to the spreadsheet menu

function onOpen() {

var spreadsheet = SpreadsheetApp.getActive();

var menuItems = [

{name: 'Send to Banner Admin', functionName: 'emailtoBannerAdmin'}

];

spreadsheet.addMenu('Send Emails', menuItems);

}

function emailtoGoogleAdmin(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName ("Preboarding"); //Get Sheet called "Preboarding"

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Preboarding");

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());

var data = dataRange.getValues();

for (var i=0;i<data.length;++i) {

var rowData = data [i];

var startdate = rowData [0];

var bannerid = rowData [1];

var firstname = rowData [2];

var lastname = rowData [3];

var previousaccountname = rowData [4];

var newaccountname = rowData [5];

var alias = rowData [7];

var emailstatus = rowData [8];

var checkbox1 =data[i][10];

Logger.log(typeof checkbox1); // string

Logger.log(checkbox1); // Returns either "TRUE" or "FALSE"

if (emailstatus !== "Email Sent" &&checkbox1 == true) {

var message = "<HTML><BODY>"

+ "Diane,"

+ " "

+ "The following request:"

+ " "

+ "Start Date: " + startdate + " "

+ "Banner ID: " + bannerid + " "

+ "First Name: " + firstname + " "

+ "Last Name: " + lastname + " "

+ "Previous Account Name: " + previousaccountname + " "

+ "New Account Name: " + newaccountname + " "

+ "Alias " + alias + " "

+ " "

+ "Chrissy"

+ "Preboarding Spreadsheet" +' \n' + urlofsheet;

+ " "

+ "</HTML></BODY>";

MailApp.sendEmail(email address, "Account Creation", "", {htmlBody: message});

sheet.getRange(i + 2,12).setValue("Email Sent");

}

Thank you,

Kerline

cbmserv...@gmail.com

unread,

Sep 20, 2021, 6:25:50 PM9/20/21

to google-apps-sc...@googlegroups.com

Here are some comments and suggestions:

If emails are being sent again to some folks, then the email sent flag is probably being put in the wrong place. Looking at the statement that sets the flag:

sheet.getRange(i + 2,10).setValue("Email Sent");

versus the statement that checks the flag:

var emailstatus = rowData [8];

tells me it seems to be setting the flag in the wrong column. The i+2 is correct because you are starting reading the array from row 2. But the 10 should be 9 to be set in the correct column.

For skipping blank rows, you should determine which cell you want to be based off of and check if that cell is blank then skip the row based on that. For example if the startdate is blank, then skip. Something like this:

If( (emailstatus != "Email Sent” ) && (startdate != “”) )

To Have your emailToGoogleAdmin function trigger automatically, just define a trigger that would trigger on change and in the function, check first to see if the change is to the checkbox column being set to true, if not, just ignore. This way only when checkbox is set to true would your function process the rest and send the email.

Add these to statements at the top of your function:

const col = e.range.getColumn();

If ( (col != 11) || (e.valu != “TRUE”) ){return;}

Good luck

--
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/4ac80cbf-812e-4a86-899e-b89664dede85n%40googlegroups.com.

kelo...@sewanee.edu

unread,

Sep 22, 2021, 5:40:54 PM9/22/21

to Google Apps Script Community

Hi George,

Thank you for your assistance.

I change var emailstatus = rowData [8]; to var emailstatus = rowData [9]; but I kept sheet.getRange(i + 2,10).setValue("Email Sent"); .

That seemed to work. I doesn't work when the column numbers match for both statements. I don't know why.

I am having trouble with the function trigger. It may be as simple as where I am placing the statements. According to the debugger e and col are undefined. Can you please tell me what I am doing wrong?

function checkboxBannerAdmin(e){

const col = e.range.getColumn();

if ( (col != 11) || (e.valu != TRUE) )

{

return;

}

function emailtoBannerAdmin() {

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName ("Preboarding"); //Get Sheet called "Preboarding"

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Preboarding");

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());

var data = dataRange.getValues();

for (var i=0;i<data.length;i++) {

var rowData = data [i];

var startdate = rowData [0];

var bannerid = rowData [1];

var firstname = rowData [2];

var lastname = rowData [3];

var previousaccountname = rowData [4];

var newaccountname = rowData [5];

var password = rowData [6];

var alias = rowData [7];

var emailstatus = rowData [9];

if (emailstatus != "Email Sent" && startdate != ""){

var message = "<HTML><BODY>"

+ "Rob,"

+ " "

+ "Please process the following request:"

+ " "

+ "Start Date: " + startdate + " "

+ "Banner ID: " + bannerid + " "

+ "First Name: " + firstname + " "

+ "Last Name: " + lastname + " "

+ "Previous Account Name: " + previousaccountname + " "

+ "New Account Name: " + newaccountname + " "

+ "Password " + password + " "

+ "Alias " + alias + " "

+ " "

+ "Chrissy"

+ " "

+ "Preboarding Spreadsheet" +' \n' + urlofsheet;

+ " "

+ "</HTML></BODY>";

MailApp.sendEmail(emailaddress, "Account Creation", "", {htmlBody: message});

CBMServices Web

unread,

Sep 22, 2021, 7:18:18 PM9/22/21

to google-apps-sc...@googlegroups.com

The variable e is passed to the function by the trigger event. It contains the various common things that people typically need to run their function and minimize access to the spreadsheet file.

But if you run it manually, the e parameter is undefined. e parameter is only defined when run automatically by the trigger. So you can not run the function manually unless you fill out what the function is expecting to find in the e parameter.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/639ffdaf-1b9b-4d89-8d5f-c388a0b3d0b4n%40googlegroups.com.

kelo...@sewanee.edu

unread,

Sep 23, 2021, 5:35:11 PM9/23/21

to Google Apps Script Community

Got it, thank you George.

I've been playing around with the code all day and I can't get it right. The trigger on edit function works but it triggers when I add the start date not when I check the checkbox. Any thoughts?

function emailtoBannerAdmin(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName ("Preboarding"); //Get Sheet called "Preboarding"

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Preboarding");

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());

var data = dataRange.getValues();

const col = e.range.getColumn();

if ( (col != 8) || (e.valu != "TRUE") )

{

return;

}

for (var i=0;i<data.length;i++) {

var rowData = data [i];

var startdate = rowData [0];

var bannerid = rowData [1];

var firstname = rowData [2];

var lastname = rowData [3];

var previousaccountname = rowData [4];

var newaccountname = rowData [5];

var password = rowData [6];

var alias = rowData [7];

var checkbox1 =rowData [8];

var emailstatus = rowData [9];

if (emailstatus != "Email Sent" && startdate != ""){

cbmserv...@gmail.com

unread,

Sep 23, 2021, 7:16:43 PM9/23/21

to google-apps-sc...@googlegroups.com

You have a typo in the IF statement:

if ( (col != 8) || (e.valu != "TRUE") )

This should be:

if ( (col != 8) || (e.value != "TRUE") )

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/857d05d4-6803-4acc-94cf-58335080032en%40googlegroups.com.

kelo...@sewanee.edu

unread,

Oct 11, 2021, 5:02:20 PM10/11/21

to Google Apps Script Community

I hate to ask but I am struggling with this script.

My on edit trigger has stopped working and I cannot tell what went wrong.

Can someone please help me figure this out?

function emailtoBannerAdmin(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName ("Preboarding"); //Get Sheet called "Preboarding"

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Preboarding");

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

const col = e.range.getColumn();

if ( (col != 8) || (e.value != "TRUE") )

{

return;

}

var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());

var data = dataRange.getValues();

for (var i=0;i<data.length;i++) {

var rowData = data [i];

var startdate = rowData [0];

var bannerid = rowData [1];

var firstname = rowData [2];

var lastname = rowData [3];

var previousaccountname = rowData [4];

var newaccountname = rowData [5];

var position = rowData [6];

var password = rowData [7];

var alias = rowData [8];

var emailstatus = rowData [9];

if (emailstatus != "Email Sent" && startdate != ""){

var message = "<HTML><BODY>"

+ "Rob,"

+ " "

+ "Please process the following request:"

+ " "

+ "Start Date: " + startdate + " "

+ "Banner ID: " + bannerid + " "

+ "First Name: " + firstname + " "

+ "Last Name: " + lastname + " "

+ "Previous Account Name: " + previousaccountname + " "

+ "New Account Name: " + newaccountname + " "

+ "Position/Title: " + position + " "

+ "Password " + password + " "

+ "Alias " + alias + " "

+ " "

+ "Chrissy"

+ " "

+ "Preboarding Spreadsheet" +' \n' + urlofsheet;

+ " "

+ "</HTML></BODY>";

MailApp.sendEmail(emailaddress, "Account Creation", "", {htmlBody: message});

sheet.getRange(i + 2,10).setValue("Email Sent");

}

Here is a link to the spreadsheet if that helps: https://docs.google.com/spreadsheets/d/1T6J3Xdt2CnyXMJ9tUNQrvsKAibvjMKQqoC4msuUBuVU/edit#gid=0

Alan Wells

unread,

Oct 11, 2021, 7:19:15 PM10/11/21

to Google Apps Script Community

Installed triggers have a history of silently failing with no error.

If you didn't do anything to the code lately, then the issue probably has nothing to do with the code.

You should remove authorization for the code, delete the existing trigger, and reinstall it.

To remove third party apps with access to your account, go into your account settings. A link is provided below.

You aren't a third party to yourself, but Google describes even apps that you own as third party apps.

To remove authorization of your code go to:

https://myaccount.google.com/permissions?continue=https%3A%2F%2Fmyaccount.google.com%2Fsecurity

You can see all of your current triggers at:

https://script.google.com/home/triggers

Find the trigger and delete it, then reinstall it. I'm assuming you know how to do that since it was running before.

Clark Lind

unread,

Oct 12, 2021, 8:11:24 AM10/12/21

to Google Apps Script Community

You have declared var sheet twice. I don't think this would mess the trigger up, but comment out the second one and see if anything changes. Also, unless you actually use a function called "onEdit(e)", your trigger will only work for you. If others are using this sheet, then I would rename the function to onEdit(e). If there are more functions, then you can set some decision logic within the onEdit function that will then call the appropriate other function.

Example:
function onEdit(e) {

const src = e.source.getActiveSheet();

if (src.getName() === "Preboarding" ) {

emailtoBannerAdmin(e);

return;

}

if (src.getName() === "anotherSheet" ) {

anotherFunction(e);

return;

}

If you have many functions to run, a Switch statement might be more efficient than a bunch of If statements. Note: I don't like using a bunch of "if else" statements; I try to set my logic up so if it is true, it executes and return, otherwise the if statement is ignored.

Check out this video from Spencer Ferris. (who I plagiarized for the above snippet, thanks Spencer!)

On Monday, October 11, 2021 at 5:02:20 PM UTC-4 kelo...@sewanee.edu wrote:

kelo...@sewanee.edu

unread,

Oct 12, 2021, 5:34:54 PM10/12/21

to Google Apps Script Community

Thank you, both.

I removed access to the app and delete the triggers a number of times. It still doesn't work.

I watched Spencer Ferris' really cool video and tweaked the script. The script still wasn't triggered.

Maybe I should give up on the on edit trigger. What's another way to go about this? I could add a "send email" button...

Here is my revised script based on Spencer's video:

function onEdit(e) {

const src = e.source.getActiveSheet();

const r = e.range;

emailtoBannerAdmin(src,r);

emailtoADAdmin(src,r);

emailtoCoordinator(src,r);

emailtoGoogleAdmin(src,r);

}

function emailtoBannerAdmin(src,r){

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName("Preboarding"); //Get Sheet called "Preboarding"

//var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Preboarding");

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

const col = r.getColumn();

if ((col () != 9) || (e.value != "TRUE"))

//{

return;

//}

var dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

var data = dataRange.getValues();

CBMServices Web

unread,

Oct 12, 2021, 6:27:14 PM10/12/21

to google-apps-sc...@googlegroups.com

You have bugs in the code which prevents it from running correctly.

Note that your parameters passed in are src and r but you are still using e.value in the if statement.

So you need to fix that. Either get the e.value in your onEdit function and pass it on or pass the e value on to allow the if statement to work.

George

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1fdc2b57-36ab-45a9-a407-e7f10e3c1554n%40googlegroups.com.

Clark Lind

unread,

Oct 12, 2021, 8:17:46 PM10/12/21

to Google Apps Script Community

As George said, there are some things needing correction.
This code works. However, there is a limitation to the onEdit(e) simple trigger... it can't send email! So go ahead and create a trigger that calls onEdit, and is triggered onEdit. Then you'll need to provide a valid email address for the recipient (around line 46). You will also need to add a column to the spreadsheet to account for previous account name between last name and new account name.

function onEdit(e) {

emailtoBannerAdmin(e); //I only fixed this one.

// emailtoADAdmin(e);

// emailtoCoordinator(e);

// emailtoGoogleAdmin(e);

}

//I pulled this out as its own function

function sendEmail(to, subj, message) {

MailApp.sendEmail({

to: to,

subject: subj,

htmlBody: message

})

}

function emailtoBannerAdmin(e){

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName("Preboarding"); //Get Sheet called "Preboarding"

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

const col = e.range.columnStart;

if ((col != 10) || (e.value != "TRUE")) {

return;

}

var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());

var data = dataRange.getValues();

for (var i=0;i<data.length;i++) {

var rowData = data[i];

var startdate = rowData[0]; //removed all spaces. bad: "rowData [0]"; good: "rowData[0]"

var bannerid = rowData[1];

var firstname = rowData[2];

var lastname = rowData[3];

var previousaccountname = rowData[4]; //had to add this column to the sheet

var newaccountname = rowData[5];

var position = rowData[6];

var password = rowData[7];

var alias = rowData[8];

var emailstatus = rowData[10];

var emailAddress = "###ENTER VALID EMAIL ADDRESS WHO THIS IS BEING SENT TO###";

var subj = "Account Creation";

if (emailstatus != "Email Sent" && startdate != ""){

var message = "<HTML><BODY>"

+ "Rob,"

+ " "

+ "Please process the following request:"

+ " "

+ "Start Date: " + startdate + " "

+ "Banner ID: " + bannerid + " "

+ "First Name: " + firstname + " "

+ "Last Name: " + lastname + " "

+ "Previous Account Name: " + previousaccountname + " "

+ "New Account Name: " + newaccountname + " "

+ "Position/Title: " + position + " "

+ "Password " + password + " "

+ "Alias " + alias + " "

+ " "

+ "Chrissy"

+ " "

+ "Preboarding Spreadsheet" +' \n' + urlofsheet;

+ " "

+ "</HTML></BODY>";

// console.log("sending email")

sendEmail(emailAddress, subj, message)

sheet.getRange(i + 2,11).setValue("Email Sent");

}

} //added missing closing bracket

kelo...@sewanee.edu

unread,

Oct 13, 2021, 5:11:22 PM10/13/21

to Google Apps Script Community

Got it! I'll work on making the changes. Thank you.

kelo...@sewanee.edu

unread,

Oct 22, 2021, 5:27:25 PM10/22/21

to Google Apps Script Community

Cwl (I'm sorry I don't know your name),

I've tried this a few different ways and the trigger keeps failing. It says, " Exception: Failed to send email: no subject at sendEmail(Notifications:6:11) at emailtoBannerAdmin(Notifications:66:3) at onEdit(Notifications:2:3)".

Any thoughts on this?

Here is my script:

function onEdit(e){

emailtoBannerAdmin(e)

}

function sendEmail(to, subj,message){

MailApp.sendEmail({

to:to,

subj:subj,

htmlBody:message

})

}

function emailtoBannerAdmin(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get active Spreadsheet

var sheet = ss.getSheetByName ("Preboarding"); //Get Sheet called "Preboarding"

var urlofsheet = ss.getUrl(); //Get URL of active spreadsheet

const col = e.range.columnStart;

if((col != 9) || (e.value != "TRUE")){

return;

}

var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());

var data = dataRange.getValues();

for (var i=0;i<data.length;i++) {

var rowData = data [i];

var startdate = rowData [0];

var bannerid = rowData [1];

var firstname = rowData [2];

var lastname = rowData [3];

var newaccountname = rowData [4];

var position = rowData [5];

var password = rowData [6];

var alias = rowData [7];

var emailstatus = rowData [8];

var emailAddress = "###myemailaddressisinthescript###";

var subj= "Account Creation";

if (emailstatus != "Email Sent" && startdate != ""){

var message = "<HTML><BODY>"

+ "Rob,"

+ " "

+ "Please process the following request:"

+ " "

+ "Start Date: " + startdate + " "

+ "Banner ID: " + bannerid + " "

+ "First Name: " + firstname + " "

+ "Last Name: " + lastname + " "

+ "New Account Name: " + newaccountname + " "

+ "Position/Title: " + position + " "

+ "Password " + password + " "

+ "Alias " + alias + " "

+ " "

+ "Chrissy"

+ " "

+ "Preboarding Spreadsheet" +' \n' + urlofsheet;

+ " "

+ "</HTML></BODY>";

console.log("sending email")

sendEmail(emailAddress,subj,message)

sheet.getRange (i+2,10).setValue("Email Sent");

}

Thank you,

Kerline

Clark Lind

unread,

Oct 22, 2021, 5:57:06 PM10/22/21

to google-apps-sc...@googlegroups.com

It may be as simple as putting a space between subj and =. You have: var subj= "Account Creation"; try: var subj = "Account Creation";

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/ZKzqJGkjPIU/unsubscribe.
To unsubscribe from this group and all its topics, 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/5468c422-d4d8-4d1e-9142-86d639286a8dn%40googlegroups.com.

kelo...@sewanee.edu

unread,

Oct 27, 2021, 12:15:54 PM10/27/21

to Google Apps Script Community

In the sendEmail function, it did not like "subj" (I don't know why). I changed it to "subject" and now everything is working. Thanks for all of your help.

Reply all

Reply to author

Forward