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, 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 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 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 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 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)
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)
