0
# pygsheets
1

2
A comprehensive Python library for interacting with Google Spreadsheets using the Google Sheets API v4. It provides an object-oriented interface for creating, reading, updating, and deleting spreadsheet data with advanced features like formatting, charts, data validation, and pandas integration.
3

4
## Package Information
5

6
- **Package Name**: pygsheets
7
- **Language**: Python
8
- **Installation**: `pip install pygsheets`
9

10
## Core Imports
11

12
```python
13
import pygsheets
14
```
15

16
Common imports for working with specific components:
17

18
```python
19
from pygsheets import authorize, Spreadsheet, Worksheet, Cell, DataRange, Chart, Address, GridRange
20
from pygsheets import FormatType, WorkSheetProperty, ChartType, ValueRenderOption, DateTimeRenderOption
21
```
22

23
## Basic Usage
24

25
```python
26
import pygsheets
27
import numpy as np
28

29
# Authenticate with Google Sheets API
30
gc = pygsheets.authorize()
31

32
# Open spreadsheet and get first worksheet
33
sh = gc.open('my new sheet')
34
wks = sh.sheet1
35

36
# Update a single cell
37
wks.update_value('A1', "Hello World")
38

39
# Update a range with data
40
my_array = np.random.randint(10, size=(3, 4))
41
wks.update_values('A2', my_array.tolist())
42

43
# Work with individual cells
44
cell = wks.cell('B1')
45
cell.value = 'Heights'
46
cell.text_format['bold'] = True
47
cell.update()
48

49
# Work with named ranges
50
heights_range = wks.range('B2:B5', returnas='range')
51
heights_range.name = "heights"
52
heights_range.update_values([[50], [60], [67], [66]])
53

54
# Use formulas with named ranges
55
wks.update_value('B6', '=average(heights)')
56

57
# Share the spreadsheet
58
sh.share("friend@gmail.com")
59
```
60

61
## Architecture
62

63
The pygsheets library follows a hierarchical object model:
64

65
- **Client**: Main entry point for authentication and spreadsheet access
66
- **Spreadsheet**: Represents a Google Spreadsheet document with multiple worksheets
67
- **Worksheet**: Individual sheets within a spreadsheet containing cells and data
68
- **Cell**: Individual cells with values, formatting, and formulas
69
- **DataRange**: Named or protected ranges of cells for bulk operations
70
- **Address/GridRange**: Flexible address representation for cell and range references
71

72
## Capabilities
73

74
### Authentication and Client Management
75

76
Authenticate with Google APIs and manage spreadsheet access using OAuth2 or service account credentials.
77

78
```python { .api }
79
def authorize(client_secret='client_secret.json',
80
              service_account_file=None,
81
              service_account_env_var=None,
82
              service_account_json=None,
83
              credentials_directory='',
84
              scopes=_SCOPES,
85
              custom_credentials=None,
86
              local=False,
87
              **kwargs) -> Client
88
```
89

90
[Authentication](./authentication.md)
91

92
### Spreadsheet Operations
93

94
Create, open, share, and manage Google Spreadsheets with full CRUD operations and permission control.
95

96
```python { .api }
97
class Client:
98
    def create(self, title, template=None, folder=None, folder_name=None, **kwargs) -> Spreadsheet
99
    def open(self, title) -> Spreadsheet
100
    def open_by_key(self, key) -> Spreadsheet
101
    def open_by_url(self, url) -> Spreadsheet
102
```
103

104
[Spreadsheet Management](./spreadsheet-management.md)
105

106
### Worksheet Operations
107

108
Manage individual worksheets with comprehensive data manipulation, formatting, and structural operations.
109

110
```python { .api }
111
class Worksheet:
112
    def get_values(self, start=None, end=None, returnas='matrix', **kwargs)
113
    def update_values(self, crange=None, values=None, **kwargs)
114
    def get_all_records(self, **kwargs) -> list
115
    def set_dataframe(self, df, start='A1', **kwargs)
116
    def get_as_df(self, **kwargs)
117
```
118

119
[Worksheet Operations](./worksheet-operations.md)
120

121
### Cell and Range Manipulation
122

123
Work with individual cells and ranges including formatting, formulas, and bulk operations.
124

125
```python { .api }
126
class Cell:
127
    def set_text_format(self, attribute, value)
128
    def set_number_format(self, format_type, pattern=None)
129
    def update(self, force=False)
130

131
class DataRange:
132
    def update_values(self, values, **kwargs)
133
    def apply_format(self, cell_list, fields="userEnteredFormat")
134
```
135

136
[Cell and Range Operations](./cell-range-operations.md)
137

138
### Charts and Visualizations
139

140
Create and manage charts within worksheets with various chart types and customization options.
141

142
```python { .api }
143
class Chart:
144
    def update_chart(self, chart_type=None, domain=None, ranges=None, **kwargs)
145
    def delete()
146

147
class Worksheet:
148
    def add_chart(self, data, start=None, end=None, chart_type=None, **kwargs) -> Chart
149
```
150

151
[Charts](./charts.md)
152

153
### Data Validation and Formatting
154

155
Set up data validation rules, conditional formatting, and advanced cell formatting options.
156

157
```python { .api }
158
class Worksheet:
159
    def set_data_validation(self, crange, condition_type, condition_values, **kwargs)
160
    def add_conditional_formatting(self, crange, condition_type, condition_values, **kwargs)
161
    def apply_format(self, ranges, cell_format, fields="userEnteredFormat")
162
```
163

164
[Data Validation and Formatting](./data-validation-formatting.md)
165

166
## Types
167

168
### Core Classes
169

170
```python { .api }
171
class Client:
172
    def __init__(self, credentials, retries=3, seconds_per_quota=100)
173

174
class Spreadsheet:
175
    def __init__(self, client, properties=None, jsonsheet=None)
176
    
177
class Worksheet:
178
    def __init__(self, spreadsheet, properties=None, jsonsheet=None)
179
    
180
class Cell:
181
    def __init__(self, pos, val='', worksheet=None, cell_data=None)
182
    
183
class DataRange:
184
    def __init__(self, namedjson=None, name_id='', worksheet=None, protectedjson=None, protect_id='')
185
```
186

187
### Address Types
188

189
```python { .api }
190
class Address:
191
    def __init__(self, label=None, row=None, col=None, index=1)
192
    
193
class GridRange:
194
    def __init__(self, label=None, start=None, end=None, worksheet=None)
195
    @staticmethod
196
    def create(start, end=None, worksheet=None) -> GridRange
197
```
198

199
### Enumerations
200

201
```python { .api }
202
class FormatType:
203
    CUSTOM = None
204
    TEXT = 'TEXT'
205
    NUMBER = 'NUMBER'
206
    PERCENT = 'PERCENT'
207
    CURRENCY = 'CURRENCY'
208
    DATE = 'DATE'
209
    TIME = 'TIME'
210
    DATE_TIME = 'DATE_TIME'
211
    SCIENTIFIC = 'SCIENTIFIC'
212

213
class ChartType:
214
    BAR = 'BAR'
215
    LINE = 'LINE'
216
    AREA = 'AREA'
217
    COLUMN = 'COLUMN'
218
    SCATTER = 'SCATTER'
219
    COMBO = 'COMBO'
220
    STEPPED_AREA = 'STEPPED_AREA'
221

222
class ValueRenderOption:
223
    FORMATTED_VALUE = 'FORMATTED_VALUE'
224
    UNFORMATTED_VALUE = 'UNFORMATTED_VALUE'
225
    FORMULA = 'FORMULA'
226

227
class DateTimeRenderOption:
228
    SERIAL_NUMBER = 'SERIAL_NUMBER'
229
    FORMATTED_STRING = 'FORMATTED_STRING'
230

231
class WorkSheetProperty:
232
    TITLE = 'title'
233
    ID = 'id'
234
    INDEX = 'index'
235

236
class ExportType:
237
    XLS = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet:.xls"
238
    ODT = "application/x-vnd.oasis.opendocument.spreadsheet:.odt"
239
    PDF = "application/pdf:.pdf"
240
    CSV = "text/csv:.csv"
241
    TSV = 'text/tab-separated-values:.tsv'
242
    HTML = 'application/zip:.zip'
243
```
244

245
### Exception Types
246

247
```python { .api }
248
class PyGsheetsException(Exception): pass
249
class AuthenticationError(PyGsheetsException): pass
250
class SpreadsheetNotFound(PyGsheetsException): pass
251
class NoValidUrlKeyFound(PyGsheetsException): pass
252
class IncorrectCellLabel(PyGsheetsException): pass
253
class WorksheetNotFound(PyGsheetsException): pass
254
class RequestError(PyGsheetsException): pass
255
class CellNotFound(PyGsheetsException): pass
256
class InvalidUser(PyGsheetsException): pass
257
class InvalidArgumentValue(PyGsheetsException): pass
258
```