Google - Sheets¶

Interact and perform various operations through a Google account. Read, write and update spreadsheet files easily via the BotCity plugin for Google Sheets.

Installation¶

PythonJava

pip install botcity-googlesheets-plugin

Importing the Plugin¶

After you installed this package and get the Google credentials file, the next step is to import the package into your code and start using the functions.

PythonJava

from botcity.plugins.googlesheets import BotGoogleSheetsPlugin

Instantiating the Plugin¶

To make the example we will instantiate the plugin using the credentials file path and the id of the spreadsheet we want to use.

Python

# Instantiate the plugin
bot_sheets = BotGoogleSheetsPlugin(CLIENT_SECRET_PATH, 'SPREADSHEET_ID')

Tip

You can find the spreadsheet ID in your URL. For example, if the spreadsheet URL is https://docs.google.com/spreadsheets/d/1a2b3c4d5e6f7g8h9i0j1k2l3m4n5o6p7q8r9s0t1u2v3w4x5y6z7/edit, the spreadsheet ID is 1a2b3c4d5e6f7g8h9i0j1k2l3m4n5o6p7q8r9s0t1u2v3w4x5y6z7.

Java

Manipulating spreadsheet data¶

Now, let's manipulate some data from our file, adding new data and then returning the read content.

PythonJava

# Adds some data
bot_sheets.add_row(['Name', 'Age'])
bot_sheets.add_rows([['Peter', '53'], ['Paulo', '35']])

# Sorts the columns by ascending age
bot_sheets.sort('B')

# Prints the resulting list
print(bot_sheets.as_list())

Complete code¶

Let's take a look into the complete code:

PythonJava

from botcity.plugins.googlesheets import BotGoogleSheetsPlugin

# Instantiate the plugin
bot_sheets = BotGoogleSheetsPlugin(CLIENT_SECRET_PATH, 'SPREADSHEET_ID')

# Adds some data
bot_sheets.add_row(['Name', 'Age'])
bot_sheets.add_rows(['Peter', '53'], ['Paulo', '35'])

# Sorts the columns by ascending age
bot_sheets.sort('B')

# Prints the resulting list
print(bot_sheets.as_list())

Tip

This plugin allow you to use method chaining so the code above could be written as:

PythonJava

data = [['Name', 'Age'], ['Peter', '53'], ['Paulo', '35']]
sorted_data = BotGoogleSheetsPlugin(CLIENT_SECRET_PATH, 'SPREADSHEET_ID')
        .add_rows(rows)
        .sort('B')
        .as_list()

print(sorted_data)