Google Apps Script Question

[Joe Sanders]

Hello everyone! I am hoping someone might be able to help me out.

I have a bit of experience with Google Apps Scripts, and already have one setup that emails different people based on what data is submitted in a form.

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

So thta is a total of 7 columns.

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 the first column 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 folks might be able to help figure this out, if it's even possible.  Thanks!!

function sendEmail(e){

  Logger.log(e.values); // just so you can see what values are

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

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

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

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

 

 

  if (col === 0 && 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)}

}

[Jon Couch]

Joe, are you calling sendEmail from an onEdit function? Do you need them to go out immediately? We have a lot of these types of applications so we just set up timed triggers that fire from between 15 minutes and and hour or two (depending on the application) that just loops through the sheet and in your case would check the value in Column D for "Yes" and a timestamp column (elsewhere on the sheet) for a value. If Column D is Yes and the Timestamp column is empty it sends the email and puts the timestamp (new Date()) in the appropriate column.  If you don't use the timestamp method, you'll need to set Column D back to null or something other than "Yes". 

Here's one of my earliest versions so it's pretty basic. The spreadsheet is set up so that if it's after 8:00 AM of the date in Column A and Column C is blank, it puts "Ready" in Column B. Every couple of hours the trigger fires and looks for "Ready" (there will only ever be one "Ready"). If that's found, it executes the email script and timestamps Column C on that row. I don't know if this will help you any but feel free to play around with it. It's similar to what you're trying to accomplish, if you don't need the immediacy of the onEdit.

function emailEmployees(){

  //Open spreadsheet from trigger

  var spreadsheet = SpreadsheetApp.openById('XXXX');

  var ss = spreadsheet.getSheetByName('Schedule');

  ss.activate();

 

   //Define variables

  var aVals = ss.getRange("A1:A").getValues();

  var aLast = aVals.filter(String).length;

  var lrow = aLast;

 

  //Check status

  for (row = 2; row<lrow+1; row++){

  var complete = ss.getRange(row,2).getValue();

   

   if (complete=="Ready")

  {

 

  //Set up email template

  var htmlBody = HtmlService.createTemplateFromFile('end_of_period_email');

  var email_html = htmlBody.evaluate().getContent();

   

  //send email

  MailApp.sendEmail(

    {

    to: "XXXX",

    bcc: "XXXX",

    subject: "End of Pay Period",

    htmlBody: email_html,

    replyto : "XXXX",

    }

  );

   var dstr = Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy HH:mm:ss");

   ss.getRange(row,3).setValue("Email Sent "+ dstr);

  }

 }

}

Good luck!

Jon

This message (and any associated files) may contain confidential and/or privileged information. If you are not the intended recipient or authorized to receive this for the intended recipient, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by sending a reply email and delete this message. Thank you for your cooperation.

--
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/3dbb1bc6-f8e8-4cda-b7c3-150986cb4034n%40googlegroups.com.

[Joe Sanders]

Hi Jon, 

Thanks for the response and the script.  So I am trying to play around with it, and see if I can get it at least working like you suggest, but I am getting the following error:

Start	Function	Error Message	Trigger	End
2023-09-01 21:55:21 Eastern Daylight Time	emailEmployees	TypeError: Cannot read properties of null (reading 'activate')	time-based	2023-09-01 21:55:22 Eastern Daylight Time

Here is my script:

function emailEmployees(){

  //Open spreadsheet from trigger

  var spreadsheet = SpreadsheetApp.openById('1DrJz1NBmTFcVRiF5g3a5TgG4MbmF1zhRn5bne9vqosg');

  var ss = spreadsheet.getSheetByName('Copy of STUDENT PRINTING REQUESTS');

  ss.activate();

 

   //Define variables

  var aVals = ss.getRange("A1:A").getValues();

  var aLast = aVals.filter(String).length;

  var lrow = aLast;

 

  //Check status

  for (row = 2; row<lrow+1; row++){

  var complete = ss.getRange(row,2).getValue();

   

   if (complete=="Ready")

  {

 

  //Set up email template

  var htmlBody = HtmlService.createTemplateFromFile('end_of_period_email');

  var email_html = htmlBody.evaluate().getContent();

   

  //send email

  MailApp.sendEmail(

    {

    to: "IwantEmailFromColumn E to go here",

    bcc: "mye...@mydomain.com",

    subject: "End of Pay Period",

    htmlBody: email_html,

    replyto : "mye...@mydomain.com",

    }

  );

   var dstr = Utilities.formatDate(new Date(), "GMT-5", "MM-dd-yyyy HH:mm:ss");

   ss.getRange(row,3).setValue("Email Sent "+ dstr);

  }

 }

}

Heres the current sheet headers:

Print Job Completed?

Email Sent Timestamp

Form Submitted Timestamp

Email Address

Name

Class Printing is for

Click yes to show you understand that printing request are in black and white only. No color copies.

Attach a PDF of your document. See the video on the HPA LIbrary site for instructions on turning your document into a PDF and save it to your Google Drive. We will not print from email or Google docs, etc. It must be a PDF attached here.

Any ideas?

Sincerely,

Joe Sanders Technology Director p: 864-316-9788 ext 777 e: jsan...@hpaspart.org w: support.hpaspartanburg.com Book an Appointment!

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

[Keith Andersen]

The 'activate' has something to do with the sheet name. Check that the sheet name is exactly as you have put it. 

Also putting spaces in sheet names often causes problems. FYI

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAOMDwTNh-J3MaQAJGp%2BT%3DHFDK4VxsuRS7XhWEFY75MXgR98L%3DA%40mail.gmail.com.