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')
