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)