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.

Image for post
Image for post
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.

  • 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)

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
Image for post
Image for post
Create something like this.

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

Image for post
Image for post
The location of the script editor.

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

Image for post
Image for post
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.

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.

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:

Image for post
Image for post
Select appendWeather

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

Image for post
Image for post

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.

Image for post
Image for post
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.”.

Image for post
Image for post
The Trigger screen

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

Image for post
Image for post
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,

Written by

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store