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)