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 get_google_sheet(sheet_key=False, sheet_id='od6'):
"""Uses python_google_spreadsheet API to interact with sheet"""
api = SpreadsheetAPI(GOOGLE_SPREADSHEET['USER'],
GOOGLE_SPREADSHEET['PASSWORD'],
GOOGLE_SPREADSHEET['SOURCE'])
sheet = api.get_worksheet(sheet_key, sheet_id)
sheet_object = sheet.get_rows()
return sheet_object
def get_google_sheet(sheet_key='1WnJL3lpsFjJIv6yM0ruA-k5YnroaxKtiFRrJNOFP84s', sheet_id='od6'):
"""Uses python_google_spreadsheet API to interact with sheet"""
api = SpreadsheetAPI(GOOGLE_SPREADSHEET['USER'],
GOOGLE_SPREADSHEET['PASSWORD'],
GOOGLE_SPREADSHEET['SOURCE'])
sheet = api.get_worksheet(sheet_key, sheet_id)
sheet_object = sheet.get_rows()
return sheet_object
def __init__(self, username, password):
self.api = SpreadsheetAPI(username, password, '')
worksheets = self.api.list_worksheets(DatabaseCUHKWMAID)
self.sheetsDict = GoogleSpreadsheet.getWorksheetsDict(worksheets)
sheet = self.api.get_worksheet(DatabaseCUHKWMAID,
self.sheetsDict['Current Members'])
#self.rows = sheet.get_rows()
#self.data = GoogleSpreadsheet.wrapRowsData(self.rows)
self.data = sheet.get_rows()
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 DatabaseCUHKWMA:
def __init__(self, username, password):
self.api = SpreadsheetAPI(username, password, '')
worksheets = self.api.list_worksheets(DatabaseCUHKWMAID)
self.sheetsDict = GoogleSpreadsheet.getWorksheetsDict(worksheets)
sheet = self.api.get_worksheet(DatabaseCUHKWMAID,
self.sheetsDict['Current Members'])
#self.rows = sheet.get_rows()
#self.data = GoogleSpreadsheet.wrapRowsData(self.rows)
self.data = sheet.get_rows()
def process_python_students():
errors = []
api = SpreadsheetAPI(env('GOOGLE_APPS_USERNAME'), env('GOOGLE_APPS_PASSWORD'), 'thinkful.com')
sheet = api.get_worksheet(PYTHON_SPREADSHEET_KEY, 'od6')
emails = map(lambda row: row['email'], sheet.get_rows())
with crm_connection() as crm:
for email in emails:
with collect_error(errors):
contact = match_email_to_contact(row['email'], crm)
add_python_potential_to_contact(contact, email)
print '<%s>' % '<\n>'.join(errors)
class SSFinGoogle:
def __init__(self, username, password):
self.api = SpreadsheetAPI(username, password, '')
worksheets = self.api.list_worksheets(SSSpendingID)
self.sheetsDict = SSFinGoogleAPI.getWorksheetsDict(worksheets)
def getDataFromMonth(self, month):
sheet = self.api.get_worksheet(SSSpendingID,
self.sheetsDict[month])
rows = sheet.get_rows()
return SSFinGoogleAPI.wrapRowsData(rows)
def get_google_sheet(sheet_key=False, sheet_id='od6'):
"""Uses python_google_spreadsheet API to interact with sheet
https://github.com/yoavaviram/python-google-spreadsheet
Returns a list of dicts with each row its own list with the first row key"""
api = SpreadsheetAPI(GOOGLE_SPREADSHEET['USER'],
GOOGLE_SPREADSHEET['PASSWORD'],
GOOGLE_SPREADSHEET['SOURCE'])
sheet = api.get_worksheet(sheet_key, sheet_id)
sheet_object = sheet.get_rows()
return sheet_object
def get_google_sheet(sheet_key=False, sheet_id='od6'):
"""Uses python_google_spreadsheet API to interact with sheet
https://github.com/yoavaviram/python-google-spreadsheet
Returns a list of dicts with each row its own list with the first row key"""
api = SpreadsheetAPI(GOOGLE_SPREADSHEET['USER'],
GOOGLE_SPREADSHEET['PASSWORD'],
GOOGLE_SPREADSHEET['SOURCE'])
sheet = api.get_worksheet(sheet_key, sheet_id)
sheet_object = sheet.get_rows()
return sheet_object
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
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 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 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
class TestWorksheet(TestCase):
"""Test Worksheet Class
Test Class for Worksheet.
"""
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
Are imported from a custom file named: 'test_settings.py'
"""
self.spreadsheet = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER,
GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE)
self.sheet = self.spreadsheet.get_worksheet(GOOGLE_SPREADSHEET_KEY,
GOOGLE_WORKSHEET_KEY)
def test_get_rows(self):
"""Test Get Rows.
"""
rows = self.sheet.get_rows()
assert_true(len(rows))
def test_update_row(self):
"""Test Update Rows.
First gets all rows, than updates lest row.
"""
rows = self.sheet.get_rows()
row_index = len(rows) - 1
new_row = rows[0]
row = self.sheet.update_row(row_index, new_row)
assert_equals(row, new_row)
def test_insert_delete_row(self):
"""Test Insert and Delete Row.
First gets all rows, than inserts a new row, finally deletes the new
row.
"""
rows = self.sheet.get_rows()
num_rows = len(rows)
new_row = rows[0]
self.sheet.insert_row(new_row)
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet.delete_row(num_rows)
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
def handle(self, *args, **options):
client = SpreadsheetAPI(os.environ["EMAIL"], os.environ["PASS"], "yrsbadger")
worksheet = client.get_worksheet("0AuaHiEjutiMUdE5SWTNvTUJTdlJ1ZXV3dUxoQkh6SGc", 1)
for row in worksheet.get_rows():
awarded = False
for title in map(str.strip, row["badgestoaward"].split(",")):
try:
badge = Badge.objects.get(title=title)
if not row["youngpersonsemailaddress"]:
print("ERROR: No email address")
elif not Award.objects.filter(badge=badge, email=row["youngpersonsemailaddress"]):
awarded = True
award = Award.objects.create(badge=badge, email=row["youngpersonsemailaddress"])
award.save()
except Badge.DoesNotExist:
print("ERROR: Badge does not exist [%s] for %s" % (title, row["youngpersonsemailaddress"]))
if awarded:
minibadge.mailer.send(row["youngpersonsemailaddress"])
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
Are imported from a custom file named: 'test_settings.py'
"""
self.spreadsheet = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER,
GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE)
__author__ = 'varun'
from google_spreadsheet.api import SpreadsheetAPI
from codeaton.settings import GOOGLE_DRIVE_FOLDER_KEY, GOOGLE_DRIVE_PASSWORD, GOOGLE_DRIVE_USERNAME, \
SUBCRIPTION_SPREADSHEET_KEY, SUBCRIPTION_WORKSHEET_KEY
__api = SpreadsheetAPI(GOOGLE_DRIVE_USERNAME, GOOGLE_DRIVE_PASSWORD, GOOGLE_DRIVE_FOLDER_KEY)
__subscription_sheet = __api.get_worksheet(SUBCRIPTION_SPREADSHEET_KEY, SUBCRIPTION_WORKSHEET_KEY)
def add_subscriber(email):
__subscription_sheet.insert_row({'email': email})
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 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
import subprocess import pandas as pd from pyquery import PyQuery as pq from settings import * from google_spreadsheet.api import SpreadsheetAPI from bs4 import BeautifulSoup # create spreasheets api object api = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER, GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE) def get_sheet_from_name(sheet_name): spreadsheets = api.list_spreadsheets() target_sheet = None for i, sheet in enumerate(spreadsheets): if spreadsheets[i][0] == sheet_name: target_sheet = sheet return target_sheet def get_rows_from_sheet(sheet): # fetch the sheet with volunteer info worksheet = api.list_worksheets(sheet[1])[0] target_sheet = api.get_worksheet(sheet[1], worksheet[1]) return target_sheet.get_rows() def sheet_name_to_df(name): sheet = get_sheet_from_name(name) sheet = get_rows_from_sheet(sheet) return pd.DataFrame(sheet)
class TestWorksheet(TestCase):
"""Test Worksheet Class
Test Class for Worksheet.
"""
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)
self.sheet = self.spreadsheet.get_worksheet(GOOGLE_SPREADSHEET_KEY,
GOOGLE_WORKSHEET_KEY)
def test_get_rows(self):
"""Test Get Rows.
"""
rows = self.sheet.get_rows()
assert_true(len(rows))
def test_update_row_by_index(self):
"""Test Update Rows By Index.
First gets all rows, than updates last row.
"""
rows = self.sheet.get_rows()
row_index = len(rows) - 1
new_row = rows[0]
row = self.sheet.update_row_by_index(row_index, new_row)
del row['__rowid__']
del new_row['__rowid__']
assert_equals(row, new_row)
def test_update_row_by_id(self):
"""Test Update Rows By ID.
First gets all rows, than updates last row.
"""
rows = self.sheet.get_rows()
new_row = rows[0]
row = self.sheet.update_row(new_row)
assert_equals(row, new_row)
def test_insert_delete_row(self):
"""Test Insert and Delete Row.
First gets all rows, than inserts a new row, finally deletes the new
row.
"""
rows = self.sheet.get_rows()
num_rows = len(rows)
new_row = rows[0]
self.sheet.insert_row(new_row)
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet._flush_cache()
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet.delete_row_by_index(num_rows)
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
self.sheet._flush_cache()
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
def test_delete_by_id(self):
"""Test Delete Row By ID.
First gets all rows, than inserts a new row, finally deletes the new
row by ID.
"""
rows = self.sheet.get_rows()
num_rows = len(rows)
new_row = rows[0]
new_row = self.sheet.insert_row(new_row)
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet._flush_cache()
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet.delete_row(new_row)
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
self.sheet._flush_cache()
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
def test_delete_all_rows(self):
"""Tests deleting of all rows in the sheet
"""
# first retrieve rows and store in memory to re-add after test
rows = self.sheet.get_rows()
self.sheet.delete_all_rows()
assert_equals(len(self.sheet.get_rows()), 0)
# add back the rows that were there so the other tests still pass
for row in rows:
self.sheet.insert_row(row)
def test_query(self):
"""Test Query.
Filter rows by a unique column vlaue.
"""
rows = self.sheet.get_rows(
query='{0} = {1}'.format(COLUMN_NAME, COLUMN_UNIQUE_VALUE))
assert_equals(len(rows), 1)
def test_sort(self):
"""Test Sort.
Sort ascending and descending.
"""
rows = self.sheet.get_rows(order_by='column:{0}'.format(COLUMN_NAME),
reverse='false')
assert_true(rows)
def test_filter(self):
"""Test Filter.
Tests filter in memory.
"""
filtered_rows = self.sheet.get_rows(filter_func=lambda row: row[
COLUMN_NAME] == unicode(COLUMN_UNIQUE_VALUE))
assert_equals(1, len(filtered_rows))
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']))
def __init__(self, username, password):
self.api = SpreadsheetAPI(username, password, '')
worksheets = self.api.list_worksheets(SSSpendingID)
self.sheetsDict = SSFinGoogleAPI.getWorksheetsDict(worksheets)
import soundcloud
#sitedetails
siteURL = 'theWebsiteURL'
s3IconURL = 'theIconURL'
s3AudioURL = 'theAudioURL'
#connect to WP - use a seperate account from your admin account
wp = Client( siteURL + 'xmlrpc.php', 'wpUser', 'wpPassword')
#connect to Soundcloud - you'll need to register an app with Soundcloud
sc = soundcloud.Client(client_id='scID', client_secret='scSecret',username='******', password='******')
#connect to google spreadsheets, again, don't use your own, create a new user and share the spreadsheet with them
googapi = SpreadsheetAPI('gEmail', 'gPassword', 'gUser')
# get the spreadsheet. You'll need it's unique ID. You can find this by playing with the API
spreadsheet = googapi.get_worksheet('ssUID','ssSheet')
rows = spreadsheet.get_rows()
#get information on today's post from the spreadsheet
today = datetime.date.today()
for x in rows:
if x['pubdate'] == today.strftime('%d/%m/%Y'):
print("Posting Chapter " + x['chapter'])
chapNum = x['chapter']
chapTitle = x['title']
Reader = x['reader']
print 'Assigned skip condition for value', val, 'in column', col
if opt.startswith('name_'):
x, col, val=opt.split('_')
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:
class TestWorksheet(TestCase):
"""Test Worksheet Class
Test Class for Worksheet.
"""
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
)
self.sheet = self.spreadsheet.get_worksheet(GOOGLE_SPREADSHEET_KEY, GOOGLE_WORKSHEET_KEY)
def test_get_rows(self):
"""Test Get Rows.
"""
rows = self.sheet.get_rows()
assert_true(len(rows))
def test_update_row_by_index(self):
"""Test Update Rows By Index.
First gets all rows, than updates last row.
"""
rows = self.sheet.get_rows()
row_index = len(rows) - 1
new_row = rows[0]
row = self.sheet.update_row_by_index(row_index, new_row)
del row["__rowid__"]
del new_row["__rowid__"]
assert_equals(row, new_row)
def test_update_row_by_id(self):
"""Test Update Rows By ID.
First gets all rows, than updates last row.
"""
rows = self.sheet.get_rows()
new_row = rows[0]
row = self.sheet.update_row(new_row)
assert_equals(row, new_row)
def test_insert_delete_row(self):
"""Test Insert and Delete Row.
First gets all rows, than inserts a new row, finally deletes the new
row.
"""
rows = self.sheet.get_rows()
num_rows = len(rows)
new_row = rows[0]
self.sheet.insert_row(new_row)
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet._flush_cache()
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet.delete_row_by_index(num_rows)
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
self.sheet._flush_cache()
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
def test_delete_by_id(self):
"""Test Delete Row By ID.
First gets all rows, than inserts a new row, finally deletes the new
row by ID.
"""
rows = self.sheet.get_rows()
num_rows = len(rows)
new_row = rows[0]
new_row = self.sheet.insert_row(new_row)
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet._flush_cache()
insert_rows = self.sheet.get_rows()
assert_equals(len(insert_rows), num_rows + 1)
self.sheet.delete_row(new_row)
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
self.sheet._flush_cache()
delete_rows = self.sheet.get_rows()
assert_equals(len(delete_rows), num_rows)
assert_equals(delete_rows[-1], rows[-1])
def test_delete_all_rows(self):
"""Tests deleting of all rows in the sheet
"""
# first retrieve rows and store in memory to re-add after test
rows = self.sheet.get_rows()
self.sheet.delete_all_rows()
assert_equals(len(self.sheet.get_rows()), 0)
# add back the rows that were there so the other tests still pass
for row in rows:
self.sheet.insert_row(row)
def test_query(self):
"""Test Query.
Filter rows by a unique column vlaue.
"""
rows = self.sheet.get_rows(query="{0} = {1}".format(COLUMN_NAME, COLUMN_UNIQUE_VALUE))
assert_equals(len(rows), 1)
def test_sort(self):
"""Test Sort.
Sort ascending and descending.
"""
rows = self.sheet.get_rows(order_by="column:{0}".format(COLUMN_NAME), reverse="false")
assert_true(rows)
def test_filter(self):
"""Test Filter.
Tests filter in memory.
"""
filtered_rows = self.sheet.get_rows(filter_func=lambda row: row[COLUMN_NAME] == unicode(COLUMN_UNIQUE_VALUE))
assert_equals(1, len(filtered_rows))
from google_spreadsheet.api import SpreadsheetAPI
GOOGLE_SPREADSHEET_USER = '******'
GOOGLE_SPREADSHEET_PASSWORD = '******'
GOOGLE_SPREADSHEET_SOURCE = 'https://docs.google.com/spreadsheets/d/1qaqBQhChaKYrX6FNz9lBh77yrQyfbk-Vj9Ex6uyJ8Xs/edit#gid=0'
api = SpreadsheetAPI(GOOGLE_SPREADSHEET_USER, GOOGLE_SPREADSHEET_PASSWORD, GOOGLE_SPREADSHEET_SOURCE)
spreadsheets = api.list_spreadsheets()
print spreadsheets
worksheets = api.list_worksheets(spreadsheets[0][1])
print worksheets
sheet = api.get_worksheet('1qaqBQhChaKYrX6FNz9lBh77yrQyfbk-Vj9Ex6uyJ8Xs', 'od6')
rows = sheet.get_rows()
print len(rows)
for row in rows:
print row
row_to_insert = {}
row_to_insert['record2'] = 'fldjfkljsfs'
row_to_insert['record1'] = 'assfdswewqe'
row = sheet.insert_row(row_to_insert)
