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 applyStandardFormatting(self, spreadsheetID, worksheetTitle,
startTimeString):
self.setSpreadsheetAndWorksheet(spreadsheetID, worksheetTitle)
breakConditionRule = ConditionalFormatRule(
ranges=[GridRange.from_a1_range('C:Z', self.worksheet)],
booleanRule=BooleanRule(
condition=BooleanCondition("TEXT_STARTS_WITH", ["Break"]),
format=gsf.cellFormat(backgroundColor=gsf.color(
0.70, 0.70, 0.70)) # should color break cells grey
))
absentConditionRule = ConditionalFormatRule(
ranges=[GridRange.from_a1_range('B5:B', self.worksheet)],
booleanRule=BooleanRule(
condition=BooleanCondition(
"TEXT_EQ", ["0:00"]), # "0:00" cells are absent
format=gsf.cellFormat(backgroundColor=gsf.color(
1, 0, 0)) # make them an angry red
))
emptyFirstTimeframeRule = ConditionalFormatRule(
ranges=[GridRange.from_a1_range('C5:C', self.worksheet)],
booleanRule=BooleanRule(
# if there's no timeframe for the first one then keep it white instead of yellow
condition=BooleanCondition("BLANK"),
format=gsf.cellFormat(backgroundColor=gsf.color(1, 1,
1)) # white
))
lateConditionRule = ConditionalFormatRule(
ranges=[GridRange.from_a1_range('C5:C', self.worksheet)],
booleanRule=BooleanRule(
# If the first timeframe doesn't have the start time in it, mark it yellow
condition=BooleanCondition("TEXT_NOT_CONTAINS",
[startTimeString]),
format=gsf.cellFormat(backgroundColor=gsf.color(1, 1,
0)) # yellow
))
onTimeConditionRule = ConditionalFormatRule(
ranges=[GridRange.from_a1_range('C5:C', self.worksheet)],
booleanRule=BooleanRule(
# if it does have the start time, mark it green
condition=BooleanCondition("TEXT_STARTS_WITH",
[startTimeString]),
format=gsf.cellFormat(backgroundColor=gsf.color(0, 1,
0)) # green
))
rules = get_conditional_format_rules(self.worksheet)
rules.clear()
# order is important, rule that is appended first takes priority
rules.append(breakConditionRule)
rules.append(absentConditionRule)
rules.append(emptyFirstTimeframeRule)
rules.append(lateConditionRule)
rules.append(onTimeConditionRule)
rules.save()
self.autoResizeCells(self.spreadsheet.id, self.worksheet.id)
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 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 create_disclaimer_worksheet(self, sheet):
worksheet = sheet.get_worksheet(0)
worksheet.update_title('Info')
disclaimer = INFO.format(self.zip_code).splitlines()
disclaimer_cells = worksheet.range(1, 1, len(disclaimer), 1)
for i, line in enumerate(disclaimer):
disclaimer_cells[i].value = line
worksheet.update_cells(disclaimer_cells)
fmt = gsf.cellFormat(backgroundColor=gsf.color(0.7, 0.77, 0.87),
textFormat=gsf.textFormat(
bold=True,
foregroundColor=gsf.color(0, 0, .54)),
horizontalAlignment='LEFT')
gsf.format_cell_ranges(worksheet, [('A1:E1', fmt), ('A3:E3', fmt),
('A4:E4', fmt), ('A9:E9', fmt)])
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_header(self):
"""Format the header of the invoice.
Turns the first row of the invoice blue
"""
header_format = cellFormat(backgroundColor=color(0, 0, 0.625))
format_cell_ranges(self.worksheet,
[(self.SHEET_START_POINT + ":" + self.LAST_COLUMN +
"1", header_format)])
def create_data_worksheet(self, sheet, rows, cols, properties_list):
worksheet = sheet.add_worksheet(title=self.zip_code,
rows=str(rows),
cols=str(cols))
worksheet.clear()
cell_list = worksheet.range(1, 1, rows, cols)
cell_values = [
'Provided to you by Engineered Cash Flow LLC, https://www.engineeredcashflow.com'
]
cell_values.extend([''] * (cols - 1))
cell_values.extend(self.fieldnames)
for p in tqdm(properties_list):
data = []
for field in self.fieldnames:
data.append(p.__dict__[field])
cell_values.extend(data)
assert len(cell_values) == len(cell_list), 'Cell/value mismatch'
for i, val in enumerate(cell_values):
cell_list[i].value = val
worksheet.update_cells(cell_list)
fmt_title = gsf.cellFormat(backgroundColor=gsf.color(0.7, 0.77, 0.87),
textFormat=gsf.textFormat(
bold=True,
foregroundColor=gsf.color(0, 0, .54)),
horizontalAlignment='LEFT')
fmt_fields = gsf.cellFormat(backgroundColor=gsf.color(0.7, 0.77, 0.87),
textFormat=gsf.textFormat(
bold=True,
foregroundColor=gsf.color(0, 0, .54)),
horizontalAlignment='CENTER')
# hack since gspread_formatting doesn't seem to support
# full row notation (e.g. '1:2')
col_label = chr(ord('a') + cols).upper()
gsf.format_cell_ranges(worksheet,
[('A1:{}1'.format(col_label), fmt_title),
('A2:{}2'.format(col_label), fmt_fields)])
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 cellWrite(output, Sheet, Color, fmtOK, formatlisting):
a = 0
x = 0
y = 0
Result = []
Check = 0
for a in range(len(output)):
Result.append([
a.strip() for a in output[a].split(',')
]) #Turn singular list in a list with 3 elements on each line
cell_listA = Sheet.range(
'A2:E{}'.format(len(Result) + 1)
) #Cell_list becomes the size of total tickets time 3. This is the amount of cells we will be taking up
for cellA in cell_listA:
cellA.value = Result[x][
y] #Fill in values of each individual cell to use back in the cell list
if Result[x][2] != 'NF':
Check = int(Result[x][2])
if Check >= 24:
if Check <= 120:
select = round(Check / 12) - 1
else:
select = 9
fmt = gspread_formatting.cellFormat(
backgroundColor=gspread_formatting.color(*Color[select]))
formatlisting.append(('C{}'.format(x + 2), fmt))
Check = 0
else:
formatlisting.append(('C{}'.format(x + 2), fmtOK))
y += 1
if y == 5: #This is to avoid index out of bounds
y = 0
x += 1
Sheet.update_cells(
cell_listA, value_input_option='USER_ENTERED') #write to google sheets
return Result
dataset_string = '=HYPERLINK("%s","%s")' % (model['dataset_url'], model['used_dataset'])
if ('molecular' in model['modelscope']) or ('subcellular' in model['modelscope']):
dataset_string = 'sim. data stored and documented within model entry --> SGA3 for Simulation Datasets in KG!'
return ['=HYPERLINK("%s","%s")' % (model['url'], name),
'=HYPERLINK("%s","%s")' % (model['custodian_url'], model['custodian']),
dataset_string,
'%s' % model['brain_structure'],
'%s' % model['dataset_species']]
base_fontsize = 10
req_data = []
req_data_format = []
blank_fmt = gf.cellFormat(
backgroundColor=gf.color(1,1,1),
textFormat=gf.textFormat(fontSize=base_fontsize, foregroundColor=gf.color(0,0,0))
)
req_data.append(["Annex D: Models released in EBrains KG from SP6 Model Components"])
fmt = gf.cellFormat(
backgroundColor=gf.color(1,1,1),
textFormat=gf.textFormat(bold=True, fontSize=base_fontsize*2, foregroundColor=gf.color(0.02,0.3,0.55))
)
req_data_format.append(fmt)
req_data.append([""])
req_data_format.append(blank_fmt)
req_data.append(ouput_col_headings)
fmt = gf.cellFormat(
backgroundColor=gf.color(0.02,0.3,0.55),
def prefill_worksheet0(worksheet, entry_dict, should_set_readonly_ranges, config):
market_season_string = "Jarní a letní" if di.is_spring_or_summer() else "Podzimní a zimní"
market_time_info_string = "{} burza {}".format(market_season_string, di.get_current_year())
row_infos = [
{
"start": "B1",
"data": ["č. prodejce:", "příjmení, jméno:", "", "tel:", "e-mail:"],
},
{
"start": "B4",
"data": [
"Číslo prodejce a pořadové číslo zboží",
"Druh zboží\n(vyber ze seznamu)",
"Barva\n(vyber ze seznamu)",
"Značka",
"Bližší specifikace - tři slova\n(stručný popis, např.motiv na zboží, název knihy, atd.......)\n-max.30 znaků-",
"Velikost",
"Cena\n(zaokrouhleno na 10 Kč)\nnapř.10, 20, 50",
],
},
{"start": "B3", "data": [market_time_info_string]},
{"start": "E3", "data": ["Prodejní seznam"]},
]
col_infos = [
{
"start": "C38",
"data": [
"Seznam je vyplněn, nehodlám do něj dále zapisovat:",
],
}
]
colors = [
"Béžová",
"Bílá",
"Černá",
"Červená",
"Fialová",
"Hnědá",
"Modrá",
"Oranžová",
"Růžová",
"Stříbrná",
"Šedá",
"Tyrkysová",
"Vínová",
"Zelená",
"Zlatá",
"Žlutá",
"jiná",
]
selection_types = [
"Autosedačky",
"Boty",
"Brusle",
"Bunda",
"Čepice, klobouk",
"Dresy",
"Dupačky, overal",
"Džíny",
"Helma",
"Hračka",
"Kabát",
"Kabelky, tašky, batohy",
"Kalhotky, slipy, boxerky",
"Kalhoty",
"Karnevalový kostým",
"Kniha",
"Kočárek",
"Kojenecká výbava -mimo oblečení",
"Kojenecké body",
"Kolo",
"Komplet, Souprava",
"Košile, Halenka, Blůzka",
"Kraťasy, Trenýrky",
"Legíny",
"Mikina",
"Nábytek",
"Pláštěnka",
"Plavky",
"Plyšová hračka",
"Podprsenka",
"Ponožky",
"Punčocháče",
"Pyžamo",
"Společenské hry",
"Společenské oblečení",
"Sportovní potřeba",
"Sukně",
"Svetr",
"Šaty",
"Těhotenské oblečení",
"Tepláky",
"Tílko",
"Tričko dl. rukáv",
"Tričko kr. rukáv",
"Župan",
"Ostatní zboží",
]
col_validations = [
{"start": "C5", "data": selection_types, "count": 30},
{"start": "D5", "data": colors, "count": 30},
]
non_editable_ranges = [
"A:B",
"I:Z34",
"C35:C",
"E38:Z38",
"D39:Z1000",
"D35:Z37",
"C1:H4",
]
bold_ranges = ["B2:H34", "B37:H40"]
underline_ranges = ["F2"]
background_color_infos = [
{"range": "B4:H4", "color": gf.color(195 / 255, 219 / 255, 226 / 255)},
{"range": "C5:H34", "color": gf.color(243 / 255, 212 / 255, 213 / 255)},
]
text_color_infos = [
{"range": "F2", "color": gf.color(165 / 255, 153 / 255, 174 / 255)}
]
grid_inner_ranges = ["B1:B2", "E1:E2", "F2", "B4:H34", "H2"]
grid_outer_ranges = [
"C1:D1",
"C2:D2",
"B3:D3",
"E3:F3",
"B1:H37",
"B35:H35",
"B36:H36",
"B37:H37",
]
column_widths = [13, 130, 213, 100, 132, 286, 85, 147, 20, 51]
row_heights = [
32,
47,
46,
115,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
40,
34,
40,
38,
]
font_infos = [
{"range": "B1:F1", "size": 11},
{"range": "B2", "size": 28},
{"range": "C2", "size": 16},
{"range": "B2", "size": 28},
{"range": "B3:H3", "size": 24},
{"range": "B4:H34", "size": 13},
{"range": "E2:F2", "size": 14},
]
email = entry_dict["email"]
phone_number = entry_dict["phone"]
full_name = entry_dict["surname"] + " " + entry_dict["name"]
vendor_id = entry_dict["vendor id"]
vendor_info = [vendor_id, full_name, "", phone_number, email]
merged_cells = ["B3:D3", "E3:F3", "C40:J40", "C37:G37"]
worksheet.update_title(config["sheet1_name"])
batch_text_fill(worksheet, row_infos, col_infos, vendor_info)
batch_format(
worksheet,
bold_ranges,
background_color_infos,
column_widths,
row_heights,
font_infos,
text_color_infos,
underline_ranges,
)
set_spreadsheet_validation(
worksheet,
[],
col_validations,
non_editable_ranges,
should_set_readonly_ranges,
config,
)
checkbox_condition(worksheet, "D38:D38")
set_merged_cells(worksheet, merged_cells)
class GoogleSheetBase:
current_flow = None
color_format = {
'green' : cellFormat(
backgroundColor=color(0, 1, 0), #set it to yellow
textFormat=textFormat(foregroundColor=color(0, 0, 0)),
),
'yellow' : cellFormat(
backgroundColor=color(1, 1, 0), #set it to yellow
textFormat=textFormat(foregroundColor=color(0, 0, 0)),
),
'white' : cellFormat(
backgroundColor=color(1, 1, 1), #set it to yellow
textFormat=textFormat(foregroundColor=color(0, 0, 0)),
)
}
def __init__(self, doc_id, credentials_filepath, tab_index, unique_header):
self.credentials_filepath = credentials_filepath
self.credentials = self.get_credentials()
self.doc_id = doc_id
doc_url = f'https://docs.google.com/spreadsheets/d/{doc_id}'
gsp = gspread.authorize(self.credentials)
doc = gsp.open_by_url(doc_url)
self.tab_index = tab_index
self.ws = doc.get_worksheet(tab_index)
self.header_info = None
self.header_info_reverse = None
self.unique_header = unique_header
def get_credentials(self, project_filepath=None):
if os.path.exists(self.credentials_filepath) == False:
logger.info(f"credentials_filepath : {self.credentials_filepath}")
url = self.__make_token_cli(project_filepath)
logger.debug(f"Auth URL : {url}")
code = input("Input Code : ")
self.__save_token(self.credentials_filepath, code)
store = Storage(self.credentials_filepath)
credentials = store.get()
if not credentials or credentials.invalid:
logger.warning('credentials error')
#flow = client.flow_from_clientsecrets('credentials.json', SCOPES)
#creds = tools.run_flow(flow, store)
os.remove(self.credentials_filepath)
return self.get_credentials(self.credentials_filepath)
return credentials
def __make_token_cli(self, project_filepath):
try:
if project_filepath == None:
project_filepath = os.path.join(os.path.dirname(__file__), 'cs.json')
self.current_flow = flow_from_clientsecrets(
project_filepath, # downloaded file
'https://www.googleapis.com/auth/drive', # scope
redirect_uri='urn:ietf:wg:oauth:2.0:oob')
return self.current_flow.step1_get_authorize_url()
except Exception as e:
logger.error(f"Exception: {e}")
logger.error(traceback.format_exc())
def __save_token(self, credentials_filepath, code):
try:
credentials = self.current_flow.step2_exchange(code)
storage = Storage(credentials_filepath)
storage.put(credentials)
return True
except Exception as e:
logger.error(f"Exception: {e}")
logger.error(traceback.format_exc())
return False
def get_sheet_data(self):
tmp = self.ws.get_all_values()#[:-1]
self.set_sheet_header(tmp[0])
rows = tmp[1:]
ret = []
for row in rows:
item = {}
for idx, col in enumerate(row):
item[self.header_info_reverse[idx+1]] = col
ret.append(item)
return ret
def set_sheet_header(self, row):
self.header_info = {}
self.header_info_reverse = {}
for idx, col in enumerate(row):
self.header_info[col] = idx + 1
self.header_info_reverse[idx+1] = col
logger.debug(self.header_info)
def find_row_index(self, total_data, data):
find_row_index = -1
#find = False
#data['IDX'] = len(total_data)+1
for idx, item in enumerate(total_data):
if item[self.unique_header] == str(data[self.unique_header]):
#find = True
find_row_index = idx
#data['IDX'] = find_row_index + 1
break
if find_row_index == -1:
data['IDX'] = len(total_data)+1
return find_row_index
def sleep(self):
time.sleep(0.5)
def sleep_exception(self):
time.sleep(10)
def after_update_cell(self, sheet_row_index, sheet_col_index, key, value, old_value):
pass
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)
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)
def write_data(self, total_data, data):
find_row_index = self.find_row_index(total_data, data)
write_count = 0
for key, value in data.items():
if key.startswith('_'):
continue
if value == None:
continue
if key not in self.header_info:
continue
while True:
try:
if find_row_index != -1 and str(total_data[find_row_index][key]) != str(value):
logger.warning(f"업데이트 : {key} {total_data[find_row_index][key]} ==> {value}")
self.ws.update_cell(find_row_index+2, self.header_info[key], value)
self.after_update_cell(find_row_index+2, self.header_info[key], key, value, total_data[find_row_index][key])
write_count += 1
self.sleep()
elif find_row_index == -1 and value != '':
logger.warning(f"추가 : {key} {value}")
self.ws.update_cell(len(total_data)+2, self.header_info[key], value)
self.after_update_cell(len(total_data)+2, self.header_info[key], key, value, None)
write_count += 1
self.sleep()
break
except gspread.exceptions.APIError:
self.sleep_exception()
except Exception as exception:
logger.error(f"{key} - {value}")
logger.error('Exception:%s', exception)
logger.error(traceback.format_exc())
logger.error(self.header_info)
self.sleep_exception()
if find_row_index == -1:
total_data.append(data)
else:
total_data[find_row_index] = data
return write_count
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)
in model['modelscope']):
dataset_string = 'sim. data stored and documented within model entry --> SGA3 for Simulation Datasets in KG!'
return [
'=HYPERLINK("%s","%s")' % (model['url'], name),
'=HYPERLINK("%s","%s")' %
(model['custodian_url'], model['custodian']), dataset_string,
'%s' % model['brain_structure'],
'%s' % model['dataset_species']
]
base_fontsize = 10
req_data = []
req_data_format = []
blank_fmt = gf.cellFormat(backgroundColor=gf.color(1, 1, 1),
textFormat=gf.textFormat(
fontSize=base_fontsize,
foregroundColor=gf.color(0, 0, 0)))
req_data.append(
["Models released in EBrains KG from SP4 Model Components"])
fmt = gf.cellFormat(backgroundColor=gf.color(1, 1, 1),
textFormat=gf.textFormat(bold=True,
fontSize=base_fontsize * 2,
foregroundColor=gf.color(
0.02, 0.3, 0.55)))
req_data_format.append(fmt)
req_data.append([""])
req_data_format.append(blank_fmt)
