def fetch_authorization_row(email):
CLIENT_EMAIL = app.config['GOOGLE_CLIENT_EMAIL']
PRIVATE_KEY = app.config['GOOGLE_PRIVATE_KEY']
SCOPE = "https://spreadsheets.google.com/feeds/"
credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY, SCOPE)
token = OAuth2TokenFromCredentials(credentials)
client = SpreadsheetsClient()
token.authorize(client)
# Load worksheet with auth info
spreadsheet_id = app.config['GOOGLE_SPREADSHEET_ID']
worksheets = client.get_worksheets(spreadsheet_id)
worksheet = worksheets.entry[0]
# worksheet.id.text takes the form of a full url including the spreadsheet, while
# we only need the last part of that
id_parts = worksheet.id.text.split('/')
worksheet_id = id_parts[len(id_parts) - 1]
list_feed = client.get_list_feed(spreadsheet_id, worksheet_id)
rows = [row.to_dict() for row in list_feed.entry]
user_auth_row = None
for row in rows:
if row['email'] == email:
user_auth_row = row
break
return user_auth_row
def getSpreadsheetsClient():
client = SpreadsheetsClient(source='odenkiapi')
access_token = loadAccessToken()
if access_token is not None:
assert isinstance(access_token, OAuthHmacToken)
client.auth_token = access_token
return client
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._client.client_login(
google_credentials.USERNAME,
google_credentials.PASSWORD,
'katholiekkwartetten'
)
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key,
self._google_worksheet_id
).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
def fetch_authorization_row(email):
CLIENT_EMAIL = app.config['GOOGLE_CLIENT_EMAIL']
PRIVATE_KEY = app.config['GOOGLE_PRIVATE_KEY']
SCOPE = "https://spreadsheets.google.com/feeds/"
credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY,
SCOPE)
token = OAuth2TokenFromCredentials(credentials)
client = SpreadsheetsClient()
token.authorize(client)
# Load worksheet with auth info
spreadsheet_id = app.config['GOOGLE_SPREADSHEET_ID']
worksheets = client.get_worksheets(spreadsheet_id)
worksheet = worksheets.entry[0]
# worksheet.id.text takes the form of a full url including the spreadsheet, while
# we only need the last part of that
id_parts = worksheet.id.text.split('/')
worksheet_id = id_parts[len(id_parts) - 1]
list_feed = client.get_list_feed(spreadsheet_id, worksheet_id)
rows = [row.to_dict() for row in list_feed.entry]
user_auth_row = None
for row in rows:
if row['email'] == email:
user_auth_row = row
break
return user_auth_row
def getSpreadsheetsClient():
client = SpreadsheetsClient(source='odenkiapi')
access_token = loadAccessToken()
if access_token is not None:
assert isinstance(access_token, OAuthHmacToken)
client.auth_token = access_token
return client
def getSpreadsheetsClient(self):
client = SpreadsheetsClient()
access_token = self.loadAccessToken()
if access_token is not None:
assert isinstance(access_token, OAuthHmacToken)
OAuthHmacToken
client.auth_token = access_token
return client
def getSpreadsheetsClient(self):
client = SpreadsheetsClient()
access_token = self.loadAccessToken()
if access_token is not None:
assert isinstance(access_token, OAuthHmacToken)
OAuthHmacToken
client.auth_token = access_token
return client
def __init__(self, google_spreadsheet_key, google_worksheet_id):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._client.client_login(google_credentials.USERNAME,
google_credentials.PASSWORD,
'katholiekkwartetten')
self.table = []
self.sync_table()
def __init__(self,
google_spreadsheet_key,
google_worksheet_id,
oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
self.sync_table()
def __init__(self, username=None, password=None, spreadsheet_key=None, source="SpreadSheetDBConnection"):
self._username = username
self._password = password
self._spreadsheet_key = spreadsheet_key
self._source = source
self._is_connected = False
self._worksheets = []
self._client = SpreadsheetsClient()
def __init__(self, credentials, sheet_name, Alt, BT, Miners): self.credentials = credentials self.gd_client = SpreadsheetsClient() self.spreadsheet_name = sheet_name self.Alt = Alt self.BT = BT self.Miners=Miners auth2token = gauth.OAuth2TokenFromCredentials(self.credentials) auth2token.authorize(self.gd_client)
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
self.sync_table()
def __init__(self, *args, **kwargs):
if len(kwargs) == 0:
if len(args) == 1:
config_path = args[0]
else:
config_path = CONFIG_PATH
print 'loading config from %s...' % config_path
with open(config_path, 'r') as fp:
self.config = json.load(fp)
else:
self.config = kwargs
if 'oauth2_key_file' not in self.config:
raise Exception('oauth2_key_file is not given')
if 'client_email' not in self.config:
raise Exception('client_email is not given')
self.default_spreadsheet_key = self.config.get(
'default_spreadsheet_key', None)
self.default_worksheet_id = self.config.get('default_worksheet_id',
None)
if _use_old_oauth2client:
# use .pem
key_file_path = self.config['oauth2_key_file']
if key_file_path[-4:] == '.p12':
key_file_path = key_file_path.replace('.p12', '.pem')
with open(key_file_path, 'rb') as f:
private_key = f.read()
self.credentials = SignedJwtAssertionCredentials(
self.config['client_email'],
private_key,
scope=["https://spreadsheets.google.com/feeds"])
else:
# use .p12
self.credentials = ServiceAccountCredentials.from_p12_keyfile(
self.config['client_email'],
self.config['oauth2_key_file'],
scopes=['https://spreadsheets.google.com/feeds'])
self.client = SpreadsheetsClient()
self.auth_token = OAuth2TokenFromCredentials(self.credentials)
self.auth_token.authorize(self.client)
def __init__(self, google_spreadsheet_key, google_worksheet_id):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._client.client_login(
google_credentials.USERNAME,
google_credentials.PASSWORD,
'katholiekkwartetten'
)
self.table = []
self.sync_table()
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
# somewhere read that following line help solving "ParseError: no element found: line 1, column 0" error,
# in atom/core.py in atom/core.py line 520, but no
self._client.additional_headers = {
'Authorization': 'Bearer %s' % oauth_decorator.credentials.access_token,
}
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._client.client_login(google_credentials.USERNAME,
google_credentials.PASSWORD,
'katholiekkwartetten')
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key, self._google_worksheet_id).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
def _service(self, http=None):
if not (http or self._oauth2token or self._decorator):
raise GoogleSpreadsheetsError('please set oauth2 credentials'
' or oauth2 decorator')
if http:
token = OAuth2BearerToken(http.request.credentials)
elif self._decorator:
token = OAuth2BearerToken(self._decorator.credentials)
else:
token = self._oauth2token
try:
return SpreadsheetsClient(auth_token=token)
except Unauthorized as e:
raise AccessTokenRefreshError(e.message)
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key,
self._google_worksheet_id
).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self,
google_spreadsheet_key,
google_worksheet_id,
oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key, self._google_worksheet_id).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
class SpreadSheetDBConnection(object):
"""
Spreadsheet wrapper class
"""
default_connection = None
def __init__(self, username=None, password=None, spreadsheet_key=None, source="SpreadSheetDBConnection"):
self._username = username
self._password = password
self._spreadsheet_key = spreadsheet_key
self._source = source
self._is_connected = False
self._worksheets = []
self._client = SpreadsheetsClient()
#self._tables = []
def connect(self, username=None, password=None, spreadsheet_key=None, source="SpreadSheetDBConnection"):
"""
make a connection with google api, and load spreadsheet information.
"""
if self._is_connected:
raise SpreadSheetDBError()
if spreadsheet_key:
self._spreadsheet_key = spreadsheet_key
if username:
self._username = username
if password:
self._password = password
if source:
self._source = source
if not self._spreadsheet_key or not self._username or not self._password or not self._source:
raise SpreadSheetDBError()
self._client.client_login(self._username, self._password, source=self._source)
self._is_connected = True
self.select_spreadsheet(self._spreadsheet_key)
def select_spreadsheet(self, spreadsheet_key):
"""
select spreadsheet
"""
if not self._is_connected:
raise SpreadSheetDBError()
self._worksheets = {}
if spreadsheet_key:
self._spreadsheet_key = spreadsheet_key
self._refresh_worksheets_info()
def _refresh_worksheets_info(self):
if not self._is_connected:
raise SpreadSheetDBError()
if not self._spreadsheet_key:
raise SpreadSheetDBError()
worksheets = self._client.get_worksheets(self._spreadsheet_key)
for worksheet in worksheets.entry:
self._worksheets[worksheet.title.text] = {'key': worksheet.get_worksheet_id(), 'row_count': worksheet.row_count.text,
'col_count': worksheet.col_count.text}
def create_list(self, model):
"""
make a new worksheet for model
"""
if not self._is_connected:
raise SpreadSheetDBError()
list_name = getattr(model, "worksheet_name")()
if not list_name:
raise SpreadSheetDBError()
fields = []
for index, value in model.__dict__.iteritems():
if isinstance(value, Field):
if value.field_name:
fields.append(value.field_name)
else:
fields.append(index)
if not list_name in self._worksheets.iterkeys():
worksheet = self._client.add_worksheet(self._spreadsheet_key, list_name, 1, len(fields))
self._worksheets[worksheet.title.text] = {'key': worksheet.get_worksheet_id(), 'row_count': worksheet.row_count.text,
'col_count': worksheet.col_count.text}
cell_feed = gdata.spreadsheets.data.build_batch_cells_update(self._spreadsheet_key, self._worksheets[list_name]['key'])
col = 1
for index in fields:
cell_feed.add_set_cell(1, col, index)
col += 1
self._client.batch(cell_feed, force=True)
def add_entry(self, list_name, fields):
"""
add new entry to worksheet
"""
if not self._is_connected:
raise SpreadSheetDBError()
try:
current_worksheet = self._worksheets[list_name]
except KeyError:
raise SpreadSheetNotExists()
inserting_row = ListEntry()
for key, val in fields.iteritems():
inserting_row.set_value(key, val)
ret = self._client.add_list_entry(inserting_row, self._spreadsheet_key, current_worksheet['key'])
return ret
def update_entry(self, entry, data):
"""
update entry
"""
for key, val in data.iteritems():
entry.set_value(key, val)
self._client.update(entry)
def delete_entry(self, entry):
"""
delete entry
"""
self._client.delete(entry)
def all_entries(self, list_name):
"""
load all entries from worksheet.
"""
list_feed = self._client.get_list_feed(self._spreadsheet_key, self._worksheets[list_name]['key'])
return list_feed.entry
def filtered_entries(self, list_name, sq=None, order_by=None, reverse=None):
"""
load filtered entries
https://developers.google.com/google-apps/spreadsheets/?hl=uk-UA&csw=1#sending_a_structured_query_for_rows
"""
query = ListQuery(order_by=order_by, reverse=reverse, sq=sq)
list_feed = self._client.get_list_feed(self._spreadsheet_key, self._worksheets[list_name]['key'], query=query)
return list_feed.entry
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
# somewhere read that following line help solving "ParseError: no element found: line 1, column 0" error,
# in atom/core.py in atom/core.py line 520, but no
self._client.additional_headers = {
'Authorization': 'Bearer %s' % oauth_decorator.credentials.access_token,
}
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
# self.sync_table() must be done explicitly!
def sync_table(self):
del self.table[:] # table = [] would break the references!
for attempt in range(5):
try:
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key,
self._google_worksheet_id
).entry
except httplib.HTTPException as e:
logging.info(e.message)
time.sleep(0.5)
continue
else:
break
else:
# we failed all the attempts - deal with the consequences.
logging.info("Retried downloading the spreadsheet data three times in vain.")
return self.table
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
return self.table
def update_fields(self, updates, id_name):
"""
@param id_name: the name of the field by which the rows can be queried
@param updates: a dict of dicts. The index is the value of the id_name field. Each dict contains the fields of
a row that must be updated.
@return: nothing
"""
# update the in-memory table
for entry in self.table:
id = entry[id_name]
if id in updates:
for label in updates[id]:
entry[label] = updates[id][label]
# update the spreadsheet
for entry in self._rows:
id = entry.get_value(id_name)
if id and id in updates:
entry.from_dict(export_for_spreadsheet(updates[id]))
for attempt in range(5):
try:
self._client.update(entry)
except httplib.HTTPException as e:
logging.info(e.message)
time.sleep(0.5)
continue
else:
break
else:
# we failed all the attempts - deal with the consequences.
logging.info("Retried updating the spreadsheet data three times in vain.")
return
logging.info('On index updated row with ' + id_name + '=' + id)
if updates:
self.sync_table()
def add_rows(self, additions, id_name):
"""
@param additions: a dict of dicts. Each dict contains the fields of a row that must be added.
@return:
"""
# update the in-memory table
for id in additions:
self.table.append(additions[id])
# update the spreadsheet
for id in additions:
additions[id][id_name] = id # to make sure this field is also filled in if it wasn't explicitly in the dict!
entry = ListEntry()
entry.from_dict(export_for_spreadsheet(additions[id]))
for attempt in range(5):
try:
self._client.add_list_entry(
entry,
self._google_spreadsheet_key,
google_spreadsheet_first_worksheet_id
)
except httplib.HTTPException as e:
logging.info(e.message)
time.sleep(0.5)
continue
else:
break
else:
# we failed all the attempts - deal with the consequences.
logging.info("Retried adding rows to the spreadsheet data three times in vain.")
return
logging.info('On index added row with id = ' + id)
if additions:
self.sync_table()
def __init__(self, basedir=None, **kwargs):
super(SpreadsheetFactory, self).__init__(basedir, **kwargs)
self.bearerToken = OAuth2BearerToken(self.credentials)
self.ssc = SpreadsheetsClient(auth_token=self.bearerToken)
class SpreadsheetFactory(DAClient):
def __init__(self, basedir=None, **kwargs):
super(SpreadsheetFactory, self).__init__(basedir, **kwargs)
self.bearerToken = OAuth2BearerToken(self.credentials)
self.ssc = SpreadsheetsClient(auth_token=self.bearerToken)
def __call__(self, **kwargs):
'''
sheetName: search sheet
parentId: search sheet from parent
sheetId: ignore sheetName and parentId when set this
'''
self.sheetName = kwargs.get('sheetName', None)
self.parentId = kwargs.get('parentId', None)
self.sheetId = kwargs.get('sheetId', None)
self.worksheetId = None
self.set_activesheet()
return self
def set_activesheet(self):
if self.sheetId is None:
q = ["mimeType='%s'" % SPREADSHEET_TYPE]
if self.sheetName: q.append("title contains '%s'" % self.sheetName)
if self.parentId: q.append("'%s' in parents" % self.parentId)
q.append('explicitlyTrashed=False')
entries = self.execQuery(' and '.join(q), noprint=True)
if not len(entries['items']):
self.sheetId = None
self.worksheetId = None
sys.stderr.write('not found: %s\n' % q)
return
self.sheetId = entries['items'][0]['id']
for ws in self.worksheets():
self.worksheetId = ws.get_worksheet_id()
break
else:
self.worksheetId = None
def sheet(self, sheetId=None):
if sheetId is None: sheetId = self.sheetId
return self.service.files().get(fileId=sheetId).execute()
def worksheets(self, sheetId=None):
'''
sheetId:
'''
if sheetId is None: sheetId = self.sheetId
return self.ssc.get_worksheets(sheetId).entry
def cells(self, sheetId=None, worksheetId=None):
'''
sheetId:
worksheetId:
'''
if sheetId is None: sheetId = self.sheetId
if worksheetId is None: worksheetId = self.worksheetId
return self.ssc.get_cells(sheetId, worksheetId).entry
def updateCell(self, row, col, val, sheetId=None, worksheetId=None):
'''
packaged version of gdata-2.0.18 does NOT contain update_cell()
(pip install gdata) or (easy_install gdata)
version (2013-07-12) some functions are added after gdata-2.0.18
https://code.google.com/p/gdata-python-client/source/list
please clone new version (python setup.py install)
hg clone https://code.google.com/p/gdata-python-client/
and bug is reported: http://stackoverflow.com/questions/9940578/
add parameter force=True to fix it
'''
if sheetId is None: sheetId = self.sheetId
if worksheetId is None: worksheetId = self.worksheetId
return self.ssc.update_cell(sheetId,
worksheetId,
row,
col,
val,
force=True)
def createSpreadsheet(self,
sheetName,
description=None,
parentId=None,
csv=None,
rows=1000,
cols=26):
'''
sheetName: file name for Google Drive
description: description for Google Drive (default same as sheetName)
parentId: create into parent folder (default 'root')
csv: string (default None: empty sheet)
rows: int (default 1000)
cols: int (default 26)
'''
body = {
'title': sheetName,
'mimeType': 'text/csv', # to be converted
'description': description if description else sheetName
}
if parentId is None: parentId = 'root'
body['parents'] = [{'id': parentId}]
mbody = MediaInMemoryUpload(csv if csv else '\n'.join([',' * cols] *
rows),
mimetype='text/csv',
chunksize=256 * 1024,
resumable=False)
req = self.service.files().insert(body=body, media_body=mbody)
req.uri += '&convert=true'
fileObj = req.execute()
if fileObj is None: return (None, None)
self.sheetId = fileObj['id']
self.set_activesheet()
return (self.sheetId, fileObj)
class UserSpreadsheet:
def __init__(self, credentials, sheet_name, Alt, BT, Miners):
self.credentials = credentials
self.gd_client = SpreadsheetsClient()
self.spreadsheet_name = sheet_name
self.Alt = Alt
self.BT = BT
self.Miners=Miners
auth2token = gauth.OAuth2TokenFromCredentials(self.credentials)
auth2token.authorize(self.gd_client)
def create_spreadsheet(self):
from apiclient.discovery import build
service = build('drive', 'v2')
http = self.credentials.authorize(httplib2.Http())
body = {
'mimeType': 'application/vnd.google-apps.spreadsheet',
'title': self.spreadsheet_name,
}
file = service.files().insert(body=body).execute(http=http)
def Run(self):
num_coins = len(self.Alt.currency)
#query for spreadsheet
q = SpreadsheetQuery(title= self.spreadsheet_name,title_exact=True)
try:
self.gd_client.get_spreadsheets(query = q).entry[0].get_spreadsheet_key()
except:
return logging.error("Error: No ExcellCoin SpreadSheet")
self.spreadsheet_key = self.gd_client.get_spreadsheets(query = q).entry[0].get_spreadsheet_key()
#get the first row data to figure out what coins to update.
#if blank update all
cq = CellQuery(range='A1',return_empty=True)
cs = self.gd_client.GetCells(self.spreadsheet_key,'od6',q=cq)
coins = cs.entry[0].cell.input_value
if coins != '':
num_coins = len(coins.split())
cq = CellQuery(range=('R1C1:R%iC3'%(num_coins+13)),return_empty=True)
cs = self.gd_client.GetCells(self.spreadsheet_key,'od6',q=cq)
count = 1
cs.entry[count*3].cell.input_value='%s'%str(datetime.now())
cs.entry[count*3+1].cell.input_value='USD/BTC'
#update the bitcoin to usd rates
count+=1
for i,coin in enumerate(self.BT.currency):
cs.entry[3*count].cell.input_value=self.BT.currency[i]
cs.entry[3*count+1].cell.input_value='%.2f'%self.BT.price[i]
count+=1
#update the Altcoin to BTC rates
count+=1
cs.entry[3*count].cell.input_value='Altcoins'
cs.entry[3*count+1].cell.input_value='Exchange Rate'
cs.entry[3*count+2].cell.input_value='Volume in BTC'
count+=1
if coins == '':
for i,coin in enumerate(self.Alt.currency):
cs.entry[3*count].cell.input_value='%s'%coin
cs.entry[3*count+1].cell.input_value='%.8f'%self.Alt.price[i]
cs.entry[3*count+2].cell.input_value='%.2f'%self.Alt.volume[i]
count+=1
else:
for i,coin in enumerate(coins.split()):
try:
if coin in self.Alt.currency:
index = self.Alt.currency.index(coin)
cs.entry[3*count].cell.input_value=self.Alt.currency[index]
cs.entry[3*count+1].cell.input_value='%.8f'%self.Alt.price[index]
cs.entry[3*count+2].cell.input_value='%.2f'%self.Alt.volume[index]
count+=1
except:
logging.error("Error: unable to update coins%s"%coin)
count+=2
cs.entry[3*count].cell.input_value='MiningPool'
cs.entry[3*count+1].cell.input_value='Workers'
cs.entry[3*count+2].cell.input_value='HashRate'
count+=1
for Miners in self.Miners:
############ Hash Rate ###################
try:
if Miners != 'none':
site = Miners.split(',')[0]
nickname = Miners.split(',')[1]
mining_token = Miners.split(',')[2]
workers, hash_rate = gb.get_stats(site,mining_token)
cs.entry[3*count].cell.input_value=nickname
cs.entry[3*count+1].cell.input_value='%i'%int(workers)
cs.entry[3*count+2].cell.input_value='%i'%int(hash_rate)
count+=1
except:
logging.error("Error: unable to update hashrate %s"%Miners.split(',')[0])
count+=1
#send a batch job to update everything in spreadsheet at once
try:
objData = gdata.spreadsheets.data
batch = objData.BuildBatchCellsUpdate(self.spreadsheet_key, 'od6')
for cell in cs.entry:
batch.add_batch_entry(cell, cell.id.text, batch_id_string=cell.title.text, operation_string='update')
self.gd_client.batch(batch, force=True)
except:
return logging.error("Error: Unable to send batch operation")
class Spreadsheet:
def __init__(self, *args, **kwargs):
if len(kwargs) == 0:
if len(args) == 1:
config_path = args[0]
else:
config_path = CONFIG_PATH
print 'loading config from %s...' % config_path
with open(config_path, 'r') as fp:
self.config = json.load(fp)
else:
self.config = kwargs
if 'oauth2_key_file' not in self.config:
raise Exception('oauth2_key_file is not given')
if 'client_email' not in self.config:
raise Exception('client_email is not given')
self.default_spreadsheet_key = self.config.get(
'default_spreadsheet_key', None)
self.default_worksheet_id = self.config.get('default_worksheet_id',
None)
if _use_old_oauth2client:
# use .pem
key_file_path = self.config['oauth2_key_file']
if key_file_path[-4:] == '.p12':
key_file_path = key_file_path.replace('.p12', '.pem')
with open(key_file_path, 'rb') as f:
private_key = f.read()
self.credentials = SignedJwtAssertionCredentials(
self.config['client_email'],
private_key,
scope=["https://spreadsheets.google.com/feeds"])
else:
# use .p12
self.credentials = ServiceAccountCredentials.from_p12_keyfile(
self.config['client_email'],
self.config['oauth2_key_file'],
scopes=['https://spreadsheets.google.com/feeds'])
self.client = SpreadsheetsClient()
self.auth_token = OAuth2TokenFromCredentials(self.credentials)
self.auth_token.authorize(self.client)
def get_spreadsheet_infos(self):
infos = []
spreadsheets_feed = self.client.get_spreadsheets()
for spreadsheet in spreadsheets_feed.entry:
spreadsheet_key = spreadsheet.id.text.split('/')[-1]
infos.append({
'id': spreadsheet_key,
'title': spreadsheet.title.text,
'sheets': self.get_worksheet_infos(spreadsheet_key)
})
return infos
def get_worksheet_infos(self, spreadsheet_key=None):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
infos = []
worksheets_feed = self.client.get_worksheets(spreadsheet_key)
for worksheet_entry in worksheets_feed.entry:
worksheet_id = worksheet_entry.id.text.split('/')[-1]
worksheet_info = self.get_worksheet_info(spreadsheet_key,
worksheet_id)
infos.append(worksheet_info)
return infos
def get_worksheet_info(self, spreadsheet_key=None, worksheet_id=None):
try:
worksheet_entry = self.client.get_worksheet(
spreadsheet_key, worksheet_id)
if not worksheet_entry:
return None
return _strip_worksheet_entry(worksheet_entry)
except:
return None
def update_entry(self, entry):
if entry:
self.client.update(entry)
def iter_rows(self, spreadsheet_key=None, worksheet_id=None):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
list_feed = self.client.get_list_feed(spreadsheet_key, worksheet_id)
for entry in list_feed.entry:
yield entry.to_dict()
def add_header(self, spreadsheet_key=None, worksheet_id=None, header=[]):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
cell_query = CellQuery(min_row=1,
max_row=1,
min_col=1,
max_col=len(header),
return_empty=True)
cells = self.client.GetCells(spreadsheet_key,
worksheet_id,
q=cell_query)
for i, name in enumerate(header):
cell_entry = cells.entry[i]
cell_entry.cell.input_value = name
self.client.update(cell_entry) # This is the
# add_list_entry
def add_row(self, spreadsheet_key=None, worksheet_id=None, values={}):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
if len(values) == 0:
return
list_entry = ListEntry()
list_entry.from_dict(
{key: _convert(value)
for key, value in values.iteritems()})
self.client.add_list_entry(list_entry, spreadsheet_key, worksheet_id)
def add_worksheet(self,
spreadsheet_key=None,
title=None,
rows=1,
cols=1): # worksheet_id, values):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
if not title:
raise Exception('title is not given')
worksheet_entry = self.client.add_worksheet(spreadsheet_key, title,
rows, cols)
return _strip_worksheet_entry(worksheet_entry)
def delete_worksheet(self, spreadsheet_key, worksheet_id):
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
if worksheet_id is None:
raise Exception('worksheet_id is not given')
worksheet_entry = self.client.get_worksheet(spreadsheet_key,
worksheet_id)
self.client.delete(worksheet_entry)
