pygsheets - Google Spreadsheets Python API v4
A simple, intutive library for google sheets which gets most of your work done.
- Open, create, delete and share spreadsheets using title or key
- Intutive models - spreadsheet, worksheet, cell, datarange
- Control permissions of spreadsheets.
- Set cell format, text format, color, write notes
- Named and Protected Ranges Support
- Work with range of cells easily with DataRange
- TeamDrive Support
- version 1.1.4 released
- uncompalitable function name changes in 1.2.0 (currently in staging)
- update_cell -> update_value
- update_cells -> update_values
- update_cells_prop -> update_cells
- teamDriveId, enableTeamDriveSupport -> client.drive.enable_team_drive, include_team_drive_items
pip install pygsheets
From GitHub (Recommended)
pip install https://github.com/nithinmurali/pygsheets/archive/master.zip
Basic features are shown here, for complete set of features see the full documentation here.
Obtain OAuth2 credentials from Google Developers Console for google spreadsheet api and drive api and save the file as
client_secret.jsonin same directory as project. read more here.
Start using pygsheets:
Sample scenario : you want to share a numpy array with your remote friend
import pygsheets gc = pygsheets.authorize() # Open spreadsheet and then workseet sh = gc.open('my new ssheet') wks = sh.sheet1 # Update a cell with value (just to let him know values is updated ;) ) wks.update_cell('A1', "Hey yank this numpy array") # update the sheet with array wks.update_cells('A2', my_nparray.to_list()) # share the sheet with your friend sh.share("myFriend@gmail.com")
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_cells('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([,,,]) # update the vales wks.update_cell('B6','=average(heights)') # set the avg value of heights using named range
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 spreasheet in a folder (by id) sht2 = gc.create("new sheet", parent_id="adF345vfvcvby67ddfc") # open enable TeamDrive support gc.enableTeamDriveSupport=True gc.teamDriveId = "Dqd3A65c8hsxOpqdfdggPR0fgfg"
# 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=another_wks) # delete this wroksheet del_worksheet(wks) # unshare the sheet sh.remove_permissions("myNotSoFriend@gmail.com")
Selecting a Worksheet
# 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
# 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 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_cells(crange='A1:E10', values=values_mat) # 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 # 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) # 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')
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()
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 cell_list = worksheet.range('A1:C7') # get a range of cells cell_list = col(5, returnas='cell') # return all cells in 5th column(E)
Most of the functions has
returnas param, if whose value is
cell it will return a list of cell objects. Also you can use label or (row,col) tuple interchangbly as a cell adress
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
Different ways of updating Cells
# using linked cells c1 = worksheet.cell('B1') # created from worksheet, so linked cell c1.col = 5 # Now c1 correponds to E1 c1.value = "hoho" # will change the value of E1 # Or onliner worksheet.update_cell('B1', 'hehe') # get a range of cells cell_list = worksheet.range('A1:C7') cell_list = worksheet.get_values(start='A1', end='C7', returnas='cells') cell_list = worksheet.get_row(2, returnas='cells') # add formula c1.formula = 'A1+C2' # get neighbouring cells c2 = c1.neighbour('topright') # you can also specify relative position as tuple eg (1,1) # set cell format c1.format = pygsheets.FormatType.NUMBER, '00.0000' # 2nd string is optional # write notes on cell c1.note = "yo mom" # set cell color c1.color = (1.0,1.0,1.0,1.0) # Red, Green, Blue, Alpha # set text format c1.text_format['fontSize'] = 14 c1.text_format['bold'] = True # sync the changes c1.update() # you can unlink a cell and set all required properties and then link it # So yu could create a model cell and update multiple sheets c.unlink() c.note = "offine note" c.link(wks1, True) c.link(wks2, True)
The DataRange is used to represent a range of cells in a worksheet. They can be named or protected.
get_ functions has a
returnas param, set it to
range to get a range object.
# Getting a Range object rng = wks.get_values('A1', 'C5', returnas='range') rng.unlink() # linked ranges will sync the changes as they are changed # Named ranges rng.name = 'pricesRange' # will make this range a named range rng = wks.get_named_ranges('commodityCount') # directly get a named range rng.name = '' # will delete this named range # Setting Format # first create a model cell with required properties model_cell = Cell('A1') model_cell.color = (1.0,0,1.0,1.0) # rose color cell model_cell.format = pygsheets.FormatType.PERCENT # now set its format to all cells in the range rng.apply_format(model_cell) # will make all cell in this range rose color and percent format # get cells in range cell = rng
How to Contribute
This library is still in development phase. So there is a lot of work to be done. Checkout the TO DO's.
- Follow the Contributing to Open Source Guide.
- Branch off of the
stagingbranch, and submit Pull Requests back to that branch. Note that the
masterbranch is used for version bumps and hotfixes only.
- Please report bugs and suggest features via the GitHub Issues.
- Before opening an issue, search the tracker for possible duplicates.
- If you have any usage questions, ask a question on stackoverflow with
- Save oauth credential file as
The gspread library is used as an outline for developing pygsheets.