Automated reporting using Google Apps Scripts (pt 2)

This is a follow up post on this one.

The Goal today

In the previous post we got the data in the Spreadsheet, now we are going to export them to a PDF and send them as attachment to an email address.

• Get the PDF from your spreadsheet
• Use Gmail to send an email with an attachment

Get the PDF from you spreadsheet

So we go back to our Spreadsheet (MyWeatherChannel) and open the script editor:

Opening the script editor

Create a new file script file

And rename the function provided to “sendUpdate”

rename myFunction to sendUpdate()

We created a new file just so we could separate the code better.

Just copy paste the following code in your editor:

This uses the DriveApp. We basically get the same file from the DriveApp but it can be exported to pdf using the getAs function.

Follow the numbers for more information about the flow.

Use Gmail to send an email the PDF as attachment

Having a file in memory somewhere is not what we want. So let’s construct the Gmail (email) function. The syntax is simple:

MailApp.sendEmail(recipient, subject, body, attachment)

So lets make a function that does just that and pass the PDF to it.

That’s it. Test it using the debugger:

Make sure to select the sendUpdate function and hit the ▶️-icon

Check your mails for the update!

Setup the triggers to get your daily email like this:

Open the current projects triggers

You can see the previous trigger (part 1). Click on “Add new Trigger”

And setup something like this: (I took 1 timeslot further to make sure the sheet is already updated before sending the update)

Enjoy your updates! 👏

Takeaway

Today we learned on how to generate a PDF from a Spreadsheet and email it to some email address.

As you can see you get a lot of stuff “for free” from Google. There are limits however, check them here.

For more information about the Google Apps script feel free to comment or check the Docs.

Want more?

If you need some advice on integrations between any 2 systems. I can provide you professional advice and integrations (Mobile, Backend systems, Websites, databases,…). More information can be found on b-nys.com. Feel free to follow me on Twitter and or LinkedIn.

If you liked this article, please share it with your network!

Matthias,