ballerinax/persist.googlesheets Ballerina library

Module Overview

This module provides Google sheets support for the bal persist feature, which provides functionality to store and query data from a Google sheets conveniently through a data model.

The Google Sheets data store is a cloud-based spreadsheet application that stores data in tables. The Google Sheets data store is useful for storing data in a spreadsheet format. The Google Sheets data store is not the default data store for the bal persist feature. Therefore, you need to explicitly specify the data store when initializing Ballerina Persistence in your application. like below,

$ bal persist init --datastore googlesheets

Supported Ballerina Types

The following table lists the Ballerina types supported by the Google Sheets data store and the corresponding Google Sheets types used to store the data in the spreadsheet.

Configuration

You need to set values for the following basic configuration parameters in the Config.toml file in your project to use the Google Sheets data store.

The following is a sample Config.toml file with the Google Sheets data store configuration. This is generated by the bal persist generate command.

[<packageName>.<moduleName>]
spreadsheetId = ""
clientId = ""
clientSecret = ""
refreshToken = ""

Please refer to the Google API documentation for more information on how to obtain the client ID, client secret, and refresh token.

How to Set up

How to run script.gs in the worksheet

The script.gs file generated from the bal persist generate command can initiate the google sheets client. This file can be executed in the Google Apps Script console using the following steps.

• Go to the respective spreadsheet.
• Open the AppScript console from the menu item Extensions - > Apps Script
• Copy the content of the script.gs file into the console.
• Click the Deploy button to Deploy the project as a Web Application.
• Click on the Run button to execute the selected function.

How to obtain Google API tokens

API tokens for the Google sheets can be obtained using the following procedure.

1. Get the clientID, and client secret using the code following guidelines.
2. If you want to use OAuth 2.0 playground to receive the authorization code and obtain the access token and refresh token, Follow below steps.
   1. Go to the OAuth 2.0 playground.
   2. Click the settings icon on the top right corner of the page.
   3. Select the checkbox Use your own OAuth credentials.
   4. Enter the OAuth Client ID and OAuth Client secret.
   5. Click Close.
   6. Select the required Google Sheets API scopes.
   7. Click Authorize APIs.
   8. Click Exchange authorization code for tokens to obtain the access token and refresh token.