Over the last few months, I have been working on integrating Safe Software’s FME with Xero, our accounting package, to review financial data within our business intelligence platform. While Xero offers some reporting tools, they aren’t comprehensive enough for all our needs.
Xero has an extensive API that, at first glance, seems well-documented. However, diving deeper often requires searching forums for crucial bits of information, which can be quite frustrating at times. Nonetheless, FME is an excellent tool for exploring the API.
Key Aspects of Interacting with the API
Web Connection
The first key aspect of interacting with the Xero API is setting up the Web Connection, which handles all the security and authentication between the API an Xero. The great thing about FME is the community that exists around it and I found that a forum member had built a Web Connection just for Xero. Connecting to Xero should have been easy, but I must admit that it was really frustrating, largely because of poor documentation from Xero.
To authenticate, you need to combine the ‘Tenant ID’ with the ‘Secret ID’ and then encode it into Base 64. FME simplifies this process using the TextEncoder transformer. You then plug the encoded text into the Web Connection to establish the connection—simple, right?
Well, not quite. Despite following the steps, I encountered 401 errors and couldn’t get the authentication to work. It was challenging to determine whether the issue was due to user error, a technical bug, or insufficient access rights.
After revisiting the entire setup, I concluded it was user error. Eventually, I discovered the problem: I had inserted a space between the Tenant ID and the Secret ID when encoding them into Base 64. The Xero documentation was mute on this point, it simply didn’t say anything useful, but perhaps if I was a proper developer I would have known not to have used a space. With the space removed I was being authenticated and that meant I could crack on.
Extracting and Transforming Data
My next tasks involved using the HTTP Caller transformer and various JSON transformers to access different parts of the API and transform the JSON responses into usable data for our BI tool. I focused on three main areas: invoices, projects, and employee data.
The Xero API Explorer was particularly useful for testing API GET requests within the Xero environment and verifying the responses in FME. This approach allowed me to quickly build a workspace that accessed the data we needed.
I extracted the necessary information from the JSON responses using the JSON Fragmentor, JSON Flattener, and JSON Extractor transformers, with help from my colleagues.
Current Status and Takeaways
While the workspace isn’t entirely finished, it effectively meets our needs. Every evening, it runs, extracting data from Xero and writing it into an Excel spreadsheet that our BI tool picks up and charts. This setup enables us to query the data in Xero and make accurate, data-driven decisions.
The Xero API could be much better documented, little things mean that the process was much harder than it needed to be, but I guess the takeaway for me is that whilst I’m not an FME expert (I’d be pushing it to use the word novice), you can still achieve a lot with some basic FME skills and a bit of help from an expert.
