ballerinax/persist.googlesheets Ballerina library

Overview

This package 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.

How to use with bal persist

By default, bal persist utilizes the in-memory data store. Therefore, you must explicitly specify Google Sheets data store as follows:

Integrate to bal build

1. Initialize bal persist and integrate to bal build using the following command,

   $ bal persist add --datastore googlesheets --module <module_name>
   

2. After defining the entities, build the application using the following command,

   $ bal build
   

One time generation

1. Initialize bal persist using the following command,

   $ bal persist init
   

2. Generate the persist client using the following command,

   $ bal persist generate --datastore googlesheets --module <module_name>
   

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.