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