def set_text_color(batch, worksheet, cell_infos):
for cell_info in cell_infos:
color = cell_info["color"]
cell_range = cell_info["range"]
text_format = gf.textFormat(foregroundColor=color)
cell_format = gf.cellFormat(textFormat=text_format)
batch.format_cell_ranges(worksheet, [(cell_range, cell_format)])
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 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 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 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 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 update_features(self, trello_features):
name_list = (self.df.loc[self.release:self.next_release,
self.team].iloc[:-1].tolist())
status_col = self.df.columns.get_loc(
self.team) # works b/c pd is zero based
self.logger.debug(f"DEBUG: {self.df.columns.has_duplicates}")
value_updates = []
format_updates = []
for feature in trello_features:
self.logger.debug(f"Starting on {feature}")
if feature.release != self.release:
raise ValueError(
"Only features matching the current release can be updated."
"Current Release: {self.release}"
"Feature Release: {feature.release}"
"Feature Name: {feature.name}")
feature_row = (
name_list.index(feature.name) +
self.df.index.get_loc(self.release) + 1 # Index is zero based
)
a1 = gspread.utils.rowcol_to_a1(feature_row, status_col)
fmt = gspread_formatting.cellFormat(
backgroundColor=self.status_to_color(feature.status),
horizontalAlignment="CENTER",
textFormat=gspread_formatting.textFormat(
bold=True,
foregroundColor=gspread_formatting.Color.fromHex(
"#ffffff"),
),
)
value_updates.append({
"range":
a1,
"values": [[
self.status_to_value(feature.status),
]],
})
format_updates.append((a1, fmt))
self.logger.debug(f"Row: {feature_row}")
self.logger.debug(f"Col: {status_col}")
self.logger.debug(f"A1: {a1}")
self._ws.batch_update(value_updates)
gspread_formatting.format_cell_ranges(self._ws, format_updates)
self._df = None
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),
textFormat=gf.textFormat(bold=True, fontSize=base_fontsize, foregroundColor=gf.color(1,1,1))
def format_for_header(self, series, dataframe):
return cellFormat(backgroundColor=self.header_background_color,
textFormat=textFormat(
bold=True,
foregroundColor=self.header_text_color))
def set_fonts(batch, worksheet, font_infos):
for font_info in font_infos:
cell_range = font_info["range"]
text_format = gf.textFormat(fontSize=font_info["size"])
cell_format = gf.cellFormat(textFormat=text_format)
batch.format_cell_ranges(worksheet, [(cell_range, cell_format)])
def set_underline(batch, worksheet, cell_ranges):
text_format = gf.textFormat(underline=True)
cell_format = gf.cellFormat(textFormat=text_format)
for cell_range in cell_ranges:
batch.format_cell_ranges(worksheet, [(cell_range, cell_format)])
def set_bold(batch, worksheet, cell_ranges):
text_format = gf.textFormat(bold=True)
cell_format = gf.cellFormat(textFormat=text_format)
for cell_range in cell_ranges:
batch.format_cell_ranges(worksheet, [(cell_range, cell_format)])
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)