Google Sheets Connector 1.0 - Examples - Mule 4

The following examples show how to use Anypoint Connector for Google Sheets (Google Sheets Connector) to create a row in an existing spreadsheet.

Prerequisites

You must have:

  • Access to Google Sheets

  • OAuth username and password

Configure HTTP Listener

Configure HTTP Listener to initiate a Mule flow when a call is made to the / account path on localhost, port 8081.

This example uses variables for some field values. You can either:

  • Replace the variables with their values in the code.

  • Provide the values for each variable in a properties file and then refer to that file from the connector configuration.

If you don’t know how to use a properties file, see Configuring Property Placeholders.

To configure HTTP Listener:

  1. Create a new Mule project in Studio.

  2. In the Mule Palette view, search for http and select the Listener operation:

  3. Drag the Listener operation onto the canvas.

  4. In the Listener configuration, click + next to the Connector configuration field to add a global element.

  5. Click OK to accept the defaults.

  6. Set the Path field to /:

Add and Configure the Append Spreadsheets Values Operation

  1. In the Mule Palette view, search for Google Sheets and select the Append Spreadsheets Values operation.

  2. Drag the Append Spreadsheets Values operation onto the canvas, to the right of the HTTP Listener component.

  3. In the Append Spreadsheets Values configuration, click + next to the Connector configuration field to add a global element.

  4. Configure the global element as follows:

    • Configuration Name: Google_Sheets_Connector_Mule4Connector_Config

    • Consumer key: ${config.consumerKey}

    • Consumer secret: ${config.consumerSecret}

    • Authorization url: Leave the default

    • Access token url: Leave the default

    • Callback path: ${config.callbackPath}

    • Authorize path: ${config.authorizePath}

  5. Click Test Connection to ensure your username, password, and tenant name are correct.

  6. Click OK.

Configure required parameters of the Append Spreadsheets Values Operation

  1. Click the Append Spreadsheets Values component.

  2. In the configuration panel of the component set parameters with your proper values (next values are examples):

    • Spreadsheet id: 19y7nVZPQz3NQSTSGfCh06KXPIqGV2YXohTupmynJyZ5

    • range: Sheet1!A1

  3. Click OK.

Add a Logger Component to Display the Response in the Mule Console

  1. In the Mule Palette view, search for logger and select Logger (Core).

  2. Drag the Logger component onto the canvas, to the right of the Append Spreadsheets Values component.

  3. Configure the following fields:

    Field Value

    Display Name

    Enter Log Response.

    Message

    Enter `#[payload]`.

    Level

    Leave the default, INFO.

  4. Create property file mule-artifact.properties in the src/main/resources with properties used in this demo

    • config.consumerKey

    • config.consumerSecret

    • config.callbackPath

    • config.authorizePath

  5. Save the project.

  6. Test the app by sending a request to / on port 8081.

  7. You will receive an JSON response similar to this one:

{
    "spreadsheetId": "19y7nVZPQz3NQSTSGfCh06KXPIqGV2YXohTupmynJyZ5",
    "tableRange": "Demo!A1:C38",
    "updates": {
        "spreadsheetId": "19y7nVZPQz3NQSTSGfCh06KXPIqGV2YXohTupmynJyZ5",
        "updatedRange": "Demo!A39"
    }
}

Append Spreadsheets Values in a Spreadsheet Example

Paste this code into the Studio XML editor to quickly load the flow for this example into your Mule app:

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

<mule xmlns:google-sheets="http://www.mulesoft.org/schema/mule/google-sheets" 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/google-sheets http://www.mulesoft.org/schema/mule/google-sheets/current/mule-google-sheets.xsd">
	<http:listener-config name="HTTP_Listener_config" doc:name="HTTP Listener config" doc:id="a841f003-9ac4-43bc-8751-10dd557b66d6" >
		<http:listener-connection host="127.0.0.1" port="8081" />
	</http:listener-config>

	<configuration-properties doc:name="Configuration properties" doc:id="7d6911bb-52b4-4d90-8724-62cfe239686a" file="mule-artifact.properties" />

    <flow name="demoFlow" doc:id="481ac26c-b66b-4f9d-b91f-4995ea8ff6a6" >
		<http:listener doc:name="Listener" doc:id="49be04b5-efec-4db6-8c49-d13475533be0" config-ref="HTTP_Listener_config" path="/"/>
		<google-sheets:create-spreadsheets-values-rangeappend-by-spreadsheet-id doc:name="Append Spreadsheets Values" doc:id="2618426a-cca7-41f0-87b2-90ea85045af2" config-ref="Google_Sheets_Connector_Mule4Connector_Config" spreadsheetId="19y7nVZPQz3NQSTSGfCh06KXPIqGV2YXohTupmynJyZ5" range="Demo"/>
		<logger level="INFO" doc:name="Logger" doc:id="9f7181ff-a7ad-4b0c-8300-07b8bd847672" />
	</flow>
</mule>