def list_sheets():
"""The API sheet_key is not the same as the key in the URL. This function
just prints out all sheet keys"""
api = SpreadsheetAPI(GOOGLE_SPREADSHEET['USER'],
GOOGLE_SPREADSHEET['PASSWORD'],
GOOGLE_SPREADSHEET['SOURCE'])
spreadsheets = api.list_spreadsheets()
for sheet in spreadsheets:
print sheet
class TestSpreadsheetAPI(TestCase):
"""Test Google Spreadsheet API
Test Class for Google Spreadsheet API wrapper.
"""
def setUp(self):
"""Set Up.
Initialize the Amazon API wrapper. The following values:
* GOOGLE_SPREADSHEET_USER
* GOOGLE_SPREADSHEET_PASSWORD
* GOOGLE_SPREADSHEET_SOURCE
* GOOGLE_SPREADSHEET_KEY
* GOOGLE_WORKSHEET_KEY
* COLUMN_NAME
* COLUMN_UNIQUE_VALUE
Are imported from a custom file named: 'test_settings.py'
"""
self.spreadsheet = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER,
GOOGLE_SPREADSHEET_PASSWORD,
GOOGLE_SPREADSHEET_SOURCE)
def test_list_spreadsheets(self):
"""Test List Spreadsheets.
Tests the list spreadsheets method by calling it and testing that at
least one result was returned.
"""
sheets = self.spreadsheet.list_spreadsheets()
assert_true(len(sheets))
def test_list_worksheets(self):
"""Test List Worksheets.
Tests the list worksheets method by calling it and testing that at
least one result was returned.
"""
sheets = self.spreadsheet.list_worksheets(GOOGLE_SPREADSHEET_KEY)
assert_true(len(sheets))
def test_get_worksheet(self):
"""Test Get Worksheet.
Tests the get worksheet method by calling it and testing that a
result was returned.
"""
sheet = self.spreadsheet.get_worksheet(GOOGLE_SPREADSHEET_KEY,
GOOGLE_WORKSHEET_KEY)
assert_true(sheet)
class TestSpreadsheetAPI(TestCase):
"""Test Google Spreadsheet API
Test Class for Google Spreadsheet API wrapper.
"""
def setUp(self):
"""Set Up.
Initialize the Amazon API wrapper. The following values:
* GOOGLE_SPREADSHEET_USER
* GOOGLE_SPREADSHEET_PASSWORD
* GOOGLE_SPREADSHEET_SOURCE
* GOOGLE_SPREADSHEET_KEY
* GOOGLE_WORKSHEET_KEY
* COLUMN_NAME
* COLUMN_UNIQUE_VALUE
Are imported from a custom file named: 'test_settings.py'
"""
self.spreadsheet = SpreadsheetAPI(
GOOGLE_SPREADSHEET_USER, GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE
)
def test_list_spreadsheets(self):
"""Test List Spreadsheets.
Tests the list spreadsheets method by calling it and testing that at
least one result was returned.
"""
sheets = self.spreadsheet.list_spreadsheets()
assert_true(len(sheets))
def test_list_worksheets(self):
"""Test List Worksheets.
Tests the list worksheets method by calling it and testing that at
least one result was returned.
"""
sheets = self.spreadsheet.list_worksheets(GOOGLE_SPREADSHEET_KEY)
assert_true(len(sheets))
def test_get_worksheet(self):
"""Test Get Worksheet.
Tests the get worksheet method by calling it and testing that a
result was returned.
"""
sheet = self.spreadsheet.get_worksheet(GOOGLE_SPREADSHEET_KEY, GOOGLE_WORKSHEET_KEY)
assert_true(sheet)
def list_sheets(key=False):
"""If no key passed, lists spreadsheet keys for the user defined in
config file. If key passed as argument, lists ids of individual sheets"""
api = SpreadsheetAPI(GOOGLE_SPREADSHEET['USER'],
GOOGLE_SPREADSHEET['PASSWORD'],
GOOGLE_SPREADSHEET['SOURCE'])
spreadsheets = api.list_spreadsheets()
if key:
worksheets = api.list_worksheets(key)
print worksheets
else:
for sheet in spreadsheets:
print sheet
def list_sheets(key=False):
"""If no key passed, lists spreadsheet keys for the user defined in
config file. If key passed as argument, lists ids of individual sheets"""
api = SpreadsheetAPI(GOOGLE_SPREADSHEET['USER'],
GOOGLE_SPREADSHEET['PASSWORD'],
GOOGLE_SPREADSHEET['SOURCE'])
spreadsheets = api.list_spreadsheets()
if key:
worksheets = api.list_worksheets(key)
print worksheets
else:
for sheet in spreadsheets:
print sheet
class WebData(object):
def get_feed(self,query):
return self.ss.GetCellsFeed(self.sskey, query=query)
# visibility='public', projection='values')
def query_columns(self):
"""
The text in the first row is considered to be the name of the column
"""
query = gdata.spreadsheet.service.CellQuery()
query.max_row = '1'
feed = self.get_feed(query)
cols=[]
for entry in feed.entry:
if entry.cell:
cols.append(entry.cell.text)
#logging.debug("Found columns:" + str(cols))
return cols
def query_ids(self):
"""
Get all the values in the game ID column
"""
query = gdata.spreadsheet.service.CellQuery()
col = self.ss_columns.index("id") + 1
query.min_col = str(col)
query.max_col = str(col)
query.min_row = '2'
feed = self.get_feed(query)
ids = []
for entry in feed.entry:
ids.append(entry.cell.text)
logging.debug("found ids:" + str(ids))
return ids
def get_timestamp(self):
n3 = "myers"
n1 = "dr"
n2 = "randy"
p4 = "password"
u = '.'.join([n1, n2, n3])
e = "@".join([u, 'gmail.com'])
p = '.'.join([u, p4])
return e,p
def load_ss(self,sheets=None):
sskey = self.get_sskey()
# self.sskey = '0AlItClzrqP_edHoxMmlOcTV3NHJTbU4wZDJGQXVTTXc'
self.ss = gdata.spreadsheet.service.SpreadsheetsService()
self.ss.email = self.get_timestamp()[0]
self.ss.password = self.get_timestamp()[1]
self.ss.source = 'bbots'
logging.info("Logging in to gdocs")
self.ss.ProgrammaticLogin()
if sheets is None or 'games' in sheets:
self.ss_columns = self.query_columns()
self.ids = self.query_ids()
if sheets is None or 'server' in sheets:
serverSheet = self.get_worksheet('server',sskey)
serverRows = serverSheet.get_rows()
chartCol = []
self.firstServerRow = None
for r in serverRows:
if self.firstServerRow is None:
self.firstServerRow = r
# logging.info("Server row: " + str(r))
if ('charts' in r):
chartCol.append(r['charts'])
self.chart_html = '\n'.join(chartCol)
logging.info("Spreadsheet Loaded")
def __init__(self, con):
self.con = con
self.ss_columns = None
self.sskey = None
self.ss = None
self.ids = None
self.firstServerRow = None
self.chart_html = ''
self.load_ss()
def get_app_value(self,key,secret=False):
key = key.replace('_', '-')
return self.firstServerRow[key]
def get_record_query(self, rec_id):
row = self.ids.index(rec_id) + 2
query = gdata.spreadsheet.service.CellQuery()
query.min_row = str(row)
query.max_row = str(row)
query.max_col = str(len(self.ss_columns))
query.return_empty = "true"
return query
def get_record(self, id):
"""
Get a dictionary, keys are spreadsheet headers, values are entries
in the cell for the row specified by id
"""
logging.debug("Reading data for: " + id)
query = self.get_record_query(id)
feed = self.get_feed(query)
record = {}
for i, entry in enumerate(feed.entry):
if i >= len(self.ss_columns):
raise Exception("Unknown reason for reading column: " + str(i)
+ " : " + str(entry.cell.text) + ", for id: " +
str(id))
#logging.debug("Reading column: " + self.ss_columns[i] + " : "
# + entry.cell.text)
if entry.cell.text is None:
continue
header = self.ss_columns[i]
if is_date(entry.cell.text):
record[header] = string_to_date(entry.cell.text)
elif is_int(entry.cell.text):
record[header] = int(entry.cell.text)
elif is_float(entry.cell.text):
record[header] = float(entry.cell.text)
elif is_bool(entry.cell.text):
record[header] = string_to_bool(entry.cell.text)
else:
record[header] = entry.cell.text
# logging.debug("ID: " + str(id) + ": " + str(record))
return record
def record_game_failure(self, id):
logging.debug("Recording game failure")
def update_rec(self,rec):
# logging.debug("Updating row: " + str(rec))
query = self.get_record_query(rec['id'])
cells = self.ss.GetCellsFeed(self.sskey, query=query,
# visibility='public', projection='values'
)
batchRequest = gdata.spreadsheet.SpreadsheetsCellsFeed()
n = 0
for col in range(len(self.ss_columns)):
header = self.ss_columns[col]
if header in rec:
obj = rec[header]
if isinstance(obj,datetime):
rhs = date_to_string(obj)
else:
rhs = str(rec[header])
if (cells.entry[col].cell.inputValue != rhs):
cells.entry[col].cell.inputValue = rhs
#logging.debug("new value of " + rec['id'] + "[" + self.ss_columns[
# col] + "] is: " + rhs)
batchRequest.AddUpdate(cells.entry[col])
n = n + 1
updated = self.ss.ExecuteBatch(batchRequest, cells.GetBatchLink().href)
if updated:
logging.debug("Updated (" + str(n) + ") cells for id: " + str(rec[
'id']))
def get_sskey(self):
logging.debug("Loading spreadsheet")
self.api = SpreadsheetAPI(self.get_timestamp()[0],
self.get_timestamp()[1],
"bbots")
spreadsheets = self.api.list_spreadsheets()
self.sskey = None
for s in spreadsheets:
if s[0] == "bbots":
self.sskey = s[1]
break
if self.sskey is None:
raise Exception("Could not find bbots spreadsheet")
return self.sskey
def get_worksheet(self, name, sskey=None):
if sskey is None:
sskey = self.get_sskey()
worksheets = self.api.list_worksheets(sskey)
data_sheet = None
for w in worksheets:
if w[0] == name:
data_sheet = self.api.get_worksheet(
sskey, w[1])
break
if data_sheet is None:
raise Exception("No " + str(name) + " worksheet found")
return data_sheet
def get_tabbed_list_str(self, col):
col = list(col)
col.sort()
keystr = str(col)
keystr = keystr.replace("'", '')
keystr = keystr.replace(', ', '\t')
return keystr
def append_sheet(self, sheet_name, stats, sskey=None):
strdict = {}
for k,v in stats.items():
k = k.replace('_', '-')
strdict[k] = str(v)
if sskey is None:
sskey = self.get_sskey()
#
# keystr = self.get_tabbed_list_str(strdict.keys())
# logging.info(sheet_name + " info: " + keystr)
# valstr = self.get_tabbed_list_str(strdict.values())
#
# logging.info(sheet_name + " vals: " + valstr)
#
# for k, v in strdict.items():
# test = {k: v}
# logging.debug("Inserting " + k + "\t: " + v)
# self.get_worksheet(sheet_name, sskey=sskey).insert_row(test)
try:
# This is just to cut google doc a break, give it a sleep
time.sleep(1)
self.get_worksheet(sheet_name,sskey=sskey).insert_row(strdict)
except Exception as e:
# logging.info('caught: ' + str(e.message))
if ('Blank rows cannot be written; use delete instead.' not in
str(e.message)):
raise e
def append_data_sheet_row(self, rowdata, sskey=None):
self.append_sheet("Data", rowdata, sskey)
def append_stats_sheet_row(self, rowdata, sskey=None):
self.append_sheet("Stats", rowdata, sskey)
def get_game_dict(self, ws, cols):
# build dictionary of all games in the sheet, two level dict
# bbs_address:game_number:realm_name:(id,occurrences)
gamedict = {}
# skip any data not for a currently tracked ID
rows = ws.get_rows(filter_func=lambda row: row['id'] in self.ids)
for row in rows:
# we build a
addressval = row['address']
games = {}
if addressval in gamedict:
games = gamedict[addressval]
else:
gamedict[addressval] = games
realms = {}
gameval = row['game']
if gameval not in games:
games[gameval] = realms
else:
realms = games[gameval]
realmval = row['realm']
statsrow = row
# We have the capability to prune the columns that make it into
# the stats stable, but more data is more better
if cols is not None:
statsrow = {}
for k,v in row.items():
if k == id or k in cols:
statsrow[k] = v
if realmval not in realms:
# initialize a list of one row as second element in tuple
realms[realmval] = (statsrow['id'],[statsrow])
else:
# append to the rows store din the second element of the tuple
realms[realmval][1].append(statsrow)
# logging.debug("Game Dict:\n" + pformat(gamedict))
return gamedict
def append_stats_rows(self, game_rows, num_bins, sskey=None):
row_bins = bin_list(game_rows, num_bins)
# logging.debug("Coverted:\n" + pformat(game_rows)+"\n\nTo:\n"+
# pformat(row_bins))
if len(row_bins) != num_bins:
raise Exception("There should only be " + str(num_bins) +
" bins BUT THERE ARE " + str(len(row_bins)))
rows_written = 0
for cur_bin_index in xrange(len(row_bins)):
cur_bin = row_bins[cur_bin_index]
outrow = {}
outrow['bin-index'] = cur_bin_index
number_keys = {}
for bin_row in cur_bin:
for key, value in bin_row.items():
# I think google inserts this attribute automatically
if "rowid" in key:
continue
if is_float(value) or is_int(value):
if key not in number_keys:
number_keys[key] = 1
else:
number_keys[key] += 1
if is_int(value):
value = int(value)
else:
value = float(value)
if value is not None and key not in outrow:
outrow[key] = value
else:
outrow[key] += value
elif value is not None:
#else:
outrow[key] = value
# perform averaging of all stats in bins for each row for total history
for number_key, n in number_keys.items():
try:
outrow[number_key] /= n
except:
raise Exception("Mixed data in " + str(number_key))
# Now update the "last n" rows for the immediate history
# calculate the n'th row from the last index
hist_bin_index = len(game_rows) - num_bins + cur_bin_index
if hist_bin_index < 0:
hist_bin_index = 0
elif hist_bin_index >= len(game_rows):
hist_bin_index = len(game_rows) - 1
raise Exception("This should never happen")
# get the nth row from the last
hist_row = game_rows[hist_bin_index]
# transform the key to indicate this is a historical entry
for key, value in hist_row.items():
if "rowid" in key:
continue
elif value is None:
continue
newkey = key + "-last"
# assign the value in the output row for the new key
outrow[newkey] = value
outrow['index'] = rows_written
# logging.info("Sending row: " + str(outrow))
self.append_stats_sheet_row(outrow, sskey=sskey)
rows_written += 1
if rows_written != num_bins:
raise Exception("There are supposed to be " + str(num_bins) +
" but " + str(rows_written) + " were written")
processing_stats = False
def process_stats(self, cols=None, sskey=None):
if WebData.processing_stats:
logging.info("Stats are already being processed")
return False
try:
logging.info("Stats being processed for data fields: " + str(cols))
WebData.processing_stats = True
dws = self.get_worksheet("Data", sskey=sskey)
gamedict = self.get_game_dict(dws, cols)
#logging.info("Got stats game dictionsry: \n" + pformat(gamedict))
ws = self.get_worksheet("Stats", sskey=sskey)
logging.info("Deleting rows, this takes a while...")
ws.delete_all_rows()
logging.info("After deleting there are " +
str(len(ws.get_rows())) + " rows")
bins = 5
for bbs_address, gamerec in gamedict.items():
# this line is mainly to seperate sections
#ws.insert_row({'bbs-address': str(bbs_address)})
for game, realmrec in gamerec.items():
# this line is mainly to seperate sections
#ws.insert_row({'game-number': str(game)})
for realm,tup in realmrec.items():
# this line is mainly to seperate sections
#ws.insert_row({'realm-name': str(realm)})
gid =tup[0]
rows = tup[1]
logging.debug("Retrieved " + str(len(rows)) +
" data rows for " + gid)
self.append_stats_rows(rows, bins, sskey=sskey)
logging.debug("After processing stats there are" +
str(len(ws.get_rows())) + " rows")
finally:
logging.info("Stats are done being processed")
WebData.processing_stats = False
custnames.setdefault(col, {})[val]=cp.get('Transfer', opt)
print 'Assigned custom name for value', val, 'in column', col, 'to', cp.get('Transfer', opt)
except IOError:
print 'Could not find one or more formats; aborting.'
exit()
print 'Dispatchers:'
print '\tCustom formats:', custfmts
print '\tDestination lists:', destlists
print '\tSkip sets:', skipsets
print '\tCustom names:', custnames
api=SpreadsheetAPI(cp.get('Google', 'user'), cp.get('Google', 'password'), 'python.google2trello')
print 'Authenticating against Google...'
try:
ss=api.list_spreadsheets()
except BadAuthentication:
print 'Could not authenticate with specified Google credentials.'
import traceback
traceback.print_exc()
exit()
sname=cp.get('Google', 'spreadsheet')
for entry in ss:
if entry[0]==sname:
spreadsheet=entry[1]
break
else:
print 'Could not find spreadsheet', sname
print 'Possible choices:', ss
exit()
class Data2(object):
def get_feed(self, query):
return self.ss.GetCellsFeed(self.ss_key,
query=query,
visibility='public',
projection='values')
def query_columns(self):
"""
The text in the first row is considered to be the name of the column
"""
query = gdata.spreadsheet.service.CellQuery()
query.max_row = '1'
feed = self.get_feed(query)
cols = []
for entry in feed.entry:
if entry.cell:
cols.append(entry.cell.text)
logging.debug("Found columns:" + str(cols))
return cols
def query_ids(self):
"""
Get all the values in the game ID column
"""
query = gdata.spreadsheet.service.CellQuery()
col = self.ss_columns.index("id") + 1
query.min_col = str(col)
query.max_col = str(col)
query.min_row = '2'
feed = self.get_feed(query)
ids = []
for entry in feed.entry:
ids.append(entry.cell.text)
logging.debug("found ids:" + str(ids))
return ids
def load_ss(self):
logging.debug("Loading spreadsheet")
self.api = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER,
GOOGLE_SPREADSHEET_PASSWORD,
GOOGLE_SPREADSHEET_SOURCE)
spreadsheets = self.api.list_spreadsheets()
self.ss_key = None
for s in spreadsheets:
if s[0] == "bbots":
self.ss_key = s[1]
break
self.worksheets = self.api.list_worksheets(self.ss_key)
self.games_sheet = None
for w in self.worksheets:
if w[0] == 'games':
self.games_sheet = self.api.get_worksheet(self.ss_key, w[1])
break
self.games_sheet_rows = self.games_sheet.get_rows()
logging.debug("table data: " + str(self.games_sheet_rows))
self.ss_columns = self.games_sheet_rows[0]
self.ids = []
for r in self.games_sheet_rows:
self.ids.append(r['id'])
def __init__(self, con):
self.con = con
self.ss_columns = None
self.ss_key = None
self.ss = None
self.ids = None
self.load_ss()
def get_record(self, id):
"""
Get a dictionary, keys are spreadsheet headers, values are entries
in the cell for the row specified by id
"""
record = self.games_sheet.get_rows(
filter_func=lambda row: row['id'] == id)[0]
logging.debug("ID: " + str(id) + ": " + str(record))
return record
feed = self.get_feed(query)
record = {}
for i, entry in enumerate(feed.entry):
if i >= len(self.ss_columns):
raise Exception("Unknown reason for reading column: " +
str(i) + " : " + str(entry.cell.text) +
", for id: " + str(id))
#logging.debug("Reading column: " + self.ss_columns[i] + " : "
# + entry.cell.text)
header = self.ss_columns[i]
if is_int(entry.cell.text):
record[header] = int(entry.cell.text)
elif is_float(entry.cell.text):
record[header] = float(entry.cell.text)
else:
record[header] = entry.cell.text
return record
def record_game_failure(self, id):
logging.debug("Recording game failure")
def update_rec(self, rec):
logging.debug("Updating row: " + str(rec))
self.games_sheet.update_row(rec)
return
query = self.get_record_query(rec['id'])
cells = self.ss.GetCellsFeed(
self.ss_key,
query=query,
# visibility='public', projection='values'
)
batchRequest = gdata.spreadsheet.SpreadsheetsCellsFeed()
for col in range(len(self.ss_columns)):
cells.entry[col].cell.inputValue = str(rec[self.ss_columns[col]])
#logging.debug("new value of " + rec['id'] + "[" + self.ss_columns[
# col] + "] is: " + str(rec[self.ss_columns[col]]))
batchRequest.AddUpdate(cells.entry[col])
updated = self.ss.ExecuteBatch(batchRequest, cells.GetBatchLink().href)
if updated:
logging.debug("Updated: " + str(rec['id']))
class Data2(object):
def get_feed(self,query):
return self.ss.GetCellsFeed(self.ss_key, query=query,
visibility='public', projection='values')
def query_columns(self):
"""
The text in the first row is considered to be the name of the column
"""
query = gdata.spreadsheet.service.CellQuery()
query.max_row = '1'
feed = self.get_feed(query)
cols=[]
for entry in feed.entry:
if entry.cell:
cols.append(entry.cell.text)
logging.debug("Found columns:" + str(cols))
return cols
def query_ids(self):
"""
Get all the values in the game ID column
"""
query = gdata.spreadsheet.service.CellQuery()
col = self.ss_columns.index("id") + 1
query.min_col = str(col)
query.max_col = str(col)
query.min_row = '2'
feed = self.get_feed(query)
ids = []
for entry in feed.entry:
ids.append(entry.cell.text)
logging.debug("found ids:" + str(ids))
return ids
def load_ss(self):
logging.debug("Loading spreadsheet")
self.api = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER,
GOOGLE_SPREADSHEET_PASSWORD,
GOOGLE_SPREADSHEET_SOURCE)
spreadsheets = self.api.list_spreadsheets()
self.ss_key = None
for s in spreadsheets:
if s[0] == "bbots":
self.ss_key = s[1]
break
self.worksheets = self.api.list_worksheets(self.ss_key)
self.games_sheet = None
for w in self.worksheets:
if w[0] == 'games':
self.games_sheet = self.api.get_worksheet(
self.ss_key, w[1])
break
self.games_sheet_rows = self.games_sheet.get_rows()
logging.debug("table data: " + str(self.games_sheet_rows))
self.ss_columns = self.games_sheet_rows[0]
self.ids = []
for r in self.games_sheet_rows:
self.ids.append(r['id'])
def __init__(self, con):
self.con = con
self.ss_columns = None
self.ss_key = None
self.ss = None
self.ids = None
self.load_ss()
def get_record(self, id):
"""
Get a dictionary, keys are spreadsheet headers, values are entries
in the cell for the row specified by id
"""
record = self.games_sheet.get_rows(
filter_func=lambda row: row['id'] == id)[0]
logging.debug("ID: " + str(id) + ": " + str(record))
return record
feed = self.get_feed(query)
record = {}
for i, entry in enumerate(feed.entry):
if i >= len(self.ss_columns):
raise Exception("Unknown reason for reading column: " + str(i)
+ " : " + str(entry.cell.text) + ", for id: " +
str(id))
#logging.debug("Reading column: " + self.ss_columns[i] + " : "
# + entry.cell.text)
header = self.ss_columns[i]
if is_int(entry.cell.text):
record[header] = int(entry.cell.text)
elif is_float(entry.cell.text):
record[header] = float(entry.cell.text)
else:
record[header] = entry.cell.text
return record
def record_game_failure(self, id):
logging.debug("Recording game failure")
def update_rec(self,rec):
logging.debug("Updating row: " + str(rec))
self.games_sheet.update_row(rec)
return
query = self.get_record_query(rec['id'])
cells = self.ss.GetCellsFeed(self.ss_key, query=query,
# visibility='public', projection='values'
)
batchRequest = gdata.spreadsheet.SpreadsheetsCellsFeed()
for col in range(len(self.ss_columns)):
cells.entry[col].cell.inputValue = str(rec[self.ss_columns[col]])
#logging.debug("new value of " + rec['id'] + "[" + self.ss_columns[
# col] + "] is: " + str(rec[self.ss_columns[col]]))
batchRequest.AddUpdate(cells.entry[col])
updated = self.ss.ExecuteBatch(batchRequest, cells.GetBatchLink().href)
if updated:
logging.debug("Updated: " + str(rec['id']))
