Using Google Sheets API and Mule data in 6 Easy Steps

Author: Monil Porwal

In today’s age, we use Google sheets a lot to maintain data and do manipulation with that data. It enables us to access the data anywhere anytime using our google account. Did you know? We can send the data to the google sheet without even opening it. In this blog, we will see how to insert data to google sheets using the google spreadsheets API and Mule 4.

Pre-requisites: 

• Anypoint Studio 
• Mule runtime version 4.x 
• An active google account
• Postman

Scenario:

We will be passing data to our mule application using postman and then consume spreadsheets API to insert the data to a Google Sheet.

Step 1: 

Create a google sheet with the headings for the columns of data: 

Fig 1.1

Step 2: 

• Log-in to google developer console: https://console.developers.google.com
• Create a new project and on the dashboard click on the Enable API and Services.

Fig 1.2

You’ll be able to see a dropdown menu to select the project, click on it. You’ll get a pop-up window, select “New Project“ and create a new project after giving the name.

Fig 1.3

Next, click on the “ENABLE APIS AND SERVICES” button from project dashboard, shown in fig 1.2 . In the API library, search for sheets API, click on it and enable it using the “ENABLE ” button.

Fig 1.4

Step 3: 

Configure the OAuth Consent Screen, click on the “OAuth Consent Screen” and select “External” and then click “Create”, this defines the type of application. If you select “Internal” additional verification checks would be required and would have to submit your app for verification.

Fig 1.5

Next, configure your app and add the spreadsheet scope by clicking on “Add Scope” as shown below:

• Application Type: set it to “Internal”
• Application Name: <as per your choice>
• Scopes: Click on add scope → and add the “../auth/spreadsheets” scope

Step 4: 

Now you need to create credentials for your app. On the app page in developer’s console (Shown in fig 1.2):

• Click on “Credentials”.
• Click on “Create Credentials” and select “OAuth Client ID”.
• Select application type as “Web Application”.
• Add authorized redirect URI as: https://developers.google.com/oauthplayground 
• Upon clicking create you will get Client ID and Secret, copy and save them, we will need that in the next step.

Step 5: 

• Since our app will need an authentication token to access the API we need to create the same.
• Go to : https://developers.google.com/oauthplayground 
• Click on settings and select “Use your own OAuth Credentials” and fill the client ID and secret that we generated in the previous step.
• Now, on the playground window to the left select the scope “Google Sheets API v4 →  https://www.googleapis.com/auth/spreadsheets” and click “Authorize API”.
• You will get the authorization code, exchange it to generate the Refresh Token and Access token, copy and save them.

Step 6:

Now we are finally ready to send the data from Postman via Mule app to the google sheet.

• Go to the spreadsheet we created in step 1 and copy the spreadsheet ID from the URL of the sheet. It would be as shown in the image below: 

• All we need is a HTTP listener to trigger the app.
• Transform message to transform the inbound data in the Sheets API expected type.
• Post it to the spreadsheet via the API using the HTTP request component.
• Please find the below configuration.xml for the Mule flow: 

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core" xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns="http://www.mulesoft.org/schema/mule/core"
	xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd">
	<http:listener-config name="HTTP_Listener_config" doc:name="HTTP Listener config" doc:id="d2da6509-7b8f-4ef4-b4ff-76d770c36031" >
		<http:listener-connection host="0.0.0.0" port="8081" />
	</http:listener-config>
	<http:request-config name="HTTP_Request_configuration" doc:name="HTTP Request configuration" doc:id="332dbba2-69dc-45e7-9f40-fb0307131643" basePath="/v4/spreadsheets/" >
		<http:request-connection protocol="HTTPS" host="sheets.googleapis.com" />
	</http:request-config>
	<flow name="google-sheet-demoFlow" doc:id="14f9e4a2-4dea-46d2-bbbd-e16348b89808" >
		<http:listener doc:name="Listener" doc:id="94742790-a089-467c-b230-2e8757f50158" config-ref="HTTP_Listener_config" path="/gsheet"/>
		<ee:transform doc:name="Transform Message" doc:id="92f96041-0103-4a96-8b26-116339a4054f" >
			<ee:message >
				<ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
{
"range":"Sheet1",
"majorDimension":"ROWS",
"values" : [
	[
		payload.id,
		payload.name,
		payload."address"
	]		
 ]
}
]]></ee:set-payload>
			</ee:message>
		</ee:transform>
		<http:request method="POST" doc:name="Request" doc:id="6594c401-7e71-421b-8ca1-b9e544eb0eb7" config-ref="HTTP_Request_configuration" path="<replace with your sheet id from step 6a>/values/Sheet1:append?valueInputOption=RAW">
			<http:headers ><![CDATA[#[output application/java
---
{
	"Authorization" : "Bearer <replace this with your access token from step 5>"
}]]]></http:headers>
		</http:request>
	</flow>
</mule>

The above code will create the necessary flows.

Note: The value of access token and spreadsheet ID are replaced with placeholder in the snippet.

Please add your corresponding values.

The access token is valid only for 1 hour. However, you can also generate new tokens using the refresh token we got in step 5.

Please refer: https://developers.google.com/identity/protocols/oauth2 for the same.

Deploy the app and trigger the app via Postman using a POST request to: 

URL: http://localhost:8081/gsheet 

Body:

 {
    "id" : 1,
    "name" : "monil",
    "address" : "test"
}

 

It shall reflect the data to the spreadsheet. Similarly, you can perform CRUD operations easily using Sheets API and Mule.

Cheers!

References: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append