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