How to Convert and Email Google Spreadsheets as PDF Files

Introduction

Google Sheets is a powerful tool for managing data, but sometimes you may need to share your spreadsheet as a PDF document. With Google Apps Script, you can automate the process of converting Google Sheets to PDF files and then emailing them directly from within Google Sheets. This can save you time and streamline your workflow.

Step 1: Set Up Your Google Spreadsheet

Ensure your Google Spreadsheet is properly set up with the necessary data that you wish to convert to PDF. Here’s an example of a simple Google Sheet layout:

| Name       | Email               | Amount |
|------------|---------------------|--------|
| John Doe   | [email protected]     | 100.00 |
| Jane Smith | [email protected]     | 250.00 |
        

Make sure to check the data before running the script so that everything looks good for conversion to PDF.

Step 2: Write a Google Apps Script to Convert and Email PDF

Now you will create a Google Apps Script to automate the process. Here's how:

1. Open your Google Spreadsheet.
2. Go to Extensions > Apps Script to open the script editor.
3. Paste the following code into the script editor:

function convertAndEmailPDF() {
    // Get the active Google Spreadsheet
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = spreadsheet.getActiveSheet();
    
    // Convert the sheet to PDF
    var pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf');
    
    // Set the email subject and body
    var subject = 'Here is your PDF Report';
    var body = 'Hello, please find your PDF report attached.';
    
    // Specify the recipient's email address (this can be dynamic based on the sheet)
    var email = sheet.getRange('B2').getValue();  // Assuming the email is in cell B2
    
    // Send the email with the PDF attached
    MailApp.sendEmail({
        to: email,
        subject: subject,
        body: body,
        attachments: [
            {
                fileName: 'Spreadsheet_Report.pdf',
                content: pdf.getBytes(),
                mimeType: 'application/pdf'
            }
        ]
    });
}
            

4. This script will convert the active Google Sheet into a PDF and email it to the address listed in cell B2 of your Google Sheet. You can adjust the email recipient to suit your needs (e.g., using a column that contains multiple email addresses).
5. Click on the disk icon to save the script.

Step 3: Configure the PDF Conversion Settings

The script provided above will convert the entire sheet to a PDF. You can customize the script to control the PDF format (e.g., landscape, portrait, size, etc.) using the following options:

var pdfOptions = {
    exportFormat: 'pdf',
    format: 'pdf',
    size: 'A4',
    portrait: true,  // Set to false for landscape
    fitw: true,      // Fit to width
    top_margin: 1,
    bottom_margin: 1,
    left_margin: 1,
    right_margin: 1
};
        

Add this to the script when fetching the PDF file:

var pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf', pdfOptions);
        

This allows you to adjust the layout and style of the PDF to better suit your needs.

Step 4: Running the Script

Once the script is saved, you can run it to convert the Google Spreadsheet into a PDF and email it. Follow these steps:

1. In the Apps Script editor, click on the play button (▶️) to run the convertAndEmailPDF function.
2. The script will convert the active sheet into a PDF and email it to the address specified in cell B2 of your spreadsheet.
3. After running the script, check your inbox to confirm that the PDF has been sent successfully.

Step 5: Automate the Process with Triggers

If you need to automate this process (e.g., email PDFs at regular intervals), you can set up a trigger in Google Apps Script:

1. In the Apps Script editor, go to Triggers > Add Trigger.
2. Select the function you want to run (e.g., convertAndEmailPDF), and set the trigger type to run based on time (e.g., every day, week, etc.).
3. Once set, the script will run automatically at the specified intervals and send PDFs as needed.

Step 6: Customize the Email Content

You can further customize the email content in the script to include more information. For example, you could pull data from the spreadsheet to personalize the email message:

var name = sheet.getRange('A2').getValue();  // Assuming the name is in cell A2
var emailMessage = 'Hello ' + name + ',\n\nPlease find your PDF report attached.';
        

This way, each recipient gets a personalized email along with their PDF report.

Conclusion

With Google Apps Script, you can easily convert Google Spreadsheets to PDF files and send them via email. Whether you need to send a single report or automate the process for multiple recipients, this method is an efficient way to share your data in a professional format. By customizing the script, you can adjust the layout, automate the process, and even personalize the email messages.