Tour de force

WooExpert Platinum WooCommerce partner - biggest elephant in the eCommerce room.
Mailchimp Partner Mailchimp partner - you know what’s the newest cutting edge customer experience solution? Great email subject!
Clutch logo Clutch - clients say we’re top dogs in eCommerce. Throw us that stick now.
WordPress Vip WordPress VIP - Enterprise hosting partner

Generate Harvest invoice from Google Docs spreadsheet – Cloud style

Learn how to generate invoices directly from Google spreadsheets and be clever about it…cloud style

Listen to this article
1x
0.5x 0.75x 1x 1.25x 1.5x

The thing about web hosting is that…you know…it’s somewhat a commodity and somewhat a geeky-techy thing –  you need to have a good pricing model but also a good technical 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 the complex pricing models behind it.

But to reverse a few years in the past…we started as a web design company with limited hosting packages that looked a lot like standard old-school hosting from the 90s (silver, gold and unlimited packages for 9, 19, and 29 $ per month :))) and I don’t need to explain what happens when 100k pageviews per day portal try 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 mention “Le cloud” in the first paragraph…well as 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 client’s websites, back-ends, business processes and so forth. We had Google Apps integration with Salesforces. We had Rackspace integrate with S3. We had S3 integrated 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 spreadsheets ’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 its own invoices. Parallel to the 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 are 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 development 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("");
        xml.AppendLine(""+due.Year.ToString()+"-"+due.Month.ToString()+"-"+due.Day.ToString()+"");

        xml.AppendLine(""+ClientID+"");

        xml.AppendLine("Croatia Kuna - HRK");
        xml.AppendLine("" + issue.Year.ToString() + "-" + issue.Month.ToString() + "-" + issue.Day.ToString() + "");

        xml.AppendLine("Client usage pricing");
        xml.AppendLine(""+notesBill+"");

        xml.AppendLine("free_form");

        xml.AppendLine("");
        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("");
        xml.AppendLine("");

    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 the 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 the generateInvoice function? You can find it in the window below but first some explanations – Our spreadsheet uses one sheet (first one [0]) for a listing of prices that are the same for all clients AND separate sheets for each client. The 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 the spreadsheet to the 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 the 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 us a comment or drop us a mention at @transmeettv

Krešimir Končić
Krešimir Končić Owner at Neuralab

Ex QBASIC developer that ventured into a web world in 2007. Leading a team of like-minded Open Source aficionados that love design, code and a pinch of BBQ. Currently writing a book that explains why ‘coding is the easier part’ of our field.


Subscribe to our quarterly newsletter

Please fill in this field.
Please thick this field to proceed.

Related blog posts