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
```