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
