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']