Customer case study: Google Apps and Xero work together to drive efficiency

At InterlockIT.com, we're Google Apps Resellers with a CPA on staff who noticed that there was a distinct lack of integration between accounting software (specifically Xero) and the wonderful collaborative features built in to Google Spreadsheets. We wondered if there was a way to maybe get the two to work together...

We built our powerful Xero reporting engine called Blink Reports to give users a faster and easier way to produce financial spreadsheets and reports from their accounting data. What we've learned along the way is that we can do so much more with the new Google Spreadsheets and Xero than we ever imagined was possible.

TargetCW is a leading provider of contingent workforce services across the US and overseas. Their corporate Kaizen philosophy of continuous improvement applies to their accounting and financial processes too. Xero recommended that Ryan Anning at TargetCW test out our Blink Reports for Xero reporting engine to overcome the limited number of columns that are normally shown on the profit and loss statement. Ryan's goal was to produce a revenue and cost analysis by worker which requires a report with more than a few hundred columns! It turned out that his need was so unique that initially our Blink Reports engine would produce an error due to waiting too long to receive the data from Xero.

On a standard profit/loss report, it's unlikely that you'd need such a wide spreadsheet, and indeed Google Sheets has traditionally had a 256 column limit. Thankfully, with the new version of Google Sheets (now the default), this limit and a number of others no longer exist. Need to generate a huge 100% cloud-based spreadsheet, with up to 2 million cells and the share it securely in real time with others? Not a problem. Google Sheets will do it for you right from within your web browser.

The technical side of how we fixed Ryan's challenge is quite clever, but we won't go into too much detail here. Mainly, we're using Google App Engine to handle all the hard work between the user's Google Spreadsheet and their Xero accounting data. Due to the flexibility of Google App Engine we were able to adapt our code, test it live without impacting existing Blink Reports users, and then make it the production version with zero downtime.

The solution means that Ryan can now generate large reports like the one you see below (click to enlarge). Note the columns stretch all the way to KJ—representing nearly double the original column limit—and it will continue growing as needed.

Xero's built-in reporting engine has good functionality, but when you need to analyze the data in a spreadsheet you're forced to export the report to Excel or Google Sheets format. This leads to static financial data that becomes quickly out of date. To put it bluntly, Xero simply cannot generate reports to rival what we can do on the Google Cloud Platform with Google Sheets.

With Google Spreadsheets and Blink Reports, all of a sudden you can review this month's revenue and expenses by worker, then change two date fields and see a different month (or even year) for comparison.

Offloading the pain of financial reporting to Blink Reports from Interlockit.com means that Ryan at TargetCW can now work more quickly through his finances and focus on the things that matter: generating business instead of generating spreadsheets.

Google Docs merging with Norada's Solve360 CRM

With the introduction of add-ons to Google Docs and Sheets last month, Google has greatly simplified what used to require the manual copying and pasting of Google Apps Script code.

Our most-often recommended CRM product, Norada's Solve360, has now introduced their own add-on for Google Docs that allows you to create merged documents and labels from records that already exist in your Solve360 database. We've tested it, and it works very well, with a couple of caveats. The biggest is that you can't create a form letter and mass-email it to your contacts; you can only email a merged document to an individual contact. Additionally, the merged document is attached to an outgoing email as a PDF.

Before you can start merging, you have to lay the groundwork for these new features. If your Google Apps domain is on Scheduled Release rather than Rapid Release, you'll need to manually enable add-ons for your users through your admin control panel. Next, either search "Solve360 CRM" in the add-on store or click this link to open the add-on's page. Click the  button in the top right-hand corner of the page, follow the prompts, and the add-on will be installed for you, though it could take up to an hour if you're adding it to an existing document.

Note that add-ons are installed on user accounts and are not domain-wide; if you want all your employees to have access to this feature, they each need to enable the add-on.

Most often when you're merging from a CRM to generate a document, it's some sort of form letter that is semi-personalized for multiple recipients. In this case, you'll need to change the first drop-down box in the add-on control panel to "set of documents" from its default, "documents". (If you want to create a single form document, feel free to leave this setting at its default; Norada has some great help docs here that you can follow.)

This feature works best if you use Category Tags to separate your contacts into appropriate groups. For this example, we'll use our "Customer-Google Apps" tag:

After clicking "Load records", we're presented with a brief summary of the number of contacts (in this case) that we'll be merging. You can see that our category tag contains 206 contacts that match the tag "Customer-Google Apps".

Switch back to your document and write out your message. Make sure that you write it in such a way that you can simply drop your Solve360 fields in place using the "Insert fields" button to insert the appropriate Solve360 fields into your message, like the document below.

Click "Create document" and wait for the system to chew through all the records (it could take a little while). You can now view the merged document in Google Docs or download it as a PDF. To send multiple messages, open the merged document in Google Docs, select the first merge, and copy and paste it into an email.

Google Docs and Sheets add-on functionality will only grow as the services become more full-featured, so if there's something you want to do with Google Docs that you couldn't in the past, check them out.

There are already dozens of add-ons available and they've only been around for a month!

ArrayFormula, Match and Offset in Google Spreadsheets

This is a repost of our contribution to the Google Gooru website that provides Tips, Tricks, and Tools for Gmail & Google Apps.

The arrayformula in Google Spreadsheets has a number of great different use cases. This video pairs the arrayformula with Match and Offset to pull in a constantly updating range of data in one sheet to create a chart in another sheet.

The advantage of using the arrayformula in conjunction with Match and Offset is it allows you to constantly add rows to your selected range, so you never have to worry about updating the formula. This would work great, for example, if you were pulling from the responses from a Google Form and wanted to consolidate them into a chart.

This video also happens to use our recently released Blink Reports Add-on for Xero Accounting!

Custom reports for Freshbooks, Xero, and Norada Solve360

We've been building custom reports for Norada Solve360 CRM in Google Spreadsheets for a while and realized that since Freshbooks has a published open API we could do the same for Freshbooks customers.

In our case we wanted to know billings/revenue by employee.  The default Freshbooks report lumps the full invoice amount under who created the invoice not who generated the billable time entry.

Our custom report directly grabs the invoice line items via the Freshbooks API so updating the report with the latest data is as simple as clicking the menu option "Refresh Invoices".

Freshbooks Billings by Staff/Employee

Since it is programmed in Google Apps Script inside Google Spreadsheets sharing the report is a snap and you can use the invoice line item data to populate pivot table reports.  Pivot Tables in Google Spreadsheets allow you to slice the data any way you choose for billings by week, by task, by rate, etc.

All the Javascript like code runs on the Google infrastructure so there is zero software to install or manage and zero recurring costs.

Contact us at Interlockit.com if you'd like to purchase your own copy of this report or have us build a different report specific to your needs for Freshbooks, Xero, or Norada Solve360.

Google Docs for your iPad vs iCloud

I've led a few iPad training classes for the Toronto/Mississauga branch of a company with 75,000 users on Google Apps.  They've been lots of fun because gadget guys like me that love to figure out cool ways to get things done get paid to show our tricks.

A common question is how do I easily sync presentations, pictures, pdf's, and documents from my desktop to my iPad and back again with iTunes or iCloud?  Well, don't...  It's easier to use Google Docs to store all your files in the cloud so you don't have to remember to sync and fiddle with iTunes

When you open for example a Microsoft Excel spreadsheet on the iPad from your Google Docs the Google servers automatically image it and display it.  At the bottom you can tap on Download to open the file in your iPad's viewer instead.

Not sure how to access your Google Docs from your iPad?  Install the Google Search App and it provides you with convenient icons to access your Docs, Photos, etc.

With more downloads than Angry Birds, Keynote for the iPad is an extremely popular application.  If you need to import Powerpoint presentations into Keynote for offline presenting first upload them to Google Docs from your desktop and make sure you don't convert it to the Google Presentations format.  On your iPad from Safari (not from the Google Search Apps embedded browser) visit http://docs.google.com.  Open the Powerpoint presentation from your Google Docs, scroll to the bottom and tap the Download link.  Now watch the top right for a black bar to appear with a button to open in Keynote.  If you miss it tap just below the standard Safari grey bar.  Click open in Keynote.  Viola, your presentation is now imported into Keynote and ready to go; no need to sync with iTunes or be anywhere near a computer.

Stating the obvious for regular Google Docs users now... but it's even better when you do everything in Google Documents, Google Spreadsheets, and Google Presentations format because team members can all collaborate on the document at the exact same time.  iPads can even edit Google Documents and Google Spreadsheets in real time.

If your team member made last minute revisions to the Google Presentation you don't need to even ask if you have the latest version.  Just open it from Google Docs on your iPad and start your presentation.

Certainly contact us at Interlockit.com if your team could benefit from our iPad training.

We're blushing... Prescient Power shows off a Solve360 report we built in the forums

We've been building custom reports like this with Google Apps Script in Google Spreadsheets for customers since 2010.  This is the first time our work has shown up in the forums.  Thank you Carl for sharing this!

You can view the forum thread at http://norada.com/forums/viewthread/2663/.  Scroll down to Nov. 20th, 2011

"wow - I guess Interlockit have some wizardry in their team!  Nice report - this is exactly what I need."
   --Q

"These custom reports are a really nice touch. Well done to interlockit for demonstrating this capability."
  --davieboy

"Just superb for management reporting and KPI tracking - forcing the “so what?“ question much more than you might expect."
   --Carl