def __init__(self):
self.gc = gspread.oauth()
self.google_sheets_name = 'statement'
self.worksheet_name = 'statement'
self.google_sheet = self.gc.open(self.google_sheets_name)
self.worksheet = self.get_worksheet()
def get_sheets(scopes, url):
gc = gspread.oauth(scopes)
workbook = gc.open_by_url(url)
timetable = workbook.worksheet("timetable")
tt = timetable.get_all_records()
return tt
def upload_report(report_path: Path) -> None:
logger.info(f"Reading reports from {report_path}")
reports = read_reports(report_path)
file_reports = [
FileReport(
path=report.path,
dates_match=report.dates_match,
has_ghotos_timestamp=report.has_google_date,
uploaded=False,
) for report in reports
]
logger.info("Authenticating with Google Spreadsheet API...")
gc = gspread.oauth()
spreadsheet_name = "Photo backup tracker"
sh = gc.open(spreadsheet_name)
logger.info(f"Fetching data from the {spreadsheet_name!r} spreadsheet...")
gsheet = fetch_worksheet(sh)
logger.info("Merging report data with the spreadsheet...")
updated_gsheet = merge(gsheet, file_reports)
logger.info("Uploading updated data to the spreadsheet...")
upload_worksheet(sh, updated_gsheet)
logger.info("Report upload successfuly completed")
def data_from_google_sheet(sheet_url):
gc = gspread.oauth()
census_data = gc.open_by_url(sheet_url)
sheet = census_data.get_worksheet(0)
data = sheet.get_all_values()
tslvec = np.vectorize(to_short_labels)
return pd.DataFrame(data=data[1:], columns=tslvec(data[0]))
def db_hospis():
gc = gspread.oauth()
sh = gc.open("d data")
worksheet = sh.worksheet("cooked")
values = worksheet.get_all_values()
keys,rows = v_to_ld(values, 0)
ilppes = ilppe_get()
for row in rows:
if not row['wikidata'] and \
row['isa'] == 'hospital' and \
row['name'] not in [
ilppe['Medical Center Name'] for ilppe in ilppes]:
print(row['row'],row['name'])
goog_q = urllib.parse.quote(row['name'])
print("https://google.com/#q={}".format(goog_q))
isa = "Q16917" if row['isa'] == 'hospital' else ''
res = label(row['name'], isa)
if len(res['results']['bindings']):
cell=worksheet.cell(row['row'], keys.index('wikidata'))
wd_id = res['results']['bindings'][0]['item']['value'].split('/')[-1]
print('worksheet.update( {}, "{}")'.format(
cell.address,
wd_id))
print("------------\n")
import code; code.interact(local=locals())
break
def __init__(self, key, product):
self.logger = Logger()
self._client = gspread.oauth()
self._sh = self._client.open_by_key(key)
self._product = product
self._ws = self._sh.worksheet(product)
self._df = None
def get_inventory_sheet():
today = date.today()
gc = gspread.oauth()
sh = gc.open("Wine Inventory")
worksheet = sh.get_worksheet(0)
complete_inv = pd.DataFrame(worksheet.get_all_records())
complete_inv["Date Purchased"] = pd.to_datetime(
complete_inv["Date Purchased"]).dt.date
complete_inv["Consume By Date (Lower)"] = pd.to_datetime(
complete_inv["Consume By Date (Lower)"]).dt.date
complete_inv["Consume By Date (Upper)"] = pd.to_datetime(
complete_inv["Consume By Date (Upper)"]).dt.date
drink_soon = complete_inv.loc[(complete_inv["Consume By Date (Upper)"] <=
today)]
in_range = complete_inv.loc[
(complete_inv["Consume By Date (Lower)"] <= today)
& (complete_inv["Consume By Date (Upper)"] >= today)]
return drink_soon, in_range
def c19it():
# gc = gspread.service_account()
gc = gspread.oauth()
sh = gc.open("Copy of COVID-19 INVENTORY TRACKER - CHICAGO")
worksheet = sh.worksheet("Requesters")
# ['Medical Center Name', 'Email Address', '', 'Region', 'Primary Contact', 'Address', 'fixed address', 'lat', 'lng', 'homepage url', 'location url', 'wikipedia url', 'Phone', 'Product Needs', 'Immediate Need - ready for order', 'How to get it to them']
# keys = worksheet.get('A2:R2')[0]
# cell.row, cell.col
values = worksheet.get_all_values()
# lists of dicts
keys,rows = v_to_ld(values, 1)
for row in rows:
if row['lng']:
continue
if row['Address'] is None or len(row['Address']) == 0:
continue
if row['fixed address']:
addr = row['fixed address']
else:
addr = addr_fixer( row['Address'] )
latlng = addr_to_latlng( addr )
worksheet.update('H{row}:I{row}'.format(**row), [latlng])
def chg():
sn = "Chicago Hospitals_Geocoded"
gc = gspread.oauth()
sh = gc.open(sn)
worksheet = sh.worksheet("Sheet1")
values = worksheet.get_all_values()
keys,rows = v_to_ld(values, 0)
for row in rows:
print("{FACILITY} - {StAddr} {City}\n".format(**row))
isas = ["Q16917", "Q4287745"] # hospital, medical organization
res = hospi_ll( row['Y'], row['X'], .2, isas=isas)
for wd in res['results']['bindings']:
if row['FACILITY'] == wd['placeLabel']['value']:
print( "match: ", wd['place']['value'] )
break
else:
print( wd['distance']['value'],
wd['placeLabel']['value'],
wd['place']['value'],
# wd['place']['value'].split('/')[-1]
)
print("----------")
# break
import code; code.interact(local=locals())
return rows
def upload():
gc = gspread.oauth()
sh = gc.open_by_key(str(config('FILE_KEY')))
with open('sources.csv', 'r') as file_obj:
content = file_obj.read().encode('utf-8')
gc.import_csv(sh.id, data=content)
def read_gsheet_and_update(self):
console.log('Attempting to login..')
gc = gspread.oauth()
console.log('Successful login')
sh = gc.open(SHEET_NAME)
ws = sh.get_worksheet(0) # use first worksheet
console.log('Found worksheet')
header = ws.row_values(1)
col_lookup = self.get_col_to_a1(header)
df = pd.DataFrame(ws.get_all_records())
df = df.reindex(
columns=header) # reorder dataframe so order is same as gsheet
ms = Morningstar()
console.log('Calling Morningstar..')
df = df.apply(self.lookup_morningstar, axis=1)
console.log('Writing back to worksheet..')
for field in ACTIVE_FIELDS:
field_pos = col_lookup.get(field)
a1 = field_pos + '2:' + field_pos
# todo: batch updates
ws.update(a1, self.col_to_list_of_list(df[field].values.tolist()))
console.log('Written field ' + field)
console.log('Complete!')
async def war_check(self, ctx, tag_or_id):
"""Check links API to see who they match"""
if not self.is_elder(ctx.author):
return await ctx.send("You are not authorized to use this command."
)
async with ctx.typing():
# Check Links API
links_resp = "**Discord Links API:**\n"
if tag_or_id.startswith("#"):
tag_list = [
tag_or_id,
]
discord_id = await self.bot.links.get_link(tag_or_id)
links_resp += f"Player Tag {tag_or_id} is linked to <@{discord_id}>."
else:
tag_list = []
player_tags = await self.bot.links.get_linked_players(tag_or_id
)
for tag in player_tags:
links_resp += f"Player Tag {tag} is linked to <@{tag_or_id}>.\n"
tag_list.append(tag)
# Check Oak Table
gc = gspread.oauth()
ot = gc.open("Oak Table")
sh = ot.worksheet("Current Members")
oak_resp = "**Oak Table Links:**\n"
for tag in tag_list:
player = await self.bot.coc.get_player(tag)
try:
name_cell = sh.find(player.name)
discord_id = sh.cell(name_cell.row, 9).value
oak_resp += f"{player.name} ({player.tag}) is linked to <@{discord_id}> in the Oak Table.\n"
except gspread.CellNotFound:
oak_resp += f"{player.name} ({player.tag}) not found in Oak Table.\n"
await ctx.send(f"{links_resp}\n{oak_resp}")
def import_gsheet(sheet_name):
gc = gspread.oauth()
sh = gc.open("PEC Map Data 2020 ")
worksheet = sh.worksheet(sheet_name)
data = worksheet.get_all_values()
column_names = data.pop(0)
df = pd.DataFrame(data, columns=column_names)
return df
def main():
gc = gspread.oauth(scopes=SCOPES)
sheets = gc.open("ETF")
sheet = sheets.worksheet("Changying")
all_values = sheet.get("D3:D1000")
existing_ids = [row[0] for row in all_values if len(row) > 0]
data_dict = get_data()
update_existing_data(sheet, existing_ids, data_dict)
def __init__(self, key, org, team, release):
self.release = release
self.team = team
self.logger = Logger()
self._client = gspread.oauth()
self._sh = self._client.open_by_key(key)
self._ws = self._sh.worksheet(org)
self._df = None
def make_copy_and_share(users):
gc = gspread.oauth()
sh = gc.open('NFL_Pickem')
for user in users:
sh_copy = gc.copy(sh.id, user[0] + '_NFL_Pickem')
#sh_copy=gc.open(user[0]+'week_%d'%week)
sh_copy.share(user[1], perm_type='user', role='writer')
def main():
'''
given the week number will create a spreadsheet of opponents
'''
gc = gspread.oauth()
sh = gc.open("NFL_Pickem")
games_list = build_schedule()
write_sheet(sh, games_list)
def login_open_sheet(spreadsheet):
"""Connect to Google Docs spreadsheet and return the first worksheet."""
try:
gc = gspread.oauth()
ws = gc.open(spreadsheet).sheet1
return ws
except:
print('Unable to login and get spreadsheet. Check spreadsheet name.')
sys.exit(1)
def connect_to_spreadsheet(key, worksheet):
# connect to google sheet
gc = gspread.oauth()
# open spreadsheet
sht1 = gc.open_by_key(key).worksheet(worksheet)
return sht1
def make_backup(sh, week):
#create a backup each time you modify a week. will delete old backup
gc = gspread.oauth()
try:
shb = gc.open("The Standings_Backup_Week%d" % week)
gc.del_spreadsheet(shb.id)
except:
pass
gc.copy(sh.id, title="The Standings_Backup_Week%d" % week)
def get_categories():
gc = gspread.oauth()
spreadsheet = gc.open('Workout Generator')
# Get exercises
exercise_weights = spreadsheet.worksheet('weights').get_all_values()[1:]
categories = set([exercise[4] for exercise in exercise_weights])
return categories
def ilppe_get():
gc = gspread.oauth()
sh = gc.open("Copy of COVID-19 INVENTORY TRACKER - CHICAGO")
worksheet = sh.worksheet("Requesters")
values = worksheet.get_all_values()
keys,rows = v_to_ld(values, 1)
return rows
def createGSheet(self,
spreadsheetName='My Spreadsheet',
email='*****@*****.**'):
# log into Google API
gc = gspread.oauth()
# create new spreadsheet
sh = gc.create(spreadsheetName)
# share spreadsheet, so it is accessible
sh.share(email, perm_type='user', role='writer')
return sh
def authenticate(self) -> None:
if self.is_service_account:
logger.debug("Using SERVICE_ACCOUNT auth")
self.google_client = gspread.service_account(self.creds_path)
else:
logger.debug("Using END_USER auth")
# ! This override should be temporary ideally we'll have a more long term solution in:
# ! https://github.com/burnash/gspread/issues/826
self._override_gspread_default_creds()
self.google_client = gspread.oauth()
self.is_authenticated = True
def from_gsheet(sheet_name: str, worksheet_name: str):
'''
read a worksheet (worksheet_name) from a google sheets spreadsheet (sheet_name)
'''
import gspread
from gspread_dataframe import get_as_dataframe, set_with_dataframe
gc = gspread.oauth()
sh = gc.open(sheet_name)
worksheet = sh.worksheet(worksheet_name)
return get_as_dataframe(worksheet)
def openGSheet(self, spreadsheetName=None):
# log into Google API
gc = gspread.oauth()
# try to open existing spreadsheet
if spreadsheetName != None:
try:
result = gc.open(spreadsheetName)
return result
except gspread.exceptions.SpreadsheetNotFound:
print(
f'ERROR: There is no spreadsheet named "{spreadsheetName}"\n'
)
pass
def get_sheet():
"""
This method will Authorize the google drive and will fetch the Sheet
Credentials will be referred from %APPDATA%/gspread/credentials.json
User interaction will be asked for the first time after that it will be stored in authorized_user.json
"""
from google.auth.exceptions import RefreshError
auth_file = os.path.join(os.getenv('APPDATA'), 'gspread',
'authorized_user.json')
gc = None
try:
gc = gspread.oauth()
except RefreshError as e:
# remove auth file and re-authorize
print_msg("Re-Authorizing the credentials ...", 1)
os.remove(auth_file)
gc = gspread.oauth()
print_msg('User authorized.')
# Return the First sheet
return gc.open(SHEET_NAME).sheet1
def chl():
sn = "Chicago Hospital Location.csv"
ws_name = "Sheet1"
gc = gspread.oauth()
sh = gc.open(sn)
worksheet = sh.worksheet(ws_name)
values = worksheet.get_all_values()
keys, rows = v_to_ld(values, 0)
rows = [row for row in rows if not row['lat']]
# fill in lat,long
for row in rows:
print("-------\n{Hospital} - {Street Address}\n".format(**row))
full_addr = "{Street Address}, {City} IL, {Zip Code}".format(**row)
try:
latlong = addr_to_latlong(full_addr)
# lat,long is G,H
worksheet.update('G{row}:H{row}'.format(**row), [latlong])
except TypeError:
# print( "{Hospital}\n{Street Address}".format(**row) )
isas = ["Q16917", "Q4287745"] # hospital, medical organization
res = label(row['Hospital'], isas)
query = "{Hospital} {City}".format(**row)
print(goog_search(query))
import code
code.interact(local=locals())
"""
isas = ["Q16917", "Q4287745"] # hospital, medical organization
res = hospi_ll( row['Y'], row['X'], .2, isas=isas)
for wd in res['results']['bindings']:
if row['FACILITY'] == wd['placeLabel']['value']:
print( "match: ", wd['place']['value'] )
break
else:
print( wd['distance']['value'],
wd['placeLabel']['value'],
wd['place']['value'],
# wd['place']['value'].split('/')[-1]
)
print("----------")
# break
"""
return rows
def history_from_google_sheet(sheet_url):
gc = gspread.oauth()
history_data = gc.open_by_url(sheet_url)
sheet = history_data.get_worksheet(0)
data = sheet.get_all_values()
dates = np.transpose(data)[0][1:]
tdt = np.vectorize(pd.to_datetime)
timetofloat = np.vectorize(lambda d: d.timestamp())
df = pd.DataFrame(data=data[1:],
index=tdt(dates, dayfirst=True),
columns=data[0])[data[0][1:]]
# df = pd.DataFrame(data=data[1:], columns=data[0])
# df["Date"] = timetofloat(tdt(dates,dayfirst=True))
return df
def download_gforms_data(session, user):
user_email = user.email
gspread_client = gspread.oauth()
for survey_type, spreadsheet_name in survey_types.items():
response_spreadsheet = gspread_client.open(spreadsheet_name)
response_worksheet = response_spreadsheet.get_worksheet(0)
unfiltered_survey_responses = response_worksheet.get_all_records()
user_survey_responses = [
response for response in unfiltered_survey_responses
if response['Email Address'] == user.email
]
add_survey_responses_to_database(session, user, survey_type,
user_survey_responses)
