def compare(f_price): prices = worksheet.col_values(2)[1:] rows = 1 o_prices = list(map(lambda x: float(x.replace(",", ".")), prices)) for a, b in zip(f_price, o_prices): rows += 1 if a < b: worksheet.update_cell(rows, 2, a) fmt = gsf.CellFormat(backgroundColor=gsf.Color(0, 1, 0), textFormat=gsf.TextFormat( bold=True, foregroundColor=gsf.Color(0, 0, 0), fontSize=10)) gsf.format_cell_range(worksheet, 'B' + str(rows) + '', fmt) print('Product is now cheaper') elif a == b: print('Product price did not change') else: worksheet.update_cell(rows, 2, a) fmt = gsf.CellFormat(backgroundColor=gsf.Color(1, 0, 0), textFormat=gsf.TextFormat( bold=True, foregroundColor=gsf.Color(0, 0, 0), fontSize=10)) gsf.format_cell_range(worksheet, 'B' + str(rows) + '', fmt) print('Price has increased')
def align_cells(ws, cells): print "align_cells range=", cells fmt = gsf.cellFormat( horizontalAlignment='CENTER' ) gsf.format_cell_range(ws, cells, fmt) pass
def set_cell_background_color(self, cell_id, color_): fmt = cellFormat( backgroundColor=color(*color_), textFormat=textFormat(bold=False, foregroundColor=color(0,0,0)), horizontalAlignment='CENTER') format_cell_range(self._wks, cell_id + ':' + cell_id, fmt)
def sheet_formatter(self, sheetname): working_sheet = self.sh.worksheet(sheetname) sheetId = working_sheet.id body = { "requests": [{ "autoResizeDimensions": { "dimensions": { "sheetId": sheetId, "dimension": 'COLUMNS', "startIndex": 1, "endIndex": 5 } } }] } self.sh.batch_update(body) header_fmt = f.CellFormat( backgroundColor=f.Color(0.043137256, 0.3254902, 0.5803922), textFormat=f.TextFormat(bold=True, foregroundColor=f.Color(1, 1, 1)), horizontalAlignment='CENTER', verticalAlignment='MIDDLE') f.set_frozen( working_sheet, rows=1, cols=2) # freeze the headers and hotel_id and status column f.format_cell_range(working_sheet, '1', header_fmt)
def bold_cell(ws, cells): print "bold_cell range=", cells fmt = gsf.cellFormat( textFormat=gsf.textFormat( bold=True) ) gsf.format_cell_range(ws, cells, fmt) pass
def write_header(self, sheet, header): header_cells = list(header.keys()) header_first_col = header[header_cells[0]] header_last_col = header[header_cells[-1]] if not (self.header_exists(sheet)): for key in header: cell_coord = header[key]['coord'] sheet.update_acell(cell_coord, key) gs_formatting.format_cell_range( sheet, header_first_col['coord'] + ':' + header_last_col['coord'], header_first_col['formatting'])
def create_combined_valuations(league): import pandas as pd import gspread import gspread_dataframe as gsdf import gspread_formatting as gsfmt from general import postgres from munging import player_names from general import gs assert league.league_name in ['SoS', 'Legacy'] #league = 'SoS' #league = 'Legacy' bbdb = postgres.connect_to_bbdb() names = player_names.get_player_names() gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json') #bb2021 = gc.open("BB 2021 " + league.league_name) bb2021 = gc.open("BB 2021 InSeason") combined_hitters = create_combined_hitter_valuations(league) combined_pitchers = create_combined_pitcher_valuations(league) hitter_projections = bb2021.worksheet('Hitter Projections - ' + league.league_name) bb2021.values_clear(hitter_projections.title + "!A:Z") gsdf.set_with_dataframe(hitter_projections, combined_hitters) hitter_projections.update format_gs.format_gs_all(league=league, ls=league, type='hitting') pitcher_projections = bb2021.worksheet('Pitcher Projections - ' + league.league_name) bb2021.values_clear(pitcher_projections.title + "!A:Z") gsdf.set_with_dataframe(pitcher_projections, combined_pitchers) pitcher_projections.update format_gs.format_gs_all(league=league.league_name, ls=league, type='pitching') combined = pd.concat([ combined_hitters[['name', 'fg_id', 'type', 'zar', 'value']], combined_pitchers[['name', 'fg_id', 'type', 'zar', 'value']] ]) combined = combined.sort_values(by='value', ascending=False) gs_combined = bb2021.worksheet('Combined Z') gsdf.set_with_dataframe(gs_combined, combined) gs_combined.update gsfmt.format_cell_range( gs_combined, 'D:E', gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0')))
def format_sheet(self, sheet, template_sheet): sheet_data = sheet.get_all_values() rows_num = self.get_sheet_rows_num(sheet) header = sheet_data[0] for cell in header: col_formatting = self.get_col_formatting(template_sheet, cell) first_row = col_formatting[0] col_coord = xl_cell_to_rowcol(first_row) time.sleep(2) last_row = xl_rowcol_to_cell(rows_num - 1, col_coord[1]) gs_formatting.format_cell_range(sheet, first_row + ':' + last_row, col_formatting[1])
def conditional_format_sheet(self, sheet, template_sheet, template_header): apreciacao_list = self.get_column_values(sheet, 'Situação', template_header) for row, cell in enumerate(apreciacao_list, start=1): coord = xl_rowcol_to_cell( row, template_header['Situação']['column_index']) if 'pauta' in cell.lower(): cell_formatting = gs_formatting.get_effective_format( sheet, coord) cell_formatting.textFormat.foregroundColor =\ gs_formatting.color(1, 0, 0) gs_formatting.format_cell_range(sheet, coord + ':' + coord, cell_formatting)
def update_track_status(ebid, message="Archive download staged", sheetname='20A - OpLog Summary', status_col=1, bool_status_colname="Staged data \nfrom archive", row_color=[1., 1., 1.], text_color=[0., 0., 0.], bold_text=False, max_retry=5): """ Update the processing status of a track running through the pipeline. """ i = 0 while i == 0: try: full_sheet = read_tracksheet() worksheet = full_sheet.worksheet(sheetname) break except requests.ReadTimeout: time.sleep(10) pass i += 1 if i >= max_retry: raise ValueError("Error: timed out multiple time reading google sheet.") cell = worksheet.find(str(ebid)) worksheet.update_cell(cell.row, status_col, message) # Update the boolean flags for the different stages. bool_cell_col = worksheet.find(bool_status_colname).col worksheet.update_cell(cell.row, bool_cell_col, "TRUE") # Check if we have a color to update for the row at this stage: key_match_status = [key for key in stage_colors if key in message] if len(key_match_status) > 1: log.info("Found multiple matching statuses: {key_match_status}. Going with the first one") if len(key_match_status) > 0: key = key_match_status[0] row_color = stage_colors[key]['row_color'] text_color = stage_colors[key]['text_color'] bold_text = stage_colors[key]['bold_text'] fmt = cellFormat(backgroundColor=color(*row_color), textFormat=textFormat(bold=bold_text, foregroundColor=color(*text_color))) format_cell_range(worksheet, f'{cell.row}', fmt)
def format_gs_all(league, ls, type): import gspread import gspread_formatting as gsfmt gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json') #bb2021 = gc.open("BB 2021 " + league) bb2021 = gc.open("BB 2021 InSeason") if type.lower() in ['hitter', 'hitters', 'hitting', 'batting']: sh_proj = bb2021.worksheet('Hitter Projections - ' + ls.league_name) elif type.lower() in ['pitcher', 'pitchers', 'pitching']: sh_proj = bb2021.worksheet('Pitcher Projections - ' + ls.league_name) rate2 = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.00')) rate3 = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.000')) cardinal = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0')) value = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0')) headers = sh_proj.row_values(1) for header in headers: column = chr(65 + headers.index(header)) #print(header + ' is column ' + column) if header in ls.hitting_counting_stats or header in ls.pitching_counting_stats or header in [ 'pa', 'ip', 'g', 'gs' ]: gsfmt.format_cell_range(sh_proj, column + ':' + column, cardinal) elif header in ls.hitting_rate_stats: gsfmt.format_cell_range(sh_proj, column + ':' + column, rate3) elif header in ls.pitching_rate_stats: gsfmt.format_cell_range(sh_proj, column + ':' + column, rate2) elif header in ['zar', 'value']: gsfmt.format_cell_range(sh_proj, column + ':' + column, value)
def formatRaw(self): ''' Format raw ''' import gspread_formatting as gsf # Format the header # ----------------- fmt = cellFormat( backgroundColor=gsf.color(1, 0.9, 0.9), textFormat=textFormat(bold=True, foregroundColor=gsf.color(1, 0, 1)), ) gsf.format_cell_range(worksheet, 'A1:MN1') # FORMAT THE SUBSEQUENT LINES # --------------------------- return None
def format_gsheet(sheet): import gspread import gspread_formatting as gsfmt headers = sheet.row_values(1) hitting_rate_stats = ['avg', 'obp', 'ops', 'slg'] hitting_rate_format = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.000')) pitching_rate_stats = [ 'era', 'whip', 'DRA', 'xxxFIP', 'gmli', 'wpa', 'kwera', 'xfip' ] pitching_rate_format = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.00')) counting_stats = [ 'hr', 'r', 'rbi', 'sb', 'pa', 'ab', 'qs', 'w', 'so', 'sv', 'hld', 'svhld', 'ip', 'cFIP' ] counting_format = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0')) value_format = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0')) for header in headers: colnum = chr(headers.index(header) + 97) if header in hitting_rate_stats: gsfmt.format_cell_range(sheet, colnum + ':' + colnum, hitting_rate_format) if header in pitching_rate_stats: gsfmt.format_cell_range(sheet, colnum + ':' + colnum, pitching_rate_format) if header in counting_stats: gsfmt.format_cell_range(sheet, colnum + ':' + colnum, counting_format) if header[0:4] == 'zar_' or header[0:6] == 'value_': gsfmt.format_cell_range(sheet, colnum + ':' + colnum, value_format)
def main(): InitialTicketCells = [ "AO", "CLERK", "TURN TIME", "THREAD LINK", "Submitter", "", "AOs", 'TICKETS', "AVG TURN TIME", "", "CLERK", "TOTAL TICKETS", "AVG TURN TIME", "", "UPDATED AT" ] #Header of google sheet Sheet = SheetGet() cell_listInitial = Sheet.range("A1:O1") #setup sheet range output, Clerks, CurrentAOs = DataScrape( ) #grab all data we are interested in InitialFormat(cell_listInitial, InitialTicketCells, Sheet, CurrentAOs, Clerks) #function call fmtheader = gspread_formatting.cellFormat( backgroundColor=gspread_formatting.color(0, 0, 0), textFormat=gspread_formatting.textFormat( bold=True, foregroundColor=gspread_formatting.color(1, 0.84, 0)), horizontalAlignment='CENTER') #formating fmtOK = gspread_formatting.cellFormat( backgroundColor=gspread_formatting.color(0.13, 0.87, 0.16)) #formating formatlisting = [('A1:R1', fmtheader)] Color = [(0.1, 1, 0.13), (0.25, 1, 0.09), (0.45, 0.99, 0.08), (0.65, 0.99, 0.07), (0.85, 0.99, 0.05), (0.99, 0.91, 0.04), (0.98, 0.7, 0.03), (0.98, 0.48, 0.02), (0.98, 0.26, 0.01), (1, 0.03, 0)] #color gradient Result = cellWrite(output, Sheet, Color, fmtOK, formatlisting) #this updates our sheet gspread_formatting.format_cell_range( Sheet, 'A2:R{}'.format(len(Result) + 1), gspread_formatting.cellFormat(horizontalAlignment='CENTER')) gspread_formatting.format_cell_ranges( Sheet, formatlisting) #these two lines format the sheet Sheet.update_cell(2, 15, strftime("%Y-%m-%d %H:%M:%S", gmtime())) if Config.CSVOutput == 1 or Config.CSVOutput == 'yes': #if we want csv writer on CSVWriter(CurrentAOs, Clerks, output)
def format_gs_hitting(league, ls): import gspread import gspread_formatting as gsfmt gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json') bb2021 = gc.open("BB 2021 " + league) hitter_proj = bb2021.worksheet('Hitter Projections') rate = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.000')) cardinal = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0')) value = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0')) headers = hitter_proj.row_values(1) for header in headers: column = chr(65 + headers.index(header)) print(header + ' is column ' + column) if header in ls.hitting_counting_stats: gsfmt.format_cell_range(hitter_proj, column + ':' + column, cardinal) elif header in ls.hitting_rate_stats: gsfmt.format_cell_range(hitter_proj, column + ':' + column, rate) elif header in ['zar', 'value']: gsfmt.format_cell_range(hitter_proj, column + ':' + column, value)
def format_gs_pitching(): import gspread import gspread_formatting as gsfmt gc = gspread.service_account(filename='./bb-2021-2b810d2e3d25.json') bb2021 = gc.open("BB 2021 SoS") rate = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.00')) cardinal = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0')) value = gsfmt.CellFormat( numberFormat=gsfmt.NumberFormat(type='NUMBER', pattern='0.0')) gsfmt.format_cell_range(bb2021.worksheet('Pitcher Projections'), 'C:G', cardinal) gsfmt.format_cell_range(bb2021.worksheet('Pitcher Projections'), 'H:I', rate) gsfmt.format_cell_range(bb2021.worksheet('Pitcher Projections'), 'J:K', value)
"sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 0, # Please set the column index. "endIndex": len(ouput_col_headings) # Please set the column index. } } } ] } res = sc.batch_update(body) # to apply formatting to the cell contents: color, background, etc for row in range(len(req_data)): if req_data_format[row] == blank_fmt: # temporary workaround to overcome google sheets API limit (100 requests per 100 seconds): continue gf.format_cell_range(worksheet, gspread.utils.rowcol_to_a1(row+1,1)+':' + gspread.utils.rowcol_to_a1(row+1, len(ouput_col_headings)), req_data_format[row]) elif sys.argv[-1]=='ANNEX-B': lifecycle_col = 0 # A publication_col = 0 # A filter_col = 5 # F: Artefact Type output_sheet_name = "Extracted Data - Annex B - New" ouput_cols = [2, 17, 3, 0, -2, -1, 15] # in order: C, R, D, A, -2, -1, P ouput_col_headings = ["Brain Region", "Species", "Cell Type", "Artefact Name", "Life Cycle Stage", "Publication", "Dissemination Status"] base_fontsize = 10 req_data = [] req_data_format = []
def set_color(self): fmt = gsf.cellFormat( textFormat=gsf.textFormat( bold=True, foregroundColor=gsf.color(112, 48, 160), fontSize=24) ) gsf.format_cell_range(self._sheet, 'B1:B1', fmt)
def set_color(self, sheet_row, sheet_col1, sheet_col2, color): format_cell_range(self.ws, gspread.utils.rowcol_to_a1(sheet_row,sheet_col1)+':' + gspread.utils.rowcol_to_a1(sheet_row,sheet_col2), color)
def set_color_row(self, sheet_row, color): format_cell_range(self.ws, gspread.utils.rowcol_to_a1(sheet_row,1)+':' + gspread.utils.rowcol_to_a1(sheet_row,len(self.header_info)), color)
# FETCH THE DATA cell_list = wks.range('A2:D4') # UPDATE VALUE (ROW/COL) wks.update_acell('D2', "TRUE") wks.update_cell(4, 1, 'TRUE') # TRUE AS A CHECKBOX # UPDATE FORMAT OF CELL - SUING GSPREAD_FORMATTING MODULE fmt = get_effective_format(wks, 'A1') fmt = cellFormat(backgroundColor=color(1, 0.5, 0.5), textFormat=textFormat(bold=True, foregroundColor=color(0.4, 0, 1))) format_cell_range(wks, 'A1:A10', fmt) # If modifying these scopes, delete the file token.pickle. SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'] # The ID and range of a sample spreadsheet. SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' SAMPLE_RANGE_NAME = 'Class Data!A2:E' MY_SPREADSHEET_ID = '1nCyw-9KB1Ut07BNQ8_zQLVvrScOZvkKPMAorcu5Ad2U' MY_RANGE_NAME = 'Sheet1!A1:E' def getservice(): """Shows basic usage of the Gmail API. Lists the user's Gmail labels.
def set_color_cell(self, sheet_row, sheet_col, color): format_cell_range(self.ws, gspread.utils.rowcol_to_a1(sheet_row,sheet_col)+':' + gspread.utils.rowcol_to_a1(sheet_row,sheet_col), color)