Google Spreadsheet Connector Example¶

The Google Sheets API lets users to read and modify any aspect of a spreadsheet. The WSO2 Google Spreadsheet Connector allows you to access the Google Spreadsheet API Version v4 from an integration sequence. It allows users to read/write any aspect of the spreadsheet via the spreadsheets collection. It has the ability to do spreadsheet operations and spreadsheet data operations.

What you'll build¶

This example explains how to use Google Spreadsheet Connector to create a Google spreadsheet, write data to it.

It will have three HTTP API resources, which are /create, /write and /read.

/create: The user sends the request payload, which includes the name of the spreadsheet. This request is sent to the integration runtime by invoking the Spreadsheet API. It creates a spreadsheet with specified title and returns the spreadsheet ID and URL.
/write: The user sends a request payload containing the spreadsheet ID and the data to be inserted into cell A1. This request is routed to the integration runtime by invoking the Spreadsheet API, which writes the data to the specified range in the spreadsheet.
/read: The user sends a request payload containing the spreadsheet ID to the integration runtime by invoking the Spreadsheet API, which then reads data from cell A1 of the specified spreadsheet.

If you do not want to configure this yourself, you can simply get the project and run it.

Setup the Integration Project¶

Create a new project¶

Follow the steps in the create integration project guide to set up the WSO2 MI and create the integration project with the Project Name as follows:

Open the WSO2 MI VS Code extension and click on the Create Integration Project icon.
Give the Project Name as GSheetIntegration and click the create button.

Create the Integration Logic for the Create Operation¶

Follow these steps to Configure Google Sheets API and obtain the Client Id, Client Secret, Access Token, and Refresh Token.
Click on the API button in create an integration project pane.
Then, enter the API name as /create and click Create.
Select the newly created create API and Click the edit icon to change the API method. Then select the POST method and click OK.
Add the Google Spreadsheet Connector to the API by clicking on the + button in the Design View and search for Google Spreadsheet in the Mediator section. Then, select the Google Spreadsheet connector and click Download.

Create Connection¶

Go to the Design View and click on the + button next to the Connections in the created integration project and select Google Spreadsheet connector.
Enter the connection name as GoogleSheet and provide the following details in the Google Spreadsheet Connection configuration pane.
- Client ID: Value of the Client Id you obtained when you registered your application with the Google Sheets API.
- Client Secret: Value of the Client Secret you obtained when you registered your application with the Google Sheets API.
- Refresh Token: Value of the Refresh Token, which generates a new Access Token when the previous one gets expired.
Note: You can obtain these values by following the steps in the Configure Google Sheets API section.

Adding Google Spreadsheet Connection

Implement the API¶

First, Let's create a sample payload request to send API. Click on the Start node and select the Add Request option. This will create a new example payload request.
Click on the + button in the Design View and select the Google Spreadsheet connector.
Select the Create New Spreadsheet operation and click OK.
Now as Connection field, select the GoogleSheet connection you created earlier.
As the spreadsheet title, select the Spreadsheet Title field from the request payload. then click on the fx button and select payload.
Then, select the title field from the response payload and click OK.
Now we need to add a Payload mediator after the create operation so that we can return the response from the create operation. To add the Payload mediator and select the Payload mediator from the mediator pane.
Then, create the payload as below and click Add.
```
  {
    {"id":"${vars.googlespreadsheet_createNewSheet_1.payload.spreadsheetId}","link":"${vars.googlespreadsheet_createNewSheet_1.payload.spreadsheetUrl}"}
  }
```
To select the spreadsheetId and spreadsheetUrl fields from the response payload, click on the fx button and select payload. Then select the spreadsheetId and spreadsheetUrl fields from the response payload and click OK.
Finally, add a Response mediator to the API. To do this, click on the + button in the Design View and select the Response mediator. After that, your integration logic should look like below.

Create the Integration Logic for the Write and Read Operation¶

Follow the same steps as above to create a new API with the name /write and /read respectively.
Go to the Design View and click on the button top right corner to switch to the Code View.
Copy the code below and paste it in the Code View in the /write and /read APIs.

Source view of the /write API

<?xml version="1.0" encoding="UTF-8"?>
<api context="/write" name="write" xmlns="http://ws.apache.org/ns/synapse">
    <resource methods="POST" uri-template="/">
        <inSequence>
            <googlespreadsheet.editCellData configKey="GoogleSheet">
                <configLevel>BASIC</configLevel>
                <spreadsheetId>{${payload.sheetId}}</spreadsheetId>
                <sheetName>Sheet1</sheetName>
                <cellId>A1</cellId>
                <value>{${payload.data}}</value>
                <responseVariable>googlespreadsheet_editCellData_7</responseVariable>
                <overwriteBody>false</overwriteBody>
            </googlespreadsheet.editCellData>
            <payloadFactory media-type="json" template-type="default">
                <format>{"results":${vars.googlespreadsheet_editCellData_7.payload}}</format>
            </payloadFactory>
            <respond/>
        </inSequence>
        <faultSequence>
        </faultSequence>
    </resource>
</api>

Design view of the /write API should look like below. Integration Logic

Source view of the /read API

<?xml version="1.0" encoding="UTF-8"?>
<api context="/read" name="read" xmlns="http://ws.apache.org/ns/synapse">
    <resource methods="POST" uri-template="/">
        <inSequence>
            <googlespreadsheet.getCellData configKey="GoogleSheet">
                <configLevel >BASIC</configLevel>
                <spreadsheetId >{${payload.sheetId}}</spreadsheetId>
                <sheetName >Sheet1</sheetName>
                <cellId >A1</cellId>
                <responseVariable >googlespreadsheet_getCellData_1</responseVariable>
                <overwriteBody >false</overwriteBody>
            </googlespreadsheet.getCellData>
            <payloadFactory media-type="json" template-type="default">
                <format>{"value":${vars.googlespreadsheet_getCellData_1.payload.values}}</format>
            </payloadFactory>
            <respond/>
        </inSequence>
        <faultSequence>
        </faultSequence>
    </resource>
</api>

Design view of the /read API should look like below. Integration Logic

Exporting Integration Logic as a CApp¶

In order to export the project, refer to the build and export the carbon application guide.

Get the project¶

You can download the ZIP file and extract the contents to get the project code.

Tip

You may need to update the value of the access token and make other such changes before deploying and running this project.

Deployment¶

In order to deploy and run the project, refer the build and run guide.

You can further refer the application deployed through the CLI tool. See the instructions on managing integrations from the CLI.

Testing¶

Invoke the SpreadsheetAPI with the following URL. You can use inbuilt HTTP client in WSO2 MI or an application such as Postman to invoke the API.

Create sheet Operation¶

curl -X 'POST' \
  'http://localhost:8290/create/' \
  -H 'accept: */*' \
  -H 'Content-Type: application/json' \
  -d '{
  "title":"Sample Sheet"
}'

You should get a success response as below with the spreadsheet ID and URL:

{
  "id": "1Vk3CJ0ZE0hhw06vZvNqFdd3hw7idqNkgqzo4rzsAI_4",
  "link": "https://docs.google.com/spreadsheets/d/1Vk3CJ0ZE0ddw06vZvNqF7F3hw7idqNkgqzo4rzsAI_4/edit"
}

Write to cell Operation¶

curl -X 'POST' \
  'http://localhost:8290/write/' \
  -H 'accept: */*' \
  -H 'Content-Type: application/json' \
  -d '{
  "sheetId": "1fOrE1y96Qh-EVV_Uln68-fDhrKDzeVglsYWAXjMN23Y",
  "data":"Sample Record Value"
}'

You should get a success response as below with the updated cell value:

{
  "results": {
    "spreadsheetId": "1fOrE1y96Qh-EVV_Uln68-fDhrKDzeVgWSO2WAXjMN23Y",
    "updatedRange": "Sheet1!A1",
    "updatedRows": 1,
    "updatedColumns": 1,
    "updatedCells": 1
  }
}

Read to cell Operation¶

curl -X 'POST' \
  'http://localhost:8290/read/' \
  -H 'accept: */*' \
  -H 'Content-Type: application/json' \
  -d '{
  "sheetId": "1fOrE1y96Qh-EVV_Uln68-WSOrKDzeVglsYWAXjMN23Y"
}'

You should get the following response returned.

{
  "value": [
    [
      "Sample Record Value"
    ]
  ]
}

What's Next¶

To customize this example for your own scenario, see Google Spreadsheet Connector Configuration documentation for all operation details of the connector.