pygsheets

A Cell object represents a single cell in a spreadsheet. It has properties like value, value_unformatted, formula, note, and address.

Linked vs Unlinked Cells

• Linked Cells: Created via worksheet.cell('A1'). Changes to these objects sync instantaneously with the spreadsheet. If you modify properties like col or value, the spreadsheet updates.
• Unlinked Cells: Created via Cell('A1', 'value'). These are local models and do not affect the spreadsheet until you explicitly link them using .link(worksheet, True).

Key Operations

• cell.fetch(): Fetches the latest data from the spreadsheet for that cell (required if accessing properties other than value directly).
• cell.neighbour(direction): Gets a neighboring cell (e.g., 'topright' or a tuple like (1, 1)).
• cell.set_number_format(type, format): Sets the number format.
• cell.set_text_format(style, value): Sets text styles like 'bold'.
• cell.update(): Syncs changes for a linked cell.

# Using linked cells
c1 = worksheet.cell('B1')
c1.col = 5  # Now c1 corresponds to E1
c1.value = "hoho"  # Changes E1

# Get a range of cells
cell_list = worksheet.range('A1:C7')

# Unlink and relink to update multiple sheets
c.unlink()
c.note = "offline note"
c.link(wks1, True)
c.link(wks2, True)

# Getting cell objects
c1 = Cell('A1',"hello")  # create a unlinked cell
c1 = worksheet.cell('A1')  # creates a linked cell whose changes syncs instantanously
cl.value  # Getting cell value
c1.value_unformatted #Getting cell unformatted value
c1.formula # Getting cell formula if any
c1.note # any notes on the cell
c1.address # address object with cell position

cell_list = worksheet.range('A1:C7')  # get a range of cells 
cell_list = worksheet.col(5, returnas='cell')  # return all cells in 5th column(E)

If you are performing multiple non-value update operations (like merging cells or applying formats), you can use batch mode to group them into a single API call. This improves performance by reducing the number of network requests.

1. Enable batch mode with gc.set_batch_mode(True).
2. Perform your operations.
3. Execute all queued requests with gc.run_batch().
4. Disable batch mode with gc.set_batch_mode(False).

Note: Batching does not apply when unlinking a worksheet; those requests are not merged.

# Enable batch mode
gc.set_batch_mode(True)

# Queue operations
wks.merge_cells("A1", "A2")
wks.merge_cells("B1", "B2")
Datarange("D1", "D5", wks).apply_format(cell)

# Execute all queued requests
gc.run_batch()

gc.set_batch_mode(False)

gc.set_batch_mode(True)
wks.merge_cells("A1", "A2")
wks.merge_cells("B1", "B2")
Datarange("D1", "D5", wks).apply_format(cell)
gc.run_batch() # All the above requests are executed here
gc.set_batch_mode(False)

A DataRange represents a contiguous range of cells. It can be used to apply formatting or manage named/protected ranges.

Key Features

• Unbounded Ranges: You can make a range unbounded on rows or columns by setting start_addr or end_addr to specific values (e.g., rng.start_addr = 'A' for all rows in column A).
• Named Ranges: Assign a name to a range using rng.name = 'my_name'. Use wks.get_named_range('name') to retrieve it.
• Protected Ranges: Protect a range and specify editors using rng.protected = True and rng.editors = ('users', 'email@example.com').
• Formatting: Apply a Cell model's format to an entire range using rng.apply_format(model_cell).

# Getting a Range object
rng = wks.get_values('A1', 'C5', returnas='range')

rng.name = 'pricesRange'  # Make this a named range

# Protected ranges
rng.protected = True
rng.editors = ('users', 'someemail@gmail.com')

# Apply format to a range using a model cell
model_cell = Cell('A1')
model_cell.color = (1.0, 0, 1.0, 1.0)
model_cell.format = (pygsheets.FormatType.PERCENT, '')
rng.apply_format(model_cell)

# Getting a Range object
rng = wks.get_values('A1', 'C5', returnas='range')
rng.start_addr = 'A' # make the range unbounded on rows <Datarange Sheet1!A:B>
drange.end_addr = None # make the range unbounded on both axes <Datarange Sheet1>

# Named ranges
rng.name = 'pricesRange'  # will make this range a named range
rng = wks.get_named_ranges('commodityCount') # directly get a named range
rng.name = ''  # will delete this named range

#Protected ranges
rng.protected = True
rng.editors = ('users', 'someemail@gmail.com')

# Setting Format
 # first create a model cell with required properties
model_cell = Cell('A1')
model_cell.color = (1.0,0,1.0,1.0) # rose color cell
model_cell.format = (pygsheets.FormatType.PERCENT, '')

 # Setting format to multiple cells in one go
rng.apply_format(model_cell)  # will make all cell in this range rose color and percent format
 # Or if you just want to apply format, you can skip fetching data while creating datarange
Datarange('A1','A10', worksheet=wks).apply_format(model_cell)

# get cells in range
cell = rng[0][1]

To apply formatting to a specific range, define a pygsheets.Cell object with the desired properties (like set_number_format or color) and use pygsheets.DataRange(...).apply_format(cell) to apply that cell's model to the range.

Note: When applying colors, the color attribute expects an RGBA tuple (e.g., (1, 1, 1, 0) for white). You can use the fields argument in apply_format to specify exactly which properties to update, such as userEnteredFormat.backgroundColor.

model_cell = pygsheets.Cell("A1")

model_cell.set_number_format(
    format_type = pygsheets.FormatType.PERCENT,
    pattern = "0%"
)
# first apply the percentage formatting
pygsheets.DataRange(
    left_corner_cell , right_corner_cell , worksheet = wks
).apply_format(model_cell)

# now apply the row-colouring interchangeably
gray_cell = pygsheets.Cell("A1")
gray_cell.color = (0.9529412, 0.9529412, 0.9529412, 0)

white_cell = pygsheets.Cell("A2")
white_cell.color = (1, 1, 1, 0)

cells = [gray_cell, white_cell]

for r in range(start_row, end_row + 1):
    print(f"Doing row {r} ...", flush = True, end = "\r")
    wks.get_row(r, returnas = "range").apply_format(cells[ r % 2 ], fields = "userEnteredFormat.backgroundColor")

OAuth is the best option for editing spreadsheets on behalf of a user. This method allows the script to access all spreadsheets in the user's account. The initial authorization flow (email/password) is only required once.

Setup Steps:

1. Configure an OAuth Consent Screen in the Google Developers Console.
2. Go to Credentials > Create Credentials > OAuth Client ID.
3. Select Other as the application type.
4. Download the client_secret[...].json file.

Usage: By default, pygsheets.authorize() looks for a file named client_secret.json in the current working directory. If your file is named differently or located elsewhere, provide the path using the client_secret parameter.

Note on Token Storage: After the first authorization, a token file is stored in your working directory. To avoid re-authorizing every time, you can use this token file by passing its path to credentials_directory. Note that credentials_directory overrides client_secret.