def record(self, data=None, worksheet=None, start_row=1):
if data is None:
data = []
if worksheet is None:
worksheet = self.last_worksheet or self.create_worksheet()
# record headers for each column
for column_index, value in enumerate(self.fields):
record_place = pygsheets.Cell(pos=(start_row, 1 + column_index),
worksheet=worksheet)
record_place.value = str(value['name'])
# record data for each issue
start_row += 1
for count, issue in enumerate(data):
for column_index, field in enumerate(self.fields):
record_place = pygsheets.Cell(pos=(start_row + count,
column_index + 1),
worksheet=worksheet)
value = ''
if 'assignee' in field['name']:
value = issue.fields.assignee.name
elif 'subtasks' in field['name']:
value = len(issue.fields.subtasks)
elif 'labels' in field['name']:
value = ', '.join(label for label in issue.fields.labels)
else:
value = getattr(issue.fields, field['name'],
field['default'])
record_place.value = str(value)
async def upload_attendance(self, attendance_list):
# build dataframe to upload to google sheet from attendance list
df = self.pd.DataFrame(attendance_list, columns = ['Date', 'Mission Name', 'Participant', 'Airframe', 'Role'])
# setup authorization for google
gc = pygsheets.authorize(service_account_file=bundled_data_path(self) / "service_account.json")
# open specific google sheet based on key, stored in dbconfig file
sh = gc.open_by_key(self.dbconfig.attendance_sheet_key)
# set worksheet to second tab
wks = sh[1]
# pull data on current sheet for use in determing where to place dataframe and coloring
cells = wks.get_col(1, include_tailing_empty=False, returnas='matrix')
last_row = len(cells)
data_rows = len(df) + last_row
beige = (0.9882353, 0.8980392, 0.8039216, 0)
no_color = (None, None, None, None)
white = (1, 1, 1, 0)
# Set pandas dataframme as cell values
wks.set_dataframe(df, start=(last_row + 1,1), copy_head=False, extend=True)
# add cell background coloring for each different mission dataframe
previous_color = wks.cell(f'A{last_row - 1}').color
if previous_color == no_color or previous_color == white and last_row != 1:
model_cell = pygsheets.Cell("A2")
model_cell.color = beige
DataRange(f'A{last_row + 1}',f'E{data_rows}', worksheet=wks).apply_format(model_cell, fields = "userEnteredFormat.backgroundColor")
elif previous_color == beige and last_row != 1:
model_cell = pygsheets.Cell("A2")
model_cell.color = white
DataRange(f'A{last_row + 1}',f'E{data_rows}', worksheet=wks).apply_format(model_cell, fields = "userEnteredFormat.backgroundColor")
status = {'worksheet': sh.title, 'tab': wks.title}
return status
def test_update_cells(self):
self.worksheet.update_cells(crange='A1:B2', values=[[1, 2], [3, 4]])
assert self.worksheet.cell((1, 1)).value == str(1)
cells = [pygsheets.Cell('A1', 10), pygsheets.Cell('A2', 12)]
self.worksheet.update_cells(cell_list=cells)
assert self.worksheet.cell((1, 1)).value == str(cells[0].value)
def test_update_cells(self):
self.worksheet.update_values(crange='A1:B2', values=[[1, 2], [3, 4]])
assert self.worksheet.cell((1, 1)).value == str(1)
self.worksheet.resize(1, 1)
self.worksheet.update_values(crange='A1', values=[[1, 2, 5], [3, 4, 6], [3, 4, 61]], extend=True)
assert self.worksheet.cols == 3
assert self.worksheet.rows == 3
assert self.worksheet.cell((3, 3)).value == '61'
self.worksheet.resize(30, 30)
cells = [pygsheets.Cell('A1', 10), pygsheets.Cell('A2', 12)]
self.worksheet.update_values(cell_list=cells)
assert self.worksheet.cell((1, 1)).value == str(cells[0].value)
def update_row_color(worksheet):
colors = [
"#447c69", "#e9d78e", "#f19670", "#e16552", "#7c9fb0", "#5698c4",
"#9abf88"
]
scaled_colors = []
for c in colors:
h = c.lstrip('#')
scaled_colors.append(
tuple(int(h[i:i + 2], 16) / 255.0 for i in (0, 2, 4)))
color_index = int(datetime.now().strftime("%w"))
worksheet.get_row(2, returnas="range").apply_format(
pygsheets.Cell('A2'),
fields="userEnteredFormat.backgroundColor",
cell_json={
"userEnteredFormat": {
"backgroundColor": {
"red": scaled_colors[color_index][0],
"green": scaled_colors[color_index][1],
"blue": scaled_colors[color_index][2]
}
}
})
def hyperlink_updater(wk, file_list):
n_rows = wk.rows + 1
for i in range(2, n_rows):
cell = pygsheets.Cell(f'A{i}', worksheet=wk, cell_data=None)
cell_value = wk.get_value(f'A{i}')
if not cell.formula and cell_value: # Must have value but no hyperlink
# print(f'Cell A{i} requires a hyperlink: {cell_value}')
file_name = cell_value.split(
'.')[0] + '_case.pptx' # Name to help locate file names
cell_nbr = f'A{i}'
file_list_names = list(file_list.keys())
if file_name in file_list_names:
file_name_zip = file_name.split('_case.pptx')[
0] + '.zip' # Reference back to original name
cell.formula = f'=HYPERLINK("https://drive.google.com/file/d/{file_list[file_name]}", "{file_name_zip}")'
print(
f'Replaced {cell_nbr}: {file_name} : {file_list[file_name]}'
)
else:
print(f'COULD NOT REPLACE {cell_nbr}: {file_name}')
def set_cell(worksheet,
pos,
value,
url=None,
font_size=None,
color=None,
background_color=None,
horizontal_alignment=None):
color_map = {
'red': (1, 0, 0, 0),
'blue': (0, 0, 1, 0),
'yellow': (1, 1, 0, 0),
'orange': (1, 0.6, 0, 0),
'white': (1, 1, 1, 0)
}
cell = pygsheets.Cell(pos, worksheet=worksheet)
if color:
cell.set_text_format('foregroundColor', color_map[color])
if background_color:
cell.color = color_map[background_color]
if font_size:
cell.set_text_format('fontSize', font_size)
if horizontal_alignment == 'center':
cell.set_horizontal_alignment(
pygsheets.custom_types.HorizontalAlignment.CENTER)
if url:
cell.set_value(f'=HYPERLINK("{url}", "{value}")')
else:
cell.set_value(value)
def _get_model_from_row(self, row):
"""Given a list of pygsheets.Cell objects, return a Model.
Args:
row (list): list of pygsheets.Cell objects
Returns:
Model: Model object populated from row
"""
model = Model(repository=self, cell_converter=self._cell_converter)
# Empty cells don't get returned so we create empties to work with
columns_to_add = set(list(self._col_to_property_name.keys()))
for cell in row:
row_number = cell.row # When we fill in emptyies, we need this
try:
columns_to_add.remove(cell.col)
except KeyError:
# Ignore cells that don't correspond to column headers
pass
for column_number in columns_to_add:
cell_pos = (row_number, column_number)
empty_cell = pygsheets.Cell(cell_pos, worksheet=self.worksheet)
row.append(empty_cell)
for cell in row:
try:
property_name = self._col_to_property_name[cell.col]
model.Metadata.add_cell(cell=cell, property_name=property_name)
except KeyError:
# Don't set properties for any values
# we don't have a mapping for
# there was no header
pass
return model
def update_row(self, region, index):
region_dict = region.flatten()
region_dict["Name"] = region.get_precinct() # fix this hack
cells = []
time.sleep(self.SLEEP_TIME)
for title in region_dict.keys():
cell = pygsheets.Cell("{letter}{index}".format(
letter=self.column_title_mappings[title], index=index),
region_dict[title],
worksheet=self.wks)
cells.append(cell)
self.wks.update_values(cell_list=cells)
def wm_gsheet_formatter(wk):
wm_columns = {
'Engagement': ['U', (0.83529411765, 0.65098039216, 0.741176470590)],
'Total conv': ['V', (0.83529411765, 0.65098039216, 0.74117647059)],
'Repeat': ['W', (0.83529411765, 0.65098039216, 0.74117647059)],
'Immediate': ['X', (0.83529411765, 0.65098039216, 0.74117647059)],
'A%': ['AC', (0.83529411765, 0.65098039216, 0.74117647059)],
'B%': ['AD', (0.83529411765, 0.65098039216, 0.74117647059)],
'C%': ['AE', (0.83529411765, 0.65098039216, 0.74117647059)],
'A+B%': ['AF', (0.83529411765, 0.65098039216, 0.74117647059)],
'Control lift': ['AI', (0.91764705882, 0.81960784314, 0.86274509804)],
'Freeosk lift': ['AJ', (0.91764705882, 0.81960784314, 0.86274509804)],
'Lift delta': ['AK', (0.91764705882, 0.81960784314, 0.86274509804)]
}
n_rows = wk.rows
for col_name, col_attr in wm_columns.items():
model_cell = pygsheets.Cell(f'{col_attr[0]}2')
model_cell.color = (col_attr[1])
# model_cell.color = (0.70588235294, 0.65490196078, 0.83921568627)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (pygsheets.FormatType.PERCENT, '0.00%')
pygsheets.DataRange(f'{col_attr[0]}2',
f'{col_attr[0]}{n_rows}',
worksheet=wk).apply_format(model_cell)
dollar_col = ['AG', 'AH']
for col in dollar_col:
model_cell = pygsheets.Cell(f'{col}2')
model_cell.color = (0.91764705882, 0.81960784314, 0.86274509804)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (
pygsheets.FormatType.CURRENCY, '$ 0.00'
) # https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/custom_types.html#FormatType
pygsheets.DataRange(f'{col}2', f'{col}{n_rows}',
worksheet=wk).apply_format(model_cell)
def update_addl_format(worksheet, addl_format):
for fmt in addl_format:
if fmt == 'bold':
worksheet.get_row(2, returnas="range").apply_format(
pygsheets.Cell('A1'),
fields="userEnteredFormat.textFormat.bold",
cell_json={
"userEnteredFormat": {
"textFormat": {
"bold": True
}
}
})
def set_headers(worksheet):
# print("called")
header_values = [[
'FirstName', 'LastName', 'DOB', 'Mobile No', 'Address', 'Occupation'
]]
worksheet.update_cells(crange='A1:F1', values=header_values)
# worksheet.update_cells(cell_list = header_values)
# data_range = worksheet.get_named_ranges(headers.name)
headers = worksheet.range('A1:F1', returnas='range')
header_cell = pygsheets.Cell('A1')
header_cell.set_text_format('bold', True)
headers.apply_format(header_cell)
def sc_gsheetformatter(wk):
sc_columns = {
'Engagement': ['T', (0.83529411765, 0.65098039216, 0.741176470590)],
'Conversion': ['U', (0.83529411765, 0.65098039216, 0.74117647059)],
'Repeat': ['V', (0.83529411765, 0.65098039216, 0.74117647059)],
'Immediate': ['W', (0.83529411765, 0.65098039216, 0.74117647059)],
'A%': ['AA', (0.83529411765, 0.65098039216, 0.74117647059)],
'B%': ['AB', (0.83529411765, 0.65098039216, 0.74117647059)],
'C%': ['AC', (0.83529411765, 0.65098039216, 0.74117647059)],
'A+B%': ['AD', (0.83529411765, 0.65098039216, 0.74117647059)],
'Freeosk lift': ['AF', (0.91764705882, 0.81960784314, 0.86274509804)],
'Control lift': ['AG', (0.91764705882, 0.81960784314, 0.86274509804)],
'Lift delta': ['AH', (0.91764705882, 0.81960784314, 0.86274509804)]
}
n_rows = wk.rows
for col_name, col_attr in sc_columns.items():
model_cell = pygsheets.Cell(f'{col_attr[0]}2')
model_cell.color = (col_attr[1])
# model_cell.color = (0.70588235294, 0.65490196078, 0.83921568627)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (pygsheets.FormatType.PERCENT, '0.00%')
pygsheets.DataRange(f'{col_attr[0]}2',
f'{col_attr[0]}{n_rows}',
worksheet=wk).apply_format(model_cell)
dollar_col = 'AE'
model_cell = pygsheets.Cell(f'{dollar_col}2')
model_cell.color = (0.91764705882, 0.81960784314, 0.86274509804)
model_cell.set_text_format('fontFamily', 'Calibri')
model_cell.set_text_format('bold', True)
model_cell.format = (pygsheets.FormatType.CURRENCY, '$ 0.00')
pygsheets.DataRange(f'{dollar_col}2',
f'{dollar_col}{n_rows}',
worksheet=wk).apply_format(model_cell)
def update_strikethrough_row(worksheet, input_string):
row = re.findall(r'~(\d+)', input_string)
if row:
cell = worksheet.find(row[0], cols=(1, 1), matchEntireCell=True)
if cell:
worksheet.get_row(cell[0].row, returnas="range").apply_format(
pygsheets.Cell('A1'),
fields="userEnteredFormat.textFormat.strikethrough",
cell_json={
"userEnteredFormat": {
"textFormat": {
"strikethrough": True
}
}
})
def __update_value_keys(self, value_dict: dict):
raw_keys = Worksheet.__get_value_keys(self)
if not dict:
return
new_var_keys = value_dict.keys()
add_keys = []
i = 1
for key in new_var_keys:
if not key in raw_keys:
add_keys.append(
pygsheets.Cell(pos=(1, len(raw_keys) + i), val=key))
i += 1
if add_keys:
new_cols = len(raw_keys) + len(add_keys) - self.worksheet.cols
if new_cols > 0:
self.worksheet.add_cols(new_cols)
print("Added {} columns".format(new_cols))
self.worksheet.update_values(cell_list=add_keys)
def create_data_sheets(self):
while True:
try:
project_name_or_id = input('input id or key of project ')
jira_project = self.jira_connect.project(project_name_or_id)
# get boards list by project and print it
boards = self.jira_connect.boards()
for i in boards:
print('Board: name - {}, id - {}'.format(i.name, i.id))
# checking if board in project and choose board id and record issues to spreadsheet
while True:
board_id = int(input('Input id board '))
matches = list(
filter(lambda board: board.id == board_id, boards))
if matches:
break
jql = ''
def add_to_jql(jql_str, new_str, type_concat='and'):
return ' '.join([jql_str, type_concat, new_str
]) if jql_str else new_str
# filter issues by type --> or view all issues
while True:
choose_type = input(
'choose the type from %s or press "rec" to record all issues '
% self.issues_type)
if choose_type.lower() == "rec":
break
elif choose_type in self.issues_type:
jql = add_to_jql(jql, 'issuetype=%s' % choose_type)
break
else:
print('choose thr correct type')
while True:
choose_period = input(
'Do you want to record for the particular period? press Y/N '
)
try:
if choose_period.lower() == 'n':
break
elif choose_period.lower() == 'y':
start_from = input('Enter from date 2019-07-01 - ')
end_to = input('Enter to date 2019-07-04 - ')
# todo check date format
jql = add_to_jql(jql, 'created>=%s' % start_from)
jql = add_to_jql(jql, 'created<=%s' % end_to)
break
else:
print("please, press Y or N")
except Exception as error:
print(error)
issues = self.jira_connect.issues_by_board(board_id, jql=jql)
# record name of project
self.add_tab_sheet = self.sheet.add_worksheet('; '.join([
jira_project.name, matches[0].name,
str(datetime.datetime.now())
]))
# headers for spreadsheet
fields = [
{
'name': 'summary',
'default': 'noname',
},
{
'name': 'priority',
'default': 'noname',
},
{
'name': 'issuetype',
'default': 'noname',
},
{
'name': 'description',
'default': 'noname',
},
{
'name': 'assignee',
'default': 'noname',
},
{
'name': 'timespent',
'default': 'noname',
},
{
'name': 'aggregatetimespent',
'default': 'noname',
},
{
'name': 'resolution',
'default': 'noname',
},
{
'name': 'resolutiondate',
'default': 'noname',
},
{
'name': 'labels',
'default': 'noname',
},
{
'name': 'timeestimate',
'default': 'noname',
},
{
'name': 'aggregatetimeoriginalestimate',
'default': 'noname',
},
{
'name': 'timeoriginalestimate',
'default': 'noname',
},
{
'name': 'status',
'default': 'noname',
},
{
'name': 'subtasks',
'default': 'noname',
},
]
# set headers for each column
for column_index, value in enumerate(fields):
self.record_place = pygsheets.Cell(
pos=(1, 1 + column_index),
worksheet=self.add_tab_sheet)
self.record_place.value = str(value['name'])
# set data for each issue
for count, issue in enumerate(issues):
for column_index, field in enumerate(fields):
self.record_place = pygsheets.Cell(
pos=(self.count_missing_rows + count,
column_index + 1),
worksheet=self.add_tab_sheet)
value = ''
if 'assignee' in field['name']:
value = issue.fields.assignee.name
elif 'subtasks' in field['name']:
value = len(issue.fields.subtasks)
elif 'labels' in field['name']:
value = ', '.join(label
for label in issue.fields.labels)
else:
value = getattr(issue.fields, field['name'],
field['default'])
self.record_place.value = str(value)
except HttpError:
print("Probably you have already create tab of spreadsheet")
except Exception:
print("You've entered incorrect id or name of project")
class CellStyle:
fields_format = {
"backgroundColor": (0.80, 0.80, 0.80),
"fontFamily": "Arial",
"fontSize": 14,
"horizontal_alignment":
pygsheets.custom_types.HorizontalAlignment.CENTER,
"borders": {
"top": {
"style": "SOLID"
},
"bottom": {
"style": "SOLID"
},
"left": {
"style": "SOLID"
},
"right": {
"style": "SOLID"
}
}
# "wrapStrategy": "OVERFLOW_CELL"
}
students_names_format = {
"backgroundColor": (0.90, 0.90, 0.90),
"fontFamily": "Arial",
"fontSize": 12,
"italic": True
}
main_table_format = {
"horizontal_alignment":
pygsheets.custom_types.HorizontalAlignment.CENTER,
"fontFamily": "Arial",
"fontSize": 12,
"bold": True,
}
fields_format_cell = pygsheets.Cell("A1")
fields_format_cell.horizontal_alignment = fields_format[
"horizontal_alignment"]
fields_format_cell.color = fields_format["backgroundColor"]
fields_format_cell.borders = fields_format["borders"]
# fields_format_cell.wrap_strategy = fields_format["wrapStrategy"]
fields_format_cell.set_text_format("fontFamily",
fields_format["fontFamily"])
fields_format_cell.set_text_format("fontSize", fields_format["fontSize"])
student_names_format_cell = pygsheets.Cell("A1")
student_names_format_cell.color = students_names_format["backgroundColor"]
student_names_format_cell.set_text_format(
"fontFamily", students_names_format["fontFamily"])
student_names_format_cell.set_text_format(
"fontSize", students_names_format["fontSize"])
student_names_format_cell.set_text_format("italic",
students_names_format["italic"])
main_table_cell = pygsheets.Cell("A1")
main_table_cell.horizontal_alignment = main_table_format[
"horizontal_alignment"]
main_table_cell.set_text_format("fontFamily",
main_table_format["fontFamily"])
main_table_cell.set_text_format("fontSize", main_table_format["fontSize"])
main_table_cell.set_text_format("bold", main_table_format["bold"])
def main(args):
now_time = dt.now()
#Parse arguments
if args.all:
start_time = 0
end_time = 0
td = 0
csv_export = "/var/log/gsstats/pk_sessions-all-{}.csv".format(
now_time.strftime('%Y-%m-%d-%H-%M-%S'))
print("Analyze all sessions")
else:
td = args.day
start_time = (now_time -
timedelta(days=td)).strftime('%Y-%m-%d 00:00:00')
end_time = (now_time -
timedelta(days=td)).strftime('%Y-%m-%d 23:59:59')
csv_export = "/var/log/gsstats/pk_sessions-{}.csv".format(
now_time.strftime('%Y-%m-%d-%H-%M-%S'))
print("Analyze session data from {} to {}".format(
start_time, end_time))
logdir = ''
if args.logdir:
logdir = args.logdir
sessions = {}
vms = get_servers()
for vm in vms:
log = get_log(vm,
start_time,
end_time,
debug=args.debug,
logdir=logdir)
s = get_sessions(log)
#Check for yesterday session
if "yesterday" in s.keys():
#Find that session start time
y_log = get_log(
vm, (now_time -
timedelta(days=td + 1)).strftime('%Y-%m-%d 00:00:00'),
(now_time -
timedelta(days=td + 1)).strftime('%Y-%m-%d 23:59:59'),
reverse=True,
debug=args.debug,
logdir=logdir)
for value in y_log:
if ' CreateSession: session_id' in value:
y_session_start_time = value.split(' ')[0][:-5].replace(
'T', ' ')
break
y_log = get_log(
vm,
y_session_start_time,
(now_time -
timedelta(days=td + 1)).strftime('%Y-%m-%d 23:59:59'),
logdir=logdir)
#Get yesterday session data
y_s = get_sessions(y_log)
#Add that session to session dict
s.update(y_s)
#Save that session ID
y_s_id = y_s.items()[0][0]
#Save incomplete session data and remove it from sessions dict
y_s = s.pop("yesterday")
#Add missing data for yesterday session to sessions dict
for k in y_s.keys():
if k in s[y_s_id].keys() and isinstance(y_s[k], list):
s[y_s_id][k].extend(y_s[k])
else:
s[y_s_id][k] = y_s[k]
#Calculate FPS and latency stats and add info about VM and host
for k, v in s.items():
s[k]["Host"] = platform.node().split('.', 1)[0]
s[k]["VM"] = vm
ping = get_latency_stats(v["Latency"])
s[k]["Latency"] = ping
FPS_Customer = get_fps_stats(v["FPS Customer"])
s[k]["FPS Customer"] = FPS_Customer
#print(v["FPS Game"])
FPS_Game = get_fps_stats(v["FPS Game"])
s[k]["FPS Game"] = FPS_Game
#Check if there will be unfinished session today and drop it from sessions dict
if not "End Time" in v.keys():
s.pop(k)
else:
s[k]["Duration"] = time_difference(v["Start Time"],
v["End Time"])
sessions.update(s)
df_cols = [
'Start Time', 'End Time', 'Host', 'VM', 'Game', 'ID', 'Continent',
'Country', 'Region', 'City', 'Latitude', 'Longitude', 'ASN',
'ASN Provider', 'Duration', 'FPS Customer', 'FPS Game', 'Latency',
'Resolution'
]
#Dict for exporting data to pandas DataFrame
sessions_export = {}
for k, v in sessions.items():
session = []
for col in df_cols:
try:
session.append(v[col])
except KeyError:
session.append('')
sessions_export[k] = session
#Debug info
if args.debug:
for v in sorted(sessions_export.items(), key=operator.itemgetter(0)):
print(v)
df = pd.DataFrame.from_dict(data=sessions_export,
orient='index',
columns=df_cols)
df.sort_index(inplace=True)
try:
df.to_csv(csv_export, index_label="Session", quoting=QUOTE_NONNUMERIC)
except Exception as e:
print('Unable to save exported data!')
else:
print('Session data is saved to: {}'.format(csv_export))
df_row_count = len(df.index)
if not args.offline:
#Connect to Google Sheets
print('Exporting data to Google Sheets')
wks_name = 'PlayKey Session Data'
client = pygsheets.authorize(service_file=args.key_path)
sheet = client.open('PlayKey-Data')
try:
wks = sheet.worksheet_by_title(wks_name)
except pygsheets.exceptions.WorksheetNotFound:
wks = sheet.add_worksheet(wks_name)
header = df_cols
header.insert(0, 'Session')
wks.update_row(1, values=header)
wks.insert_rows(1, df_row_count)
#Format columns
cell_date = pygsheets.Cell('A1')
cell_date.set_number_format(pygsheets.FormatType.DATE_TIME)
pygsheets.datarange.DataRange(start='B', end=None,
worksheet=wks).apply_format(cell_date)
pygsheets.datarange.DataRange(start='C', end=None,
worksheet=wks).apply_format(cell_date)
cell_number = pygsheets.Cell('K1')
cell_number.set_number_format(pygsheets.FormatType.NUMBER)
pygsheets.datarange.DataRange(start='K', end=None,
worksheet=wks).apply_format(cell_number)
pygsheets.datarange.DataRange(start='L', end=None,
worksheet=wks).apply_format(cell_number)
#Export data
wks.set_dataframe(df, (2, 1),
copy_index=True,
copy_head=False,
extend=False)
else:
print('Offline mode: no data will be exported to Google Sheets')
def enter_size_v2(filename, sheetname, mouseno, measurements, date, isdead,
causeofdeath, dateofdeath):
# authenticate and read google spreadsheet
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
gc = pygsheets.authorize(
service_file=
'/data/data/com.termux/files/home/storage/downloads/Tumor Entry-f14983e25cb2.json'
)
# gc = pygsheets.authorize(service_file='c:/users/wade/downloads/Tumor Entry-f14983e25cb2.json')
sheet = gc.open(filename)
worksheet = sheet.worksheet_by_title(sheetname)
cell_list = []
# find the row to write to
row_headers = np.array([
i[0] for i in worksheet.get_values(
start=(1, 1), end=(worksheet.rows, 1), returnas='matrix')
])
myRow = int(np.where(row_headers == mouseno)[0][0] + 1)
print(myRow)
# find the col to write to
column_headers = worksheet.get_values(start=(1, 1),
end=(1, worksheet.cols),
returnas='matrix')[0]
while column_headers[-1] == '':
column_headers.pop()
column_headers = np.array(column_headers)
if np.where(column_headers == date)[0].size > 0:
myCol = int(np.where(column_headers == date)[0][0])
else:
myCol = int(len(column_headers))
cell_list += [
pygsheets.Cell((1, myCol + 1), str(date)),
pygsheets.Cell((1, myCol + 2), str(date)),
pygsheets.Cell((1, myCol + 3), str(date))
]
area_cell = pygsheets.Cell((myRow, myCol + 3))
myformula = measurements[0] + '*' + measurements[1]
for i in range(2, len(measurements), 2):
myformula += '+' + measurements[i] + '*' + measurements[i + 1]
area_cell.formula = myformula
cell_list += [
pygsheets.Cell((myRow, myCol + 1), measurements[0]),
pygsheets.Cell((myRow, myCol + 2), measurements[1]), area_cell
]
print(myCol)
print(cell_list)
# if columns are running low, add more
if myCol + 3 >= worksheet.cols:
worksheet.add_cols(100)
if (not worksheet.cell((myRow, myCol + 1)).value and not worksheet.cell(
(myRow, myCol + 2)).value and not worksheet.cell(
(myRow, myCol + 3)).value):
worksheet.update_cells(cell_list)
if (isdead == 'on' and not worksheet.cell((myRow, 4)).value
and not worksheet.cell((myRow, 5)).value and not worksheet.cell(
(myRow, 6)).value):
dateofdeath = re.search('\d\d\d\d-[0-1]\d-[0-3]\d',
sys.argv[8]).group(0)
worksheet.update_cells([
pygsheets.Cell((myRow, 4), dateofdeath),
pygsheets.Cell((myRow, 5), 1),
pygsheets.Cell((myRow, 6), causeofdeath)
])
def write_game_sheet(sheet_api, games, options, new_sheet=False):
"""
Build a game matrix and write it to a worksheet
:param sheet_api: worksheet to work on
:param games: array of games to write
:param options: array of options to add to the sheet info
:param new_sheet: if true, write to a new worksheet
:return: 0
"""
# Fixed sheet data
data_start_row = 12
left_rating_column = 'B'
left_name_column = 'C'
left_last_column = 'F'
right_rating_column = 'H'
right_name_column = 'I'
rating_range_cell = 'L3'
games_range_cell = 'L4'
information_range = 'B3:D9'
if len(games) == 0:
sys.exit("No games found.")
else:
print("Writing {} games to worksheet...".format(len(games)))
backup_title = str('Data Set {}'.format(options['run_count']))
if 'title' in options.keys():
title = str(options['title'])
else:
title = backup_title
try:
if new_sheet:
base_worksheet = sheet_api.worksheet_by_title('Template')
worksheet = sheet_api.add_worksheet(title,
src_worksheet=base_worksheet,
index=-1)
else:
worksheet = sheet_api.worksheet_by_title(title)
except HttpError:
if new_sheet:
base_worksheet = sheet_api.worksheet_by_title('Template')
worksheet = sheet_api.add_worksheet(backup_title,
src_worksheet=base_worksheet,
index=-1)
else:
worksheet = sheet_api.worksheet_by_title(backup_title)
if len(games) > worksheet.rows + data_start_row:
worksheet.rows = data_start_row + len(games)
worksheet.cell(rating_range_cell).value = str('{}{}:{}{}'.format(
left_rating_column, data_start_row, left_rating_column,
data_start_row + len(games)))
worksheet.cell(games_range_cell).value = str('{}{}:{}{}'.format(
left_rating_column, data_start_row, left_name_column,
data_start_row + len(games)))
full_border = {
'top': {
'style': 'SOLID',
'width': 1,
'color': {
'red': 0.0,
'green': 0.0,
'blue': 0.0,
'alpha': 0.0
}
},
'left': {
'style': 'SOLID',
'width': 1,
'color': {
'red': 0.0,
'green': 0.0,
'blue': 0.0,
'alpha': 0.0
}
},
'right': {
'style': 'SOLID',
'width': 1,
'color': {
'red': 0.0,
'green': 0.0,
'blue': 0.0,
'alpha': 0.0
}
},
'bottom': {
'style': 'SOLID',
'width': 1,
'color': {
'red': 0.0,
'green': 0.0,
'blue': 0.0,
'alpha': 0.0
}
}
}
rating_cell_model = pygsheets.Cell(
str("{}{}".format(left_rating_column, data_start_row)))
rating_cell_model.format = pygsheets.FormatType.NUMBER, '0"%"'
rating_cell_model.set_text_alignment('CENTER')
rating_cell_model.set_text_alignment('MIDDLE')
rating_cell_model.borders = full_border
rating_cell_range = worksheet.get_values(
str("{}{}".format(left_rating_column, data_start_row)),
str("{}{}".format(left_rating_column, data_start_row + len(games))),
returnas='range')
rating_cell_range.apply_format(rating_cell_model)
rating_cell_range = worksheet.get_values(
str('{}{}'.format(right_rating_column, data_start_row)),
str('{}{}'.format(right_rating_column, data_start_row + len(games))),
returnas='range')
rating_cell_range.apply_format(rating_cell_model)
border_cell_model = pygsheets.Cell('A1')
border_cell_model.borders = full_border
border_cell_range = worksheet.get_values(
str('{}{}'.format(left_name_column, data_start_row)),
str('{}{}'.format(left_last_column, data_start_row + len(games))),
returnas='range')
border_cell_range.apply_format(border_cell_model)
border_cell_range = worksheet.get_values(
str('{}{}'.format(right_name_column, data_start_row)),
str('{}{}'.format(right_name_column, data_start_row + len(games))),
returnas='range')
border_cell_range.apply_format(border_cell_model)
worksheet.cell('B1').value = title
show_text = {}
show_count = {}
information_matrix = []
information_labels = {
'search_platforms': 'Searched Platforms',
'search_genres': 'Searched Genres',
'allowed_platforms': 'Allowed Platforms',
'allow_genres': 'Allowed Genres',
'disallowed_platforms': 'Disallowed Platforms',
'disallowed_genres': 'Disallowed Genres'
}
for key in [
'search_platforms', 'search_genres', 'allowed_platforms',
'allowed_genres', 'disallowed_platforms', 'disallowed_genres'
]:
if key in options.keys():
show_text[key] = ', '.join(options[key])
show_count[key] = options['information'][key]
else:
show_text[key] = ''
show_count[key] = ''
information_matrix.append(
[show_count[key],
information_labels.get(key), show_text[key]])
information_matrix.append(
[len(games), 'Release Status', options['release_status']])
worksheet.update_cells(crange=information_range, values=information_matrix)
if 'sort' not in options.keys():
sort_mode = 'name'
else:
sort_mode = options['sort']
platform_count = {}
genre_count = {}
game_matrix = []
for game in sorted(games, key=lambda n: n[sort_mode]):
if 'platforms' in game.keys():
platform_string = []
for platform in game['platforms']:
platform_string.append(str(lookup('platforms', platform)))
platforms_text = ', '.join(platform_string)
for platform in game['platforms']:
if platform in platform_count.keys():
platform_count[platform] += 1
else:
platform_count[platform] = 1
else:
platforms_text = ''
if 'genres' in game.keys():
genre_string = []
for genre in game['genres']:
genre_string.append(str(lookup('genres', genre)))
genres_text = ', '.join(genre_string)
for genre in game['genres']:
if genre in genre_count.keys():
genre_count[genre] += 1
else:
genre_count[genre] = 1
else:
genres_text = ''
if 'first_release_date' in game.keys():
release_text = readable_time(game['first_release_date'])
else:
release_text = ''
if 'total_rating' in game.keys() and 'total_rating_count' in game.keys(
):
if game['total_rating_count'] > 1:
rating_text = game['total_rating']
else:
rating_text = ''
else:
rating_text = ''
game_matrix.append([
rating_text, game['name'], genres_text, platforms_text,
release_text
])
cell_range = str('{}{}:{}{}'.format(left_rating_column, data_start_row,
left_last_column,
data_start_row + len(games)))
worksheet.update_cells(crange=cell_range, values=game_matrix)
return 0
def record_data(issues, tab_sheet_id):
tab_sheet = sheet.worksheet(property='id', value=tab_sheet_id)
fields = [
{
'name': 'created',
'default': 'noname',
},
{
'name': 'summary',
'default': 'noname',
},
{
'name': 'priority',
'default': 'noname',
},
{
'name': 'issue_type',
'default': 'noname',
},
{
'name': 'description',
'default': 'noname',
},
{
'name': 'assignee',
'default': 'noname',
},
{
'name': 'timespent',
'default': 'noname',
},
{
'name': 'aggregatetimespent',
'default': 'noname',
},
{
'name': 'resolution',
'default': 'noname',
},
{
'name': 'resolutiondate',
'default': 'noname',
},
{
'name': 'labels',
'default': 'noname',
},
{
'name': 'timeestimate',
'default': 'noname',
},
{
'name': 'aggregatetimeoriginalestimate',
'default': 'noname',
},
{
'name': 'timeoriginalestimate',
'default': 'noname',
},
{
'name': 'status',
'default': 'noname',
},
{
'name': 'subtasks',
'default': 'noname',
},
]
# set headers for each column
for column_index, value in enumerate(fields):
record_place = pygsheets.Cell(pos=(1, 1 + column_index),
worksheet=tab_sheet)
record_place.value = str(value['name'])
# set data for each issue
for count, issue in enumerate(issues):
for column_index, field in enumerate(fields):
record_place = pygsheets.Cell(pos=(count_missing_rows + count,
column_index + 1),
worksheet=tab_sheet)
value = ''
value = issue.get(field['name'], field['default'])
record_place.value = str(value)
df['Month_Abbrev'] = df.Month.apply(lambda x: x[:3])
df['Month_Num'] = df.date.dt.month
df.date = pd.to_datetime(df.date).dt.date # removing timestamp
# Replacing single anomaly in Gender col (missing gender)
df.loc[(df.id == 2956) & (df.name == 'Scout Schultz'), 'gender'] = 'Male'
# Uploading df to Google Sheets (pygsheets)
client = pyg.authorize(service_account_file='creds.json')
url = 'https://docs.google.com/spreadsheets/d/1xn4Wori5gD8j5U51c1OUeiHaDuRqRIq0Php66hUh2d0/edit#gid=1344755995'
sheet = client.open_by_url(url)
wks = sheet.worksheet_by_title('Sheet1')
wks.set_dataframe(df, start=(1, 1))
# Changing format of Month_Num col to Number ("0") so that it's treated as an integer by Google Data Studio
mo_num_cell = pyg.Cell("S1")
mo_num_cell.set_number_format(format_type=pyg.FormatType.NUMBER, pattern="0")
pyg.DataRange(start='S1', worksheet=wks).apply_format(mo_num_cell)
"""# Uploading df to Google Sheets (df2g)
# Resources: https://techwithtim.net/tutorials/google-sheets-python-api-tutorial/ & https://stackoverflow.com/questions/59117810/changing-column-format-in-google-sheets-by-gspread-and-google-sheets-api
#import gspread as gspread
#from df2gspread import df2gspread as df2g
#from oauth2client.service_account import ServiceAccountCredentials
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet_key = "1xn4Wori5gD8j5U51c1OUeiHaDuRqRIq0Php66hUh2d0"
sheet_name = "Sheet1"
df2g.upload(df, sheet_key, sheet_name, credentials=creds, row_names=False)
