-
Notifications
You must be signed in to change notification settings - Fork 43
Google Docs Custom Reporting
This Howto was graciously provided by the folks over at phxend.com
We recently migrated from Excel to Harvest as our time entry and invoicing system. We still had a need for a customized accounting system to track individual level earnings. None of the available pre-built online solutions seemed to be a strong fit, so we chose to build our own Google Docs solution.
Google hosts our email. We used the Google Apps integration with Harvest to generate users in Harvest. Time and expense entry and invoicing are done regularly in Harvest through both the web interface and mobile application. For a few clients we are also uploading time records from a comma-delimited file. Invoicing is done every 2 weeks.
Data from Harvest is pulled to a Google Docs Spreadsheet on a schedule or as needed. Only administrators can view the Google Docs Spreadsheet. Individual users access a Google Site that displays only their relevant information. For performance reasons this information is only pulled from the Google Docs, although it would technically be possible to pull from Harvest.
To create or edit a Google Doc script, choose the following menu from within your spreadsheet.
A very basic script that I have found useful is the following. It adds menu items to the spreadsheet so administrative users can refresh data from Harvest on demand.
//**********************************************************************
// Add menu items
//**********************************************************************
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Refresh Invoices",
functionName : "harvest_invoice"
}];
entries.push({name: "Refresh Time", functionName: "harvest_time"});
entries.push({name: "Refresh Master", functionName: "harvest_master"});
entries.push({name: "Process Time", functionName: "process_time"});
sheet.addMenu("Harvest Data", entries);
};
I am using separate scripts to perform various actions because there is a timeout limit to each script. Scripts can also be scheduled in the script editor.
I have a common function that is executed at the beginning of any script that pulls data from Harvest. It contains some basic parameters that will need to be passed. The variable auth will be populated with an encrypted version of your username and password – you need to update them in the script example below.
//************************************************************************
// Global common code
//************************************************************************
function common(){
var fields = {'last-invoice-kind':true,'client':true,'updated-at':true};
var auth = Utilities.base64Encode("[email protected]:password");
headers = {
"Content-Type": "application/xml",
"Accept": "application/xml",
"Authorization": auth
};
options = {
"method": "get",
"headers": headers
};
spreadsheet_doc = SpreadsheetApp.getActiveSpreadsheet();
inv_range = "A2:J900";
}
Next is an example of a full script that connects to Harvest and places the data in an existing tab named “invoices” on the spreadsheet. The range of relevant data in the spreadsheet was set above in the common script. A separate script named refresh_clients() is taking a similar action to refresh a tab of client information and also populating an internal array of data.
With this particular interface to Harvest, it is necessary to call the URL multiple times as each call is limited to 50 returned records. For other interfaces, we loop at our list of consultants or projects in order to pass correct parameters. Full API Documentation is available at http://www.getharvest.com/api. Each API call returns an XML document, which is then converted into an object for processing in the script.
We aren’t using all fields returned by the API in our spreadsheet, so this code is specifically moving fields by name into specific columns of the array that is subsequently written into the spreadsheet. This script loops at the related client information to find the client’s name based upon their ID. I have tried to limit this type of logic within our data retrieval functions, but there are a few small examples where it made sense to include the overhead to get more useful data immediately.
//************************************************************************
// Refresh invoice data
//************************************************************************
function harvest_invoice(){
common();
refresh_clients();
x = 0;
var invoice_sheet = spreadsheet_doc.getSheetByName("invoices");
spreadsheet_doc.setActiveSheet(invoice_sheet);
var inv_array = invoice_sheet.setActiveSelection(inv_range).clear().getValues();
var client_entries = client_array.length;
for (var i_page=1;i_page<=5;i_page++) {
url = "https://mycompany.harvestapp.com/invoices?page="+i_page;
response = UrlFetchApp.fetch(url, options);
var invoices_doc = Xml.parse(response.getContentText(), true);
for (var i in invoices_doc.invoices.invoice) {
inv_array[x][0] = invoices_doc.invoices.invoice[i].id.getText();
inv_array[x][1] = invoices_doc.invoices.invoice[i].number.getText();
inv_array[x][2] = invoices_doc.invoices.invoice[i].amount.getText();
for (var i_client = 0; i_client < client_entries; i_client++)
{
if (client_array[i_client][0] ==
invoices_doc.invoices.invoice[i].getElement("client-id").getText())
{
inv_array[x][9] = client_array[i_client][1];
}
}
inv_array[x][3] = invoices_doc.invoices.invoice[i].getElement("client-id").getText();
inv_array[x][4] = invoices_doc.invoices.invoice[i].state.getText();
inv_array[x][5] = invoices_doc.invoices.invoice[i].getElement("issued-at").getText();
inv_array[x][6] = invoices_doc.invoices.invoice[i].getElement("due-at").getText();
inv_array[x][7] = invoices_doc.invoices.invoice[i].getElement("period-start").getText();
inv_array[x][8] = invoices_doc.invoices.invoice[i].getElement("period-end").getText();
i++;
x++;
}
}
invoice_sheet.setActiveSelection(inv_range).setValues(inv_array);
invoice_sheet.sort(1);
};
I encountered some instances where Google scripts weren’t able to decipher hyphenated collection names from within the XML document. I found it easiest to just replace the hyphenated name with a non-hyphenated name within the string prior to converting the string into an object.
var stringer = response.getContentText();
var newstringer2 = stringer.replace(/user-assignment/g,"combo");
var proju_doc = Xml.parse(newstringer2, true);
As mentioned earlier, we process much of our accounting logic separate from the Harvest extracts. This is an incomplete script as the full script is quite lengthy, but it might be helpful to see the basic looping concept and subsequent write-back performed on the data within the Google Spreadsheet.
function process_time(){
common();
var proju_sheet = spreadsheet_doc.getSheetByName("projcons");
var proju_array = proju_sheet.setActiveSelection(proju_range).getValues();
var time_sheet = spreadsheet_doc.getSheetByName("time");
var time_array = time_sheet.setActiveSelection(time_range).getValues();
//Time records
for( var i_time = 0; i_time < time_entries; i_time++ )
{
// Be done
if (time_array[i_time][0] == " ")
{
i_time = time_entries;
}
else
{
// Find project/consultant for rate
for( var i_proju = 0; i_proju < proju_entries; i_proju++ )
{
if ( proju_array[i_proju][1] == time_array[i_time][4] &&
proju_array[i_proju][2] == time_array[i_time][3] )
{
time_array[i_time][8] = proju_array[i_proju][3] * time_array[i_time][1];
time_array[i_time][9] = proju_array[i_proju][4];
time_array[i_time][10] = proju_array[i_proju][5];
time_array[i_time][11] = proju_array[i_proju][6];
i_proju = proju_entries;
}
}
}
}
time_sheet.setActiveSelection(time_range).setValues(time_array);
};
We use a Google Site to present user-specific reporting. Again this does not interact with Harvest, but script code presented earlier could be used to do so. Here is a simple example that pulls data from the underlying Google Spreadsheet and presents results to the user. This script is then inserted into a page on a Google Site.
function doGet(e) {
var app = UiApp.createApplication();
var vPanel = app.createVerticalPanel();
vPanel.setWidth("810");
vPanel.setStyleAttribute("padding","20px");
vPanel.setStyleAttribute("fontSize", "12pt");
vPanel.setStyleAttribute("textAlign", "right");
vPanel.setId("panel");
var vtable_pl = app.createFlexTable();
vtable_pl.setStyleAttribute("textAlign", "right");
pmt_range = "A2:D1000";
var spreadsheet_doc = SpreadsheetApp.openById("GET FROM URL IN GOOGLE DOC");
var payments_sheet = spreadsheet_doc.getSheetByName("payments");
var pmt_array = payments_sheet.setActiveSelection(pmt_range).getValues();
var pmt_entries = pmt_array.length;
var i_row = 1;
for (var i_pmt = 0; i_pmt < pmt_entries; i_pmt++)
{
if ( pmt_array[i_pmt][1] == " " )
{
i_pmt = pmt_entries;
}
else if ( pmt_array[i_pmt][1] == Session.getUser().getEmail() )
{
vtable_pl.setText(i_row, 1, Utilities.formatDate(pmt_array[i_pmt][0], "CST", "MM/dd/yyyy"));
var num = Number(pmt_array[i_pmt][2]);
var str = num.toFixed(2).toString();
var len = str.length;
if ( len > 7 || ( len > 6 && str.substring(0,1) != "-" ) )
{
var len_a = len - 6;
vtable_pl.setText(i_row, 2, str.substring(0,len_a)+","+str.substring(len_a,9));
}
else
{
vtable_pl.setText(i_row, 2, str);
}
// vtable_pl.setText(i_row, 2, pmt_array[i_pmt][2]);
i_row++;
}
}
vPanel.add(vtable_pl);
app.add(vPanel);
return app;
};