Generate Harvest invoice from Google Docs spreadsheet ... Cloud style
The thing about web hosting is that…you know…it’s somewhat a comodity and somewhat a geeky-techy thing – you need to have a good pricing model but also a good technology background to run the operations (as smoothly as possible :)). When the “Cloud hit the shelves” it didn’t make the whole thing a lot easier, at least for us back-end guys. Sure the clients wanted the new model, usage-based pricing, scalability and availability but didn’t want to know about complex pricing models behind it.
But to reverse few years in the past…we started as a web design company with limited hosting packages that looked a lot like a standard old-school hosting from the 90′s (silver,gold and unlimited packages for 9, 19, 29 $ per month :))) and I don’t need to explain what happens when 100k pageviews per day portal tries to buy “unlimited package”…talking about Overselling that is…these times are now over.
Anyways, making invoices at that time was pretty easy. We invoiced clients on a yearly basis and they deposited regular and stable amounts of money based on the package they consumed. But why did i mentioned “Le cloud” in the first paragraph…well as the time passed by a lot more clients started asking for that cloud thing and we as a company also had a “thing” for new technologies. We started to implement Cloud solutions to our clients web sites, back-ends, business processes and so forth. We had Google Apps integration with Salesforces. We had Rackspace integrate with S3. We had S3 integrate with Encoding integrate with s3 and back….you get the picture.
For the record, our main hosting platform (PaaS) resides on Rackspace servers but our vital infrastructure is also on AWS (IaaS – CloudFront, S3, ELB, Route53 etc.). In some cases we resell Google Apps, Mailchimp, Salesforce (SaaS) and also some specialized services such as Encoding but that’s another story. The MAIN THING here is that our clients receive only ONE invoice for all that computing power…
From starters we can handle all that items only in spreadsheet ’cause we needed the flexibility and reporting possibility of Google spreadsheets. Every client wanted to have monthly stats and we also needed usage reporting for our own business. Getting the numbers through API or some sort of importing is not a big deal when it comes to G.Apps – every cloud service has some sort of adapter or XML format for their own invoices. Parallel to evolution of our hosting platform, we started to use Harvest for our project management, hours and billing but mainly for the “agency side” business i.e. Design, UX/UI, Mobile development and other non-commodity stuff. These projects are spanning through several months and are not high in frequency so automated billing and invoicing is not a priority. As it is a good practice to consolidate all financials in one place, we took a look at Harvest API for automated invoice creation from Google spreadsheets and here is the result…
First you need to have some sort of web service that resides on your website or subdomain that will call Harvest API. We use mainly ASP.NET C# for our developement so here is the code that will generate Harvest invoice from your backend. Note that it parses data from query string so you can be flexible and put all of your data into the POST or GET parameters. Just place this code into the .ASHX generic handler or ASP.NET page…
using System;
using System.Web;
using System.Configuration;
using System.Net;
using System.IO;
using System.Text;
public class GenerateInvoice : IHttpHandler
{
public void ProcessRequest (HttpContext context)
{
string notesBill = "Insert your note to the customer here";
string ClientID = context.Request.QueryString["ClientID"].ToString();
string itemPrice1 = context.Request.QueryString["itemPrice1"].ToString().Replace(".", ",");
string itemUsage1 = context.Request.QueryString["itemUsage1"].ToString().Replace(".", ",");
string itemPrice2 = context.Request.QueryString["itemPrice2"].ToString().Replace(".", ",");
string itemUsage2 = context.Request.QueryString["itemUsage2"].ToString().Replace(".", ",");
//...YOU CAN HAVE MULTIPLE ITEMS HERE
//
DateTime issue = DateTime.Now;
DateTime due = issue.AddDays(2); //OR INSERT YOUR OWN DUE DATE
string uri = "https://yourcompany.harvestapp.com/invoices";
string username = "yourUserName";
string password = "yourPassWord";
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri);
request.Method = "POST";
request.Accept = "application/xml";
request.ContentType = "application/xml";
request.AllowWriteStreamBuffering = true;
request.MaximumAutomaticRedirections = 1;
request.AllowAutoRedirect = true;
request.UserAgent = ".Net test application";
request.Headers.Add("Authorization", "Basic " + Convert.ToBase64String(new ASCIIEncoding().GetBytes(username + ":" + password)));
StringBuilder xml = new StringBuilder();
xml.Append("<invoice>");
xml.AppendLine("<due -at type='date'>"+due.Year.ToString()+"-"+due.Month.ToString()+"-"+due.Day.ToString()+"</due>");
xml.AppendLine("<client -id type='integer'>"+ClientID+"</client>");
xml.AppendLine("<currency>Croatia Kuna - HRK</currency>");
xml.AppendLine("<issued -at type='date'>" + issue.Year.ToString() + "-" + issue.Month.ToString() + "-" + issue.Day.ToString() + "</issued>");
xml.AppendLine("<subject>Client usage pricing</subject>");
xml.AppendLine("<notes>"+notesBill+"</notes>");
xml.AppendLine("<kind>free_form</kind>");
xml.AppendLine("<csv -line-items>");
xml.AppendLine("kind,description,quantity,unit_price,amount,taxed,taxed2,project_id");
xml.AppendLine(",Item 1 usage,\"" + itemUsage1 + "\",\"" + itemPrice1 + "\",true,false,");
xml.AppendLine(",Item 2 Usage,\"" + itemUsage2 + "\",\"" + itemPrice2 + "\",true,false,");
xml.AppendLine("</csv>");
xml.AppendLine("</invoice>");
StreamWriter sw = new StreamWriter(request.GetRequestStream());
sw.WriteLine(xml.ToString());
sw.Close();
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
if (request.HaveResponse == true && response != null)
{
context.Response.Write("Invoice generated!");
}
response.Close();
}
}
This service is online so your other back-end processes can use this script to generate invoices. The second phase is to integrate Google API into this process so we can generate Harvest Invoice from within the Google Docs Spreadsheet and this is done through Google Apps scripts.
First it is a good idea to populate spreadsheet menu with buttons that will call our script….
function onOpen()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Generate Invoice", functionName: "generateInvoice"} ];
ss.addMenu("HARVEST", menuEntries);
}
Notice that call to “generateInvoice” function? You can find it in the window bellow but first some explanations – Our spreadsheet uses one sheet (first one [0]) for listing of prices that are same for all clients AND separate sheets for each client. That client sheets hold consumption data, usage statistics and so forth. You could setup you back-end any other way. This function will just show you how to send data from spreadsheet to earlier explained Harvest API. Our process is simple… We just select the row in usage statistics and hit the button “Generate Invoice”. That row holds the Client Harvest ID (that you must input manually) and all the usage data that needs to be priced (item1, item2…) you get the idea.
function generateInvoice()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
// get spreadsheet with prices
var pricessheet = ss.getSheets()[0];
// get prices from the spreadsheet
var itemPrice1 = pricessheet.getRange("A1").getValue();
var itemPrice2 = pricessheet.getRange("A2").getValue();
// get clients usage from active spreadsheet row
var clientbill = ss.getActiveSheet().getActiveSelection().getValues();
var clientID = clientbill[0][0];
var itemUsage1 = clientbill[0][1];
var itemUsage2 = clientbill[0][2];
var options = { "method" : "post" }
var posttoharvestURL = "http://harvestapi.yourcompany.com/cloudHostingGenerateBill.ashx?ClientID=" + clientID + "&itemPrice1 =" + itemPrice1 + "&itemPrice2=" + itemPrice2 + "&itemUsage1=" + itemUsage1 + "&itemUsage2=" + itemUsage2;
var response = UrlFetchApp.fetch(posttoharvestURL, options);
Browser.msgBox("Invoice generated");
}
Future work: You could probably call Harvest API directly from Google scripts as this service supports OAUTH but we like to have system modulated i.e. we could use server side script for later use in other systems. This is subjective of course and depends on business needs. Also…populating data into the spreadsheet is possible by some cloud services through their API and can be fully automated but this topic is outside this blog post.
If you have any suggestions / ideas or you would like to share some thoughts on your cloud integration…give as a comment or drop us a mention at @transmeettv
