def getreslink(): client = authorizeGspread() vaccine = client.open("Links_for_Vaccine").sheet1 oxygen = client.open("Links_for_oxygen").sheet1 plasma = client.open("Links_for_plasma").sheet1 return (vaccine.col_values(1)[1:], oxygen.col_values(1)[1:], plasma.col_values(1)[1:])
def get_data(file_name1, file_name2): # Use creds to create a client to interact with the Google Drive API scope = ['https://spreadsheets.google.com/feeds'] creds = ServiceAccountCredentials.from_json_keyfile_name( 'client_secret.json', scope) client = gspread.authorize(creds) # Find a workbook by name and open the first sheet sheet1 = client.open( file_name1).sheet1 # Original story file verified by Adam and Tristan sheet2 = client.open(file_name2).sheet1 # Answers to form # Extract and print all of the values # List of lists table1 = sheet1.get_all_values() table2 = sheet2.get_all_values() del (table1[0]) del (table2[0]) for each_story in table2: del (each_story[0]) del (each_story[0]) stories = [] for each_story in table1: stories.append(each_story) for each_story in table2: stories.append(each_story) # Sort by alphabetical order of title stories.sort(key=lambda story: story[0]) return stories # list of lists
def new_entry(sheet_name: str, folder_id: str, name: str, doc_name: str, doc_type: str, doc_id: str, document, worksheet_index=0): """ Adds a new upload entry to the Google Spreadsheet identified by sheet_name The entry has the following form: """ creds = ServiceAccountCredentials.from_json_keyfile_dict(credential_info, scope) client = authorize(creds) sheet = client.open(sheet_name).get_worksheet(worksheet_index) now = datetime.now().strftime("%m/%d/%Y %H:%M:%S") sheet.append_row([now, name, doc_name, doc_type, doc_id]) service = build('drive', 'v3', credentials=creds) file_metadata = { 'name': f"{name}_{now}.pdf", 'parents': [folder_id] } media = MediaFileUpload(document.path(), mimetype='application/pdf', resumable=True) file = service.files().create(body=file_metadata, media_body=media, fields='id').execute()
def opengspreadsheet(row, col, content): scope = [ "https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets', "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive" ] creds = ServiceAccountCredentials.from_json_keyfile_name( "visionAPI.json", scope) client = gspread.authorize(creds) sheet = client.open("output").sheet1 # Open the spreadhseet #data = sheet.get_all_records() # Get a list of all records #row = sheet.row_values(3) # Get a specific row #col = sheet.col_values(3) # Get a specific column #cell = sheet.cell(1,2).value # Get the value of a specific cell #insertRow = ["hello", 5, "red", "blue"] #sheet.add_rows(insertRow, 4) # Insert the list as a row at index 4 sheet.update_cell(row, col, content) # Update one cell numRows = sheet.row_count
def load_roster(client, filename): # format is 'Tournament Date' print('****************Loading Roster*********************') tournament, date = filename.lower().split() date = datetime.strptime(date, '%m/%d/%y') JV = client.open(filename).get_worksheet(2) Varsity = client.open(filename).get_worksheet(1) for team in [JV, Varsity]: add_tournament(date, tournament, team.title.lower()) events = team.col_values(14) user1s = team.col_values(15) user2s = team.col_values(16) user3s = team.col_values(17) for i in range(1, len(events)): print(user1s[i], user2s[i], user3s[i]) add_event(tournament, team.title.lower(), events[i].lower(), [user1s[i], user2s[i], user3s[i]])
def load_users(client, filename): print('******************Loading Users*********************') sheet = client.open(filename).get_worksheet(0) firstnames = sheet.col_values(2) lastnames = sheet.col_values(3) # grades = sheet.col_values(3) emails = sheet.col_values(4) for i in range(1, len(firstnames)): add_user(firstnames[i].lower(), lastnames[i].lower(), '0', emails[i])
async def cmd3(self, ctx): def check(m): return m.channel.id == ctx.channel.id and ctx.author.id == ctx.message.author.id await ctx.send("Email Address?") email = await ctx.bot.wait_for("message", check=check) await ctx.send("First Name?") firstname = await ctx.bot.wait_for("message", check=check) await ctx.send("Last Name?") lastname = await ctx.bot.wait_for("message", check=check) await ctx.send("Pirate Name?") piratename = await ctx.bot.wait_for("message", check=check) await ctx.send("Phone Number?") phonenumber = await ctx.bot.wait_for("message", check=check) msg1 = await ctx.send("Do you have an Emergency Contact? Y/N") start_adding_reactions(msg1, ReactionPredicate.YES_OR_NO_EMOJIS) pred = ReactionPredicate.yes_or_no(msg1, ctx.author) await ctx.bot.wait_for("reaction_add", check=pred) if pred.result is True: # User responded with tick await ctx.send("Emergency Contact Name?") emgname = await ctx.bot.wait_for("message", check=check) await ctx.send("Emergency Contact Phone Number?") emgphone = await ctx.bot.wait_for("message", check=check) await ctx.send("Emergency Contact Relationship?") emgrelation = await ctx.bot.wait_for("message", check=check) # Begin posting data to Spreedsheet scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] file_path = bundled_data_path(self) / 'client_secret.json' creds = ServiceAccountCredentials.from_json_keyfile_name( file_path, scope) client = gspread.authorize(creds) sheet = client.open("DragonBot Tester").sheet1 index = 2 now = datetime.now() ts = int(datetime.timestamp(now)) row = [ datetime.utcfromtimestamp(ts).strftime('%m/%d/%Y %H:%M:%S'), email.content, firstname.content, lastname.content, piratename.content, phonenumber.content ] sheet.insert_row(row, index)
def get_sheet(worksheet): scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( 'sheets_secret.json', scope) client = gspread.authorize(creds) print("sheets creds collected") sheet = client.open('Time Table').get_worksheet(worksheet - 1) return sheet
def get_google_workbook(workbook_name): client = get_gspread_client() sheet_dictionaries = [] for i in range(NUMBER_OF_SHEETS_PER_WORKBOOK): sheet = client.open(workbook_name).get_worksheet(i) data_list = sheet.get_all_records() data = to_data_frame(data_list) data = clean_workbook_data(data, workbook_name) sheet_title = sheet.title sheet_dictionary = {'sheet_title': sheet_title, 'data': data} sheet_dictionaries.append(sheet_dictionary) return sheet_dictionaries
def googleSheets(self): # use creds to create a client to interact with the Google Drive API scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( 'calendarscraper.json', scope) client = gspread.authorize(creds) # Find a workbook by name and open the first sheet # Make sure you use the right name here. sheet = client.open('Events calendar').sheet1 with open('eventscalendar.csv') as csv: client.import_csv('1MhI2x6at1UkCbVM4WwCRR0PCHv7zNkUzf4zmdezXry4', csv) # Extract and print all of the values list_of_hashes = sheet.get_all_records() print(list_of_hashes)
def upload_to_gsheets(file_to_upload, workbook, sheetname): ''' Upload parsed csv files ''' # when token expires, try going to google api, oauth 2.0 OAuth client IDs and grab another or create another and download, save it as ``~/.gdrive_private`` (as a file) sheet = client.open(workbook).id df = pd.read_csv(file_to_upload) df.fillna('', inplace=True) if file_to_upload == os_to_upload: df = df.fillna('0') df[['fileid']] = df[['fileid']].astype(int) d2g.upload(df, sheet, sheetname) msg = '= uploaded %s to %s' % (file_to_upload, workbook) if verbose: print(msg) logging.info(msg)
def _download_current_price(client,cfgfile,spreadsheet,sheet,email='*****@*****.**',output='D:\\QT_Engineering\\Calculation\Data\\',timesleep=5): import pandas as pd size=pd.read_csv(cfgfile).shape try: sh=client.open(spreadsheet) except: sh=client.create(spreadsheet) sh.share(email, perm_type='user', role='writer') try: worksheet=sh.worksheet(sheet) except: worksheet=sh.add_worksheet(title=sheet,rows=str(size[0]),cols=str(size[1])) csv2sheet(client,cfgfile,spreadsheet,sheet) import time time.sleep(timesleep) df=sheet2df(client,spreadsheet,sheet) df.replace(to_replace=r"^#.*", value='', regex=True,inplace=True) outputfile=output+spreadsheet[14:]+_current_date()+'.csv' df.to_csv(outputfile,index=False) empty=df[df['tradetime']==''] return empty
def Upload_to_Gsheets(Paysheet_row): # use creds to creat a lient to interact woth the Google Drive API SCOPE = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( 'Email add CSV.json', SCOPE) client = gspread.authorize(creds) # Find a workbook by name and open the first sheet sheet = client.open('Paysheet 2019-2020').sheet1 # Extract and print all of the values of payslip date col datelist = sheet.col_values(1) if Paysheet_row[0] in datelist: print('already uploaded') elif Paysheet_row[0] not in datelist: sheet.append_row(Paysheet_row) print(f"adding new row for date {Paysheet_row[0]}")
def get_dataset(dataset): """returns a dataframe named dataset from the drive after all data preprocessing""" dataframe = [] scope = ["https://www.googleapis.com/auth/drive"] creds = ServiceAccountCredentials.from_json_keyfile_name( "credentials.json", scope) client = gspread.authorize(creds) spreadSheet = client.open(dataset) sheet = spreadSheet.sheet1 for i, worksheet in enumerate(spreadSheet.worksheets()): with open("myfile", "w") as file: writer = csv.writer(file) writer.writerows(worksheet.get_all_values()) dataframe = loadCSV("myfile", dataframe) dataframe = dataframe[1:] # since first row contains names of features strRow_to_float(dataframe) minmax = minmax_normalisation(dataframe) normalisze_dataframe(dataframe, minmax) return dataframe
def get_all_names(): scope = [ 'https://spreadsheets.google.com/feeds' + ' ' + 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( 'client_secret.json', scope) client = gspread.authorize(creds) sheet = client.open("Notas - Alunos").sheet1 grades_sheet = sheet.get_all_records() students_data = list() sheet_line = 1 for student in grades_sheet: students_data.append((sheet_line, student['Nome'], student[' Login'])) sheet_line = sheet_line + 1 students_data.pop() return students_data
authInst = drive_auth.auth(SCOPES, CLIENT_SECRET_FILE, APPLICATION_NAME) credentials = authInst.getCredentials() http = credentials.authorize(httplib2.Http()) drive_service = discovery.build('drive', 'v3', http=http) #Sheets API setup scope = [ "https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets', "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive" ] creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope) client = gspread.authorize(creds) sheet = client.open("Coded Aperture Database").sheet1 def listFiles(size): results = drive_service.files().list( pageSize=size, fields="nextPageToken, files(id, name)").execute() items = results.get('files', []) if not items: print('No files found.') else: print('Files:') for item in items: print('{0} ({1})'.format(item['name'], item['id'])) def downloadFile(file_id, filepath):
Motor = GPIO.PWM(18, 50) # 50 Hz Frequency Motor.start(0) # initialization #json filename for credentials JSON = 'leafy.json' json_key = json.load(open(JSON)) scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name('leafy.json', scope) client = gspread.authorize(creds) sheet = client.open('Results_Raspberry_Pi').sheet1 ADDR = '192.168.86.79' PORT = 10000 # Create a UDP socket client_sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM) server_address = (ADDR, PORT) device_id = sys.argv[1] if not device_id: sys.exit('The device id must be specified.') print('Bringing up device {}'.format(device_id))
absentsheet.update_cell(int(nar(absentsheet)), 1, str(pernum)) absentsheet.update_cell(int(nar(absentsheet))-1, 2, str(absentlist)) JSON_FILENAME = '/home/vishal/Downloads/RFID-b01812ec4010.json' # Google sheet to save to` GSHEET_NAME = 'Attendance Sheet' SCOPES = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] #load credentials from json and open the spreadsheet for writing json_key = json.load(open(JSON_FILENAME)) creds = ServiceAccountCredentials.from_json_keyfile_name(JSON_FILENAME, SCOPES) client = gspread.authorize(creds) log = client.open(GSHEET_NAME).get_worksheet(1) log.clear() names = client.open(GSHEET_NAME).sheet1 absentsheet = client.open(GSHEET_NAME).get_worksheet(3) signedin = [] signedout = [] lastentry = [00000, 0] row = 1 col = 1 per=str(1) timeout=15 pernum=str(int(per)+1) period="Period "+pernum
def get_workbook(config): creds = get_credentials(config) client = gspread.authorize(creds) wks = client.open(config['workbook']) wk = wks.get_worksheet(0) return wk
async def lookup2(self, ctx, *, name): """Lookup contact info""" # Your code will go here member = ctx.author message = await member.send('Fetching Information, Please Wait') client = gspread.authorize(creds) sheet = client.open("Draconian Fleet Contact Sheet").sheet1 try: cell = sheet.find(name.capitalize()) row = cell.row values_list = sheet.row_values(row) discordid = discord.Member fname = values_list[0] lname = values_list[1] pname = values_list[3] dob = values_list[4] email = values_list[5] phone = values_list[6] ship = values_list[7] rank = values_list[8] homeaddr = values_list[9] mailaddr = values_list[10] paypale = values_list[11] prefcont = values_list[12] allerg1 = values_list[13] allerg2 = values_list[14] cname = values_list[15] cphone = values_list[16] crel = values_list[17] faceurl = values_list[18] discordid = values_list[19] updated = values_list[20] wname = fname + ' ' + lname userid = self.bot.get_user(int(discordid)) discordav = userid.avatar_url discordhandle = userid.name contact1 = '**Pirate Name:** ' + pname + '\n**Phone:** ' + phone + '\n**Email:** ' + email + '\n**Discord:** ' + discordhandle contact2 = '**Name:** ' + cname + '\n**Phone:** ' + cphone + '\n**Relationship:** ' + crel contact3 = '**Medical Allergies:** ' + allerg1 + '\n**Food Allergies:** ' + allerg2 contact4 = '**Birthday:** ' + dob contact5 = '**Ship:** ' + ship + '\n**Rank:** ' + rank embed = discord.Embed(colour=discord.Colour(0x106bca)) embed.set_author( name=wname, icon_url= "https://i2.wp.com/www.draconianfleet.com/wp-content/uploads/2017/09/cropped-frame-DF2-1.png?w=250&ssl=1" ) embed.set_thumbnail(url=discordav) embed.set_image(url=faceurl) embed.add_field(name="Contact Info", value=contact1, inline=True) embed.add_field(name="Emergency Contact", value=contact2, inline=True) embed.add_field(name="Allergies", value=contact3, inline=True) embed.add_field(name="Personal", value=contact4, inline=True) embed.add_field(name="Fleet Alignment", value=contact5, inline=False) await message.edit(content="", embed=embed) except gspread.exceptions.CellNotFound: await message.edit('Name not found')
# @Dhhearj Kumar Rao import gspread from oauth2client.service_account import ServiceAccountCredentials from oauth2client import client # use creds to create a client to interact with the Google Drive API scope = ['https://spreadsheets.google.com/feeds'] creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope) client = gspread.authorize(creds) # Find a workbook by name and open the first sheet # Make sure you use the right name here. sheet = client.open("students").sheet1 print(sheet.title) print(sheet.get_all_values()) print(sheet.row_count) print(sheet.row_values(1)); sheet.update_cell(1, 1, "Name") sheet.update_cell(4, 1, "Hello word!") sheet.update_cell(4, 2, "google.com") sheet.clear(); print(sheet.find("rao")) print(sheet.findall("rao")) cell_list = sheet.range('A1:C7') for cell in cell_list: print(cell) sht = client.open('students')
def addresourcelink(type_, link): client = authorizeGspread() sheet = client.open("Waitlist").sheet1 insertRow = [link, type_] sheet.append_row(insertRow)
#Web Scraper for BoA to find balance information for bank accounts #For personal non-profit use only from __future__ import print_function from apiclient.discovery import build from httplib2 import Http from oauth2client import file, client, tools import gspread from oauth2client.service_account import ServiceAccountCredentials #Access Google Sheet For Editing scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( 'client_secret.json', scope) client = gspread.authorize(creds) sheet = client.open('Finances').sheet1 counter = sheet.cell(80, 3).value while (1): #time.sleep(5) newValue = sheet.cell(80, 3).value if (newValue != counter): counter = newValue exec(open("./boaParser2.py").read())
from oauth2client import tools from oauth2client.file import Storage from apiclient.http import MediaFileUpload, MediaIoBaseDownload scope = [ "https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets', "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive" ] creds = ServiceAccountCredentials.from_json_keyfile_name("creds1.json", scope) client = gspread.authorize(creds) sheet = client.open("Nilesh").sheet1 data = sheet.get_all_records() col5 = sheet.col_values(17) i = 1 j = ".pdf" pdf_list = [ 8, 16, 19, 43, 52, 57, 61, 68, 71, 73, 80, 81, 88, 97, 99, 105, 119, 128, 133, 134, 140, 142, 144, 146, 147, 148, 154, 157, 160, 162, 164, 166, 186 ] try: import argparse flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args() except ImportError: flags = None
os.path.dirname(os.path.abspath(__file__))) return os.path.join(base_path, relative_path) # use credentials to create a client to interact with the Google Drive API scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets' ] credentials = ServiceAccountCredentials.from_json_keyfile_name( resource_path('drivetimes-f9856c45d8c0.json'), scope) # gspread old drive delete client = gspread.authorize(credentials) sheet = client.open("Weezer Spring Tour 2019").sheet1 codeSheet = client.open("Weezer Spring Tour 2019").worksheet('codes') # Extract all of the values driveList = sheet.get_all_records() # Leave a five star review and i'll leave you one too """ DELETE BLANK ROWS """ print('Deleting blank rows from the sheet...') rowIndex = 2 # google sheets are not zero indexed and you want to skip the header row when you deleete for rowDictionary in driveList: if rowDictionary['date'] == '':
def process_sheet(): # use creds to create a client to interact with the Google Drive API scope = [ 'https://spreadsheets.google.com/feeds', # Getting the scope right is incredibly important 'https://www.googleapis.com/auth/drive' ] # Not totally sure what this means but it works creds = ServiceAccountCredentials.from_json_keyfile_name( 'gspread_secret.json', scope) client = gspread.authorize(creds) # Open the space requests sheet sheet = client.open( "Hanszen Space Request Form (2018-2019) (Responses)").sheet1 # Spreadsheet starts at row 3 i = 3 end = False while not end: GMT_OFF = '-06:00' # Make sure that the entry exists timestamp = re.search("\'.*\'", str(sheet.cell(i, 1))).group(0) if timestamp != "\'\'": # Get event information Space = re.search("\'.*\'", str(sheet.cell(i, 5))).group(0).replace("\'", "") Event = re.search("\'.*\'", str(sheet.cell(i, 6))).group(0).replace("\'", "") Date = re.search("\'.*\'", str(sheet.cell(i, 7))).group(0).replace("\'", "") Start = re.search("\'.*\'", str(sheet.cell(i, 8))).group(0).replace( "\'", "").replace(" ", "") End = re.search("\'.*\'", str(sheet.cell(i, 9))).group(0).replace( "\'", "").replace(" ", "") Recurring = re.search("\'.*\'", str(sheet.cell(i, 10))).group(0).replace( "\'", "").replace(" ", "") month, day, year = Date.split("/") # Add leading zeroes to month and day if len(month) == 1: month = "0" + month if len(day) == 1: day = "0" + day Date = "{0}-{1}-{2}".format(year, month, day) Start = str(dparser.parse(Start)).split(" ")[1] End = str(dparser.parse(End)).split(" ")[1] summary = "{0}, {1}".format(Event, Space) dt_start = "{0}T{1}{2}".format(Date, Start, GMT_OFF) dt_end = "{0}T{1}{2}".format(Date, End, GMT_OFF) event_json = { 'summary': summary, # Title of the event. 'start': { 'dateTime': dt_start, }, # "location": "A String", # Geographic location of the event as free-form text. Optional. 'end': { 'dateTime': dt_end, # "dateTime": "2018-03-24T14:00:00%s" % GMT_OFF }, } # Write the event to the calendar write_event(event_json) i += 1 else: end = True