Integrating With Google Sheets (MuleSoft)
Google Sheet Module (MuleSoft)
Prerequisites:
- We need to have an active Google account and we need to make use of Google APIs to leverage the Google Sheet module provided by MuleSoft.
- To do so we need to follow the following steps:
- First we need to go to the Google API console using the URL https://console.cloud.google.com/apis/library.
2. Now we need to create a new project by clicking on the select project tab. To create a new project we need to provide a name for the project.
3. Once the project is created we need to enable the API and Services for the project. To do so we need to click the Enable API and Services.
4. We need to search for Google Sheets API to configure it so that we can leverage the Google Sheets API services.
5. We need to enable the Google Sheets API for our project by clicking on the enable tab.
6. To configure the OAuth Client ID we need to configure the OAuth consent screen. Hit on the OAuth consent screen and choose the User Type as External and hit on Create.
7. Now we need to create an app for the Oauth consent screen.
- First we need to fill up the App Name, User support Email, and the email address in the Developer Contact Information and hit Save and Continue.
- Then we must provide the necessary scopes for our application and hit Save and Continue.
- Then we need to add a Test User for our application and hit Save and Continue.
- Then hit Back To Dashboard and we can see our app.
8. Now we can see our app in the OAuth consent screen.
9. We need to create credentials for our project to get the client id and client secret. To do so we need to hit on the create credentials and choose “OAuth Client ID”.
10. We need to provide the Application Type as “Web Application” and we need to provide a name for the application. Then we need to configure the redirect Uri in the Authorized Redirect URI as http://localhost:8081/oauth2callback.
11. We can see the OAuth Client ID under OAuth 2.0 Client IDs and by clicking on it we can get the Client ID and Client Secret which will be used later in the Google Sheets Module.
12. We need to create one Google Spread Sheet under the same Email ID that we configured in our Google API. In the URL we can find the Spreadsheet ID which will be used later.
Now let’s configure our Mule application to use the Append Spreadsheet Values from the GoogleSheet Module.
Step 1:
- Open the Anypoint Studio create a Mule project and provide a name for the project.
Step 2:
- Now drag and drop a Flow and add an HTTP listener to it to create a source listener to get the mule event for the application.
Step 3:
- Now we need to configure the HTTP listener by providing the connector configuration and path.
- In connector configuration we need to provide the below details.
- Protocol: HTTP or HTTPS
- Host: All Interfaces[0.0.0.0](default)
- Port:8081
- Rest we can leave as default.
- Then hit OK.
- For path we can define any resource path following “/”.(Example: /SheetTest)
Step 4:
- Now we need to add a Google Sheet module to our mule palette. To do so we can search in the exchange about the Google Sheet module to add it to our anypoint studio. Once added we can see in the mule palette.
- Once we got the Google Sheets module we can see all the connectors to perform the required operation.
- We are using the Append Spreadsheet Values connector for the configuration for this particular scenario. We need to drag it to the mule flow.
Step 5: In the Append Spreadsheet Values connector configuration section we need to hit the plus button.
- We need to provide the values for the following:
- Access_type : offline
- Prompt: consent
- Consumer Key: The Consumer ID provided in the Google API application
- Consumer Secret: The Consumer Secret provided in the Google API application
- Authorization URL: https://accounts.google.com/o/oauth2/auth
- Access token URL: https://oauth2.googleapis.com/token
- Scope: https://www.googleapis.com/auth/spreadsheets
- Listenere config: Choose the listener config of the HTTP listener
- Callback path: /oauth2callback
- Authorize path: /authorize
- External Callback URI: http://localhost:8081/oauth2callback
Step 6:
- Then we need to provide the details below to complete the setup for the Append Spreadsheet Values connector.
- Spreadsheet: The Spreadsheet ID which we can get from the URL of the spreadsheet.
- Range: Sheet1!A1:C2 (The range of the spreadsheet columns in which we want to append the data)
- Value Input Option: Default
- Insert Data Option: Empty
- Content-Type: application/json
- Spreadsheet values range append contents: The payload that we want to store in a spreadsheet in the below format.
%dw 2.0
output application/json
—
{
“values”:[[payload.id,payload.name,payload.loc]]
}
Step 7:
- Now drag and drop a Transform Message shape to transform the output payload to JSON format by using the Dataweave
%dw 2.0
output application/json
—
payload
Step 8:
- Now hit on Save and Run the project.
Step 9: In the console we can see the “Status” as “Deployed” to ensure our app is deployed and ready to be tested.
- Once the Application is up and running we can Test it by triggering the Mule app from Postman.
Step 10:
- Now we need to go to any browser and hit http://localhost:8081/authorize.
- Now we need to log in with the Mail ID which we configured in the Google Spreadsheet API and provide the necessary permissions to get the Access token.
Step 11:
- To trigger the Mule app we need to create a Request and we can hit the URL(http://localhost:8081/GoogleSheetTest) from Postman.
- We need to send a JSON body from the postman to the Mule Application to store the data in the Spreadsheet.
- We can see in the response the data is appended in the Spreadsheet.
- Now we can see in the console that the mule app got executed and we can check the Spreadsheet for the appended values.