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)
