0
# Worksheet Operations
1

2
Comprehensive worksheet management including data manipulation, structural operations, and formatting capabilities for individual sheets within a spreadsheet.
3

4
## Capabilities
5

6
### Reading Data
7

8
Read data from worksheets in various formats and ranges.
9

10
```python { .api }
11
class Worksheet:
12
    def get_value(self, addr, value_render_option=ValueRenderOption.FORMATTED_VALUE):
13
        """
14
        Get value from a single cell.
15
        
16
        Parameters:
17
        - addr (str): Cell address (e.g., 'A1', 'B2')
18
        - value_render_option (ValueRenderOption): How values should be represented
19
        
20
        Returns:
21
        Value from the cell (str, int, float, or None)
22
        """
23
    
24
    def get_values(self, start, end, returnas='matrix', majdim='ROWS', include_tailing_empty=True,
25
                   include_tailing_empty_rows=False, value_render=ValueRenderOption.FORMATTED_VALUE,
26
                   date_time_render_option=DateTimeRenderOption.SERIAL_NUMBER, grange=None, **kwargs):
27
        """
28
        Get values from a range of cells.
29
        
30
        Parameters:
31
        - start (str): Start cell address
32
        - end (str): End cell address  
33
        - returnas (str): Return format ('matrix', 'cell', 'range')
34
        - majdim (str): Major dimension ('ROWS' or 'COLUMNS')
35
        - include_tailing_empty (bool): Include trailing empty cells
36
        - include_tailing_empty_rows (bool): Include trailing empty rows
37
        - value_render (ValueRenderOption): How values should be represented
38
        - date_time_render_option (DateTimeRenderOption): How dates should be represented
39
        - grange (GridRange): GridRange object instead of start/end
40
        - **kwargs: Additional options
41
        
42
        Returns:
43
        Requested data in specified format
44
        """
45
    
46
    def get_all_values(self, returnas='matrix', **kwargs):
47
        """
48
        Get all values from the worksheet.
49
        
50
        Parameters:
51
        - returnas (str): Return format ('matrix', 'cell')
52
        - **kwargs: Additional options
53
        
54
        Returns:
55
        All worksheet data in specified format
56
        """
57
    
58
    def get_all_records(self, **kwargs) -> list:
59
        """
60
        Get all data as list of dictionaries using first row as headers.
61
        
62
        Parameters:
63
        - **kwargs: Additional options (empty_value, head, etc.)
64
        
65
        Returns:
66
        list: List of dictionaries with column headers as keys
67
        """
68
    
69
    def get_row(self, row, returnas='matrix', **kwargs):
70
        """
71
        Get all values from a specific row.
72
        
73
        Parameters:
74
        - row (int): Row number (1-indexed)
75
        - returnas (str): Return format
76
        - **kwargs: Additional options
77
        
78
        Returns:
79
        Row data in specified format
80
        """
81
    
82
    def get_col(self, col, returnas='matrix', **kwargs):
83
        """
84
        Get all values from a specific column.
85
        
86
        Parameters:
87
        - col (int): Column number (1-indexed)
88
        - returnas (str): Return format
89
        - **kwargs: Additional options
90
        
91
        Returns:
92
        Column data in specified format
93
        """
94
```
95

96
### Writing Data
97

98
Update worksheet data with various methods for different data types and ranges.
99

100
```python { .api }
101
class Worksheet:
102
    def update_value(self, addr, val, **kwargs):
103
        """
104
        Update value in a single cell.
105
        
106
        Parameters:
107
        - addr (str): Cell address (e.g., 'A1')
108
        - val: Value to set
109
        - **kwargs: Additional options
110
        """
111
    
112
    def update_values(self, crange=None, values=None, **kwargs):
113
        """
114
        Update values in a range of cells.
115
        
116
        Parameters:
117
        - crange (str): Range to update (e.g., 'A1:C3')
118
        - values: Data to update (list of lists)
119
        - **kwargs: Additional options (majordimension, value_input_option, etc.)
120
        """
121
    
122
    def update_row(self, index, values, **kwargs):
123
        """
124
        Update entire row with values.
125
        
126
        Parameters:
127
        - index (int): Row number (1-indexed)
128
        - values (list): Values to set in row
129
        - **kwargs: Additional options
130
        """
131
    
132
    def update_col(self, index, values, **kwargs):
133
        """
134
        Update entire column with values.
135
        
136
        Parameters:
137
        - index (int): Column number (1-indexed)
138
        - values (list): Values to set in column
139
        - **kwargs: Additional options
140
        """
141
    
142
    def append_table(self, values, start='A1', **kwargs):
143
        """
144
        Append data to worksheet as a table.
145
        
146
        Parameters:
147
        - values: Data to append (list of lists)
148
        - start (str): Starting cell address
149
        - **kwargs: Additional options (dimension, value_input_option, etc.)
150
        """
151
```
152

153
### Worksheet Structure
154

155
Manage worksheet structure including size, rows, columns, and layout.
156

157
```python { .api }
158
class Worksheet:
159
    def resize(self, rows=None, cols=None):
160
        """
161
        Resize worksheet dimensions.
162
        
163
        Parameters:
164
        - rows (int): New number of rows
165
        - cols (int): New number of columns
166
        """
167
    
168
    def add_rows(self, rows):
169
        """
170
        Add rows to worksheet.
171
        
172
        Parameters:
173
        - rows (int): Number of rows to add
174
        """
175
    
176
    def add_cols(self, cols):
177
        """
178
        Add columns to worksheet.
179
        
180
        Parameters:
181
        - cols (int): Number of columns to add
182
        """
183
    
184
    def delete_rows(self, index, number=1):
185
        """
186
        Delete rows from worksheet.
187
        
188
        Parameters:
189
        - index (int): Starting row index (1-indexed)
190
        - number (int): Number of rows to delete
191
        """
192
    
193
    def delete_cols(self, index, number=1):
194
        """
195
        Delete columns from worksheet.
196
        
197
        Parameters:
198
        - index (int): Starting column index (1-indexed)
199
        - number (int): Number of columns to delete
200
        """
201
    
202
    def insert_rows(self, row, number=1, values=None, **kwargs):
203
        """
204
        Insert rows into worksheet.
205
        
206
        Parameters:
207
        - row (int): Row index where to insert (1-indexed)
208
        - number (int): Number of rows to insert
209
        - values: Optional values for new rows
210
        - **kwargs: Additional options
211
        """
212
    
213
    def insert_cols(self, col, number=1, values=None, **kwargs):
214
        """
215
        Insert columns into worksheet.
216
        
217
        Parameters:
218
        - col (int): Column index where to insert (1-indexed)
219
        - number (int): Number of columns to insert
220
        - values: Optional values for new columns
221
        - **kwargs: Additional options
222
        """
223
```
224

225
### Worksheet Properties
226

227
Access and modify worksheet properties and metadata.
228

229
```python { .api }
230
class Worksheet:
231
    @property
232
    def id(self) -> int:
233
        """Worksheet ID."""
234
    
235
    @property
236
    def index(self) -> int:
237
        """Worksheet index/position."""
238
    
239
    @property
240
    def title(self) -> str:
241
        """Worksheet title/name."""
242
    
243
    @property
244
    def url(self) -> str:
245
        """Worksheet URL."""
246
    
247
    @property
248
    def rows(self) -> int:
249
        """Number of rows in worksheet."""
250
    
251
    @property
252
    def cols(self) -> int:
253
        """Number of columns in worksheet."""
254
    
255
    @property
256
    def frozen_rows(self) -> int:
257
        """Number of frozen rows."""
258
    
259
    @property
260
    def frozen_cols(self) -> int:
261
        """Number of frozen columns."""
262
    
263
    @property
264
    def hidden(self) -> bool:
265
        """Whether worksheet is hidden."""
266
    
267
    def adjust_column_width(self, start, end=None, pixel_size=100):
268
        """
269
        Adjust column width.
270
        
271
        Parameters:
272
        - start (int): Starting column index
273
        - end (int): Ending column index (None for single column)
274
        - pixel_size (int): Width in pixels
275
        """
276
    
277
    def adjust_row_height(self, start, end=None, pixel_size=100):
278
        """
279
        Adjust row height.
280
        
281
        Parameters:
282
        - start (int): Starting row index
283
        - end (int): Ending row index (None for single row)
284
        - pixel_size (int): Height in pixels
285
        """
286
```
287

288
### Search and Replace
289

290
Find and replace data within worksheets.
291

292
```python { .api }
293
class Worksheet:
294
    def find(self, query, **kwargs):
295
        """
296
        Find cells matching query.
297
        
298
        Parameters:
299
        - query (str): Search query
300
        - **kwargs: Additional search options
301
        
302
        Returns:
303
        list: List of matching Cell objects
304
        """
305
    
306
    def replace(self, find, replace, **kwargs):
307
        """
308
        Replace all occurrences of text.
309
        
310
        Parameters:
311
        - find (str): Text to find
312
        - replace (str): Replacement text
313
        - **kwargs: Additional replace options
314
        
315
        Returns:
316
        int: Number of replacements made
317
        """
318
```
319

320
### DataFrame Integration
321

322
Seamless integration with pandas DataFrames for data analysis workflows.
323

324
```python { .api }
325
class Worksheet:
326
    def set_dataframe(self, df, start='A1', **kwargs):
327
        """
328
        Set worksheet content from pandas DataFrame.
329
        
330
        Parameters:
331
        - df (pandas.DataFrame): DataFrame to write
332
        - start (str): Starting cell address
333
        - **kwargs: Additional options (copy_index, copy_head, etc.)
334
        """
335
    
336
    def get_as_df(self, **kwargs):
337
        """
338
        Get worksheet content as pandas DataFrame.
339
        
340
        Parameters:
341
        - **kwargs: Additional options (has_header, index_col, etc.)
342
        
343
        Returns:
344
        pandas.DataFrame: Worksheet data as DataFrame
345
        """
346
```
347

348
## Usage Examples
349

350
### Basic Data Operations
351

352
```python
353
import pygsheets
354
import pandas as pd
355

356
# Get worksheet
357
gc = pygsheets.authorize()
358
sh = gc.open('My Spreadsheet')
359
wks = sh.sheet1
360

361
# Read single value
362
value = wks.get_value('A1')
363

364
# Read range of values
365
values = wks.get_values('A1:C3')
366

367
# Read all values
368
all_data = wks.get_all_values()
369

370
# Read as records (first row as headers)
371
records = wks.get_all_records()
372

373
# Update single cell
374
wks.update_value('A1', 'Hello World')
375

376
# Update range
377
data = [['Name', 'Age'], ['Alice', 25], ['Bob', 30]]
378
wks.update_values('A1:B3', data)
379

380
# Append data
381
new_data = [['Charlie', 35], ['Diana', 28]]
382
wks.append_table(new_data, start='A4')
383
```
384

385
### DataFrame Integration
386

387
```python
388
# Create DataFrame
389
df = pd.DataFrame({
390
    'Name': ['Alice', 'Bob', 'Charlie'],
391
    'Age': [25, 30, 35],
392
    'City': ['NYC', 'LA', 'Chicago']
393
})
394

395
# Write DataFrame to worksheet
396
wks.set_dataframe(df, start='A1', copy_index=False)
397

398
# Read worksheet as DataFrame
399
df_from_sheet = wks.get_as_df(has_header=True)
400
```
401

402
### Worksheet Structure Management
403

404
```python
405
# Resize worksheet
406
wks.resize(rows=100, cols=20)
407

408
# Add rows and columns
409
wks.add_rows(10)
410
wks.add_cols(5)
411

412
# Insert rows with data
413
wks.insert_rows(5, number=2, values=[['New', 'Data'], ['More', 'Info']])
414

415
# Delete rows and columns
416
wks.delete_rows(10, number=3)
417
wks.delete_cols(15, number=2)
418

419
# Adjust dimensions
420
wks.adjust_column_width(1, 3, pixel_size=150)
421
wks.adjust_row_height(1, pixel_size=30)
422
```
423

424
## Types
425

426
### Value Render Options
427

428
```python { .api }
429
class ValueRenderOption:
430
    FORMATTED_VALUE = 'FORMATTED_VALUE'
431
    UNFORMATTED_VALUE = 'UNFORMATTED_VALUE'
432
    FORMULA = 'FORMULA'
433

434
class DateTimeRenderOption:
435
    SERIAL_NUMBER = 'SERIAL_NUMBER'
436
    FORMATTED_STRING = 'FORMATTED_STRING'
437
```
438

439
### Exceptions
440

441
```python { .api }
442
class WorksheetNotFound(PyGsheetsException):
443
    """Raised when worksheet cannot be found."""
444
    pass
445

446
class CellNotFound(PyGsheetsException):
447
    """Raised when cell cannot be found."""
448
    pass
449

450
class RangeNotFound(PyGsheetsException):
451
    """Raised when range cannot be found."""
452
    pass
453
```