Why We Chose Excel Functions For API Data Transformation On Connect iPaaS?

February 18, 2020

This week RoboMQ made available their first 2020 release of Connect iPaaS named Raphael, Sprint One. Named after the famed Italian painter and the architect of the High Renaissance whose work is admired for its clarity of form, ease of composition, and visual achievement of the Neoplatonic ideal of human grandeur.

has context menu ComposeAs we continue through the year, we are attempting to add features to make workflow composition using APIs on Connect iPaaS intuitive and easy for the business users or the citizen integrators. Also, it is not just for the citizen integrator but for anyone performing API integration. Why should you spend weeks or months of development cycle when you can create a workflow in 15 minutes or less?

One of the most involved tasks in creating an API or data integration is data mapping and transformations or as we often say massaging the data. It has been the holy grails of EAI, BPM and other integration technologies, since time immemorial, to provide a fast and easy way to map and transform data from one system to another. Various approaches have been adopted by different products from script snippets to custom mapping language and even to full-blown ability to write transformation in a programming language like Java or Javascript. We looked at all of these options with the quest to create something that can make the job of the end-user of Connect iPaaS simple, easy and intuitive while composing an API Integration on a no-code UX.

has context menu ComposeAfter extensive analysis and evaluation of alternative approaches, we settled on using a library of Excel style functions to provide data mapping and transformation between APIs and applications.

The fundamental assumption that we made is that the business users are skilled in the use of Microsoft Excel for day to day data manipulation
The design objective was that the Excel functions provided by Connect iPaaS should be exactly the same in syntax and in functionality to what is offered by Microsoft Excel
UX design should be easy and intuitive and at the same time allow complex data manipulation using provided Excel functions

As a result, we added 100+ Excel-style functions for data transformation on Connect iPaaS. These are grouped together under various heads like math, statistical, date and time, finance, text manipulation, logical operator, data type checking, engineering, and Trigonometry. You can use a combination of these functions along with the data elements from APIs to create powerful transformations.

Fig 1: Excel Style Functions on Connect iPaaS for data transformations

The platform also provides on-screen help on the usage and the syntax of the Excel functions as they are clicked or dropped in the transformation mapping.

Fig 2: A use case of using Excel-style functions on Connect iPaaS for API and data transformation

There are hundreds of use cases for these functions in data mapping while performing API Integration. Imagine getting data from Salesforce and transforming it to move the data to an accounting system like NetSuite, Dynamics 365 or QuickBooks. Another example could be integrating an accounting system like NetSuite to an enterprise expense management system like Coupa.

Excel functions in the hands of accounting, ERP or line of business users provide a powerful paradigm to use already mastered skills to API integration and business process automation needs. To summarize – if you can use Excel functions, you can do API Integration on Connect iPaaS!

Want to learn more? Reach out to me or one of the API experts at RoboMQ.

Do you have a Connect iPaaS account? If not, you can click here for a frictionless sign up of a free trial in the next two minutes.

Bramh Gupta

Bramh Gupta is the founder and CEO of RoboMQ. He has a background in large scale real-time manufacturing systems, telecommunications and design and architecture of highly scalable and resilient enterprise systems. He is passionate about real-time integration and the value that it brings to business operations and critical decision making.

Bramh holds an MBA from the Kellogg School of Business and Industrial Engineering degree from the National Institute of Technology, Jamshedpur. Bramh combines his business insights and architectural skills to design and create highly scalable, integration platforms and tools that are needed to power the API economy.

Check our Excel functions on Connect iPaaS and lets us know what you think about it?