def setPermissions(self, resource, users_to_add=[]):
"""
Sets the persmissions of the provided resource so that only the app's
username, the staff person, and a small group of others can view and
edit the resource.
Input: resource - the Resource instance to set permissions for
Side Effects: The ACL of the provided resource will be changed so that
only the above mentioned group of users will be able to
view and edit the resource.
"""
acl_feed = tryXTimes(lambda: self.docsClient.GetResourceAcl(
resource).entry)
for acl in acl_feed:
if acl.role.value == 'owner' or \
acl.scope.value == settings['app_username']:
continue
tryXTimes(lambda: self.docsClient.DeleteAclEntry(acl))
users_to_add.extend(settings['all_access_users'])
for user in set(users_to_add):
new_acl = AclEntry.GetInstance(role='writer', scope_type='user',
scope_value=user)
tryXTimes(lambda: self.docsClient.AddAclEntry(resource, new_acl,
send_notifications=False))
def getListFeed(self, spreadsheet, title):
"""
A general method for finding and retrieving the ListFeed for a
sheet of a specified name (title).
Input: spreadsheet - a Resource instance of a spreadsheet with
a sheet of the specified name.
title - The specific sheet to look for
Output: A ListFeed of the specified sheet
"""
if not isinstance(spreadsheet, Resource) and \
spreadsheet.GetResourceType() != 'spreadsheet':
raise TypeError('a Resource instance of type spreadsheet required')
sid = spreadsheet_id(spreadsheet)
q = WorksheetQuery(title=title)
sheets = tryXTimes(lambda: self.spreadsheetsClient.GetWorksheets(sid,
q=q).entry)
if len(sheets) == 0:
raise LookupError('Provided spreadsheet does not have a %s sheet' %
title)
sheet_id = worksheet_id(sheets[0])
return tryXTimes(lambda: self.spreadsheetsClient.GetListFeed(sid,
sheet_id).entry)
def spreadsheetsClient(self):
if self.__spreadsheetsClient__ is None:
self.__spreadsheetsClient__ = SpreadsheetsClient()
tryXTimes(lambda: self.__spreadsheetsClient__.ClientLogin(
settings['app_username'],
settings['app_password'],
settings['app_name']))
assert self.__spreadsheetsClient__
return self.__spreadsheetsClient__
def createBouncedSpreadsheet(self, batch):
"""
Creates a new Google Spreadsheet to store the rows from the provided
Batch instance that caused a bounce, and returns a Spreadsheet
instance.
The created Spreadsheet will have a meta sheet with just one attribute
(id of the provided Batch). It will also have a Raw sheet that
contains the data from Batch of all people who bounced, plus a Person
ID number, a time at which the bounce was detected, and the bounce
message.
Input: batch - a Batch instance or key of a Batch instance to create
a bounced spreadsheet for.
Output:A tuple with a Spreadsheet instance for the newly created
spreadsheet, and a Worksheet for the new spreadsheet's Raw sheet.
Side Effect: A new spreadsheet will be created on Google Drive, in a
folder specified by the failed_spreadsheets_folder attribute of
settings.
"""
batch = Batch.verifyOrGet(batch)
ogs = batch.spreadsheets.get()
if not ogs:
raise LookupError('Provided batch does not have a Google' +
'Spreadsheet associated with it.')
ogsid = ogs.gsid
batch_id = str(batch.key())
headers_to_add = ['Occurred', 'Message', 'Person ID']
(new_spreadsheet, new_raw_sheet) = self.cloneSpreadsheetForFailure(
ogsid, batch_id, " - Bounced", headers_to_add)
ngsid = spreadsheet_id(new_spreadsheet)
nrsid = worksheet_id(new_raw_sheet)
# Get the Bounces for this batch and populate the cloned Raw sheet
#TODO see about making this a batch operation
nbslf = tryXTimes(lambda: self.spreadsheetsClient.GetListFeed(ngsid,
nrsid).entry)
for i, bounce in enumerate(batch.bounces):
bounce_dict = bounce.person.asDict()
# Adjust some keys, add additional key/values
bounce_dict['person_id'] = bounce.person.key()
bounce_dict['occurred'] = bounce.occurred
bounce_dict['message'] = bounce.message
bounce_row = self.personDictToRow(bounce_dict)
bounce_entry = nbslf[i]
bounce_entry.from_dict(bounce_row.to_dict())
tryXTimes(lambda: self.spreadsheetsClient.Update(bounce_entry,
force=True))
self.setPermissions(new_spreadsheet, [batch.staff_email])
return (new_spreadsheet, new_raw_sheet)
def createOptOutSpreadsheet(self, batch):
"""
Creates a new Google Spreadsheet to store the rows from the provided
Batch instance that opted out, and returns a Spreadsheet instance.
The created Spreadsheet will have a meta sheet with just one attribute
(id of the provided Batch). It will also have a people sheet that
contains the data from Batch of all people who opted out, plus a Person
ID number and a reason given for opting out.
Input: batch - a Batch instance to create an opt-out spreadsheet for.
Output: A tuple with a Spreadsheet instance for the newly created
spreadsheet, and a Worksheet for the new spreadsheet's Raw sheet.
Side Effect: A new spreadsheet will be created on Google Drive, in a
folder specified by the failed_spreadsheets_folder attribute of
settings.
"""
batch = Batch.verifyOrGet(batch)
ogs = batch.spreadsheets.get()
if not ogs:
raise LookupError('Provided batch does not have a Google' +
'Spreadsheet associated with it.')
ogsid = ogs.gsid
batch_id = str(batch.key())
headers_to_add = ['Occurred', 'Reason', 'Person ID']
(new_spreadsheet, new_raw_sheet) = self.cloneSpreadsheetForFailure(
ogsid, batch_id, " - OptOuts", headers_to_add)
ngsid = spreadsheet_id(new_spreadsheet)
nrsid = worksheet_id(new_raw_sheet)
# Get the Optouts for this batch and populate the cloned Raw sheet
#TODO see about making this a batch operation
nrslf = tryXTimes(lambda: self.spreadsheetsClient.GetListFeed(ngsid,
nrsid).entry)
for i, optout in enumerate(batch.optouts):
optout_dict = optout.person.asDict()
# Adjust some keys, add additional key/values
optout_dict['occurred'] = optout.occurred
optout_dict['reason'] = optout.reason
optout_dict['person_id'] = optout.person.key()
optout_row = self.personDictToRow(optout_dict)
optout_entry = nrslf[i]
optout_entry.from_dict(optout_row.to_dict())
tryXTimes(lambda: self.spreadsheetsClient.Update(optout_entry,
force=True))
self.setPermissions(new_spreadsheet, [batch.staff_email])
return (new_spreadsheet, new_raw_sheet)
def spreadsheets(self, folder, query=None):
"""
Generates a list of Google Spreadsheets based on the Resource
instance provided, which is assumed to be a folder. If the provided
folder contains other folders, they will be recursively searched
for spreadsheets and other folders, breadth first.
Returned spreadsheets will be Resource instances.
Input: folder - a Resource instance representing a folder to be
search on Drive.
query - a Query instance that is used to search for
spreadsheets with specific attributes.
Output: a list of Resource instances that represent spreadsheets
contained in the provided folder or its subfolders.
"""
if not isinstance(folder, Resource) and \
folder.GetResourceType() != 'folder':
raise TypeError('a Resource instance of type folder required')
folders = []
contents = tryXTimes(lambda: self.docsClient.GetResources(
uri=folder.content.src, q=query))
for entry in contents.entry:
entry_id = entry.resource_id.text.replace('spreadsheet:', '')
if entry.GetResourceType() == 'folder':
folders.append(entry)
elif entry.GetResourceType() == 'spreadsheet' and \
entry_id != settings['base_spreadsheet_id']:
yield entry
for folder in folders:
for spreadsheet in self.spreadsheets(folder):
yield spreadsheet
def addRawSheetHeaders(self, new_spreadsheet, headers_to_add):
"""
Alters the headers of the Raw worksheet via the Google Spreadsheets
API for people who were not successfully signed up. Existing
headers in the row will not be touched. headers_to_add will be
appended to the headers row.
Input: new_spreadsheet - Instance of the new Google Spreadsheet
headers_to_add - A list of strings, representing headers
that are to be added to the new
spreadsheet's Raw sheet, in addition to
headers from the original spreadsheet's
Raw sheet.
Side Effects: The Raw sheet of new_spreadsheet will have
headers_to_add appended to its header row.
"""
ngsid = spreadsheet_id(new_spreadsheet)
nrsid = self.rawSheetId(new_spreadsheet)
# Determine the start and end positions of the headers to add
new_raw_headers = tryXTimes(lambda: self.spreadsheetsClient.GetCells(
ngsid, nrsid, q=CellQuery(1, 1)).entry)
new_headers_start = len(new_raw_headers) + 1
# Add the new headers
new_raw_headers_update = BuildBatchCellsUpdate(ngsid, nrsid)
for i, cell in enumerate(headers_to_add):
try:
new_header_pos = new_headers_start + i
new_raw_headers_update.AddSetCell(
1, new_headers_start + i, cell)
new_header = tryXTimes(lambda: self.spreadsheetsClient.GetCell(
ngsid, nrsid, 1, new_header_pos))
new_header.cell.input_value = cell
#self.spreadsheetsClient.update(new_header)
except Exception as e:
logging.exception(e)
tryXTimes(lambda: self.spreadsheetsClient.batch(new_raw_headers_update,
force=True))
return True
def deleteFirstRow(self, gsid, wsid):
"""
Deletes the first row in the spreadsheet and worksheet associated with
ogsid and wsid.
Input: gsid - ID of the Google Spreadsheet to modify
wsid - ID of the worksheet to modify
Output: True
Side Effect: The first row on the corresponding worksheet on Google
Drive is deleted
"""
# Deleting the first row actually involves:
# 1.) Overwriting the values of the first row with those of the second
#first_row = self.spreadsheetsClient.GetCells(gsid, wsid,
# q=CellQuery(1, 1)).entry
second_row = tryXTimes(lambda: self.spreadsheetsClient.GetCells(gsid,
wsid, q=CellQuery(2, 2)).entry)
first_row_update = BuildBatchCellsUpdate(gsid, wsid)
for i, cell in enumerate(second_row):
first_row_update.AddSetCell(1, i+1, cell.content.text)
tryXTimes(lambda: self.spreadsheetsClient.batch(first_row_update,
force=True))
# 2.) Deleting the second row
rows = tryXTimes(lambda: self.spreadsheetsClient.GetListFeed(gsid,
wsid).entry)
second_row = rows[0] # Think about that for a second
tryXTimes(lambda: self.spreadsheetsClient.Delete(second_row))
return True
def isFirstRawRowValid(self, spreadsheet):
"""
Indicates weather the first row of the Raw sheet is a valid set of
headers.
Input: spreadsheet - a Spreadsheet instance
Output: True if the first row is the set of expected headers, False
otherwise
"""
required_headers = ['email', 'firstname', 'lastname', 'fullname']
sid = spreadsheet_id(spreadsheet)
rsid = self.rawSheetId(spreadsheet)
row_cells = tryXTimes(lambda: self.spreadsheetsClient.GetCells(sid,
rsid, q=CellQuery(1, 1)).entry)
for i, cell in enumerate(row_cells):
if cell.content.text.lower().replace(" ", "") in required_headers:
required_headers.remove(cell.content.text
.lower().replace(" ", ""))
if not required_headers:
break
return not required_headers
def getWorksheet(self, spreadsheet, title):
"""
Returns a Worksheet instance for the worksheet in the provided
spreadsheet of the provided title. This method assumes that a
spreadsheet should only have one worksheet of the provided title.
Input: spreadsheet - An instance of a Google Spreadsheet to retrive
a sheet from
title - String indicating the title of the worksheet to get
Output: A worksheet instance if the desired worksheet can be found,
None otherwise.
Throws: IndexError if there is more than one worksheet of the
provided title.
"""
sid = spreadsheet_id(spreadsheet)
sheets = tryXTimes(lambda: self.spreadsheetsClient.GetWorksheets(sid,
q=WorksheetQuery(title=title)
).entry)
if not sheets:
return None
if len(sheets) != 1:
raise IndexError('Spreadsheet %s should have 1 %s sheet'
% (sid, title))
return sheets[0]
def cloneSpreadsheetForFailure(self, ogsid, batch_id,
suffix=None, headers_to_add=[]):
"""
Creates a new Google Spreadsheet that is a clone of the
structure/metadata of the spreadsheet used as input for the provided
batch. The cloned spreadsheet is intended to be used by this system to
output information on signups that failed. Returns a reference to the
resulting Spreadsheet and the Raw sheet as a tuple.
Input: ogsid - Original Google Spreadsheet ID; id of the spreadsheet
we are going to clone.
batch_id - ID/key of the batch that these spreadsheets are
associated with.
suffix - a string to be appended to the name of the original
spreadsheet to form the name of the new spreadsheet.
headers_to_add - a list of headers to add to the Raw sheet in
the cloned spreadsheet
Output: a tuple that contains a Spreadsheet instance refering to the
newly created spreadsheet and a Worksheet instance referring to
the Raw sheet of the newly created spreadsheet.
"""
# Get original and base spreadsheets
original_spreadsheet = tryXTimes(
lambda: self.docsClient.GetResourceById(ogsid))
base_spreadsheet = tryXTimes(lambda: self.docsClient.GetResourceById(
settings['base_spreadsheet_id']))
# Get Failed Signups folder
failed_signups_folder = tryXTimes(
lambda: self.docsClient.GetResourceById(
settings['failed_signups_folder_id']))
# Create a new Spreadsheet in Failed Signups folder
new_spreadsheet_title = original_spreadsheet.title.text + suffix
new_spreadsheet = tryXTimes(lambda: self.docsClient.CopyResource(
base_spreadsheet, new_spreadsheet_title))
tryXTimes(lambda: self.docsClient.MoveResource(new_spreadsheet,
failed_signups_folder))
ngsid = spreadsheet_id(new_spreadsheet)
# Add the prev_batch column to the meta sheet of the new copy
new_meta_sheet = self.getWorksheet(new_spreadsheet,
settings['meta_sheet_title'])
nmsid = worksheet_id(new_meta_sheet)
new_meta_headers = tryXTimes(lambda: self.spreadsheetsClient.GetCells(
ngsid, nmsid, q=CellQuery(1, 1)).entry)
prev_header_pos = len(new_meta_headers) + 1
new_prev_header = tryXTimes(lambda: self.spreadsheetsClient.GetCell(
ngsid, nmsid, 1, prev_header_pos))
new_prev_header.cell.input_value = 'prevbatch'
tryXTimes(lambda: self.spreadsheetsClient.update(new_prev_header))
# Put the meta sheet values from the original spreadsheet into the new
# spreadsheet, plus the prevbatch value
orgi_meta_feed = self.getMetaListFeed(original_spreadsheet)
entry = orgi_meta_feed[0]
entry.set_value('prevbatch', batch_id)
tryXTimes(lambda: self.spreadsheetsClient.AddListEntry(entry, ngsid,
nmsid))
# TODO
# Add the additional headers to the Raw sheet
# Create the cloned Raw sheet
self.addRawSheetHeaders(new_spreadsheet, headers_to_add)
nrsid = self.rawSheetId(new_spreadsheet)
new_raw_sheet = tryXTimes(lambda: self.spreadsheetsClient.GetWorksheet(
ngsid, nrsid))
return (new_spreadsheet, new_raw_sheet)
