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)