Automating Billing with Perl and the Google Docs API

Since returning from my epic adventure, I’ve been doing some consulting work to keep myself amused while I find something new to sink my teeth into. I’ve long been a champion of shared authoring solutions such as wikis, and have also written about tricks you can do with Google Docs to kick old habits. So, as part of this engagement, I’ve been forcing encouraging my client to use Google Docs to develop and edit our work. Although he was initially open to the idea, he’s moved beyond acceptance to really embracing the solution. When I asked him what he thought, he replied:

I actually thought that it worked pretty well, and definitely beats sending Word documents back and forth. 

With Google Talk allowing quick communication, email for longer discussions and Google Docs for writing, I think we’ve been doing a really superb job with our cross-country collaboration.

Besides using Google Docs for writing, I also use a spreadsheet to track the dates I worked on a task, how long I spent on it, who else was involved, and a short note describing the activity. I compile this data into an invoice twice a month, but my client’s finance department wanted a weekly report of the hours I was charging. Of course, it would be easy enough to cut and paste some rows out of my spreadsheet since this is a one-off engagement. But, what if it wasn’t? More importantly, when I built a route and scheduling optimization tool with the Google Maps API, I had a lot of fun coming up with a solution. I had been looking for a reason to experiment with the Google Documents API, and this seemed like the perfect little project to get started.

My automation solution uses a combination of Perl, AppleScript and iCal and goes something like this:

  1. Every Saturday at 6PM, a recurring appointment in iCal invokes an AppleScript.
  2. The AppleScript calls a Perl script to:
    1. Log on to Google Docs
    2. Find my Billing spreadsheet
    3. Collect the rows of data for the current 7-day period
    4. Prepare a basic HTML document
  3. The AppleScript then resumes control, and creates an email to my client with the HTML file as an attachment.

The solution requires my computer to be on, and for me to be here to complete the email and send it off. Those steps could be automated, of course, and the whole process could be set up to run on my Web server, where I’d never have to think about it. Moving to that level of automation would be pretty simple; however, at this point, it makes more sense for me to be involved just at the very end. It gives me a chance to personalize the note by asking about his week or including a URL to an interesting article – a few small steps that help strengthen our relationship.

For reference, the Google Docs spreadsheet looks similar to the figure below:

Billing Spreadsheet

And the resulting HTML attachment the client receives looks like the next figure:

Billing Email

If this sort of automation seems like a decent idea to you, I’ve provided the AppleScript and Perl below. The Perl code slurps in a couple of Header and Footer template-esque files, which I’ve also included. At the bottom of the Footer include is a mailto HREF, which you should change to reflect your contact information. The Header and Footer files should appear in an includes directory subordinate to the location of the Perl code, but you can change that in the configuration section of the Perl code if you like.

Two final things: One, you’ll need to have the CryptSSL installed for Perl’s LWP module to authenticate with Google using SSL. You can read how to get HTTPS and LWP working on Mac OSX in another article I wrote. Two, I’ve added lots of explanatory comments in the code, and included URL links to relevant online documentation. Nonetheless, if you have any questions, drop me a line and I’ll try to give you a hand. Have at it.

Apple Script

-- Generate the billing file using Perl
do shell script "cd '/Projects/Weekly Billing/'; /usr/bin/perl 'WeeklyBilling.pl'"

-- The name of the Mail.app account
set theAccount to "The Name Of Your Mail Account"

-- Message subject
set theSubject to "The Subject Of Your Message"

-- Path to file attachment
set theFile to "HD:Some Directory:Some Other Directory:Perl_Script_Generated_File.html" as alias

-- Recipient address
set theAddress to "client@clientcompany.com"

-- Create the message with the file attachment
tell application "Mail"
	activate
	set newMessage to make new outgoing message with properties {account:theAccount, subject:theSubject}
	tell newMessage
		-- Add the file attachment
		tell content
			make new attachment with properties {file name:theFile} at after the last paragraph
		end tell

		-- Address the message and show it for editing
		make new to recipient at end of to recipients with properties {address:theAddress}
		set visible to true

	end tell
end tell

Perl

#!/usr/bin/perl -w
# -----------------------------------------------------------------------------
# Title: WeeklyBilling.pl
#
# Author: Harley Young
#
# Date: Ongoing
#
# Purpose: Connects to the Google Apps API and fetches data from a
#          specific worksheet and prepares an HTML file showing
#          weekly billings
#
#
# SYNTAX: WeeklyBilling.pl
#
# -- Licence Terms --
# Permission to use, copy, modify, distribute, and sell this software and its
# documentation for any purpose is hereby granted without fee, provided that
# the above copyright notice appear in all copies and that both that
# copyright notice and this permission notice appear in supporting
# documentation.  No representations are made about the suitability of this
# software for any purpose.  It is provided "as is" without express or
# implied warranty.
#
# -----------------------------------------------------------------------------

# Pull in required modules and force declaration
use strict;
use LWP::UserAgent;
use XML::Simple;
use Time::Local;


# Configure the program
# The various parameters passed in the UA POST are documented on Google's page
# Authentication: http://bit.ly/1apxYA
# Spreadsheets access: http://bit.ly/Qfcxg
my $iTotalHours;
my %hConfig = (
		URL 		=> "https://www.google.com/accounts/ClientLogin",
		AccountType	=> "GOOGLE",   
		UserName 	=> "YOUR_USERNAME",
		Password 	=> "YOUR_PASSWORD",
		Serivce 	=> "wise",
		Source 		=> "Billing Automator",
		APIVersion	=> "2",
		AuthToken	=> "",
		Header		=> "includes/header.tpl",
		Footer		=> "includes/footer.tpl",
		OutputFile	=> "Weekly Billing.html"
	      );

# Create browser and XML objects, and send a request for authentication
my $objUA = LWP::UserAgent->new;
my $objXML = XML::Simple->new;
my $objResponse = $objUA->post(
	$hConfig{URL},
	{
	accountType	=> $hConfig{AccountType},
	Email		=> $hConfig{UserName},
	Passwd		=> $hConfig{Password},
	service		=> $hConfig{Serivce},
	source		=> $hConfig{Source},
	"GData-Version" => $hConfig{APIVersion},
	}
);

# Fail if the HTTP request didn't work
die "\nError: ", $objResponse->status_line unless $objResponse->is_success;

# Extract the authentication token from the response and add
# it to future UA requests
$hConfig{AuthToken} = ExtractAuth($objResponse->content);
$objUA->default_header('Authorization' => "GoogleLogin auth=$hConfig{AuthToken}");

# Worksheet contents list
# The URL I'm using here is a list-based feed of a particular worksheet. If you
# want to build a more generic solution, you can loop through all
# your documents, find one in particular, open it, loop through all the worksheets
# in the spreadsheet and then open the one you want.
# Details on the different URL constructions are described in the developers guide:
# http://code.google.com/apis/spreadsheets/docs/2.0/developers_guide_protocol.html
#
# If you just want to do the same thing I've done, open the document of interest
# in your web browser and look at your URL. You'll notice a key parameter in the
# query string. Clip that and slip it into the the URL below where I have
# this value: MY_SHEET_KEY
$objResponse = Fetch($objUA, "http://spreadsheets.google.com/feeds/list/MY_SHEET_KEY/od6/private/full");
my $objWorksheet = $objXML->XMLin($objResponse, ForceArray => 1);

# Open the file handle to create the output file
open (fhWRITE, ">$hConfig{OutputFile}") || die "Could not write to output file: $!\n";

# Put a header on the file
print fhWRITE GetFileChunk($hConfig{Header});

# For each row in the Google Docs sheet, print the date, hours, participants and notes
foreach my $sRow (@{$objWorksheet->{entry}}) {

	# Print out the rows where the date falls within the current week
        # For this to work you have to use the D/M/Y format in your date field
        # Depending what you name your spreadsheet columns, the gsx:date, etc
        # elements in the XML will change. You can use Data::Dumper to print the
        # XML to see what you're getting back if needed for debugging
	if (IsDateInPeriod($sRow->{'gsx:date'}[0])) {
		print fhWRITE "<tr>\n";

		print fhWRITE "<td>" . $sRow->{'gsx:date'}[0] . "</td>\n";
		print fhWRITE "<td class=\"hours\">" . $sRow->{'gsx:timehours'}[0] . "</td>\n";
		print fhWRITE "<td>" . $sRow->{'gsx:participants'}[0] . "</td>\n";
		print fhWRITE "<td>" . $sRow->{'gsx:notes'}[0] . "</td>\n";

		print fhWRITE "</tr>\n";

		# Accumulate the total hours
		$iTotalHours += $sRow->{'gsx:timehours'}[0];

	}
}


# Add the totals row
print fhWRITE "<tr class=\"totals\">\n";
print fhWRITE "<td colspan=\"2\" class=\"hours\">$iTotalHours</td>\n";
print fhWRITE "<td colspan=\"2\">Total Hours for Period</td>\n";
print fhWRITE "</tr>\n";

# Put a footer on the file
print fhWRITE GetFileChunk($hConfig{Footer});

close fhWRITE || warn "Could not write to output file: $!\n";

#------------------------------------------------------------------------------
# Subroutines
#------------------------------------------------------------------------------

# Extract the authorization token from Google's return string
sub ExtractAuth {
	# Split the input into lines, loop over and return the value for the
	# one starting Auth=
   	for (split /\n/, shift) {
   		return $1 if $_ =~ /^Auth=(.*)$/;
   	}
   	return '';
 }

# Fetch a URL
sub Fetch {
	# Create the local variables and pull in the UA and URL
	my ($objUA, $sURL) = @_;

	# Grab the URL, but fail if you can't get the content
	my $objResponse = $objUA->get($sURL);
	die "Failed to fetch $sURL " . $objResponse->status_line if !$objResponse->is_success;

	# Return the result
	return $objResponse->content;
}


# Bring in an external file chunk and print it out
sub GetFileChunk {
	# Pull the file name to print into a local variable
	my $sFile = shift;
	my $sFileChunk;

	# Whip through the file and fetch it into an array
	open(fhREAD, $sFile) || die "Could not open $sFile: $!\n";

	while (<fhREAD>) {
		# Local variables
		my $sLine = $_;
		my $sReplacementString;

		# Fetch the date range
		if (/BILLING__PERIOD/) {
			my ($sStartDate, $sEndDate) = FetchDateRange();
			$sLine =~ s/BILLING__PERIOD/$sStartDate - $sEndDate/;
		}

		# Append this line
		$sFileChunk .= $sLine;
	}

	# Close the file handle
	close(fhREAD) || warn "Could not close $sFile: $!\n";

	# Return the result
	return($sFileChunk);
}


# Get the weekly date range
sub FetchDateRange {
	# Get the current time
	my $iEndTime = time();

	# Go back 7 days (604,800 seconds) to get the start of the billing period
	my $iStartTime = $iEndTime - 604800;

	# Convert all the dates into standard DD/MM/YYYY format
	my ($iEndDay, $iEndMonth, $iEndYear) = (localtime($iEndTime))[3,4,5];
	my ($iStartDay, $iStartMonth, $iStartYear) = (localtime($iStartTime))[3,4,5];

	# Fix the zero based month & year
	$iStartYear+=1900;
	$iEndYear+=1900;
	$iStartMonth+=1;
	$iEndMonth+=1;

	# Pad the dates with leading zeros if required
	$iStartDay = PadNumber($iStartDay);
	$iStartMonth = PadNumber($iEndMonth);
	$iEndDay = PadNumber($iEndDay);
	$iEndMonth = PadNumber($iEndMonth);

	# Return the results
	return ("$iStartMonth/$iStartDay/$iStartYear", "$iEndMonth/$iEndDay/$iEndYear");
}

sub IsDateInPeriod {
	# Get the date and time (resetting the 0 based month and 1900-less year)
	my ($iMonth, $iDay, $iYear) = split(/\//, shift);
	my $iTime = timelocal(0, 0, 0, $iDay, $iMonth-1, $iYear-1900);

	# Get the current time and the period start time (604,800 seconds ago)
	my $iEndTime = time();
	my $iStartTime = $iEndTime - 604800;

	# Test to see if the supplied time falls in the range and return T(1) or F(0)
	if ( $iTime > $iStartTime && $iTime < $iEndTime ) {
		return(1);
	}
	else {
		return(0);
	}
}


# Pad data where the value is less than 10
sub PadNumber {
	# Local variables
	my $sData = $_[0];

	# Zero pad if the value is less than 10
	if ($sData < 10) {
		$sData = "0" . $sData;
	}

	# Return the results
	return($sData);
}

HTML Header Template

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <title>Client Time Tracker</title>
    <style type="text/css">
    	.meta {font-size:70%;color:#808080;margin-top:40px;padding-top:10px;border-top:#000 thin solid;}
    	table {border-collapse: collapse;}
    	th {background:#ccc;}
    	td,th {border: #000 thin solid;padding:5px;margin:0;}
    	.hours {text-align:right;}
    	.totals {background:#ccc;font-weight:bold;}
    </style>


</head>

<body>


<h1>Client Time Tracker (BILLING__PERIOD)</h1>

<table>
	<tbody>
		<tr>
			<th>Date</th>
			<th>Time (hours)</th>
			<th>Participants</th>
			<th>Notes</th>
		</tr>

	</tbody>
</table>

<p class="meta">For the Period: BILLING__PERIOD &nbsp;&nbsp;|&nbsp;&nbsp;
Questions? Please email <a href="mailto:YOUR_EMAIL_ADDRESS">YOUR_NAME</a></p>

</body>
</html>