0
# Spreadsheet Management
1
2
Comprehensive spreadsheet operations including creation, opening, sharing, and management of Google Spreadsheets.
3
4
## Capabilities
5
6
### Creating Spreadsheets
7
8
Create new Google Spreadsheets with optional templates and folder placement.
9
10
```python { .api }
11
class Client:
12
def create(self, title, template=None, folder=None, folder_name=None, **kwargs) -> Spreadsheet:
13
"""
14
Create a new spreadsheet.
15
16
Parameters:
17
- title (str): Name of the new spreadsheet
18
- template (Spreadsheet): Spreadsheet to use as template
19
- folder (str): Folder ID where spreadsheet should be created
20
- folder_name (str): Name of folder where spreadsheet should be created
21
22
Returns:
23
Spreadsheet: New spreadsheet instance
24
"""
25
```
26
27
### Opening Spreadsheets
28
29
Open existing spreadsheets using various identification methods.
30
31
```python { .api }
32
class Client:
33
def open(self, title) -> Spreadsheet:
34
"""
35
Open spreadsheet by title.
36
37
Parameters:
38
- title (str): Title of the spreadsheet
39
40
Returns:
41
Spreadsheet: Opened spreadsheet instance
42
43
Raises:
44
SpreadsheetNotFound: If no spreadsheet with given title is found
45
"""
46
47
def open_by_key(self, key) -> Spreadsheet:
48
"""
49
Open spreadsheet by key/ID.
50
51
Parameters:
52
- key (str): Spreadsheet key/ID
53
54
Returns:
55
Spreadsheet: Opened spreadsheet instance
56
"""
57
58
def open_by_url(self, url) -> Spreadsheet:
59
"""
60
Open spreadsheet by URL.
61
62
Parameters:
63
- url (str): Full URL of the spreadsheet
64
65
Returns:
66
Spreadsheet: Opened spreadsheet instance
67
68
Raises:
69
NoValidUrlKeyFound: If URL doesn't contain valid spreadsheet key
70
"""
71
72
def open_all(self, query='') -> list:
73
"""
74
Open all accessible spreadsheets matching query.
75
76
Parameters:
77
- query (str): Search query to filter spreadsheets
78
79
Returns:
80
list: List of Spreadsheet instances
81
"""
82
```
83
84
### Spreadsheet Properties and Management
85
86
Access and modify spreadsheet properties, metadata, and structure.
87
88
```python { .api }
89
class Spreadsheet:
90
def __init__(self, client, jsonsheet=None, id=None):
91
"""
92
Initialize spreadsheet instance.
93
94
Parameters:
95
- client (Client): Authenticated client instance
96
- properties (dict): Spreadsheet properties
97
- jsonsheet (dict): Full spreadsheet JSON representation
98
"""
99
100
@property
101
def id(self) -> str:
102
"""Spreadsheet ID."""
103
104
@property
105
def title(self) -> str:
106
"""Spreadsheet title."""
107
108
@property
109
def url(self) -> str:
110
"""Spreadsheet URL."""
111
112
@property
113
def locale(self) -> str:
114
"""Spreadsheet locale."""
115
116
@property
117
def sheet1(self) -> Worksheet:
118
"""First worksheet in the spreadsheet."""
119
120
def fetch_properties(self, jsonsheet=None):
121
"""
122
Fetch and update spreadsheet properties from API.
123
124
Parameters:
125
- jsonsheet (dict): Optional JSON representation to use instead of API call
126
"""
127
128
def worksheets(self, sheet_property=None, value=None, force_fetch=False) -> list:
129
"""
130
Get list of all worksheets in spreadsheet.
131
132
Parameters:
133
- property (WorkSheetProperty): Property to return for each worksheet
134
135
Returns:
136
list: List of worksheet titles, IDs, or indices based on property parameter
137
"""
138
139
def worksheet(self, property=WorkSheetProperty.TITLE, value=0) -> Worksheet:
140
"""
141
Get specific worksheet by property value.
142
143
Parameters:
144
- property (WorkSheetProperty): Property to search by
145
- value: Value to match
146
147
Returns:
148
Worksheet: Matching worksheet instance
149
150
Raises:
151
WorksheetNotFound: If no matching worksheet is found
152
"""
153
154
def add_worksheet(self, title, rows=100, cols=26, src_tuple=None, src_worksheet=None, index=None) -> Worksheet:
155
"""
156
Create or copy a worksheet and add it to the spreadsheet.
157
158
Parameters:
159
- title (str): Title of the new worksheet
160
- rows (int): Number of rows (default: 100)
161
- cols (int): Number of columns (default: 26)
162
- src_tuple (tuple): Source spreadsheet tuple for copying
163
- src_worksheet: Source worksheet to copy from
164
- index (int): Position to insert the worksheet
165
166
Returns:
167
Worksheet: The newly created worksheet
168
"""
169
170
def del_worksheet(self, worksheet):
171
"""
172
Delete a worksheet from the spreadsheet.
173
174
Parameters:
175
- worksheet: The worksheet instance to delete
176
"""
177
```
178
179
### Sharing and Permissions
180
181
Manage spreadsheet sharing and access permissions.
182
183
```python { .api }
184
class Spreadsheet:
185
def share(self, addr, role='reader', typ='user', **kwargs):
186
"""
187
Share spreadsheet with user or group.
188
189
Parameters:
190
- addr (str): Email address or domain to share with
191
- role (str): Permission role ('owner', 'writer', 'reader')
192
- typ (str): Type of recipient ('user', 'group', 'domain', 'anyone')
193
- **kwargs: Additional sharing options
194
"""
195
196
def remove_permission(self, addr):
197
"""
198
Remove sharing permission for user or group.
199
200
Parameters:
201
- addr (str): Email address or domain to remove permission from
202
203
Raises:
204
CannotRemoveOwnerError: If trying to remove last owner permission
205
"""
206
207
@property
208
def permissions(self) -> list:
209
"""
210
List of current sharing permissions.
211
212
Returns:
213
list: List of permission dictionaries
214
"""
215
```
216
217
### Export and Data Operations
218
219
Export spreadsheet data and perform bulk operations.
220
221
```python { .api }
222
class Spreadsheet:
223
def export(self, file_format=ExportType.CSV, filename=None, path='') -> str:
224
"""
225
Export spreadsheet to file.
226
227
Parameters:
228
- file_format (ExportType): Export format (CSV, XLS, PDF, etc.)
229
- filename (str): Name for exported file
230
- path (str): Directory path for export
231
232
Returns:
233
str: Path to exported file
234
"""
235
236
def delete(self):
237
"""
238
Delete the spreadsheet.
239
240
Raises:
241
RequestError: If deletion fails
242
"""
243
244
def to_json(self) -> dict:
245
"""
246
Get JSON representation of spreadsheet.
247
248
Returns:
249
dict: Complete spreadsheet data as JSON
250
"""
251
```
252
253
## Usage Examples
254
255
### Basic Spreadsheet Operations
256
257
```python
258
import pygsheets
259
260
# Authenticate
261
gc = pygsheets.authorize()
262
263
# Create new spreadsheet
264
sh = gc.create('My New Spreadsheet')
265
266
# Open existing spreadsheet by title
267
sh = gc.open('Existing Spreadsheet')
268
269
# Open by key/ID
270
sh = gc.open_by_key('1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms')
271
272
# Open by URL
273
url = 'https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
274
sh = gc.open_by_url(url)
275
276
# Get all spreadsheets
277
all_sheets = gc.open_all()
278
279
# Search for specific spreadsheets
280
finance_sheets = gc.open_all('finance')
281
```
282
283
### Sharing and Permissions
284
285
```python
286
# Share with specific user
287
sh.share('user@example.com', role='writer')
288
289
# Share with domain
290
sh.share('example.com', role='reader', typ='domain')
291
292
# Make publicly readable
293
sh.share('', role='reader', typ='anyone')
294
295
# Remove permission
296
sh.remove_permission('user@example.com')
297
298
# Check current permissions
299
permissions = sh.permissions
300
for perm in permissions:
301
print(f"Email: {perm.get('emailAddress')}, Role: {perm.get('role')}")
302
```
303
304
## Types
305
306
### Export Types
307
308
```python { .api }
309
class ExportType:
310
XLS = 'application/vnd.ms-excel'
311
ODT = 'application/vnd.oasis.opendocument.text'
312
PDF = 'application/pdf'
313
CSV = 'text/csv'
314
TSV = 'text/tab-separated-values'
315
HTML = 'text/html'
316
```
317
318
### Worksheet Property Types
319
320
```python { .api }
321
class WorkSheetProperty:
322
TITLE = 'title'
323
ID = 'id'
324
INDEX = 'index'
325
```
326
327
### Exceptions
328
329
```python { .api }
330
class SpreadsheetNotFound(PyGsheetsException):
331
"""Raised when spreadsheet cannot be found or accessed."""
332
pass
333
334
class NoValidUrlKeyFound(PyGsheetsException):
335
"""Raised when URL doesn't contain valid spreadsheet key."""
336
pass
337
338
class CannotRemoveOwnerError(PyGsheetsException):
339
"""Raised when trying to remove last owner permission."""
340
pass
341
```