Retrieve Google Sheets Records Using SQL Query

Author: Soumyadeep D Mahaldar

Introduction

Google Visualization Query is a query language that allows developers to retrieve and manipulate data stored in Google Sheets using SQL-like syntax and provides a simple URL-based interface, making it easier to retrieve data programmatically. This approach has a few advantages over using the Google Sheet API, including:

1. Faster Retrieval: Retrieving data using the Google Visualization Query is faster compared to the Google Sheets API. This is because the Visualization Query uses caching, which reduces the number of requests made to the server.
2. Easier to Use: The Google Visualization Query uses SQL-like syntax, which is familiar to many developers. This makes it easier to write queries and retrieve data in a format that is easy to work with.
3. Condition Based Search: Google Sheet Get API requests for the cell range, which makes it difficult to retrieve values dynamically and based on specific parameters. However, this limitation is overcome by Google Visualization Query, as it uses SQL-like syntax with select and where clauses that enable us for condition-based searching.

Google sheet connector to retrieve spreadsheet values:

As we can see, in order to retrieve values, we are required to input the range of the cells in the general configuration, which becomes inconvenient when we want to retrieve the values dynamically. This blog post will provide a way to retrieve values dynamically.

Pre-requisites: 

Before implementing the Google Visualization Query in MuleSoft, you need to ensure that you have the following prerequisites in place:

– A Google account with Google Cloud console access and a Google Sheet containing the data you want to retrieve.

– Basic knowledge of SQL syntax and MuleSoft.

– An active MuleSoft Anypoint Platform account and Anypoint Studio installed.

Note: In the demonstration confidential fields are masked.

Let’s get started:

We have the following records in our Google Sheet ‘Class Data,’ and we will retrieve student records where students are from the Junior Class Level and are 19 years of age.

Class Data Sheet:

Student Records to retrieve (Row number 7,8 and 20 satisfies our condition):

Step 1: Google Cloud Console Setup

We need to set up a Google Cloud Console account to create a clientId, clientSecret, and refresh token, which will help us in creating an access token for the authorization of the Google Visualization Query URL and retrieve records from Google Sheets.

To set up Google Cloud Console, please follow the steps mentioned in any one of the below articles:

1. https://apisero.com/implementation-of-mulesofts-google-sheet-connector/
2. https://apisero.com/inserting-data-to-google-sheet-using-google-sheets-api-and-mule-in-6-easy-steps/

Please keep the clientId, clientSecret, and refresh token handy for our implementation.

Step 2: Anypoint Point Studio Setup

Create a simple flow with a listener, HTTP request, and a Transform Message component.

In this example, we have created two flows, each with an HTTP request.

• To connect with Google Sheets using Google Visualization Query:

• Google Sheet Request Query Transform Message – A payload with spreadsheetId, sheetId, and query.

SpreadsheetId and sheetId can be fetched from the Google Sheet URL. For example:

Query: Google Visualization query uses column identifiers and not the column names which is why we have to use column numbers that identify the required column names.

Google Sheet API retrieve records (HTTP request connector):

Method: GET

Url: https://docs.google.com/spreadsheets/d/<spreadsheetId>/gviz/tq?tq=<query>&gid=<sheetId>

Header: Authorization: Bearer <accessToken>

• Flow Reference: To fetch access token from <get-access-token-flow>
• Convert Text to JSON: As the response from the visualization query comes in text format, we need to convert it to JSON for better readability.

To retrieve access tokens:

Access tokens are only valid for 1 hour; hence, we would have to create a new access token after every one hour. That’s why we have a flow reference before the Google Visualization API call in the above flow.

• Token Request: We have to send the grant type, refresh token, client_id, and client_secret as payload to the Google Sheet authorization URL to retrieve the access token.

• Retrieve Access Token (HTTP request): 

Method: POST

Url: https://accounts.google.com/o/oauth2/token

Payload: Token request payload from previous step

• Token Response: Extract access token from token response and send it back to <retrieve-records-flow>

Note: The values of sheet ID, spreadsheet ID, refresh_token, client_id, and client_secret are replaced with placeholders in the snippet. Please add your corresponding values.

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

URL: http://localhost:8081/retrieve-records

Important Tip:

Since the access tokens are only valid for 1 hour, we can employ an object store for the access token and use the same token for multiple requests coming in during that 1-hour duration.

Conclusion

Using the Google Visualization Query to retrieve data from Google Sheets can be a powerful tool for developers who need to access data programmatically. While it has some limitations, such as the inability to retrieve data from multiple worksheets in a single query, it provides a simple and efficient way to retrieve and manipulate data using SQL-like syntax. By combining the power of the Google Visualization Query with the flexibility of MuleSoft, developers can create powerful applications that retrieve and analyze data from Google Sheets.

References:

1. https://developers.google.com/chart/interactive/docs/querylanguage
2. https://docs.mulesoft.com/google-sheets-connector/1.1/
3. https://developers.google.com/identity/protocols/oauth2