Google Sheets Apps Script

[ParatrooperJoe]

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)}

}

[Kim_Nilsson]

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

[ParatrooperJoe]

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 

[ParatrooperJoe]

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

[Kim_Nilsson]

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.

[ParatrooperJoe]

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 😞

[Kim_Nilsson · Accepted solution]

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.😁

[SteveHarmon]

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.

[icrew]

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

[ParatrooperJoe]

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.  

[ParatrooperJoe]

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!