Implementation of MuleSoft’s Google Sheet Connector

Authors: Mayank Prakash, Sharma, Rohit Singh & Mitali Chaudhary

Introduction:

This blog will help you in understanding the process of using the Google Sheet connector provided by MuleSoft with MuleSoft’s Anypoint studio. 

Pre-required concepts:

Google APIs:

Google APIs are pre-developed interfaces by Google that allow communication or integration of Google Services with other services. It uses the client id, client secret, and the scope to authorize the user, and using it the access token can be generated.

Google Refresh Token:

 It allows users to generate access tokens when the user is offline. The web applications store these refresh tokens so that the application can be authorized wherever needed. It omits the use case of manually creating access tokens multiple times.

OAuth Dance:

OAuth dance is an authentication process performed by the client, Mule application (server), and OAuth 2.0 provider. where: –

1. MULE application: The Mule application is

             secured by the 2.0 policy.

2. Client: Any application which wants to access the above

             Mule application.

OAuth provider:

OAuth provider: It is software that provides the secure token to the client and validates the token. There are many third-party OAuth providers like Okta, GitHub, Salesforce, OpenAM, etc. MuleSoft also has its own OAuth provider. We are going to use a Mule OAuth provider.

Prerequisites For the Integration:

1. MuleSofts’s Anypoint Studio 

2. Google Cloud Console Access

Note: In the demonstration confidential fields are masked.

Google Cloud Console Setup:

Step 1: Log in to http://console.cloud.google.com/

Step 2: Create a new project or choose a previously created project through the drop-down menu. This project will serve as a single window for accessing services provided by Google Cloud Console.

Step 3: Open the API & Services Tab of your created Project

Step 4:   Click on Library Tile from left-side menu 

Step 5:   Search for Google Sheets API and Enable it by clicking on Manage Tab

Step 6:   Now Go to the credentials tab, In this window following details will appear

1. Client ID
2. Client secret
3. Creation Date

Add the following URL https://developers.google.com/oauthplayground/  in place of Authorized redirect URIs which will serve as the OAuth provider for the API as we are using an external OAuth service.

Step 7: Now go to the following link https://developers.google.com/oauthplayground/

We will use the OAuth playground for providing OAuth and access management.

Step 8: Click On the settings icon and add the Client Id and Client Secret  

Step 9: Scroll down to Google Sheet API and Enable the following 

Step 10: Now click on Authorize APIs 

Step 11: A Window will appear, Login with the google account linked to the Google API console to finish the OAuth dance process.

Anypoint Studio Setup:

1. Get Spreadsheets Values Connector:

• Go to Mule palette and Add Google Sheet Connector from Exchange

• Create a basic mule flow and add the Get Spreadsheet Values connector to the flow

• Add the configurations
• Configure the connector with the following fields:

1. Consumer Key and Consumer Secret (Available on Google Cloud Console)
2. Access Token URL, Authorization URL, and Scopes are prefilled fields
3. Callback path: https://developers.google.com/oauthplayground (OAuth provider URL)
4. Authorize path: The /authorize endpoint is used to interact with the resource owner and get the authorization to access the protected resource.

• Configure the connector with the following fields:

• Spreadsheet:

Specify the Google Spreadsheet Id

• Range: 

Specifies the Spreadsheet row and column number which has to be read to extract the data.

• Call the flow using Postman and the data will be received:

2. Append Spreadsheets Values Connector:

• Go to Mule palette and Add Google Sheet Connector from Exchange

• Create a basic mule flow and add the Append Spreadsheet Values connector to the flow

• Configure the connector with the following fields:

• Spreadsheet:

Specify the Google Spreadsheet Id

• Range: 

Specifies the Spreadsheet row and column number which has to be read to extract the data.

Insert Data Option:

Type of operation used to add data (Insert New Row or Overwrite the previous data)

• Content-Type
• Content to be added (Payload Value)

• Add the content to be added in the Spreadsheet in the set payload

• Call the flow using Postman and the payload data will be Inserted in the spreadsheet and success response will be received:

• Spreadsheet View:

Note: Other Google sheet connectors can be used in similar manner and the component configuration remains the same.

Summary:

In this tutorial, we understand the process of establishing Google sheets integration with MuleSoft’s anypoint studio using the Google Sheet connector provided by MuleSoft. We used Get spreadsheets values and Append spreadsheets values connectors to learn the configuration and working of the connectors. We got familiar with the Google cloud interface and Google OAuth playground and learned to use them for the integration.