XTRF Reporting with Google SpreadSheets

Combining data from different systems, their convenient and easy presentation, and enabling cooperation between multiple users are the key features of any good reporting system. And since all of them are supported by both XTRF and Google SpreadSheets, we struck upon the idea that the combination of these two tools would make reporting yet easier, more enjoyable, and even more effective.

A beautiful and flexible dashboard

The advantage of Google SpreadSheets lies in the ease of processing and presenting data from various sources. Business data can be filtered, aggregated, and shown in a way that facilitates analysis, supports business decision-taking, and assists administration at various management levels.

There are many ways of presenting information in reports, and below we have given an example of a financial and operational report containing data on current projects, offers and the status of financial settlements, both present-day and projected for subsequent periods. The report helps in assessing the company’s condition in the context of ongoing and contracted projects, up to and including high-level analyses of cash-flow risks.

Sample operation report in Google Spreadsheets basing on XTRF data

Creating a similar report requires only a basic practical knowledge of spreadsheets. This one cited is available under the link http://bit.ly/XTRFDashboad, and you can copy it (menu option -> file-> make a copy) to your disk drive and modify it freely.

However, in order for our Dashboard to show the correct values, it has to be properly populated. In our example, the data is retrieved from subsequent tabs of the spreadsheet, in which information relating to projects, invoices, clients, quotes, and payables is presented in tabular form.

Integration – accessing the right data

The data provided in consecutive tabs can be copied from a database, CSV files, or even from another browser window (where it is listed in tables).

Project view configuration - XTRF Smart Views

The XTRF system makes it extremely easy to prepare such data for reporting purposes. Thanks to XTRF SmartViews, each set of data can be presented in the required format. The user has full control over what columns are in the table. These can be either specific fields of exported entities, associated entity fields, or even custom fields and custom columns, i.e. fields whose content is calculated dynamically based on the content of other fields.

XTRF SmartViews also allows users to configure filters in order to restrict data solely to that which is required for the work performed by the operator. Thus prepared, the data can be copied to the sheet.

Integration – access to current data

However, copying data from various sources is a very ineffective method of action. All too often, it results in the analysis of outdated, inconsistent or even incorrect data. Worse still, manual copying leads to simple errors, while the continuous repetition of the same process is simply irksome.

XTRF for Google SpreadSheet - solution architecture

XTRF for Google SpreadSheet is a tool that automatically synchronizes data between any SmartView pane and the Google SpreadSheet platform. Using simple steps, you define the range of cells into which data are to be inserted and provide the url of the SmartView pane from which data is to be imported (the url is the address taken from the browser bar when displaying data in XTRF).

Many different views can be synchronized within one sheet simultaneously, and data can be synchronized on demand by means of the “synchronize all” option. A very useful option is automatic synchronization: with just one click you can switch the extension into a mode in which data is imported from XTRF automatically every hour, even if the sheet is not actually open in a browser window!

XTRF for Google SpreadSheet – how to start

All that you need is access to the API in your XTRF installation. XTRF for Google SpreadSheet is currently in beta, so you will not find it in Google’s Add-On Store, although you can easily install it using this link: https://bit.ly/XTRF4GoogleSpreadSheet

XTRF for Google SpreadSheet is not yet publicly available in Google App Store

After opening the link, click “Free” to install the add-on in your Google Sheets. Then follow the instructions, providing the additional required consents. Your add-ons menu will now have the “XTRF for Sheets” option. After selecting “Import Smart View”, a mechanism for synchronization with XTRF (highly convenient!) will appear on the right.

To use, enter the “XTRF API access token” created in your XTRF user settings under the “Access token” tab; detailed instructions can be found here.

You can now define further data sources by linking a SmartView pane in your XTRF instance with the SpreadSheet cell address where the data is to be inserted.
You can define as many data sources as you need.

If you want to enable automatic synchronization of your data, use the option at the bottom of the extension. There is also an option to change the API token.

Price & Support

The add-on is currently a trial version and will remain free until September 2018.
After this date, a small fee will be introduced.

Assistance and discussions regarding additive and reporting capabilities are available on XTRF User Echo.

Dominik Radziszowski, PhD Eng.
A full stack IT analyst, technology visionary, cloud expert, solution architect, IT expert and auditor spanning the IT and business worlds, and software localization and internationalization practitioner. Co-founder, architect and developer of the XTRF Management System.