So, this is the first of a three-parter series of blog posts in which we’ll cover basics and details about using the Google connectors and how they play with other connectors and each other. We’ll show this by building an app that:
- Takes a list of clients from a Google Spreadsheets
- Creates those clients in SalesForce
- Then create the same clients in Google Contacts
- Creates appointments on Google Calendars to meet with the new clients
- Creates task on Google Tasks to follow up with those new clients
- And finally, marks those clients as Synced in the original Spreadsheet
Quite big isn’t? Don’t worry, for ease of read and understanding we’ll split this post in 3 parts:
- Part 1: which introduces the series and shows how to install the connector and a high level view of all the flows
- Part 2: We’ll discuss common use cases and quick code examples
- Part 3: This requires a whole bunch of data transformation. We’ll show you how to close the integration circle with extreme use cases of Mule’s Data Mapper
Get setFirst thing to do is to install the connectors. If you’re using Mule Studio, then the connectors are available in the Cloud Connectors update site. Go to the Help menu -> install new software and select the Mule Cloud Connectors Update Site. Then select the Google Cloud connectors and follow the wizard: If you’re not a Studio user, then you can find how to install each connector through Maven following the links above.
Mission DebriefSo, as we already said, this application will take a list of clients from a Google Docs Spreadsheet. This is how the list looks like:
(and yes, in case you’re wondering, I forgot to mention that our company also sells utility belts to super heroes) So this list is pretty straight forward: it has an Id, a Name, a contact phone number and a flag that says if the client has already been synced by this application or not. So, let us now take a look at the flow that starts the magic:
Let’s take a deep dive into this! We first start with an Http Endpoint that triggers the integration. Note that all through this post we’ll assume that we have an authenticated session against both Google Apps and Salesforce. The details on how that happens will be discussed in the second part of this series. Then we use the “Gel Cells” processor from the Spreadsheets Connector to get the information on the list. The configuration looks like this: All we need to do is provide the name of the spreadsheet and the worksheet we want to get and this processor will return a list of objects that represent all initialized rows and cells in the document. There’re also two optional index parameters since per Doc’s API, it is possible to have several spreadsheets/worksheets with the same name. Note: Although for the purposes of this example we’ll be taking all the initialized cells in the spreadsheet, please note that the connector provides processors that allow fetching a specified range only For clarity sake, I grouped the next tasks in three groups, or in Mule’s terminology, sub-flows. One to do the integration with Salesforce, another one to do the integration with the rest of the Google Apps, and finally one to mark the contacts we just got as Synced. We use the <all> router to fork the current message into each of these sub flows.
Say hello to my little Friend, the Data Mapper
Let’s begin by taking an overall look of the sub flow that takes care of integrating with Salesforce.
So, from the previous Get Cells operation, the message’s current payload is a List of objects representing the Rows in the spreadsheet. This object contains both the number of the row and a List of Cell objects which contains the actual value. However, what the Salesforce Cloud Connector needs is a List of Maps. DataMapper allows to easily perform this transformation with the following mapping:
So on the left hand side of the image above you can see the actual object graph that the Spreadsheets connector produced and we described. On the right hand side, you can see the List<Map> instance that the Salesforce connector needs. If you look closely, you’ll also see that this mapping is using several jxpath rules. We’ll go into detail of how this work on part 3 of this series, but for now, the important thing is to get a hold on how the List<Row> structure looks like and the fact that DataMapper is super useful for transforming it into a List in which each entry represent a row, and where each Map represents a Cell (the map will use the cell number as key and the cell content as value)
After converting the message payload from List<Row> to List<Map>, a second transformation is used to remove the entries that correspond to rows already synced. Just as before, this transformation will be discussed in detail on part 3.
Finally, the integration to Salesforce is done by performing a bulk upsert operation.
From Google to Google
Let’s continue by taking an overall look to the sub-flow that integrates the spreadsheet to Google’s Calendar, Tasks and Contacts.
What you have to remember in order to understand this sub- flow is that it’s being invoked in the context of an <all> router in the main flow. So, although we just saw how to transform the message payload from List<Row> to List<Map>, the message that this sub-flow is receiving is the original List<Row> instance. We’ll then start the flow by using the <foreach> processor introduced in Mule 3.3.0 to iterate through each of the rows.
After doing that, we use a <choice> router. Why? Because the
first item of the List belongs to the column’s headers in the
spreadsheet and we do not want to do anything with those. So, now we’ll
use the power of the Mule Expression Language (MEL) also introduced in Mule 3.3.0 to decide if the current row is useful or not. The expression looks like this:
payload.getRowNumber() > 1
In summary, if the above expression is true then we integrate that row, otherwise we just log a message. To make this example easier to read and understand, we’re now going to discard most of the information in the row and we’ll just keep the name of the contact. The JXPath expression evaluator is helpful for extracting the name out of the row. Let’s see how:
<set-payload value="#[jxpath://cells[columnNumber = '2']/evaluatedValue]">
So what this expression does is going into the row’s cells and get the one that corresponds to the second column which is the one holding the name. Once that cell is located, it selects its value.
Finally, we again use the <all> router to invoke another three sub-flows which will take care of integrating to the other google apps. These sub-flows are very simple ones. We use the Calendar’s Quick Add processor to create an appointment:
We have another one that uses the Insert Contact processor in the Google Contacts Connector. This time we’ll use a simple Java transformer to map the client’s name to a Contact object:
And we use the same strategy to insert the Task:
Raise the Flag
Last step is to change the Synced flag on the original spreadsheet so that the same clients are not re synced each time around. The corresponding sub-flow looks like this:
We first use DataMapper to change the flag value (details on this will be explained in part 3) to finally yield to the Spreadsheet Connector’s “Set Row Values” processor. This processor is one of my favorites! It takes a List<Row> and updates the values of each cell while leaving all other cells untouched, making it extremely easy to fetch cells, modify them and then updating their status. And to make it extra cool, all of this happens in a batch operation so that you can modify large amounts of cells in only one remote request.
To Be Continued…
So in this post we introduced the Google Connector, proposed a sample application and gave and overview of it. Please follow us as we prepare the second and third part of this series where we’ll show code snippets that can get you through the most common use cases. If you want to get a sneak peak into the example’s code you can visit our github page and look at the google-connectors-example application.