def removeWorksheet(self, worksheet): url = _get_first( worksheet._element.findall(_ns_w3('link')), 'rel', 'edit').get('href') r = requests.delete(url, headers=self._token.getAuthorizationHeader()) _check_status(r)
def getRefreshToken(self, user_code): """Using the user token provided by visiting the url from getOauthUrl() returns a refresh token (a string). You should persist the token and use it on future Token initializations This method calls the Google API """ r = requests.post( 'https://www.googleapis.com/oauth2/v3/token', data={ 'code': user_code, 'client_id': self._client_id, 'client_secret': self._client_secret, 'redirect_uri': self._redirect_uri, 'grant_type': 'authorization_code', }) _check_status(r) # Returns a dictionary with the keys: # access_token # expires_in # refresh_token # 'token_type': 'Bearer' data = json.loads(r.content.decode()) return data['refresh_token']
def __init__(self, token, key, **kwargs): """Initialize a Spreadsheet The key is either the URL of your spreadsheet or the *key* part as shown below: https://docs.google.com/spreadsheets/d/{{key}}/edit Initialization involves calling the Google API. """ # did we get a URL? m = re.match(r'^(?:https?://)?(?:www\.)?' 'docs\.google\.com/spreadsheets/d/([^/]*)', key ) if m: key = m.group(1) key = urllib.parse.quote(key) url = ('https://spreadsheets.google.com/feeds/spreadsheets' '/private/full/{}'.format(key)) r = requests.get(url, headers=token.getAuthorizationHeader()) _check_status(r) element = ElementTree.fromstring(r.content.decode()) super().__init__(token=token, element=element, **kwargs)
def addWorksheet(self, title, rows=1, cols=1): """Adds a new worksheet to a spreadsheet. :param title: A title of a new worksheet. :param rows: Number of rows. :param cols: Number of columns. Returns a newly created :class:`worksheets <Worksheet>`. """ entry = Element('entry', { 'xmlns': 'http://www.w3.org/2005/Atom', 'xmlns:gs': 'http://schemas.google.com/spreadsheets/2006', }) SubElement(entry, 'title').text = title SubElement(entry, 'gs:rowCount').text = str(rows) SubElement(entry, 'gs:colCount').text = str(cols) key = self.getKey() url = ('https://spreadsheets.google.com/feeds/worksheets/{}' '/private/full'.format(urllib.parse.quote(key))) r = requests.post( url, data=ElementTree.tostring(entry), headers=self._token.getAuthorizationHeader( {'Content-Type': 'application/atom+xml'}) ) _check_status(r) element = ElementTree.fromstring(r.content.decode()) worksheet = Worksheet(self._token, element) return worksheet
def _getFeed(self): self_uri = _get_first( self._element.findall(_ns_w3('link')), 'rel', 'self').get('href') r = requests.get(self_uri, headers=self._token.getAuthorizationHeader()) _check_status(r) self._element = ElementTree.fromstring(r.content.decode()) return self._element
def asDataFrame(self, set_index=True, set_columns=True, values=False): """Returns a DataFrame representation of the sheet The index/column names are the row/column numbers, unless set_index or set_columns is set respectively Setting values=True returns the values of the cell, reather than a formula. Currently all values are returned as a string. """ cell_feed_uri = ( _get_first(self._element.findall(_ns_w3('link')), 'rel', 'http://schemas.google.com/spreadsheets/2006#cellsfeed') .get('href') ) r = requests.get( cell_feed_uri, headers=self._token.getAuthorizationHeader()) _check_status(r) cell_feed = ElementTree.fromstring(r.content.decode()) df = pd.DataFrame() for cell in cell_feed.findall(_ns_w3('entry')): gs = cell.find(_ns_sheet('cell')) if values: content = list(gs.itertext())[0] else: content = gs.get('inputValue') column = int(gs.get('col')) row = int(gs.get('row')) df.loc[row, column] = content # fill in blanks and order if len(df): df = df.reindex(columns=list(range(1, max(df.columns)+1))) df = df.reindex(list(range(1, max(df.index)+1))) if set_columns: df.columns = df.iloc[0] df = df.drop(1) if set_index and len(df): df.index = df[df.columns[0]] del df[df.columns[0]] if not set_columns: df.index.name = "" # we use the index name, not the columns name df.columns.name = "" return df
def getWorksheets(self): """Returns a list of Worksheet objects representing the worksheets of this Spreadsheet This involves calling the Google API. """ link = self._element.find(_ns_w3('link')) assert link.get('rel') == ( 'http://schemas.google.com/spreadsheets/2006#worksheetsfeed') r = requests.get( link.get('href'), headers=self._token.getAuthorizationHeader()) _check_status(r) e = ElementTree.fromstring(r.content.decode()) return [Worksheet(self._token, a) for a in e.findall(_ns_w3('entry'))]
def _addCells(self, cells): """Updates the referenced cells. *cells* is a list of tuples: (row, col, content) """ if len(cells) == 0: return feed = Element('feed', { 'xmlns': 'http://www.w3.org/2005/Atom', 'xmlns:batch': 'http://schemas.google.com/gdata/batch', 'xmlns:gs': 'http://schemas.google.com/spreadsheets/2006', }) id_elem = SubElement(feed, 'id') id_elem.text = ( _get_first(self._element.findall(_ns_w3('link')), 'rel', 'http://schemas.google.com/spreadsheets/2006#cellsfeed') .get('href') ) def add_entry(feed, row, col, content): code = 'R{}C{}'.format(row, col) 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(row), 'col': str(col), 'inputValue': content}) for row, col, content in cells: add_entry(feed, row, col, content) data = ElementTree.tostring(feed) r = requests.post( id_elem.text + '/batch', data=data, headers=self._token.getAuthorizationHeader({ 'Content-Type': 'application/atom+xml', 'If-Match': '*'})) _check_status(r)
def _refreshToken(self): """Gets a new access token. """ request_time = datetime.datetime.utcnow() r = requests.post( 'https://www.googleapis.com/oauth2/v3/token', data={ 'refresh_token': self._refresh_token, 'client_id': self._client._client_id, 'client_secret': self._client._client_secret, 'grant_type': 'refresh_token', }) _check_status(r) # We have a dictionary with the keys # access_token # expires_in # 'token_type': 'Bearer' data = json.loads(r.content.decode()) self._access_token = data['access_token'] self._setExpiresTime(request_time, data['expires_in'])
def _resize(self, feed, rows=None, cols=None): if cols is None and rows is None: return edit_uri = _get_first( feed.findall(_ns_w3('link')), 'rel', 'edit').get('href') if cols is not None: feed.find(_ns_sheet('colCount')).text = str(cols) if rows is not None: feed.find(_ns_sheet('rowCount')).text = str(rows) if (int(feed.find(_ns_sheet('colCount')).text) * int(feed.find(_ns_sheet('rowCount')).text) > 2000000): raise PGSheetsValueError( "No sheet may be more than 2000000 cells large" ) r = requests.put( edit_uri, data=ElementTree.tostring(feed), headers=self._token.getAuthorizationHeader( {'content-type': 'application/atom+xml'})) _check_status(r)