def set_departure_time(date, name, time):
assert coming_to_church(date, name)
spreadsheet = gspread_connection.connect()
worksheet = spreadsheet.worksheet('Coming {}'.format(date))
time_column = get_info.get_column_index('departure time', worksheet)
person_row = get_person_row(date, name)
worksheet.update_cell(person_row, time_column, time)
def create_person(name):
spreadsheet = gspread_connection.connect()
worksheet = spreadsheet.worksheet('Coming 2017-02-17')
empty_row = get_info.get_empty_row(worksheet)
worksheet.update_cell(empty_row, 1, name)
worksheet.update_cell(empty_row, 2, "unknown")
worksheet.update_cell(empty_row, 3, "unknown")
def add_person(name, number, pickup=None):
'Add a person to the "People" worksheet. Returns their id number.'
# connect to spreadsheet
spreadsheet = gspread_connection.connect()
worksheet = spreadsheet.worksheet('People')
# get the column numbers
phone_number_column = get_info.get_column_index("phone number", worksheet)
id_column = get_info.get_column_index("id", worksheet)
name_column = get_info.get_column_index("name", worksheet)
pickup_column = get_info.get_column_index("default pickup location",
worksheet)
# get the id for the new person
id_number = get_info.next_id()
# parse and format phone number
number = phonenumbers.parse(number, "US")
number = phonenumbers.format_number(
number, phonenumbers.PhoneNumberFormat.NATIONAL)
# find the next empty row in the worksheet
row = get_info.get_empty_row(worksheet)
# set the cells on the worksheet
worksheet.update_cell(row, name_column, name)
worksheet.update_cell(row, id_column, id_number)
worksheet.update_cell(row, phone_number_column, number)
worksheet.update_cell(row, pickup_column, pickup)
return id_number
def set_as_rider(date, name):
'Sets a person as rider for the date'
assert coming_to_church(date, name)
spreadsheet = gspread_connection.connect()
worksheet = spreadsheet.worksheet('Coming {}'.format(date))
field_column = get_info.get_column_index('driver/rider', worksheet)
person_row = get_person_row(date, name)
worksheet.update_cell(person_row, field_column, 'rider')
def person_field_missing(date, name, field):
'Returns true if the field has "unknown" in it'
assert coming_to_church(date, name)
spreadsheet = gspread_connection.connect()
worksheet = spreadsheet.worksheet('Coming {}'.format(date))
field_column = get_info.get_column_index(field, worksheet)
person_row = get_person_row(date, name)
return worksheet.cell(person_row, field_column).value == 'unknown'
def get_person_row(date, name):
"Gets the row for a person. Returns -1 if they're not coming this week"
if not coming_to_church(date, name):
return -1
else:
spreadsheet = gspread_connection.connect()
worksheet = spreadsheet.worksheet('Coming {}'.format(date))
name_column = get_info.get_column_index('name', worksheet)
for row, person_name in enumerate(worksheet.col_values(name_column),
1):
if name == person_name:
return row
def write_to_spreadsheet(self):
'''Writes this person's info to the spreadsheet. Returns exception if
they have a duplicate id'''
SPREADSHEET = gspread_connection.connect()
worksheet = SPREADSHEET.worksheet('People')
id_column_index = get_info.get_column_index('id', worksheet)
# get list of ids and strip the header on the spreadsheet
ids = list()
for id_number in worksheet.col_values(id_column_index)[1:]:
try:
ids.append(int(id_number))
except ValueError: # ignore the value if it can't be converted to an int.
pass
if self._id in ids:
raise Exception("There is a person on the spreadsheet who has the same ID!")
empty_row = get_info.get_empty_row(worksheet)
name_column_index = get_info.get_column_index('name', worksheet)
pickup_column_index = get_info.get_column_index('default pickup location', worksheet)
phone_number_column_index = get_info.get_column_index('phone number', worksheet)
worksheet.update_cell(empty_row, id_column_index, self.get_id())
worksheet.update_cell(empty_row, name_column_index, self.get_name())
worksheet.update_cell(empty_row, pickup_column_index, self.get_pickup_location())
worksheet.update_cell(empty_row, phone_number_column_index, self.get_national_phone())
def coming_to_church(date, name):
'Returns true/false depending on if the person is coming this week'
spreadsheet = gspread_connection.connect()
worksheet = spreadsheet.worksheet('Coming {}'.format(date))
name_column = get_info.get_column_index('name', worksheet)
return name in worksheet.col_values(name_column)
def find(self, attribute, value, single_row=True):
'''Returns the row(s) that have the value
in the given column.'''
rows_to_return = []
for row in self._rows:
if row[attribute].get().lower() == value.lower():
if single_row:
return row
else:
rows_to_return.append(row)
if single_row: # couldn't find a row
return None
else: # return the list of rows, empty or not
return rows_to_return
def find_all(self, attribute, value):
return self.find(attribute, value, single_row=False)
def commit(self):
for row in self._rows:
row.commit()
if __name__ == '__main__':
import time
start = time.time()
spreadsheet = gspread_connection.connect()
worksheet = RideWorksheet(spreadsheet, 'Coming 2017-02-17')
end = time.time()
worksheet.print()
print("time elapsed: {:.2f} seconds".format(end - start))
# get_info.py
# retrieves information from the google spreadsheet
import person
import gspread_connection
import phonenumbers
# 1. go to https://console.developers.google.com/iam-admin/projects
# 2. enable drive API
# 3. go to Credentials > New Credentials > Service account key >
# use defaults and it will download a json file
# 4. insert the downloaded json file for credentials here
# 5. create a google spreadsheet on your own account and "share" it with
# the email included in the json file
SPREADSHEET = gspread_connection.connect()
def get_person_by_phone(phone_number, region='US'):
'''Given a phone number, returns the corresponding Person object.
None if they don't exist'''
# Converts phonen_number into phonenumbers.PhoneNumber object if they aren't already
if not isinstance(phone_number, phonenumbers.PhoneNumber):
phone_number = phonenumbers.parse(phone_number, region)
worksheet = SPREADSHEET.worksheet("People")
phone_number_column = get_column_index("phone number", worksheet)
id_column = get_column_index("id", worksheet)
name_column = get_column_index("name", worksheet)
pickup_column = get_column_index("default pickup location", worksheet)