def model_cell(wks: Worksheet, index: str) -> Cell: '''???''' cell = Cell(index) wks.unlink() cell.text_format['fontSize'] = 10 cell.text_format['bold'] = True wks.link() return cell
def update_pyladies_list(gsheets_api, directory_name, last_download_sheet, tracker_sheet): try: last_download = gsheets_api.get_worksheet_by_title( sheet=directory_name, worksheet_title=last_download_sheet) except SpreadsheetNotFound: print(f'Download sheet: {last_download_sheet} not found') return False tracker = gsheets_api.get_worksheet_by_title(sheet=directory_name, worksheet_title=tracker_sheet) if not last_download: print("No latest data, skipping PyLadies list update ...") return False last_download_data = last_download.get_all_records() tracker_data = tracker.get_all_records() existing_pyladies_emails = list( map(lambda record: record.get('Email Address [Required]'), tracker_data)) header = tracker.row_values(1) new_pyladies = list( filter( lambda record: record.get('Email Address [Required]') not in existing_pyladies_emails, last_download_data)) new_pyladies += tracker_data new_pyladies = sorted( new_pyladies, key=lambda record: record.get('First Name [Required]')) cells_to_update = list( map( lambda col_name: Cell( row=1, col=header.index(col_name) + 1, value=col_name), header)) for indx, record in enumerate(new_pyladies): if not record.get('First Name [Required]'): continue for h_indx, col in enumerate(header): cells_to_update.append( Cell( row=indx + 2, # Row aren't 0 based, Row 1 is the header col=h_indx + 1, # Col aren't 0 based like an index :-) value=record.get(col))) tracker.clear() tracker.update_cells(cells_to_update) return True
def get_pyladies_emails_for_survey(gsheets_api, directory_name, tracker_sheet, chapter_directory_sheet): tracker = gsheets_api.get_worksheet_by_title(sheet=directory_name, worksheet_title=tracker_sheet) tracker_data = tracker.get_all_records() chapter_directory = gsheets_api.get_worksheet_by_title( sheet=directory_name, worksheet_title=chapter_directory_sheet) chapter_directory_data = chapter_directory.get_all_records() chapter_directory_emails = list( map(lambda record: record.get('What is your PyLadies official email?'), chapter_directory_data)) cells_to_update, users_to_email = [], [] header = tracker.row_values(1) chapter_directory_indx = header.index('Chapter Directory') for indx, record in enumerate(tracker_data): email = record.get('Email Address [Required]') if email in chapter_directory_emails: record['Chapter Directory'] = 'YES' else: record['Chapter Directory'] = 'NO' users_to_email.append(record) cells_to_update.append( Cell( row=indx + 2, # Row aren't 0 based, Row 1 is the header col=chapter_directory_indx + 1, # Col aren't 0 based like an index :-) value=record.get('Chapter Directory'))) print(f'Done with {indx}') tracker.update_cells(cells_to_update) return users_to_email
def create_cell_list(ws,patient_link,colToUpdate,output,df): """ creates a list of cells to update all at once with the call worksheet.update_cells(cell_list) from Gspread Use as: cell_list=create_cell_list(ws,test_df['patient_link'].tolist(), report_column,test_df['Output'].tolist()) then: ws.update_cells(cell_list) """ cell_list = [] for i in range(len(patient_link)): # print(i) cellLookup = df[df['patient_id']==patient_link[i]].index[0]#ws.find(patient_link[i]) # cellToUpdate = ws.cell(cellLookup+2, colToUpdate) cellToUpdate = Cell(cellLookup+2, colToUpdate) cellToUpdate.value = output[i] cell_list.append(cellToUpdate) return cell_list
def convert_values_to_cells(values: List[list], *, start_row=1, start_col=1) -> List[Cell]: _log.debug("converting values to cells...") return [ Cell(row=row_idx, col=col_idx, value=value) for row_idx, row in enumerate(values, start=start_row) for col_idx, value in enumerate(row, start=start_col) ]
def update(self, field=None, value=None, row_numbers=None, where=None, new_values=None): """Updates the values for records matching certain conditions. Args: field: str, the field to check. value: str, the value to check record[field] against. row_numbers: [int], which rows to update. where: [(str, str, str)], list of conditions (tuples). new_values: {str: str}, the values to replace. Keys should match fields in header, and values are the new values. """ if field or value: if not field and value: raise GSpreadDbError( 'Field and value must both be assigned or both be None.') if row_numbers or where: raise GSpreadDbError('Only one way to select rows can be' 'used at the same time.') if row_numbers and where: raise GSpreadDbError( 'Only one way to select rows can be used at the same time.') if not any([field, value, row_numbers, where]): raise GSpreadDbError( 'Cannot update records without a select mechanism.') self._parse_header() if not row_numbers: if field: where = [(field, 'eq', value)] row_numbers = [] for row_number, record in enumerate(self.get_all_values()[1:], 2): if self._record_matches_conditions(record, where): row_numbers.append(row_number) cell_list = [] for row_number in row_numbers: for field_to_update, new_value in new_values.items(): # fields_map is 0-based, while coordinates are passed # as 1-based values, thus the +1. col_number = self.fields_map[field_to_update] + 1 cell = Cell(row_number, col_number, new_value) cell_list.append(cell) if len(cell_list) > 0: cell_list.sort(key=lambda x: (x.row, x.col)) self.update_cells(cell_list)
def update_frts(spreadsheet): ''' This method is used to push the changes from the frt spreadsheet to the database. It includes addition, updation and deletion of data. :spreadsheet object: the access to google spreadsheet. ''' # Read the frt sheet and make a update list. frt_sheet = spreadsheet.get_worksheet(0) update_list = frt_sheet.col_values(7) gs_cells = [] delete_rows = [] print('read sheet') for i, update_status in enumerate(update_list, 1): if update_status == SUBMIT_MESSAGE: try: values_list = frt_sheet.row_values(i) frt = Frt(*values_list) frt.insert_to_frt_table() frt.update_frt_place_table() frt.add_rti_replies() frt.add_govt_link() frt.add_media_link() # Acknowledge the database updation on the spreadsheet. gs_cells.append(Cell(i, 1, value=frt.id)) gs_cells.append(Cell(i, 7, value=ACCEPT_MESSAGE)) except: gs_cells.append(Cell(i, 7, value=REJECT_MESSAGE)) if update_status == DELETE_MESSAGE: values_list = frt_sheet.row_values(i) frt = Frt(*values_list) frt.delete_frt() delete_rows.append(i) delete_rows.reverse() for i in delete_rows: frt_sheet.delete_rows(i) if gs_cells: frt_sheet.update_cells(gs_cells)
def update_tech_partner(spreadsheet): ''' This method is used to push the changes from the tech_partners spreadsheet to the database. It includes addition, updation and deletion of data. :spreadsheet object: the access to google spreadsheet. ''' tp_sheet = spreadsheet.get_worksheet(1) print('read sheet') update_list = tp_sheet.col_values(5) gs_cells = [] for i, update_status in enumerate(update_list, 1): if update_status == SUBMIT_MESSAGE: values_list = tp_sheet.row_values(i) tech_partner = TechPartner(*values_list) tech_partner.insert_to_tech_partners() gs_cells.append(Cell(i, 1, value=tech_partner.id)) gs_cells.append(Cell(i, 5, value=ACCEPT_MESSAGE)) if gs_cells: tp_sheet.update_cells(gs_cells)
def send_emails(emails, gmail_user, gmail_password, directory_name, email_sheet_name): email_sheet = gsheets_api.get_worksheet_by_title( sheet=directory_name, worksheet_title=email_sheet_name) try: server = smtplib.SMTP_SSL('smtp.gmail.com', 465) server.login(gmail_user, gmail_password) except Exception as e: print(e) print( f'Unable to login into {gmail_user} account, cancelling emails...') return False email_cells = [] for indx, email in enumerate(emails): print(email.get('email_address')) email_address = email.get('email_address') message = MIMEMultipart() message['to'] = email_address message['from'] = gmail_user message['subject'] = '[IMPORTANT] We need your PyLadies Chapter ' \ 'Information for the PyLadies Chapter Directory for voting' message_text = f'Dear {email.get("recipient")},\n\nPlease review and complete your PyLadies Chapter ' \ f'information for the PyLadies Chapter Directory: {email.get("survey_url")}.\n\nWe are ' \ f'using this to update the PyLadies map found on pyladies.com. Additionally your chapter ' \ f'directory information will be used for the forthcoming vote for selecting the PyLadies' \ f'Global Council selection process (https://github.com/pyladies/global-organizing/issues/50). ' \ f'A forthcoming email will be additionally sent detailing this process.' \ f'\n\nIf you have any questions you can email [email protected].\n\nThanks!\n\nLorena Mesa on ' \ f'behalf of the PyLadies Global Team' msg = MIMEText(message_text) message.attach(msg) try: server.sendmail(from_addr=gmail_user, to_addrs=email_address, msg=message.as_string()) print(f'Sent message successfully: {email_address}') email_cells.append( Cell( row=indx + 2, # Row aren't 0 based, Row 1 is the header col=3, # Col aren't 0 based like an index :-) value='YES')) except Exception as e: print( f'An error occurred while trying to send email for {email_address}: {e}' ) email_sheet.update_cells(email_cells) return True
def post_to_spreadsheet(self, character="Y"): """Update spreadsheet with current transaction's has_receipt values.""" worksheet_transactions = defaultdict(list) for transaction in self.transactions: worksheet_transactions[transaction.worksheet].append(transaction) for worksheet, transactions in worksheet_transactions.items(): cell_list = [ Cell( *a1_to_rowcol(transaction.label), character if transaction.has_receipt else "", ) for transaction in transactions ] worksheet.update_cells(cell_list=cell_list)
def clear_expenses(self): """Clear all expenses and notes for the month in all categories.""" _, col_1 = a1_to_rowcol(f"{self.FIRST_DAY_COLUMN}1") _, col_31 = a1_to_rowcol(f"{self.LAST_DAY_COLUMN}1") cell_list = [ Cell(row=row, col=col, value="") for col in range(col_1, col_31 + 1) for row in self.CATEGORY_ROWS.values() ] label_notes = { rowcol_to_a1(cell.row, cell.col): "" for cell in cell_list } self.worksheet.update_cells(cell_list=cell_list) self.worksheet.spreadsheet.client.insert_notes( worksheet=self.worksheet, labels_notes=label_notes, replace=True)
def _prices(self): """ Get all prices and recognize their type. This method practices lazy evaluation too for the same reasons. :return dict: a map sorted by cell label { "D10": (Decimal(3.45), CellType.REGULAR), "D12": (Decimal(4.56), CellType.REGULAR), ... "D13": (Decimal(1.23), CellType.TOTAL), "D15": (Decimal(1.23), CellType.TAX), } """ result = {} _, col = a1_to_rowcol(f"{self.PRICE_COLUMN}1") price_cells = [ Cell(row=row, col=col, value=line[col - 1]) for row, line in enumerate(self.content, 1) if line[col - 1] and row > 1 ] for cell in reversed(price_cells): label = rowcol_to_a1(cell.row, cell.col) amount = price_to_decimal(cell.value, worksheet_title=self.worksheet.title, label=label) is_summary_collected = all(price_type in result for price_type in SUMMARY_TYPES) if is_summary_collected: result[label] = (amount, CellType.REGULAR) # if all summary prices are identified already, then we don't need # to check the color of other prices because the rest of them are # regular prices. That's why we move on to the next cell right away. continue cell_type = self.get_cell_type(label=label) or CellType.REGULAR result[label] = (amount, cell_type) result = dict(natsorted(result.items())) return result
def update_report(report: Report, spreadsheet_id: str = None, credentials: Union[Dict, str] = None): if not spreadsheet_id: spreadsheet_id = os.environ["SPREADSHEET_ID"] creds = get_credentials(credentials) gc = gspread.authorize(creds) ss = gc.open_by_key(spreadsheet_id) sheet = get_sheet(ss, "{} - {}".format(report.code, report.name), rows=2, cols=2) total_rows = sheet.row_count total_cols = sheet.col_count cell_list = sheet.range(1, 1, total_cols, total_rows) for cell in cell_list: cell.value = "" dict_cells = dict(map(lambda x: ((x._row, x._col), x), cell_list)) for i, row in enumerate(report.report, 1): for j, value in enumerate(row, 1): try: v = float(value) except ValueError as err: v = value try: dict_cells[(i, j)].value = v except KeyError as err: cell = Cell(row=i, col=j, value=v) cell_list.append(cell) result = sheet.update_cells(cell_list) return result
def update(self, document_id: Any, new_values: Dict[str, Any]) -> Dict[str, Any] or None: log("UPDATE %s %s" % (repr(document_id), repr(new_values)), logging.DEBUG) new_values_converted = self.__convert_pg_row(new_values) row = self.__find_row_by_id(document_id) if row is None: return None cells = [ Cell(row=row, col=self.__find_column_by_name(key), value=val if val is not None else '') for (key, val) in new_values_converted.items() if key != self.rowid_column and key not in self.formula_columns ] self.sheet.update_cells(cell_list=cells, value_input_option=self.value_input_option) return new_values
def create_prefilled_surveys(users_to_email, directory_name, email_sheet_name): # Use form_url and inspect with dev tools to update these for the questions, if needed form_items = [ 'entry.1005228805', 'entry.1845393520', 'entry.628679392', 'entry.1350360094', 'entry.525776572', 'entry.1794068471', 'entry.1755579263', 'entry.305825898', 'entry.1016984817' ] # This survey form url shouldn't change, but if so ask PyLadies Global Team form_url = 'https://docs.google.com/forms/d/e/1FAIpQLSf43R4FbiIE4z76k5z42UU4HKMKJnTr2ldh4KecE4WRTJZLUw/viewform?' email_sheet = gsheets_api.get_worksheet_by_title( sheet=directory_name, worksheet_title=email_sheet_name) email_sheet.clear() # Instantiate header for email survey sheet today_string = datetime.now().strftime('%Y-%m-%d') email_cells = [ Cell(row=1, col=1, value='Chapter Email'), Cell(row=1, col=2, value='Survey URL'), Cell(row=1, col=3, value=f'{today_string} Email Sent') ] emails_to_send = [] for indx, user in enumerate(users_to_email): email_address = user.get("Email Address [Required]") recipient = f'{user.get("First Name [Required]")} {user.get("Last Name [Required]")}' query_params = { form_items[0]: recipient, form_items[1]: email_address, form_items[2]: f'{user.get("First Name [Required]")}' if not user.get('city') else f'{user.get("City")}', form_items[3]: f'{user.get("Country")}', form_items[4]: f'{user.get("Organizer")}', form_items[5]: f'{user.get("Organizer Email")}', form_items[6]: f'{user.get("Chapter Language")}', form_items[7]: f'{user.get("Chapter MeetUp Website")}', form_items[8]: f'{user.get("Chapter Website")}' } query_string = urlencode(query_params) survey_url = f'{form_url}{query_string}' emails_to_send.append({ 'email_address': email_address, 'recipient': recipient, 'survey_url': survey_url }) # Add each cell in: email, survey link, email sent email_cells.append( Cell( row=indx + 2, # Row aren't 0 based, Row 1 is the header col=1, # Col aren't 0 based like an index :-) value=email_address)) email_cells.append( Cell( row=indx + 2, # Row aren't 0 based, Row 1 is the header col=2, # Col aren't 0 based like an index :-) value=survey_url)) email_cells.append( Cell( row=indx + 2, # Row aren't 0 based, Row 1 is the header col=3, # Col aren't 0 based like an index :-) value='NO')) email_sheet.update_cells(email_cells) return emails_to_send
def parse(self, data, provider=None): index = self.parse_titles(data[0]) items = [] cells_list = [] # use for patch update to reduce write requests usage # skip first two title rows for row in range(3, len(data) + 1): if not row: break item = {} error_message = None values = data[row - 1] is_updated = values[index['_STATUS']].strip().upper( ) if len(values) - 1 > index['_STATUS'] else None try: # only insert item if _STATUS is empty if is_updated in ('UPDATED', 'ERROR'): guid = values[index['_GUID']] # check if it's exists and guid is valid if not superdesk.get_resource_service('events').find_one( guid=guid, req=None): raise KeyError('GUID is not exists') else: guid = generate_guid(type=GUID_NEWSML) # avoid momentsJS throw null timezone value error tzone = values[index['Timezone']] if values[ index['Timezone']] != 'none' else 'UTC' start_datetime = parse(values[index['Start date']] + ' ' + values[index['Start time']]) end_datetime = parse(values[index['End date']] + ' ' + values[index['End time']]) if values[index['All day']] == 'TRUE': start_datetime = parse(values[index['Start date']]) end_datetime = parse( values[index['End date']]) + timedelta(days=1, seconds=-1) if end_datetime < start_datetime: raise ValueError( 'End datetime is smaller than Start datetime') item = { 'type': 'event', 'name': values[index['Event name']], 'slugline': values[index['Slugline']], 'dates': { 'start': local_to_utc(tzone, start_datetime), 'end': local_to_utc(tzone, end_datetime), 'tz': tzone, }, 'definition_short': values[index['Description']], 'definition_long': values[index['Long description']], 'internal_note': values[index['Internal note']], 'ednote': values[index['Ed note']], 'links': [values[index['External links']]], 'guid': guid, 'status': is_updated, } item.setdefault(ITEM_STATE, CONTENT_STATE.DRAFT) occur_status = values[index['Occurence status']] if occur_status and occur_status in self.occur_status_qcode_mapping: item['occur_status'] = { 'qcode': self.occur_status_qcode_mapping.get( values[index['Occurence status']]), 'name': values[index['Occurence status']], 'label': values[index['Occurence status']].lower(), } calendars = values[index['Calendars']] if calendars: item['calendars'] = [{ 'is_active': True, 'name': calendars, 'qcode': calendars.lower(), }] if all(values[index[field]] for field in self.required_location_field): item['location'] = [{ 'name': values[index['Location Name']], 'address': { 'line': [values[index['Location Address']]], 'locality': values[index['Location City/Town']], 'area': values[index['Location State/Province/Region']], 'country': values[index['Location Country']], } }] if all(values[index[field]] for field in self.required_contact_field) \ and (all(values[index[field]] for field in ['Contact First name', 'Contact Last name']) or values[index['Contact Organisation']]): is_public = values[index['Contact Phone Public']] == 'TRUE' if values[index['Contact Phone Usage']] == 'Confidential': is_public = False item['contact'] = { 'honorific': values[index['Contact Honorific']], 'first_name': values[index['Contact First name']], 'last_name': values[index['Contact Last name']], 'organisation': values[index['Contact Organisation']], 'contact_email': [values[index['Contact Email']]], 'contact_address': [values[index['Contact Point of Contact']]], 'contact_phone': [{ 'number': values[index['Contact Phone Number']], 'public': is_public, 'usage': values[index['Contact Phone Usage']], }] } # ignore invalid item missing_fields = [ field for field in self.required_field if not item.get(field) ] if missing_fields: missing_fields = ', '.join(missing_fields) logger.error( 'Provider %s: Event "%s". Missing %s fields', provider.get('name'), item.get('name'), missing_fields, ) error_message = 'Missing ' + missing_fields + ' fields' except UnknownTimeZoneError: error_message = 'Invalid timezone' logger.error('Provider %s: Event "%s": Invalid timezone %s', provider.get('name'), values[index['Event name']], tzone) except (TypeError, ValueError, KeyError) as e: error_message = e.args[0] logger.error('Provider %s: Event "%s": %s', provider.get('name'), item.get('name'), error_message) if error_message: cells_list.extend([ Cell(row, index['_STATUS'] + 1, 'ERROR'), Cell(row, index['_ERR_MESSAGE'] + 1, error_message) ]) elif not is_updated or is_updated == 'UPDATED': cells_list.extend([ Cell(row, index['_STATUS'] + 1, 'DONE'), Cell(row, index['_ERR_MESSAGE'] + 1, ''), ]) if not is_updated: # only update _GUID when status is empty cells_list.append(Cell(row, index['_GUID'] + 1, guid)) items.append(item) return items, cells_list
def create_Cell(row,col,value): cell = Cell(row, col)
def job(server_id: str = None) -> None: """The main job; will get the data from the provided server id, and update the sheet with a new row. :param server_id: The server ID to query """ logger.info('==========================================') logger.info('Starting next run...') logger.info('Getting data...') data = get_data(server_id=server_id) dl_bandwidth = data.get('download', {}).get('bandwidth', 0) / 125000 ul_bandwidth = data.get('upload', {}).get('bandwidth', 0) / 125000 result_url = data.get('result', {}).get('url', 'Unknown') logger.info( f'{dl_bandwidth} Mbps / {ul_bandwidth} Mbps (D/U) --> {result_url}') logger.debug('Data:') logger.debug(pprint.pformat(data, indent=4)) # Update gSheet sheet = get_sheet() next_row = len(sheet.col_values(1)) + 1 timestamp = datetime.strptime(data['timestamp'], DATE_FORMAT).strftime(GSHEET_DATE_FORMAT) cells = [ Cell(row=next_row, col=1, value=timestamp), # Timestamp Cell(row=next_row, col=2, value=data['isp']), # ISP Cell(row=next_row, col=3, value=data['server']['country']), # Server Country Cell(row=next_row, col=4, value=data['server']['host']), # Server Host Cell(row=next_row, col=5, value=data['server']['id']), # Server ID Cell(row=next_row, col=6, value=data['server']['ip']), # Server IP Cell(row=next_row, col=7, value=data['server']['location']), # Server Location Cell(row=next_row, col=8, value=data['server']['name']), # Server Name Cell(row=next_row, col=9, value=data['server']['port']), # Server Port Cell(row=next_row, col=10, value=data['ping']['jitter']), # Ping Jitter Cell(row=next_row, col=11, value=data['ping']['latency']), # Ping Latency Cell(row=next_row, col=12, value=data['download'] ['bandwidth']), # Download Bandwidth (bytes/sec) Cell(row=next_row, col=13, value=data['download']['bytes']), # Download Bytes Cell(row=next_row, col=14, value=data['download']['elapsed']), # Download Elapsed Cell( row=next_row, col=15, value=data['upload']['bandwidth']), # Upload Bandwidth (bytes/sec) Cell(row=next_row, col=16, value=data['upload']['bytes']), # Upload Bytes Cell(row=next_row, col=17, value=data['upload']['elapsed']), # Upload Elapsed Cell(row=next_row, col=18, value=data['interface']['externalIp']), # Interface ExternalIp Cell(row=next_row, col=19, value=data['interface']['internalIp']), # Interface InternalIp Cell(row=next_row, col=20, value=data['interface']['isVpn']), # Interface IsVpn Cell(row=next_row, col=21, value=data['interface']['macAddr']), # Interface MacAddr Cell(row=next_row, col=22, value=data['interface']['name']), # Interface Name Cell(row=next_row, col=23, value=data['result']['url']), # Result URL Cell(row=next_row, col=24, value=data['result']['id']), # Result ID ] sheet.update_cells(cell_list=cells, value_input_option='USER_ENTERED')