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)
