Generate PDF and attach Email - School Reports

[Kareem Gomez]

Hi Good Night All. I'm new to the community, and not a "programmer" so I don't even know if this is the correct place to get help for my issue.

I had someone help me with a code that turns the info on a Google Sheet (a student's academic report) into a pdf and send it as an attachment to the email address located in a cell on said sheet. This code works fine. 

However, I wanted to be able to send out reports for an entire class via one script/button instead of sending each student's report individually. I assume this is possible because I was able to get it done in Microsoft Excel via VBA... with help of course :)

If this is not the place to seek such assistance, can you point me in the right direction?

Kind regards,

Kareem

[Scott Bennett]

Are the spreadsheets different files or different tabs in the same file?

Could you share a sample of the code and the setup of the sheet?

Scott Bennett

Sent from my iPhone 

On Dec 14, 2021, at 7:22 PM, Kareem Gomez <school.re...@gmail.com> wrote:

Hi Good Night All. I'm new to the community, and not a "programmer" so I don't even know if this is the correct place to get help for my issue.

--
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/e825661e-e3bd-4df5-996a-938d16091a5fn%40googlegroups.com.

[Kareem Gomez]

Hi Scott. It's different tabs in the same File.

Below is code that I got help with that tries to send for the entire class. The emails are sent but there seems to be an error with the PDF attachment. I will attach a pic of the tab as well so you can see what the student report looks like.

function handleMultipleEmails() {

  // Get the active spreadsheet

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Get the Marks Master sheet and from that sheet, get the list of students

  const sheet = ss.getSheetByName("Marks Master");

  const students = sheet.getRange("AO2:AO41").getValues();

  // For each student, call the emailReportK function passing in the student's name

  // The student's name will be set to the C8 cell

  const emailInfo = getPDFUrls(students);

  const pdfs = convertToPDF(emailInfo);

  sendEmails(pdfs, emailInfo);

}

function getPDFUrls(students) {

  let emailInfo = []

  

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const studentTerm = ss.getSheetByName("Student Report");

  var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/";

  const sheetId = studentTerm.getSheetId();

  let url = ss.getUrl();

  url += '#gid=';

  url += sheetId;

  ss.setActiveSheet(studentTerm);

  for(let i = 0; i < students.length; i++) {

    //Set the student's name

    ss.getRange('C8').setValue(students[i]);

    const words = ss.getRange('C8');

    const title = words.getValues()[0];

    const term = ss.getRange("D10");

    const terms = term.getValues()[0];

    const email = ss.getRange('g6').getValues()[0];

    const school = ss.getRange('C1').getValues()[0];

    const subject = school + ": " + title + "_Term_"+ terms +" Report";

    const motto = ss.getRange('C2').getValues()[0];

    const body = "Good day Parent/Guardian, \n\nPlease find attached, school term report for " + title + "."+ "\n\nRegards, \n" + school + "\n" + motto + "\n";

  

    

    const exportOptions ='export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided

      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 

      // following parameters are optional...

      + '&size=letter'      // paper size

      + '&portrait=true'    // orientation, false for landscape

      + '&fitw=false'        // fit to width, false for actual size

      + '&sheetnames=false&printtitle=false&pagenumbers=true'  //hide optional headers and footers

      + '&gridlines=false'  // hide gridlines

      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

      emailInfo.push({url: url_base+exportOptions, email: email, subject: subject, body: body})

  }

  return emailInfo

}

function convertToPDF(emailInfo) {

  const urls = []

  for(let i = 0; i < emailInfo.length; i++) {

    urls.push(emailInfo[i].url)

  }

  var pdfs = UrlFetchApp.fetchAll(urls);

  return pdfs

}

function sendEmails(pdfs, emailInfo) {

  for(let i = 0; i < pdfs.length; i++) {

    var blob = pdfs[i].getBlob().setName(emailInfo[i].subject + '.pdf');

    

    const mailOptions = {

      attachments:blob

    }

    GmailApp.sendEmail(emailInfo[i].email, emailInfo[i].subject, emailInfo[i].body, mailOptions);

  }

}

function emailReportK(student) {

 

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const studentTerm = ss.getSheetByName("Student Report");

  const sheetId = studentTerm.getSheetId();

  var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/";

  let url = ss.getUrl();

  url += '#gid=';

  url += sheetId;

  Logger.log("Url: ", url);

  ss.setActiveSheet(studentTerm);

  // const sheet = ss.getRange(1, 1, 46, 16);

  // Subject of the email message

  

  //Set the student's name

  ss.getRange('C8').setValue(student);

  const words = ss.getRange('C8');

  const title = words.getValues()[0];

  const term = ss.getRange("D10");

  const terms = term.getValues()[0];

  const email = ss.getRange('g6').getValues()[0];

  const school = ss.getRange('C1').getValues()[0];

  const subject = school + ": " + title + "_Term_"+ terms +" Report";

  const motto = ss.getRange('C2').getValues()[0];

  const body = "Good day Parent/Guardian, \n\nPlease find attached, school term report for " + title + "."+ "\n\nRegards, \n" + school + "\n" + motto + "\n";

  

  //"Good day Parent/Guardian," & vbLf & vbLf _

 // & "Please find attached, " & Title & "'s School Term Report." & vbLf & vbLf _

 // & "Regards," & vbLf _

  //& School & vbLf _

 // & Motto & vbLf

  

  // Email Text. You can add HTML code here - see ctrlq.org/html-mail

 

   // let sheet = ss.getSheetName("Student-Term");

  

 // const unformattedUrl = studentTerm.getUrl();

 // Logger.log("SpreadSheet Url " + unformattedUrl);

 // let formattedUrl = unformattedUrl.split("/");

 // formattedUrl = formattedUrl.slice(0, formattedUrl.length - 1);

 // formattedUrl = formattedUrl.join("/");

 // formattedUrl = formattedUrl + "/export?";

  

    const exportOptions ='export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided

      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 

      // following parameters are optional...

      + '&size=letter'      // paper size

      + '&portrait=true'    // orientation, false for landscape

      + '&fitw=false'        // fit to width, false for actual size

      + '&sheetnames=false&printtitle=false&pagenumbers=true'  //hide optional headers and footers

      + '&gridlines=false'  // hide gridlines

      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  

  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  

  var response = UrlFetchApp.fetch(url_base+exportOptions, params);

  var blob = response.getBlob().setName(subject + '.pdf')

  

  var mailOptions = {

      attachments:blob

  }

  

  //var pdfFile = ss.getBlob().getAs('application/pdf').setName("Pdf1");

  

  //// Send the PDF file as an attachement 

    GmailApp.sendEmail(email, subject, body, mailOptions);

}

[Scott Bennett]

I will have to study this and dissect what is going on exactly. 

Scott Bennett

Sent from my iPhone 

On Dec 14, 2021, at 7:51 PM, Kareem Gomez <school.re...@gmail.com> wrote:

Hi Scott. It's different tabs in the same File.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c605c1a0-e383-46d0-8ffc-9810f73deb2fn%40googlegroups.com.

[Kareem Gomez]

I can give you my layman's interpretation lol. I don't know how much it will help. 

The last function  emailReportK(student) by itself, successfully sends the report as seen in the pic as a PDF to the email address located in cell g6 (it's in white ink lol)

I initially thought that some sort of basic For Loop would work 

but the person who was trying to help me put these 4 additional functions ie (Handle Multiple Emails, GetpdfURL, Convert to PDF, Send Emails)

I assume he knows why he needed these steps. 

But as it is the code is not attaching the PDFs correctly when sending the emails for the entire class.

[Kareem Gomez]

Update... I watched a couple of YouTube videos on For Loops and I put it into the code that works for sending one report. The first time I got it to do 8 reports successfully ie (email and pdf) and then I got an error msg. The second attempt it did 7 reports then the same error message.

Exception: Request failed for https://docs.google.com returned code 429. Truncated server response: <!DOCTYPE html><html lang="en"><head><meta name="description" content="Web word processing, presentations and spreadsheets"><meta name="viewport" c... (use muteHttpExceptions option to examine full response)

emailALL

@ MyEmailAll.gs:65

Below is the code that I used.

function emailALL() {

 

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const studentTerm = ss.getSheetByName("Student Report");

  const sheetId = studentTerm.getSheetId();

  const sheet = ss.getSheetByName("Marks Master");

  const students = sheet.getRange("Ao2:Ao41").getValues();

  var loopCount= sheet.getRange("Ao1").getValues();

  var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/";

  let url = ss.getUrl();

  url += '#gid=';

  url += sheetId;

  Logger.log("Url: ", url);

  ss.setActiveSheet(studentTerm);

  for(var i=0; i<loopCount +1; i++){

    ss.getRange('C8').setValue(students[i]);

  

  // const sheet = ss.getRange(1, 1, 46, 16);

  // Subject of the email message

  Logger.log(url+exportOptions);

  var response = UrlFetchApp.fetch(url_base+exportOptions, params);

  var blob = response.getBlob().setName(title + '.pdf')

[Zack Reynolds]

I just dealt with this situation and you need to incorporate a Utilities.sleep(5000); in between creating each PDF so as to not exceed an unofficial cap on how many PDFs Google will let you create in a particular time period.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d3b2c243-b17f-4259-a3dc-2f9b48c7349en%40googlegroups.com.

[Kareem Gomez]

I DID IT and IT WORKED!!!!

Thank you so much Zack... And Scott before you. A problem that was bothering me for 2 weeks I got solved in one night.

Really Really Appreciate It.

Thanks Again

Good Night