Automatically email a Google Sheet as a PDF attachment

In this post, I'll teach you how to automatically email a Google Sheet as a PDF attachment from within Google sheets itself. Sending emails with attachments is a very common use case in the business world. Just ask any analyst - they probably spend hours every day putting together and emailing spreadsheet based reports to various stakeholders.

A typical workflow might look like this:

• There is usually a spreadsheet that contains some raw data.

• A report is created either in a document or in the spreadsheet itself as a separate tab.

• The report is exported as a PDF.

• The PDF is emailed to various stakeholders.

In this post, I'll show you how to automate the above workflow. Especially steps 3 and 4. Whether you can automate the first two steps will depend on your specific use case.

Consider the following spreadsheet where I have data on one sheet and a report template in another.

The Data sheet has the monthly sales numbers.

The Report sheet has a template that summarizes the key info the CEO cares about.

The goal is to send out the report as a PDF attachment from within Google sheet itself. It's very easy to do that using Apps Script. First open the script editor by selecting Extensions → Apps Script. Then copy paste the following code into the editor.

function sendReport() {
  var message = {
    to: "youremail@example.com",
    subject: "Monthly sales report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "Bob",
    attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("Monthly sales report")]
  }
  MailApp.sendEmail(message);
}

to: "youremail@example.com, email2@example.com, email3@example.com",

The message object has a property called attachments that is used to specify an array of files to attach with the email message.

The following code exports the spreadsheet as a PDF file named "Monthly sales report".

SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("Monthly sales report")

To run the script, click the Run button.

When you run it, Google will ask you to authorize the script to access your data and send out the email. Once you authorize it, you'll receive an email like the one below.

There's one problem though, the attached PDF also includes the sheet containing the raw data that was used to generate the report. We don't want that. We only want to attach the sheet called "Report".

One way to solve this is to first hide the data sheet using code, send out the email and then unhide the data sheet using code.

Here's the code to do that:

function sendReport() {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").hideSheet();
  var message = {
    to: "youremail@example.com",
    subject: "Monthly sales report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "Bob",
    attachments: [SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("Monthly sales report")]
  }
  MailApp.sendEmail(message);
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").activate();
}

The following line hides the sheet called "Data".

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").hideSheet();

Then this other line in the code unhides it after sending the email.

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").activate();

Now, the attached PDF only contains the sales report and the raw data isn't included anymore. It looks really nice too! Here's a preview of the attached PDF from within Gmail.

The final step is to make it possible so this report can be emailed with just a single click. The easiest way to do that is to insert a button (using a drawing) into the data sheet and assign the sendReport script to that drawing.

Insert a new drawing in the data sheet by selecting Insert→Drawing.

Then, draw a button like the one below and insert it into your sheet by clicking Save and Close.

Once you save your drawing, you should see the button in your sheet.

The final step is to assign the sendReport script to this button so that clicking the button will run the script.

Click the "three dots menu" (it will appear if you move your mouse over the button) and select Assign script.

Then, enter sendReport in the popup window and click OK.

Now, whenever you click the button, the sendReport script will be run and the email will be sent out with the report attached as a PDF.

Summary

In this post, you learned how to automate emailing a Google Sheet as a PDF attachment.