ballerinax/googleapis.sheets Ballerina library

Overview

The Google Sheets, developed by Google LLC, allows users to programmatically interact with Google Sheets, facilitating tasks such as data manipulation, analysis, and automation.

The Google Sheets connector offers APIs to connect and interact with Sheets API endpoints, specifically based on Google Sheets API v4.

Key Features

• Programmatically interact with Google Sheets
• Support for data manipulation and analysis
• Automate tasks related to spreadsheets
• Compatible with Google Sheets API v4

Setup guide

To use the Google Sheets connector, you must have access to the Google Sheets API through a Google Cloud Platform (GCP) account and a project under it. If you do not have a GCP account, you can sign up for one here.

Step 1: Create a Google Cloud Platform project

1. Open the Google Cloud Platform Console.

2. Click on Select a project in the drop-down menu and either select an existing project or create a new one.

   

Step 2: Enabling Google Sheets API

1. Select the created project.

2. Navigate to APIs & Services > Library.

3. Search and select Google Sheets API. Then click ENABLE.

   

Note: If you intend to use the deleteSpreadsheet operation, you must also enable the Google Drive API in the same project. Search for Google Drive API in the library and click ENABLE.

Step 3: Creating an OAuth consent app

1. Click on the OAuth Consent Screen in the sidebar.

2. Select External and click CREATE.

3. Fill in the app information and add the necessary scopes for Google Sheets API.

   

Step 4: Generating client ID & client secret

1. In the left sidebar, click on Credentials.

2. Click on + CREATE CREDENTIALS and choose OAuth Client ID.

   

3. You will be directed to the OAuth consent screen, in which you need to fill in the necessary information below.

   Field	Value
   Application type	Web Application
   Name	Sheets Client
   Authorized Redirect URIs	https://developers.google.com/oauthplayground

   

Step 5: Obtain the access and refresh tokens

Follow these steps to generate the access and refresh tokens.

Note: It is recommended to use the OAuth 2.0 playground to acquire the tokens.

1. Configure the OAuth playground with the OAuth client ID and client secret.

   

2. Authorize the Google Sheets APIs. If you intend to use the deleteSpreadsheet operation, also add the https://www.googleapis.com/auth/drive.file scope.

   

3. Exchange the authorization code for tokens.

   

Quickstart

To use the Google Sheets connector in your Ballerina project, modify the .bal file as follows:

Step 1: Import connector

Import the ballerinax/googleapis.sheets module.

import ballerinax/googleapis.sheets;

Step 2: Create a new connector instance

Create a sheets:ConnectionConfig with the obtained OAuth2.0 tokens and initialize the connector with it.

configurable string clientId = ?;
configurable string clientSecret = ?;
configurable string refreshToken = ?;
configurable string refreshUrl = ?;

sheets:Client spreadsheetClient = check new ({
    auth: {
        clientId,
        clientSecret,
        refreshToken,
        refreshUrl
    }
});

Step 3: Invoke connector operation

Now, utilize the available connector operations.

Create a spreadsheet with a given name

public function main() returns error? {

    // create a spreadsheet
    sheets:Spreadsheet response = check spreadsheetClient->createSpreadsheet("NewSpreadsheet");

    // Add a new worksheet with given name to the Spreadsheet
    string spreadsheetId = response.spreadsheetId;
    sheets:Sheet sheet = check spreadsheetClient->addSheet(spreadsheetId, "NewWorksheet");
}

Step 4: Run the Ballerina application

bal run

Examples

The Google Sheets connector provides practical examples illustrating usage in various scenarios. Explore these examples, covering use cases like creating, reading, and appending rows.

1. Cell operations - Operations associated with a cell, such as clearing, setting, and deleting cell values.

2. Grid filtering - Demonstrate filtering sheet values using a grid range.

3. Sheet modifying - Basic operations associated with sheets such as creating, reading, and appending rows.

Report issues

To report bugs, request new features, start new discussions, view project boards, etc., go to the Ballerina library parent repository.

Useful links

• Chat live with us via our Discord server.
• Post all technical questions on Stack Overflow with the #ballerina tag.