Introducing the Google Cloud Connectors Suite (Part 1/3)


Mark Zuckerberg once said: “How can you connect the world if you leave out China”. Well, I now at this moment say: “How can you connect the cloud if you leave out Google.” I know I don’t have his net worth, but I have a point nevertheless. The reality is that Google has done a great job building a Gazillion of different and very cool APIs, and you’d be right to feel that it’s hard to keep their pace. To help you with that is that we proudly present to you the first release of the Google Cloud Connectors Suite.

This will be an ongoing process of building Cloud Connectors for Google’s most popular APIs to help you integrate them into your apps. So far we have covered:

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:

Quite big isn’t? Don’t worry, for ease of read and understanding we’ll split this post into 3 parts:

  1. Part 1: which introduces the series and shows how to install the connector and a high-level view of all the flows
  2. Part 2: We’ll discuss common use cases and quick code examples
  3. 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 set

The first 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 Debrief

So, 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 integrationNote 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. They’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 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 in 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 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 () also introduced in Mule 3.3.0 to decide if the current row is useful or not. The expression looks like this:

Could not embed GitHub Gist 3805325: Bad credentials. The API can't be accessed using username/password authentication. Please create a personal access token to access this endpoint:

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:

Could not embed GitHub Gist 3805340: Bad credentials. The API can't be accessed using username/password authentication. Please create a personal access token to access this endpoint:

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

The 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.


We'd love to hear your opinion on this post

17 Responses to “Introducing the Google Cloud Connectors Suite (Part 1/3)”

  1. The link to the iapp-examples in github seems to be broken.

  2. Hello Diddy, thanks for pointing that out! you can access it now.


  3. […] we’d love to hear from you.Stay tuned for our GA release in December 2012.Related posts:Introducing the Google Cloud Connectors Suite (Part 1/3)Welcome to Mule EnterpriseMule 3.3 has launched!Getting started with Mule: Free Training, Videos, […]

  4. Hi Mariano,

    I have used your example app to try and build a flow in Mule ESB where and I want to retrieve a list of Google Calendar events. I can get the authentication flow working using a google calendar connector (authorise) and seem receive a valid googleId (OAuthAccessTokenId). I tried to use that Id in a new flow (after storing and retrieving it in the mapping object). I also created a variable and linked the value to the ‘googleTokenId’. I use a next calendar google connector (getEvents) but I get always the same error saying : “The accessTokenId cannot be null”. I seems to me that the AccessTokenId is not passed correctly (?) I am obviously missing something here. Can help me out ?

    • Bart,

      Could you add your mule config so that I can take a look at it?

      Thanks for the feedback?

    • Hi Mariano,
      I am trying to fetch the spreadsheet values. when I hit the application every time it is asking for permission(Allow or Deny). Is there any way to Allow directly with out asking every time.
      Thanks in advance.

    • Hi Mariano,
      Please provide me the simple example with code which fetch the data from
      spreadsheet and the output is List. Please do the needful. From past 4 days I am struggling with this.
      Thanks in advance

  5. Hello Bart,

    I saw that you stored the accessTokenId in a flowVar called googleTokenId. The problem is that you’re not using it.

    Right now your config says:

    It should say:

    Please let me know if this helps. Sorry my answer took so long!

  6. Mariano, I can’t find parts 2 and 3 of your blog. Where are they? And another question, I just can’t find which values to fill in the “Config Reference” for the Spreadsheets connector. I’ve searched in Google Apps and found a Client ID, Email address and Public Key fingerprints… but I don’t think that is what I need?

  7. Raul, Here’re the links:

    Part 2: /ms-office-is-so-last-year-connect-to-google-apps/
    Part 3: /one-to-map-them-all-google-cloud-connectors-suite-part-33/

    As for your question, the config reference is the name that you gave the connector. You can create the config clicking on the green plus icon that shows up in studio next to the config reference selector. You can also find an XML example in this post: /one-to-map-them-all-google-cloud-connectors-suite-part-33/

    Things like the api key, the consumer secret, callback path, etc are set in this config.


    • Hi Mariano, The links for part 2 and part 3 are not working. I am getting 404 error. Please reply.

      • Part 3 can be accessed here: /dev/anypoint-studio-dev/one-to-map-them-all-google-cloud-connectors-suite-part-33/

      • Hello,

        Thanks for point out the broken links. I just fix that on the related links.

        Part 2: /dev/anypoint-platform-dev/ms-office-is-so-last-year-connect-to-google-apps/
        Part 3: /dev/anypoint-studio-dev/one-to-map-them-all-google-cloud-connectors-suite-part-33/

  8. Hello Raul

    You could check the links do not work these

  9. Hi Mariano,
    I have used your example app to retrieve a list of rows from spreadsheet. When I try to execute this program, every time it is asking for gmail access permissions( Allow/Deny).Is there any way to automatically Allow the program to read the spreadsheet data. I am obviously missing something here. Could you please help me out ?

    • Hello,

      To do that set the access_type parameter to “offline”.