def open(self, title):
"""
Opens a spreadsheet.
:param title: A title of a spreadsheet.
:type title: str
:returns: a :class:`~gspread.models.Spreadsheet` instance.
If there's more than one spreadsheet with same title the first one
will be opened.
:raises gspread.SpreadsheetNotFound: if no spreadsheet with
specified `title` is found.
>>> c = gspread.authorize(credentials)
>>> c.open('My fancy spreadsheet')
"""
try:
properties = finditem(
lambda x: x["name"] == title, self.list_spreadsheet_files(title)
)
# Drive uses different terminology
properties["title"] = properties["name"]
return Spreadsheet(self, properties)
except StopIteration:
raise SpreadsheetNotFound
def test_frozen_rows_cols(self):
set_frozen(self.sheet, rows=1, cols=1)
fresh = self.sheet.spreadsheet.fetch_sheet_metadata({'includeGridData': True})
item = utils.finditem(lambda x: x['properties']['title'] == self.sheet.title, fresh['sheets'])
pr = item['properties']['gridProperties']
self.assertEqual(pr.get('frozenRowCount'), 1)
self.assertEqual(pr.get('frozenColumnCount'), 1)
self.assertEqual(get_frozen_row_count(self.sheet), 1)
self.assertEqual(get_frozen_column_count(self.sheet), 1)
def test_merge_cells(self):
self.sheet.update('A1:B2', [[42, 43], [43, 44]])
# test merge rows
self.sheet.merge_cells(1, 1, 2, 2, merge_type="MERGE_ROWS")
meta = self.sheet.spreadsheet.fetch_sheet_metadata()
merges = utils.finditem(
lambda x: x['properties']['sheetId'] == self.sheet.id,
meta['sheets'])['merges']
self.assertEqual(len(merges), 2)
# test merge all
self.sheet.merge_cells(1, 1, 2, 2)
meta = self.sheet.spreadsheet.fetch_sheet_metadata()
merges = utils.finditem(
lambda x: x['properties']['sheetId'] == self.sheet.id,
meta['sheets'])['merges']
self.assertEqual(len(merges), 1)
def test_merge_cells(self):
self.sheet.update_cell(1, 1, '42')
self.sheet.update_cell(1, 2, '80')
# test merge rows
self.sheet.merge_cells(1, 1, 1, 2)
meta = self.sheet.spreadsheet.fetch_sheet_metadata()
merges = utils.finditem(
lambda x: x['properties']['sheetId'] == self.sheet.id,
meta)['merges']
self.assertEqual(len(merges), 2)
# test merge all
self.sheet.merge_cells(1, 1, 1, 2, merge_type="MERGE_ALL")
meta = self.sheet.spreadsheet.fetch_sheet_metadata()
merges = utils.finditem(
lambda x: x['properties']['sheetId'] == self.sheet.id,
meta)['merges']
self.assertEqual(len(merges), 1)
def get_sheet():
sheets = self.sheet.spreadsheet.fetch_sheet_metadata()['sheets']
return utils.finditem(
lambda x: x['properties']['sheetId'] == self.sheet.id, sheets
)
def get_frozen_column_count(worksheet):
md = worksheet.spreadsheet.fetch_sheet_metadata({'includeGridData': True})
sheet_data = finditem(
lambda i: i['properties']['title'] == worksheet.title, md['sheets'])
grid_props = sheet_data['properties']['gridProperties']
return grid_props.get('frozenColumnCount')
def get_sheet():
sheets = self.sheet.spreadsheet.fetch_sheet_metadata()["sheets"]
return utils.finditem(
lambda x: x["properties"]["sheetId"] == self.sheet.id, sheets)
def set_with_dataframe(worksheet,
dataframe,
row=1,
col=1,
include_index=False,
include_column_header=True,
resize=False,
allow_formulas=True):
"""
Sets the values of a given DataFrame, anchoring its upper-left corner
at (row, col). (Default is row 1, column 1.)
:param worksheet: the gspread worksheet to set with content of DataFrame.
:param dataframe: the DataFrame.
:param include_index: if True, include the DataFrame's index as an
additional column. Defaults to False.
:param include_column_header: if True, add a header row before data with
column names. (If include_index is True, the index's name will be
used as its column's header.) Defaults to True.
:param resize: if True, changes the worksheet's size to match the shape
of the provided DataFrame. If False, worksheet will only be
resized as necessary to contain the DataFrame contents.
Defaults to False.
:param allow_formulas: if True, interprets `=foo` as a formula in
cell values; otherwise all text beginning with `=` is escaped
to avoid its interpretation as a formula. Defaults to True.
"""
# x_pos, y_pos refers to the position of data rows only,
# excluding any header rows in the google sheet.
# If header-related params are True, the values are adjusted
# to allow space for the headers.
y, x = dataframe.shape
if include_index:
col += 1
if include_column_header:
row += 1
if resize:
worksheet.resize(y + row - 1, x + col - 1)
else:
_resize_to_minimum(worksheet, y + row - 1, x + col - 1)
updates = []
if include_column_header:
for idx, val in enumerate(dataframe.columns):
updates.append((row - 1, col + idx, _cellrepr(val,
allow_formulas)))
if include_index:
for idx, val in enumerate(dataframe.index):
updates.append((idx + row, col - 1, _cellrepr(val,
allow_formulas)))
if include_column_header:
updates.append((row - 1, col - 1,
_cellrepr(dataframe.index.name, allow_formulas)))
for y_idx, value_row in enumerate(dataframe.values):
for x_idx, cell_value in enumerate(value_row):
updates.append(
(y_idx + row, x_idx + col, _cellrepr(cell_value,
allow_formulas)))
if not updates:
logger.debug("No updates to perform on worksheet.")
return
# Google limits cell update requests such that the submitted
# set of updates cannot contain 40,000 cells or more.
# Make update batches with less than 40,000 elements.
update_batches = [
updates[x:x + GOOGLE_SHEET_CELL_UPDATES_LIMIT]
for x in range(0, len(updates), GOOGLE_SHEET_CELL_UPDATES_LIMIT)
]
logger.debug(
"%d cell updates to send, will send %d batches of "
"%d cells maximum", len(updates), len(update_batches),
GOOGLE_SHEET_CELL_UPDATES_LIMIT)
for batch_num, update_batch in enumerate(update_batches):
batch_num += 1
logger.debug("Sending batch %d of cell updates", batch_num)
feed = Element('feed', {
'xmlns': ATOM_NS,
'xmlns:batch': BATCH_NS,
'xmlns:gs': SPREADSHEET_NS
})
id_elem = SubElement(feed, 'id')
id_elem.text = (finditem(lambda i: i.get('rel') == CELLS_FEED_REL,
worksheet._element.findall(
_ns('link'))).get('href'))
for rownum, colnum, input_value in update_batch:
code = 'R%sC%s' % (rownum, colnum)
entry = SubElement(feed, 'entry')
SubElement(entry, 'batch:id').text = code
SubElement(entry, 'batch:operation', {'type': 'update'})
SubElement(entry, 'id').text = id_elem.text + '/' + code
SubElement(
entry, 'link', {
'rel': 'edit',
'type': "application/atom+xml",
'href': id_elem.text + '/' + code
})
SubElement(entry, 'gs:cell', {
'row': str(rownum),
'col': str(colnum),
'inputValue': input_value
})
data = ElementTree.tostring(feed)
worksheet.client.post_cells(worksheet, data)
logger.debug("%d total update batches sent", len(update_batches))
def get_grid_props():
sheets = self.sheet.spreadsheet.fetch_sheet_metadata()['sheets']
return utils.finditem(
lambda x: x['properties']['sheetId'] == self.sheet.id, sheets
)['properties']['gridProperties']