Using Google Calendar and Spreadsheet API for billing automation — part II

In part I we created a simple script to fetch events from our google calendar, now we are going to use this data to create a spreadsheet we can use for billing our customers. The code is available on github.

Spreadsheet API

Before we can use the APIwe have to activate it. The process is similar to activating google calendar API— follow the steps in the official google guide (Step 1: Turn on the Google Sheets API).

One great feature of APIsprovided by google is the API Explorer — you can try out the API right in the browser and make real requests with it. Our goal is to use the data we got from the calendar and create a spreadsheet with it. After heading to the reference section, we can see some basics on how to create spreadsheets in different languages, including Ruby, and try it out in the API Explorer. After tinkering with it a little bit, I created the following request that basically did what we want:

### Ensure valid credentials, either by restoring from the saved credentials# files or intitiating an OAuth2 authorization. If authorization is required,# the user's default browser will be launched to approve the request.## @return [Google::Auth::UserRefreshCredentials] OAuth2 credentialsdef authorize FileUtils.mkdir_p(File.dirname(CREDENTIALS_PATH))

We use the request hash we created before (after converting it to ruby syntax) and make a request to create a spreadsheet. Run ruby spreadsheet.rb in the console and check you google spreadsheets app — you should see a new Untitled spreadsheet with A1-foo, A2–10.

Authorizing both services

Both Apis we use require authorization and use the same code to acquire it, so let’s make a class responsible for that. In your project directory, create authorizer.rb file

The authorize method is basically the same method that we used in calendar.rb to authorize our request. The method requires a scope arguement, which is a URL used to authorize and grant specific privileges.

Orchestrating the whole process

We need a class that controls the whole flow of authorizing, fetching data and creating spreadsheets. Create invoicer.rb file:

I think the code above is self-explanatory but feel free to ask questions if something is not clear.

Let’s test the whole thing by running ruby invoicer.rb in the console (make sure your calendar has a few events in it). You should see a list of events in the console as well as the response from sheets API. Check you Google spreadsheets account, a new spreadsheet titled ‘test’ should be available.

Summary

We managed to create a simple ruby program that can use google Apis to make our lives little bit easier. The code is far from perfect but it’s enough to start playing with the APIs.

Thanks for reading. Follow me on Twitter, LinkedIn if you like what I wrote.