1# pygsheets - Google Spreadsheets Python API v4 2[![Build Status](https://travis-ci.org/nithinmurali/pygsheets.svg?branch=staging)](https://travis-ci.org/nithinmurali/pygsheets) [![PyPI version](https://badge.fury.io/py/pygsheets.svg)](https://badge.fury.io/py/pygsheets) [![Documentation Status](https://readthedocs.org/projects/pygsheets/badge/?version=latest)](http://pygsheets.readthedocs.io/en/latest/?badge=latest) 3 4A simple, intuitive library for google sheets which gets your work done. 5 6Features: 7 8* Open, create, delete and share spreadsheets using _title_ or _key_ 9* Intuitive models - spreadsheet, worksheet, cell, datarange 10* Control permissions of spreadsheets. 11* Set cell format, text format, color, write notes 12* Named and Protected Ranges Support 13* Work with range of cells easily with DataRange and Gridrange 14* Data validation support. checkboxes, drop-downs etc. 15* Conditional formatting support 16* get multiple ranges with get_values_batch and update wit update_values_batch 17 18## Updates 19* version [2.0.5](https://github.com/nithinmurali/pygsheets/releases/tag/2.0.5) released 20 21## Installation 22 23#### From PyPi (Stable) 24 25```sh 26pip install pygsheets 27 28``` 29 30If you are installing from pypi please see the docs [here](https://pygsheets.readthedocs.io/en/stable/). 31 32 33#### From GitHub (Recommended) 34 35```sh 36pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip 37 38``` 39 40If you are installing from github please see the docs [here](https://pygsheets.readthedocs.io/en/staging/). 41 42## Basic Usage 43 44Basic features are shown here, for complete set of features see the full documentation [here](http://pygsheets.readthedocs.io/en/staging/). 45 461. Obtain OAuth2 credentials from Google Developers Console for __google spreadsheet api__ and __drive api__ and save the file as `client_secret.json` in same directory as project. [read more here.](https://pygsheets.readthedocs.io/en/latest/authorization.html) 47 482. Start using pygsheets: 49 50Sample scenario : you want to share a numpy array with your remote friend 51 52```python 53import pygsheets 54import numpy as np 55 56gc = pygsheets.authorize() 57 58# Open spreadsheet and then worksheet 59sh = gc.open('my new sheet') 60wks = sh.sheet1 61 62# Update a cell with value (just to let him know values is updated ;) ) 63wks.update_value('A1', "Hey yank this numpy array") 64my_nparray = np.random.randint(10, size=(3, 4)) 65 66# update the sheet with array 67wks.update_values('A2', my_nparray.tolist()) 68 69# share the sheet with your friend 70sh.share("myFriend@gmail.com") 71 72``` 73 74Sample Scenario: you want to fill height values of students 75```python 76 77## import pygsheets and open the sheet as given above 78 79header = wks.cell('A1') 80header.value = 'Names' 81header.text_format['bold'] = True # make the header bold 82header.update() 83 84# or achive the same in oneliner 85wks.cell('B1').set_text_format('bold', True).value = 'heights' 86 87# set the names 88wks.update_values('A2:A5',[['name1'],['name2'],['name3'],['name4']]) 89 90# set the heights 91heights = wks.range('B2:B5', returnas='range') # get the range as DataRange object 92heights.name = "heights" # name the range 93heights.update_values([[50],[60],[67],[66]]) # update the vales 94wks.update_value('B6','=average(heights)') # set the avg value of heights using named range 95 96``` 97 98## More Examples 99 100### Opening a Spreadsheet 101 102```python 103# You can open a spreadsheet by its title as it appears in Google Docs 104sh = gc.open("pygsheetTest") 105 106# If you want to be specific, use a key 107sht1 = gc.open_by_key('1mwA-NmvjDqd3A65c8hsxOpqdfdggPR0fgfg5nXRKScZAuM') 108 109# create a spreadsheet in a folder (by id) 110sht2 = gc.create("new sheet", folder_name="my worksheets") 111 112# open enable TeamDrive support 113gc.drive.enable_team_drive("Dqd3A65c8hsxOpqdfdggPR0fgfg") 114 115``` 116 117### Operations on Spreadsheet [doc](http://pygsheets.readthedocs.io/en/latest/spreadsheet.html) 118 119```python 120 121import pygsheets 122c = pygsheets.authorize() 123sh = c.open('spreadsheet') 124 125# create a new sheet with 50 rows and 60 colums 126wks = sh.add_worksheet("new sheet",rows=50,cols=60) 127 128# create a new sheet with 50 rows and 60 colums at the begin of worksheets 129wks = sh.add_worksheet("new sheet",rows=50,cols=60,index=0) 130 131# or copy from another worksheet 132wks = sh.add_worksheet("new sheet", src_worksheet='<other worksheet instance>') 133 134# delete this wroksheet 135sh.del_worksheet(wks) 136 137# unshare the sheet 138sh.remove_permissions("myNotSoFriend@gmail.com") 139 140``` 141 142### Selecting a Worksheet 143 144```python 145import pygsheets 146c = pygsheets.authorize() 147sh = c.open('spreadsheet') 148 149# Select worksheet by id, index, title. 150wks = sh.worksheet_by_title("my test sheet") 151 152# By any property 153wks = sh.worksheet('index', 0) 154 155# Get a list of all worksheets 156wks_list = sh.worksheets() 157 158# Or just 159wks = sh[0] 160``` 161 162### Operations on Worksheet [doc](http://pygsheets.readthedocs.io/en/latest/worksheet.html) 163 164```python 165# Get values as 2d array('matrix') which can easily be converted to an numpy aray or as 'cell' list 166values_mat = wks.get_values(start=(1,1), end=(20,20), returnas='matrix') 167 168# Get values of - rows A1 to B10, column C, 1st row, 10th row 169wks.get_values_batch(['A1:B10', 'C', '1', (10, None)]) 170 171# Get all values of sheet as 2d list of cells 172cell_matrix = wks.get_all_values(returnas='matrix') 173 174# update a range of values with a cell list or matrix 175wks.update_values(crange='A1:E10', values=values_mat) 176 177# update multiple ranges with bath update 178wks.update_values_batch(['A1:A2', 'B1:B2'], [[[1],[2]], [[3],[4]]]) 179 180# Insert 2 rows after 20th row and fill with values 181wks.insert_rows(row=20, number=2, values=values_list) 182 183# resize by changing rows and colums 184wks.rows=30 185 186# use the worksheet as a csv 187for row in wks: 188 print(row) 189 190# get values by indexes 191 A1_value = wks[0][0] 192 193# clear all values 194wks.clear() 195 196# Search for a table in the worksheet and append a row to it 197wks.append_table(values=[1,2,3,4]) 198 199# export a worksheet as csv 200wks.export(pygsheets.ExportType.CSV) 201 202# Find/Replace cells with string value 203cell_list = worksheet.find("query string") 204 205# Find/Replace cells with regexp 206filter_re = re.compile(r'(small|big) house') 207cell_list = worksheet.find(filter_re, searchByRegex=True) 208cell_list = worksheet.replace(filter_re, 'some house', searchByRegex=True) 209 210# Move a worksheet in the same spreadsheet (update index) 211wks.index = 2 # index start at 1 , not 0 212 213# Update title 214wks.title = "NewTitle" 215 216# Update hidden state 217wks.hidden = False 218 219# working with named ranges 220wks.create_named_range('A1', 'A10', 'prices') 221wks.get_named_range('prices') 222wks.get_named_ranges() # will return a list of DataRange objects 223wks.delete_named_range('prices') 224 225# Plot a chart/graph 226wks.add_chart(('A1', 'A6'), [('B1', 'B6')], 'Health Trend') 227 228# create drop-downs 229wks.set_data_validation(start='C4', end='E7', condition_type='NUMBER_BETWEEN', condition_values=[2,10], strict=True, inputMessage="inut between 2 and 10") 230 231 232``` 233 234#### Pandas integration 235If you work with pandas, you can directly use the dataframes 236```python 237#set the values of a pandas dataframe to sheet 238wks.set_dataframe(df,(1,1)) 239 240#you can also get the values of sheet as dataframe 241df = wks.get_as_df() 242 243``` 244 245 246### Cell Object [doc](http://pygsheets.readthedocs.io/en/latest/cell.html) 247 248Each cell has a __value__ and cordinates (__row__, __col__, __label__) properties. 249 250Getting cell objects 251 252```python 253c1 = Cell('A1',"hello") # create a unlinked cell 254c1 = worksheet.cell('A1') # creates a linked cell whose changes syncs instantanously 255cl.value # Getting cell value 256c1.value_unformatted #Getting cell unformatted value 257c1.formula # Getting cell formula if any 258c1.note # any notes on the cell 259c1.address # address object with cell position 260 261cell_list = worksheet.range('A1:C7') # get a range of cells 262cell_list = worksheet.col(5, returnas='cell') # return all cells in 5th column(E) 263 264``` 265 266Most 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 267 268### Cell Operations 269 270Each 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 271Also not that bu default only the value of cell is fetched, so if you are directly accessing any cell properties call `cell.fetch()` beforehand. 272 273Different ways of updating Cells 274```python 275# using linked cells 276c1 = worksheet.cell('B1') # created from worksheet, so linked cell 277c1.col = 5 # Now c1 correponds to E1 278c1.value = "hoho" # will change the value of E1 279 280# Or onliner 281worksheet.update_value('B1', 'hehe') 282 283# get a range of cells 284cell_list = worksheet.range('A1:C7') 285cell_list = worksheet.get_values(start='A1', end='C7', returnas='cells') 286cell_list = worksheet.get_row(2, returnas='cells') 287 288 289# add formula 290c1.formula = 'A1+C2' 291c1.formula # '=A1+C2' 292 293# get neighbouring cells 294c2 = c1.neighbour('topright') # you can also specify relative position as tuple eg (1,1) 295 296# set cell format 297c1.set_number_format(pygsheets.FormatType.NUMBER, '00.0000') 298 299# write notes on cell 300c1.note = "yo mom" 301 302# set cell color 303c1.color = (1.0, 1.0, 1.0, 1.0) # Red, Green, Blue, Alpha 304 305# set text format 306c1.text_format['fontSize'] = 14 307c1.set_text_format('bold', True) 308 309# sync the changes 310 c1.update() 311 312# you can unlink a cell and set all required properties and then link it 313# So yu could create a model cell and update multiple sheets 314c.unlink() 315c.note = "offine note" 316c.link(wks1, True) 317c.link(wks2, True) 318 319``` 320 321### DataRange Object [doc](http://pygsheets.readthedocs.io/en/latest/datarange.html) 322 323The DataRange is used to represent a range of cells in a worksheet. They can be named or protected. 324Almost all `get_` functions has a `returnas` param, set it to `range` to get a range object. 325```python 326# Getting a Range object 327rng = wks.get_values('A1', 'C5', returnas='range') 328rng.start_addr = 'A' # make the range unbounded on rows <Datarange Sheet1!A:B> 329drange.end_addr = None # make the range unbounded on both axes <Datarange Sheet1> 330 331# Named ranges 332rng.name = 'pricesRange' # will make this range a named range 333rng = wks.get_named_ranges('commodityCount') # directly get a named range 334rng.name = '' # will delete this named range 335 336#Protected ranges 337rng.protected = True 338rng.editors = ('users', 'someemail@gmail.com') 339 340# Setting Format 341 # first create a model cell with required properties 342model_cell = Cell('A1') 343model_cell.color = (1.0,0,1.0,1.0) # rose color cell 344model_cell.format = (pygsheets.FormatType.PERCENT, '') 345 346 # Setting format to multiple cells in one go 347rng.apply_format(model_cell) # will make all cell in this range rose color and percent format 348# Or if you just want to apply format, you can skip fetching data while creating datarange 349Datarange('A1','A10', worksheet=wks).apply_format(model_cell) 350 351# get cells in range 352cell = rng[0][1] 353 354``` 355 356### Batching calls 357 358If you are calling a lot of spreadsheet modification functions (non value update). you can merge them into a single call. 359By doing so all the requests will be merged into a single call. 360 361```python 362gc.set_batch_mode(True) 363wks.merge_cells("A1", "A2") 364wks.merge_cells("B1", "B2") 365Datarange("D1", "D5", wks).apply_format(cell) 366gc.run_batch() # All the above requests are executed here 367gc.set_batch_mode(False) 368 369``` 370Batching also happens when you unlink worksheet. But in that case the requests are not merged. 371 372 373## How to Contribute 374 375This library is still in development phase. 376 377* Follow the [Contributing to Open Source](https://guides.github.com/activities/contributing-to-open-source/) Guide. 378* Branch off of the `staging` branch, and submit Pull Requests back to 379 that branch. Note that the `master` branch is used for version 380 bumps and hotfixes only. 381* For quick testing the changes you have made to source, run the file tests/manual_testing.py. It will give you an IPython shell with lastest code loaded. 382 383### Report Issues/Features 384 385* Please report bugs and suggest features via the [GitHub Issues](https://github.com/nithinmurali/pygsheets/issues). 386* Before opening an issue, search the tracker for possible duplicates. 387* If you have any usage questions, ask a question on stackoverflow with `pygsheets` Tag 388 389## Run Tests 390* install `py.test` 391* run `make test` 392 393Now that you have scrolled all the way down, **finding this library useful?** <a href="https://www.buymeacoffee.com/pygsheets" target="_blank"><img src="https://www.buymeacoffee.com/assets/img/custom_images/orange_img.png" alt="Buy Me A Coffee" style="height: auto !important;width: auto !important;" ></a> 394