Automated reporting using Google Apps Scripts (pt 1)

Some people may remember Visual Basic for Applications (VBA). Think a lot of programmers have at least touched or learned it while studying. It was a fast way to work with data from Excel, Access and even Word.

One of the main use cases for me was connecting to an external API and inserting the data into excel so I could make “interactive” spreadsheets with data from the internet (XML that was).

But I can’t tell the last time I’ve used Microsoft Office in the first place. So my VBA skills are not relevant anymore. I’m more of a Google Drive user now. So I started checking out Google Apps Scripts.

11 Google apps, 1 platform in the cloud.

Google Apps Scripts

The main goals of Apps Scripts is to allow you to have programmatically access to some neat features and combine several “apps” into 1 macro.

Examples

  • Instantly Translate Text in Docs.
  • Send a daily report from Google Sheets with a click of a button to pre-defined email addresses.
  • Run a scheduled backup from files in Google Drive to anywhere in the world.
  • Fill in timesheets in Sheets from Harvest automatically every day.
  • Sync Orders from your Favorite ERP to Google Sheets.

As you can see there is really a lot of functionality out of the box available. Google does all the heavy lifting with authentication and implementation to most of it’s services.

The Goal today

My goal in this post is to successfully get the weather (purely for the example, can be anything, really!) here in a Google Sheet and email me daily a PDF with the current temperature and the log of the previous days.

To do so we need following parts

  • A Google Sheet “MyWeatherChannel” that holds your data (the “database” or “log”)
  • A script that fetches data from OpenWeatherMap (signup for an API key, it’s free) at a certain time in the day. (Part 1, this part)
  • A second script that generates a PDF from the data and sends an email to you. (Part 2)

The google Sheet

Just go to your Google Drive account and create a new spreadsheet. For this one we will use the first sheet. Add some headers:

  • date
  • weather (text on how the weather is)
  • temperature
Create something like this.

The First script: Getting data from OpenWeatherMap

To get started with Google Scripts you need to open the Script Editor in your sheet.

The location of the script editor.

This will open a new tab and show you a code editor in the browser.

Upon first opening you see this

This is a Javascript editor with some extra Google Sugar. Most things can be found in the Google Apps Script documentation.

The API

First step is checking what data we have from the API.

We are gonna use the /current api. This one gives us the weather for now at a certain location.

The API is very simple:

http://api.openweathermap.org/data/2.5/weather?q=Ghent&appid=[APIKEY]&units=metric

  • q parameter: where do you want to fetch the weather from. This case: Ghent
  • appid parameter: replace [APIKEY] with the key you can obtain here
  • units we set this to metric (because we are normal)

This is an example of the data that is returned when calling this URL (in the browser for example)

The things we are interested in:

  • weather[0].main -> our weather column (first item [0] of the weather property)
  • main.temp -> our temperature column

To put this in a Google Spreadsheet we need to call the URL, parse the data and save it to the spreadsheet.

The Script

Don’t forget to change the [REPLACE_ME_BY_APIKEY] with your API key. and change the city to the city you want to monitor.

I’ve documented everything inline, be sure to check out the comments!

After pasting this in your editor. You need to run it:

Select appendWeather

Select appendWeather and tap the ▶️ — button. Go back to the spreadsheet and you should see the first results run in.

Everytime you tap ▶️-icon the API will execute and a record will be added. Try it! You just made your first integration! 🎉

Note: Running could show a dialog to accept some privileges. Please accept these to run the scripts.

Setting up the Time Based trigger

Google scripts has a very cool tool called “triggers”. These are configured execution moments for a certain function. This makes sure we can run the function for example everyday at noon.

Open the project triggers

You get the following screen. Nothing has been setup, so click: “No Triggers set up, Click here to add one now.”.

The Trigger screen

Setup something that fits your needs, this is my example:

Setup for once a day, executed between noon and 1pm.

Let the magic happen! (between noon and 1pm)

Takeaway

Today we learned on how to fetch data from an external service, parse it and get it into a Google Spreadsheet. This is what happens in a most integrations.

I just took the weather api because it provides:

  • Free data
  • simple data
  • API key concept

I wanted to mimic a real life concept to provide you some hands on!

Next up: Part2: Exporting data from a Google Sheet via email.

Want more?

If you need some advice on integrations between any 2 systems. I can provide you professional advice and integrations (Mobile, 2 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,

Mobile Solutions Architect — Coffee Geek. Tech Freak. Entrepreneur — Trefork.io | B-NYS.com