topic Re: Google Sheets Apps Script in Peer-Peer Topics

Google Sheets Apps Script

ParatrooperJoe — Fri, 01 Sep 2023 15:41:10 GMT

I have a bit of experience with Google Apps Scripts, and already have one setup that emails specific people when a certain form is submitted.

I am now trying to set one to do slightly different task. There is a form that students fill out requesting to have a document printed by the Librarian. The Librarian has the sheet setup with the following headers:

Print Job Completed?, Timestamp, Email Address, Name, Class Printing is for, Click yes that you understand printing is in black and white only, Attached document

In this Sheet, the first column named "Print Job Completed?" is setup as a dropdown list with the only option of "YES!!!" . The default value for this field is blank

What the Librarian is hoping to accomplish, is the following: When she prints one of the submitted documents, find that row in the Sheet and change column one value to "YES!!!" and then she would like the Apps Script to send an email to the "Email Address" for that particular row, to let them know that document "Class Printing for" has been printed and is ready for pickup. I am sure that is as clears mud, lol.

I read an article that was sort of in line with what I am trying to accomplish, and here is what I have so far, which doesnt work, so I have no doubt I am doing it wrong. I am wondering if this fine group of Admins might be able to help figure this out, if its even possible. Thanks!!

function sendEmail(e){

const r = e.range; // Cell being edited.

const row = r.rowStart; // Row of the edit.

const col = r.columnStart; // Column of the edit.

const sh = r.getSheet(); // Sheet of the edit.

if (col === 1 && sh.getRange(row,4).getValue() == "Yes") {

const caseStatus = sh.getRange(row,5).getValue();

const email = sh.getRange(row,3).getValue();

const keName = sh.getRange(row,3).getValue();

const msg = "Hello, " + keName + ". Your document: " + caseStatus + " has been printed and is ready for pickup in the Library. Thank you.";

MailApp.sendEmail(email, "Document printed!", msg)}

}

Re: Google Sheets Apps Script

Kim_Nilsson — Fri, 01 Sep 2023 18:41:24 GMT

YES!!! and Yes are not the same thing.

Re: Google Sheets Apps Script

ParatrooperJoe — Fri, 01 Sep 2023 18:49:36 GMT

Doh! Thanks, Kim... that was a typo... Here is the error i get emailed after making that correction.

TypeError: Cannot read properties of undefined (reading '0') edit

Re: Google Sheets Apps Script

ParatrooperJoe — Fri, 01 Sep 2023 19:13:32 GMT

Okay this is the actual error message....

Start Function Error Message

2023-09-01 15:10:28 sendEmail TypeError: Cannot read properties of undefined (reading 'rowStart')

Trigger End

Edit 2023-09-01 15:10:29

Re: Google Sheets Apps Script

Kim_Nilsson — Fri, 01 Sep 2023 19:24:16 GMT

Looks like you are pulling your values in the wrong order? You can't pull a range before you know the sheet, and if you don't know the range, you can't pull the rowstart.

Re: Google Sheets Apps Script

ParatrooperJoe — Fri, 01 Sep 2023 19:38:24 GMT

i do beleive you are right, changing the order definitely changes the error, lol. Sadly, Im lost on what Im doing here.

If all i needed to do was email myself when an edit is made, i can do that with no problem, but figuring out how extract the email from the edited row is evading me 😞

Re: Google Sheets Apps Script

Kim_Nilsson — Fri, 01 Sep 2023 19:59:41 GMT

You could circumvent the entire problem by using Form Mule, and telling it to only email if the row has the Yes value.

It's fun to learn new things, but not everyone needs to invent the wheel.😁

Re: Google Sheets Apps Script

SteveHarmon — Fri, 01 Sep 2023 20:19:54 GMT

I agree with Kim's Form Mule suggestion. I use that for a ton of Forms and Sheets and it works great.

The only issue I've had was when our primary domain changed and it broke a couple forms, but that is a super-rare event for most districts.

Re: Google Sheets Apps Script

icrew — Fri, 01 Sep 2023 20:24:01 GMT

You might try asking this question over on the (very active) Google Apps Script list: https://groups.google.com/g/google-apps-script-community/about

Re: Google Sheets Apps Script

ParatrooperJoe — Sat, 02 Sep 2023 02:20:46 GMT

Unfortunately, FormMule does not accomplish what I am trying to do. Well, it does, except I cant seem to figure out how to setup any additional logic to keep it from continuing to email every single person that it has already emailed. The only way I can figure out how to do that, is to change the value in one cell, use the form mule button to click through sending preview, then after it sends the email, change that same rows specified cell to some other value other than the "trigger" value.

Re: Google Sheets Apps Script

ParatrooperJoe — Sat, 02 Sep 2023 03:15:48 GMT

Well, nevermind... I must have done something wrong... I started fron scratch on a new clean sheet, setup form mule, and form mule is working perfectly now. Thanks Kim!