Module

googleapis.sheets

Module googleapis.sheets

Client

createRandomUUIDWithoutHyphens

Cell
Column
ConnectionConfig
File
FilesResponse
GridProperties
Range
Row
Sheet
SheetProperties
Spreadsheet
SpreadsheetProperties

ValueInputOption
ValueRenderOption

REFRESH_URL

ballerinax/googleapis.sheets

3.2.0

Overview

The Ballerina connector for Google Sheets makes it convenient to implement some of the most common use cases of Google Sheets. With this connector, you can programmatically manage spreadsheets, manage worksheets, perform CRUD operations on worksheets, and perform column-level, row-level, and cell-level operations.

This module supports Google Sheets API v4.

Prerequisites

Before using this connector in your Ballerina application, complete the following:

Quickstart

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

Step 1: Import connector

Import the ballerinax/googleapis.sheets module into the Ballerina project.

Copy
import ballerinax/googleapis.sheets as sheets;

Step 2: Create a new connector instance

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

Copy
sheets:ConnectionConfig spreadsheetConfig = {
    auth: {
        clientId: <CLIENT_ID>,
        clientSecret: <CLIENT_SECRET>,
        refreshUrl: sheets:REFRESH_URL,
        refreshToken: <REFRESH_TOKEN>
    }
};

sheets:Client spreadsheetClient = check new (spreadsheetConfig);

Step 3: Invoke connector operation

  1. Now you can use the operations available within the connector. Note that they are in the form of remote operations.

    Following is an example on how to create a spreadsheet using the connector.

    Create Spreadsheet with given name

    Copy
    public function main() returns error? {
    sheets:Spreadsheet response = check spreadsheetClient->createSpreadsheet("NewSpreadsheet");
    log:printInfo("Successfully created spreadsheet!");
}

  2. Use bal run command to compile and run the Ballerina program.

You can find a list of samples here

Clients

googleapis.sheets: Client

Isolated

Ballerina Google Sheets connector provides the capability to access Google Sheets API. The connector let you perform spreadsheet management operations, worksheet management operations and the capability to handle Google Sheets data level operations.

Constructor

Initializes the connector. During initialization you can pass either http:BearerTokenConfig if you have a bearer token or http:OAuth2RefreshTokenGrantConfig if you have Oauth tokens. Create a Google account and obtain tokens following this guide. Configure the OAuth2 tokens to have the required permissions.

init (ConnectionConfig config)

createSpreadsheet

Isolated FunctionRemote Function
function createSpreadsheet(string name) returns Spreadsheet|error

Creates a new spreadsheet.

Parameters
  • name string - Name of the spreadsheet
Return Type

openSpreadsheetById

Isolated FunctionRemote Function
function openSpreadsheetById(string spreadsheetId) returns Spreadsheet|error

Opens a spreadsheet by the given ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
Return Type

openSpreadsheetByUrl

Isolated FunctionRemote Function
function openSpreadsheetByUrl(string url) returns Spreadsheet|error

Opens a spreadsheet by the given Url.

Parameters
  • url string - Url of the spreadsheet
Return Type

getAllSpreadsheets

Isolated FunctionRemote Function
function getAllSpreadsheets() returns stream<File, error?>|error

Get all spreadsheet files.

Return Type

renameSpreadsheet

Isolated FunctionRemote Function
function renameSpreadsheet(string spreadsheetId, string name) returns error?

Renames the spreadsheet with the given name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • name string - New name for the spreadsheet
Return Type
  • error? - Nil() on success, or else an error

getSheets

Isolated FunctionRemote Function
function getSheets(string spreadsheetId) returns Sheet[]|error

Get worksheets of the spreadsheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
Return Type
  • Sheet[]|error - Array of sheets:Sheet records on success, or else an error

getSheetByName

Isolated FunctionRemote Function
function getSheetByName(string spreadsheetId, string sheetName) returns Sheet|error

Get a worksheet of the spreadsheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - Name of the worksheet to retrieve
Return Type
  • Sheet|error - sheets:Sheet record on success, or else an error

addSheet

Isolated FunctionRemote Function
function addSheet(string spreadsheetId, string sheetName) returns Sheet|error

Add a new worksheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
Return Type
  • Sheet|error - sheets:Sheet record on success, or else an error

removeSheet

Isolated FunctionRemote Function
function removeSheet(string spreadsheetId, int sheetId) returns error?

Delete specified worksheet by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - The ID of the worksheet to delete
Return Type
  • error? - Nil() on success, or else an error

removeSheetByName

Isolated FunctionRemote Function
function removeSheetByName(string spreadsheetId, string sheetName) returns error?

Delete specified worksheet by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet to delete
Return Type
  • error? - Nil() on success, or else an error

renameSheet

Isolated FunctionRemote Function
function renameSheet(string spreadsheetId, string sheetName, string name) returns error?

Renames the worksheet of a given spreadsheet with the given name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The existing name of the worksheet
  • name string - New name for the worksheet
Return Type
  • error? - Nil() on success, or else an error

setRange

Isolated FunctionRemote Function
function setRange(string spreadsheetId, string sheetName, Range range, string? valueInputOption) returns error?

Sets the values of the given range of cells of the worksheet.

Parameters
  • spreadsheetId string - ID of the Spreadsheet
  • sheetName string - The name of the Worksheet
  • range Range - The Range record to be set
  • valueInputOption string? (default ()) - Determines how input data should be interpreted. It's either "RAW" or "USER_ENTERED". Default is "RAW" (Optional). For more information, see ValueInputOption
Return Type
  • error? - Nil() on success, or else an error

getRange

Isolated FunctionRemote Function
function getRange(string spreadsheetId, string sheetName, string a1Notation, string? valueRenderOption) returns Range|error

Gets the given range of the worksheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • a1Notation string - The required range in A1 notation
  • valueRenderOption string? (default ()) - Determines how values should be rendered in the output. It's either "FORMATTED_VALUE","UNFORMATTED_VALUE" or "FORMULA". Default is "FORMATTED_VALUE" (Optional). For more information, see ValueRenderOption
Return Type
  • Range|error - sheets:Range record on success, or else an error

clearRange

Isolated FunctionRemote Function
function clearRange(string spreadsheetId, string sheetName, string a1Notation) returns error?

Clears the range of contents, formats, and data validation rules.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • a1Notation string - The required range in A1 notation
Return Type
  • error? - Nil() on success, or else an error

addColumnsBefore

Isolated FunctionRemote Function
function addColumnsBefore(string spreadsheetId, int sheetId, int index, int numberOfColumns) returns error?

Inserts the given number of columns before the given column position by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
  • index int - The position of the column before which the new columns should be added
  • numberOfColumns int - Number of columns to be added
Return Type
  • error? - Nil() on success, or else an error

addColumnsBeforeBySheetName

Isolated FunctionRemote Function
function addColumnsBeforeBySheetName(string spreadsheetId, string sheetName, int index, int numberOfColumns) returns error?

Inserts the given number of columns before the given column position by worksheet name.

Parameters
  • spreadsheetId string - ID of the Spreadsheet
  • sheetName string - The name of the Worksheet
  • index int - The position of the column before which the new columns should be added
  • numberOfColumns int - Number of columns to be added
Return Type
  • error? - Nil() on success, or else an error

addColumnsAfter

Isolated FunctionRemote Function
function addColumnsAfter(string spreadsheetId, int sheetId, int index, int numberOfColumns) returns error?

Inserts the given number of columns after the given column position by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
  • index int - The position of the column after which the new columns should be added
  • numberOfColumns int - Number of columns to be added
Return Type
  • error? - Nil() on success, or else an error

addColumnsAfterBySheetName

Isolated FunctionRemote Function
function addColumnsAfterBySheetName(string spreadsheetId, string sheetName, int index, int numberOfColumns) returns error?

Inserts the given number of columns after the given column position by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • index int - The position of the column after which the new columns should be added
  • numberOfColumns int - Number of columns to be added
Return Type
  • error? - Nil() on success, or else an error

createOrUpdateColumn

Isolated FunctionRemote Function
function createOrUpdateColumn(string spreadsheetId, string sheetName, string column, (int|string|decimal)[] values, string? valueInputOption) returns error?

Create or Update a Column.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • column string - Position of column (string notation) to set the data
  • valueInputOption string? (default ()) - Determines how input data should be interpreted. It's either "RAW" or "USER_ENTERED". Default is "RAW" (Optional). For more information, see ValueInputOption
Return Type
  • error? - Nil() on success, or else an error

getColumn

Isolated FunctionRemote Function
function getColumn(string spreadsheetId, string sheetName, string column, string? valueRenderOption) returns Column|error

Gets the values in the given column of the worksheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • column string - Position of Column (string notation) to retrieve the data
  • valueRenderOption string? (default ()) - Determines how values should be rendered in the output. It's either "FORMATTED_VALUE","UNFORMATTED_VALUE" or "FORMULA". Default is "FORMATTED_VALUE" (Optional). For more information, see ValueRenderOption
Return Type
  • Column|error - sheets:Column record on success, or else an error

deleteColumns

Isolated FunctionRemote Function
function deleteColumns(string spreadsheetId, int sheetId, int column, int numberOfColumns) returns error?

Deletes the given number of columns starting at the given column position by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
  • column int - Starting position of the columns
  • numberOfColumns int - Number of columns from the starting position
Return Type
  • error? - Nil() on success, or else an error

deleteColumnsBySheetName

Isolated FunctionRemote Function
function deleteColumnsBySheetName(string spreadsheetId, string sheetName, int column, int numberOfColumns) returns error?

Deletes the given number of columns starting at the given column position by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • column int - Starting position of the columns
  • numberOfColumns int - Number of columns from the starting position
Return Type
  • error? - Nil() on success, or else an error

addRowsBefore

Isolated FunctionRemote Function
function addRowsBefore(string spreadsheetId, int sheetId, int index, int numberOfRows) returns error?

Inserts the given number of rows before the given row position by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
  • index int - The position of the row before which the new rows should be added
  • numberOfRows int - The number of rows to be added
Return Type
  • error? - Nil() on success, or else an error

addRowsBeforeBySheetName

Isolated FunctionRemote Function
function addRowsBeforeBySheetName(string spreadsheetId, string sheetName, int index, int numberOfRows) returns error?

Inserts the given number of rows before the given row position by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • index int - The position of the row before which the new rows should be added
  • numberOfRows int - The number of rows to be added
Return Type
  • error? - Nil() on success, or else an error

addRowsAfter

Isolated FunctionRemote Function
function addRowsAfter(string spreadsheetId, int sheetId, int index, int numberOfRows) returns error?

Inserts a number of rows after the given row position by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
  • index int - The row after which the new rows should be added.
  • numberOfRows int - The number of rows to be added
Return Type
  • error? - Nil() on success, or else an error

addRowsAfterBySheetName

Isolated FunctionRemote Function
function addRowsAfterBySheetName(string spreadsheetId, string sheetName, int index, int numberOfRows) returns error?

Inserts a number of rows after the given row position by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • index int - The row after which the new rows should be added.
  • numberOfRows int - The number of rows to be added
Return Type
  • error? - Nil() on success, or else an error

createOrUpdateRow

Isolated FunctionRemote Function
function createOrUpdateRow(string spreadsheetId, string sheetName, int row, (int|string|decimal)[] values, string? valueInputOption) returns error?

Create or update a row.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • row int - Position of row (integer notation) to set the data
  • valueInputOption string? (default ()) - Determines how input data should be interpreted. It's either "RAW" or "USER_ENTERED". Default is "RAW" (Optional). For more information, see ValueInputOption
Return Type
  • error? - Nil() on success, or else an error

getRow

Isolated FunctionRemote Function
function getRow(string spreadsheetId, string sheetName, int row, string? valueRenderOption) returns Row|error

Gets the values in the given row of the worksheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • row int - Row number to retrieve the data
  • valueRenderOption string? (default ()) - Determines how values should be rendered in the output. It's either "FORMATTED_VALUE","UNFORMATTED_VALUE" or "FORMULA". Default is "FORMATTED_VALUE" (Optional). For more information, see ValueRenderOption
Return Type
  • Row|error - sheets:Row record on success, or else an error

deleteRows

Isolated FunctionRemote Function
function deleteRows(string spreadsheetId, int sheetId, int row, int numberOfRows) returns error?

Deletes the given number of rows starting at the given row position by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
  • row int - Starting position of the rows
  • numberOfRows int - Number of rows from the starting position
Return Type
  • error? - Nil() on success, or else an error

deleteRowsBySheetName

Isolated FunctionRemote Function
function deleteRowsBySheetName(string spreadsheetId, string sheetName, int row, int numberOfRows) returns error?

Deletes the given number of rows starting at the given row position by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • row int - Starting position of the rows
  • numberOfRows int - Number of rows from the starting position
Return Type
  • error? - Nil() on success, or else an error

setCell

Isolated FunctionRemote Function
function setCell(string spreadsheetId, string sheetName, string a1Notation, int|string|decimal value, string? valueInputOption) returns error?

Sets the value of the given cell of the worksheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • a1Notation string - The required cell in A1 notation
  • valueInputOption string? (default ()) - Determines how input data should be interpreted. It's either "RAW" or "USER_ENTERED". Default is "RAW" (Optional). For more information, see ValueInputOption
Return Type
  • error? - Nil() on success, or else an error

getCell

Isolated FunctionRemote Function
function getCell(string spreadsheetId, string sheetName, string a1Notation, string? valueRenderOption) returns Cell|error

Gets the value of the given cell of the sheet.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • a1Notation string - The required cell in A1 notation
  • valueRenderOption string? (default ()) - Determines how values should be rendered in the output. It's either "FORMATTED_VALUE","UNFORMATTED_VALUE" or "FORMULA". Default is "FORMATTED_VALUE" (Optional). For more information, see ValueRenderOption
Return Type
  • Cell|error - sheets:Cell record on success, or else an error

clearCell

Isolated FunctionRemote Function
function clearCell(string spreadsheetId, string sheetName, string a1Notation) returns error?

Clears the given cell of contents, formats, and data validation rules.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • a1Notation string - The required cell in A1 notation
Return Type
  • error? - Nil() on success, or else an error

appendRowToSheet

Isolated FunctionRemote Function
function appendRowToSheet(string spreadsheetId, string sheetName, (int|string|decimal)[] values, string? a1Notation, string? valueInputOption) returns error?

Adds a new row with the given values to the bottom of the worksheet. The input range is used to search for existing data and find a "table" within that range. Values will be appended to the next row of the table, starting with the first column of the table.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • a1Notation string? (default ()) - The required range in A1 notation (Optional)
  • valueInputOption string? (default ()) - Determines how input data should be interpreted. It's either "RAW" or "USER_ENTERED". Default is "RAW" (Optional). For more information, see ValueInputOption
Return Type
  • error? - Nil() on success, or else an error

copyTo

Isolated FunctionRemote Function
function copyTo(string spreadsheetId, int sheetId, string destinationId) returns error?

Copies the sheet to a given spreadsheet by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
  • destinationId string - ID of the spreadsheet to copy the sheet to
Return Type
  • error? - Nil() on success, or else an error

copyToBySheetName

Isolated FunctionRemote Function
function copyToBySheetName(string spreadsheetId, string sheetName, string destinationId) returns error?

Copies the sheet to a given spreadsheet by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
  • destinationId string - ID of the spreadsheet to copy the sheet to
Return Type
  • error? - Nil() on success, or else an error

clearAll

Isolated FunctionRemote Function
function clearAll(string spreadsheetId, int sheetId) returns error?

Clears the worksheet content and formatting rules by worksheet ID.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetId int - ID of the worksheet
Return Type
  • error? - Nil() on success, or else an error

clearAllBySheetName

Isolated FunctionRemote Function
function clearAllBySheetName(string spreadsheetId, string sheetName) returns error?

Clears the worksheet content and formatting rules by worksheet name.

Parameters
  • spreadsheetId string - ID of the spreadsheet
  • sheetName string - The name of the worksheet
Return Type
  • error? - Nil() on success, or else an error

Functions

createRandomUUIDWithoutHyphens

function createRandomUUIDWithoutHyphens() returns string

Create a random UUID removing the unnecessary hyphens which will interrupt querying opearations.

Return Type
  • string - A string UUID without hyphens

Records

googleapis.sheets: Cell

Single cell in a sheet.

Fields
  • a1Notation string - The column letter followed by the row number. For example for a single cell "A1" refers to the intersection of column "A" with row "1"

googleapis.sheets: Column

Single column in a sheet.

Fields
  • columnPosition string - The column letter

googleapis.sheets: ConnectionConfig

Closed record

Client configuration details.

Fields
  • httpVersion HttpVersion(default http:HTTP_1_1) - The HTTP version understood by the client

googleapis.sheets: File

File information

Fields
  • kind string - Identifies what kind of resource is this. Value: the fixed string "drive#file".
  • id string - The Id of the file
  • name string - The name of the file
  • mimeType string - The MIME type of the file

googleapis.sheets: FilesResponse

Response from File search

Fields
  • kind string - Identifies what kind of resource is this. Value: the fixed string "drive#fileList".
  • nextPageToken string? - The page token for the next page of files. This will be absent if the end of the files list has been reached. If the token is rejected for any reason, it should be discarded, and pagination should be restarted from the first page of results.
  • incompleteSearch boolean - Whether the search process was incomplete. If true, then some search results may be missing, Since all documents were not searched. This may occur when searching multiple drives with the "allDrives" corpora, but all corpora could not be searched. When this happens, it is suggested that clients narrow their query by choosing a different corpus such as "user" or "drive".
  • files File[] - The list of files. If nextPageToken is populated, then this list may be incomplete and an additional page of results should be fetched.

googleapis.sheets: GridProperties

Grid properties.

Fields
  • rowCount int(default 0) - The number of rows in the grid
  • columnCount int(default 0) - The number of columns in the grid
  • frozenRowCount int(default 0) - The number of rows that are frozen in the grid
  • frozenColumnCount int(default 0) - The number of columns that are frozen in the grid
  • hideGridlines boolean(default false) - True if the grid is not showing gridlines in the UI

googleapis.sheets: Range

Single cell or a group of adjacent cells in a sheet.

Fields
  • a1Notation string - The column letter followed by the row number. For example for a single cell "A1" refers to the intersection of column "A" with row "1", and for a range of cells "A1:D5" refers to the top left cell and the bottom right cell of a range

googleapis.sheets: Row

Single row in a sheet.

Fields
  • rowPosition int - The row number

googleapis.sheets: Sheet

Worksheet information.

Fields

googleapis.sheets: SheetProperties

Worksheet properties.

Fields
  • sheetId int(default 0) - The ID of the worksheet
  • title string(default "") - The name of the worksheet
  • index int(default 0) - The index of the worksheet within the spreadsheet
  • sheetType string(default "") - The type of worksheet
  • gridProperties GridProperties(default {}) - Additional properties of the worksheet if this worksheet is a grid
  • hidden boolean(default false) - True if the worksheet is hidden in the UI, false if it is visible
  • rightToLeft boolean(default false) - True if the worksheet is an RTL worksheet instead of an LTR worksheet

googleapis.sheets: Spreadsheet

Spreadsheet information.

Fields
  • spreadsheetId string(default "") - Id of the spreadsheet
  • sheets Sheet[](default []) - The sheets that are part of a spreadsheet
  • spreadsheetUrl string(default "") - The Url of the spreadsheet

googleapis.sheets: SpreadsheetProperties

Spreadsheet properties.

Fields
  • title string(default "") - The title of the spreadsheet
  • locale string(default "") - The locale of the spreadsheet
  • autoRecalc string(default "") - The amount of time to wait before volatile functions are recalculated
  • timeZone string(default "") - The time zone of the spreadsheet

Enums

googleapis.sheets: ValueInputOption

Members

RAW
USER_ENTERED

googleapis.sheets: ValueRenderOption

Members

FORMATTED_VALUE
UNFORMATTED_VALUE
FORMULA

Constants

googleapis.sheets: REFRESH_URL

string "https://www.googleapis.com/oauth2/v3/token"

Other versions

3.4.03.3.0

3.2.0

3.1.03.0.1
See more...

Terms of service

Privacy policy

Cookie policy

© 2018-2023 WSO2 LLC