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:

{
"sheets": [
{
"data": [
{
"startRow": 0,
"rowData": [
{
"values": [
{
"userEnteredValue": {
"stringValue": "foo"
}
}
]
}
]
},
{
"startRow": 1,
"rowData": [
{
"values": [
{
"userEnteredValue": {
"numberValue": 10
}
}
]
}
]
}
]
}
]
}

You can paste the above hash into the Explorer window and click Execute. A new spreadsheet should be created the following data — A1-foo, A2–10.

Spreadsheet service

Let’s make a new class that will be responsible for creating new spreadsheets. Create spreadsheet.rb file with the following code:

require 'google/apis/sheets_v4'
require 'googleauth'
require 'googleauth/stores/file_token_store'
require 'fileutils'
OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'
APPLICATION_NAME = 'Google Calendar API Ruby Quickstart'
CLIENT_SECRETS_PATH = 'client_secret.json'
CREDENTIALS_PATH = File.join(Dir.home, '.credentials',
"calendar-ruby-quickstart.yaml")
SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS
##
# 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 credentials
def authorize
FileUtils.mkdir_p(File.dirname(CREDENTIALS_PATH))
client_id = Google::Auth::ClientId.from_file(CLIENT_SECRETS_PATH)
token_store = Google::Auth::Stores::FileTokenStore.new(file: CREDENTIALS_PATH)
authorizer = Google::Auth::UserAuthorizer.new(
client_id, SCOPE, token_store)
user_id = 'default'
credentials = authorizer.get_credentials(user_id)
if credentials.nil?
url = authorizer.get_authorization_url(
base_url: OOB_URI)
puts "Open the following URL in the browser and enter the " +
"resulting code after authorization"
puts url
code = gets
credentials = authorizer.get_and_store_credentials_from_code(
user_id: user_id, code: code, base_url: OOB_URI)
end
credentials
end
# Initialize the API
service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = APPLICATION_NAME
service.authorization = authorize
sheet_hash = {
sheets: [
{
data: [
{
start_row: 0,
row_data: [
{
values: [
{
user_entered_value: {
string_value: 'foo'
}
}
]
}
]
},
{
start_row: 1,
row_data: [
{
values: [
{
user_entered_value: {
number_value: 10
}
}
]
}
]
}
]
}
]
}
request_body = Google::Apis::SheetsV4::Spreadsheet.new(sheet_hash)
p request_body
response = service.create_spreadsheet(request_body)
p response

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

###authorizer.rb###
require ‘googleauth’
require ‘googleauth/stores/file_token_store’
require ‘fileutils’
class Authorizer
OOB_URI = ‘urn:ietf:wg:oauth:2.0:oob’
CLIENT_SECRETS_PATH = ‘client_secret.json’
CREDENTIALS_PATH = File.join(Dir.home, ‘.credentials’,
“calendar-ruby-quickstart.yaml”)
def self.authorize(scope:)
FileUtils.mkdir_p(File.dirname(CREDENTIALS_PATH))
client_id = Google::Auth::ClientId.from_file(CLIENT_SECRETS_PATH)
token_store = Google::Auth::Stores::FileTokenStore.new(file: CREDENTIALS_PATH)
authorizer = Google::Auth::UserAuthorizer.new(
client_id, scope, token_store)
user_id = ‘default’
credentials = authorizer.get_credentials(user_id)
if credentials.nil?
url = authorizer.get_authorization_url(
base_url: OOB_URI)
puts “Open the following URL in the browser and enter the “ +
“resulting code after authorization”
puts url
code = gets
credentials = authorizer.get_and_store_credentials_from_code(
user_id: user_id, code: code, base_url: OOB_URI)
end
credentials
end
end

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:

require_relative 'calendar'
require_relative 'spreadsheet'
require_relative 'authorizer'
class Invoicer
SCOPES = [Google::Apis::SheetsV4::AUTH_SPREADSHEETS, Google::Apis::CalendarV3::AUTH_CALENDAR_READONLY]
def create
authorization = Authorizer.authorize(scope: SCOPES)
calendar = Calendar.new(authorization: authorization)
events = calendar.fetch_events
spreadsheet = Spreadsheet.new(events: events, authorization: authorization)
spreadsheet.create
end
end
Invoicer.new.create

We require calendar, spreadsheet and authorizer so we can use the methods to fetch and create data. The class has one method, create, that orchestrates the workflow:

  1. We create authorization using the Authorizer class and pass in SCOPES (which are the URLs for getting the right authorization) as argument.
  2. We instantiate a new Calendar and pass authorization as argument.
  3. We fetch events from the calendar.
  4. We instantiate a new Spreadsheet and pass authorization and events as arguments.
  5. We call create on the spreadsheet.

Now we need to update Calendar.rb and Spreadsheet.rb to make it all work.

Update Calendar

Update your calendar.rb with the following code:

require 'google/apis/calendar_v3'
require_relative 'authorizer'
class Calendar
def initialize(authorization:)
@authorization = authorization
end
def fetch_events
service = Google::Apis::CalendarV3::CalendarService.new
service.client_options.application_name = Authorizer::APPLICATION_NAME
service.authorization = @authorization
calendar_id = 'primary'
now = Time.now
first_day_of_month = Date.new(now.year, now.month, 1).to_time.iso8601
last_day_of_month = Date.new(now.year, now.month, -1).to_time.iso8601
response = service.list_events(calendar_id,
single_events: true,
order_by: 'startTime',
time_min: first_day_of_month,
time_max: last_day_of_month)
puts "Upcoming events:"
puts "No upcoming events found" if response.items.empty?
response.items.each do |event|
start_time = event.start.date_time
end_time = event.end.date_time
hours = ((end_time - start_time) * 24).to_i
puts "- #{event.summary} (#{hours}) - date: #{ start_time } - #{ end_time }"
end
return response.items
end
end

We got rid of the authorize method and instead accept authorization when initializing the Calendar object.

Update Spreadsheet

The spreadsheet.rb requires a little bit more changes, let’s take it step by step.

require 'google/apis/sheets_v4'
require_relative 'authorizer'
class Spreadsheet
def initialize(events: [], rate_per_h: 100, authorization:)
@events = events
@rate_per_h = rate_per_h
@authorization = authorization
end
end

We make a Spreadsheet class that accepts an array of events and authorization when initialized. We also set our hourly rate to default 100.

...
def create
service = Google::Apis::SheetsV4::SheetsService.new
service.client_options.application_name = Authorizer::APPLICATION_NAME
service.authorization = @authorization
    request_body = Google::Apis::SheetsV4::Spreadsheet.new(sheet_hash)
p request_body
response = service.create_spreadsheet(request_body)
p response
end
..

Next, we add create method that creates a new spreadsheet using the sheet_hash method.

def sheet_hash
{
sheets: [
{
data: events_data
}
],
properties: {
title: 'test'
}
}
end

The sheet_hash creates a hash using events_data that we passed from our calendar and gives our sheet a title — ‘test’.

def events_data
data = []
last_row_index = 0
@events.each.with_index do |event, i|
data << event_hash(event, i)
last_row_index += 1
end
data << total_hours(last_row_index: last_row_index)
last_row_index += 1
data << rate_per_h(last_row_index: last_row_index)
last_row_index += 1
data << total(last_row_index: last_row_index)
data
end

The events_data returns an array of hashes with the details of our working hours. We also add total hours, rate per hour and total due at the end.

Here’s the rest of the code:

def event_hash(event, i)
{
start_row: i,
row_data: [
{
values: [
{
user_entered_value: {
string_value: event_date(event).to_s
}
},
{
user_entered_value: {
number_value: event_time(event).to_s
}
}
]
}
]
}
end
def event_date(event)
event.start.date_time
end
def event_time(event)
start_time = event.start.date_time
end_time = event.end.date_time
((end_time - start_time) * 24).to_i
end
def total_hours(last_row_index:)
new_row(last_row_index + 1, string_value: 'Total hours', formula_value: "=SUM(B1:B#{ last_row_index })")
end
def rate_per_h(last_row_index:)
new_row(last_row_index + 1, string_value: 'Rate per hour', number_value: @rate_per_h)
end
def total(last_row_index:)
new_row(last_row_index + 1, string_value: 'Total', formula_value: "=(B#{last_row_index }*B#{ last_row_index + 1 })")
end
def new_row(row_index, *args)
{
start_row: row_index,
row_data: [
{
values: row_data_values(*args)
}
]
}
end
def row_data_values(args)
result = []
args.each do |value_type, value|
result << {
user_entered_value: {
value_type => value
}
}
end
result
end

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.