Reading Google Sheet tabs data using Mule 4

Photo by Rubaitul Azad on Unsplash

In this post I want to give a pretty quick proof of concept on how we can read a Google spreadsheet using Mule 4, as well as include this as part of the mulesoft recipes Github repository.

The expected result.

I have created a Google spreadsheet in my personal account, and the idea is to have a document that at least at the beginning is not a regular column row format, meaning that in order to read the data we need to start from a different cell or row and the spreadsheet also contains multiple tabs. The spreadsheet looks like this:

The output or the result, is to be able to grab the data in the columns and prepare a payload to send it to a different place like a database / salesforce, etc.

Setting up Google credentials.

In order to connect to Google spreadsheets, we need to follow few steps

1. Login into Google cloud and select from the side menu the option APIs and Services > Enabled APIs & Services.

2. Click on add services and search for “google sheets api” and enable the service:

3. Now we need to generate our credentials, and we need to create a OAuth client ID ones

4. Select Web application type and once you complete the section it should be able to see client_id and client_secret. Remember to set the redirect url to be http://localhost:8081/callback (for running local)

The Mulesoft setup

The Mule application is not more than one single flow where we need to setup the Google Sheet connector and setup the connection.

1. From Exchange search, we search for Google Sheet and add the module

2. Now select from the Mule palette the “Get Spreadsheets Values”

3. Let’s create a new Google Sheet Configuration, here the consumer key and secret are taken from our credentials created before, in the second section, we need to setup the http configuration where we need to authorize the application the first time and store the token.

4. Now in the connector we need to setup the spreadsheet and range. The spreadsheet value is taken from the URL

and the range is taken in format of tabname!rangeOfData. (Sheet1!A10:E40). In this case your tab name is My cool tab for example, then you need to use the URL endoded value which will translate to (My%20cool%20tab!A10:E40).

Running the application

1. In order to authorize the application we need to go to the browser and use http://localhost:8081/authorize, then you need basically to authorize the call.

2. Now let’s run the application, we will see how data looks out from the connector based on the range, as we can see the first row contains the headers and the rest is the data we are pulling from the range.

3. In order to map the headers and data we need to use this DataWeave script and data will map correctly to the column names.

%dw 2.0
output application/json
var headers = payload.values[0] //collects headers
---
(payload.values map(record,index)-> {
    (headers map (header,index)->{
        (header):record[index]
    })
})

In the end this is just the beginning and we should be able to extend this POC.

You can access the code on the Muleosft-recipes. Enjoy!