Streamlining Document Workflow with Google Apps Script: Creating PDFs and Sending Emails

In the realm of Google Workspace automation, efficiency and seamless document handling are key. Google Apps Script provides a powerful toolset to enhance your workflow, and in this blog post, we'll explore how to leverage it for creating PDFs from Google Sheets and seamlessly sending them via email. We'll dive into the code and discuss how these scripts work together to automate document generation and distribution.

Creating PDFs with convertSpreadsheetToPdf

Let's start by examining the convertSpreadsheetToPdf function, responsible for converting a Google Sheet into a PDF document:

function convertSpreadsheetToPdf(sheetName, pdfName) {

  // Retrieve the sheet ID or default to null

  var sheetId = sheetName ? SPREADSHEET.getSheetByName(sheetName).getSheetId() : null;  

  // Set the PDF name or default to the spreadsheet name

  var pdfName = pdfName ? pdfName : SPREADSHEET.getName();

  // Retrieve the parent folder of the spreadsheet

  var parents = DriveApp.getFileById(GSHEET_ID).getParents();

  var folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();

  // Construct the URL for exporting the spreadsheet as PDF

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

  var url_ext = 'export?exportFormat=pdf&format=pdf'   

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

      + '&size=letter'      

      + '&portrait=true'    

      + '&fitw=true'        

      + '&sheetnames=false&printtitle=false&pagenumbers=false'  

      + '&gridlines=false'  

      + '&fzr=false';

  // Set the headers for URL Fetch

  var options = {

    headers: {

      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),

    }

  }

  // Fetch the PDF content

  var response = UrlFetchApp.fetch(url_base + url_ext, options);

  // Convert the response to a Blob

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

  // Create the PDF file in the specified folder

  folder.createFile(blob);

  // Return the created Blob

  return blob;

}        

Explanation:

• The function takes two optional parameters: sheetName and pdfName. It allows you to specify a particular sheet within the spreadsheet and a custom name for the PDF.
• It constructs a URL for exporting the spreadsheet as a PDF, including various parameters for customization.
• The script fetches the PDF content using UrlFetchApp.fetch.
• It then creates a Blob from the response and saves it as a PDF file in the specified folder.

Sending Email with sendMail

Now, let's explore the sendMail function, responsible for sending an email with the generated PDF attachments:

function sendMail(email, blobs) {

  if (email) {

    // HTML content for the email body

    var html = <h3>Please find attached PMS for this week.</h3>

    // Get the subject for the email

    var weekdays = subjectTime();

    // Send the email with the specified subject, body, and attachments

    MailApp.sendEmail({

      noReply: true,

      to: email,

      subject: PMS - Calendar week from ${weekdays[0]} to ${weekdays[1]},

      htmlBody: html,

      attachments: blobs

    });

  }

}        

Explanation:

• The function takes an email parameter and an array of blobs representing the PDF attachments.
• It constructs an HTML body for the email.
• The script then sends an email using MailApp.sendEmail with the specified subject, HTML body, and attached PDFs.

Integration for Seamless Automation

Now, let's consider how these functions can be integrated into a seamless automation process. For example, you might run the following script to generate a PDF and send it via email:

// Define the sheet and PDF names

var sheetName = "YourSheetName";

var pdfName = "WeeklyReport";

// Convert the spreadsheet to PDF

var pdfBlob = convertSpreadsheetToPdf(sheetName, pdfName);

// Define the recipient email

var recipientEmail = "[email protected]";

// Send the email with the PDF attachment

sendMail(recipientEmail, [pdfBlob]);

// Log a confirmation message

Logger.log("PDF created and email sent successfully!");        

By combining these functions, you can create a streamlined workflow for generating PDFs from Google Sheets and delivering them directly to your recipients' inboxes. Whether it's weekly reports, invoices, or any other document, this approach enhances your document management with Google Apps Script. Experiment with these scripts, tailor them to your specific use case, and enjoy the benefits of automated document workflows in Google Workspace.