Pygsheets authorize

Obtain OAuth2 credentials from Google Developers Console for google spreadsheet api and drive api and save the file as

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

Sample scenario : you want to share a numpy array with your remote friend

import pygsheets
import numpy as np

gc = pygsheets.authorize()

# Open spreadsheet and then worksheet
sh = gc.open('my new sheet')
wks = sh.sheet1

# Update a cell with value (just to let him know values is updated ;) )
wks.update_value('A1', "Hey yank this numpy array")
my_nparray = np.random.randint(10, size=(3, 4))

# update the sheet with array
wks.update_values('A2', my_nparray.tolist())

# share the sheet with your friend
sh.share("[email protected]")

Sample Scenario: you want to fill height values of students

## import pygsheets and open the sheet as given above

header = wks.cell('A1')
header.value = 'Names'
header.text_format['bold'] = True # make the header bold
header.update()

# or achive the same in oneliner
wks.cell('B1').set_text_format('bold', True).value = 'heights'

# set the names
wks.update_values('A2:A5',[['name1'],['name2'],['name3'],['name4']])

# set the heights
heights = wks.range('B2:B5', returnas='range')  # get the range as DataRange object
heights.name = "heights"  # name the range
heights.update_values([[50],[60],[67],[66]]) # update the values
wks.update_value('B6','=average(heights)') # set the avg value of heights using named range

More Examples

Opening a Spreadsheet

# You can open a spreadsheet by its title as it appears in Google Docs 
sh = gc.open("pygsheetTest")

# If you want to be specific, use a key
sht1 = gc.open_by_key('1mwA-NmvjDqd3A65c8hsxOpqdfdggPR0fgfg5nXRKScZAuM')

# create a spreadsheet in a folder (by id)
sht2 = gc.create("new sheet", folder_name="my worksheets")

# open enable TeamDrive support
gc.drive.enable_team_drive("Dqd3A65c8hsxOpqdfdggPR0fgfg")

Operations on Spreadsheet doc

import pygsheets
c = pygsheets.authorize()
sh = c.open('spreadsheet')

# create a new sheet with 50 rows and 60 colums
wks = sh.add_worksheet("new sheet",rows=50,cols=60)

# create a new sheet with 50 rows and 60 colums at the begin of worksheets
wks = sh.add_worksheet("new sheet",rows=50,cols=60,index=0)

# or copy from another worksheet
wks = sh.add_worksheet("new sheet", src_worksheet='')

# delete this wroksheet
sh.del_worksheet(wks)

# unshare the sheet
sh.remove_permissions("[email protected]")

Selecting a Worksheet

import pygsheets
c = pygsheets.authorize()
sh = c.open('spreadsheet')

# Select worksheet by id, index, title.
wks = sh.worksheet_by_title("my test sheet")

# By any property
wks = sh.worksheet('index', 0)

# Get a list of all worksheets
wks_list = sh.worksheets()

# Or just
wks = sh[0]

Operations on Worksheet doc

# Get values as 2d array('matrix') which can easily be converted to an numpy aray or as 'cell' list
values_mat = wks.get_values(start=(1,1), end=(20,20), returnas='matrix')

# Get values of - rows A1 to B10, column C, 1st row, 10th row
wks.get_values_batch(['A1:B10', 'C', '1', (10, None)])

# Get all values of sheet as 2d list of cells
cell_matrix = wks.get_all_values(returnas='matrix')

# update a range of values with a cell list or matrix
wks.update_values(crange='A1:E10', values=values_mat)

# update multiple ranges with bath update
wks.update_values_batch(['A1:A2', 'B1:B2'], [[[1],[2]], [[3],[4]]])

# Insert 2 rows after 20th row and fill with values
wks.insert_rows(row=20, number=2, values=values_list)

# resize by changing rows and colums
wks.rows=30

# use the worksheet as a csv
for row in wks:
    print(row)

# get values by indexes
 A1_value = wks[0][0]

# clear all values
wks.clear()

# Search for a table in the worksheet and append a row to it
wks.append_table(values=[1,2,3,4])

# export a worksheet as csv
wks.export(pygsheets.ExportType.CSV)

# Find/Replace cells with string value
cell_list = worksheet.find("query string")

# Find/Replace cells with regexp
filter_re = re.compile(r'(small|big) house')
cell_list = worksheet.find(filter_re, searchByRegex=True)
cell_list = worksheet.replace(filter_re, 'some house', searchByRegex=True)

# Move a worksheet in the same spreadsheet (update index)
wks.index = 2 # index start at 1 , not 0

# Update title
wks.title = "NewTitle"

# Update hidden state
wks.hidden = False

# working with named ranges
wks.create_named_range('A1', 'A10', 'prices')
wks.get_named_range('prices')
wks.get_named_ranges()  # will return a list of DataRange objects
wks.delete_named_range('prices')

# apply format
wks.apply_format(['A1:B1', 'D:E'], ["NUMBER", "TEXT"])

# Plot a chart/graph
wks.add_chart(('A1', 'A6'), [('B1', 'B6')], 'Health Trend')

# create drop-downs
wks.set_data_validation(start='C4', end='E7', condition_type='NUMBER_BETWEEN', condition_values=[2,10], strict=True, inputMessage="inut between 2 and 10")

# set a formula and extend it to more cells
cell = wks.cell('C1')
cell.value = '=A1+B1'
wks.apply_format('C1:C10', cell, 'userEnteredValue.formulaValue')

Pandas integration

If you work with pandas, you can directly use the dataframes

#set the values of a pandas dataframe to sheet
wks.set_dataframe(df,(1,1))

#you can also get the values of sheet as dataframe
df = wks.get_as_df()

Cell Object doc

Each cell has a value and cordinates (row, col, label) properties.

Getting cell objects

c1 = Cell('A1',"hello")  # create a unlinked cell
c1 = worksheet.cell('A1')  # creates a linked cell whose changes syncs instantanously
cl.value  # Getting cell value
c1.value_unformatted #Getting cell unformatted value
c1.formula # Getting cell formula if any
c1.note # any notes on the cell
c1.address # address object with cell position

cell_list = worksheet.range('A1:C7')  # get a range of cells 
cell_list = worksheet.col(5, returnas='cell')  # return all cells in 5th column(E)

Most of the functions has

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

Cell Operations

Each cell is directly linked with its cell in spreadsheet, hence changing the value of cell object will update the corresponding cell in spreadsheet unless you explictly unlink it Also not that bu default only the value of cell is fetched, so if you are directly accessing any cell properties call

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

Different ways of updating Cells

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

DataRange Object doc

The DataRange is used to represent a range of cells in a worksheet. They can be named or protected. Almost all

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

Batching calls

If you are calling a lot of spreadsheet modification functions (non value update). you can merge them into a single call. By doing so all the requests will be merged into a single call.

How do I authorize credentials in Pygsheets?

How to use Pygsheets Python?