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 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 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 align_cells(ws, cells):
print "align_cells range=", cells
fmt = gsf.cellFormat(
horizontalAlignment='CENTER'
)
gsf.format_cell_range(ws, cells, fmt)
pass
def format_number(self):
fmt = gspread_formatting.cellFormat(
numberFormat=gspread_formatting.NumberFormat(type="NUMBER",
pattern="#,##0"))
gspread_formatting.format_cell_ranges(self.worksheet, [('F', 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 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 format_for_column(self, column, col_number, dataframe):
if column.name in self.column_formats:
return self.column_formats[column.name]
dtype = column.dtype
if dtype.kind == 'O' and hasattr(column, 'infer_objects'):
dtype = column.infer_objects().dtype
if dtype.kind == 'f':
return cellFormat(numberFormat=self.decimal_format,
horizontalAlignment='RIGHT')
elif dtype.kind == 'i':
return cellFormat(numberFormat=self.integer_format,
horizontalAlignment='RIGHT')
elif dtype.kind == 'M':
return cellFormat(numberFormat=self.date_format,
horizontalAlignment='CENTER')
else:
return cellFormat(
horizontalAlignment=('LEFT' if col_number == 1 else 'CENTER'))
def set_text_alignment(
batch, worksheet, middle_cell_ranges, left_cell_ranges, right_cell_ranges
):
middle_cell_format = gf.cellFormat(
horizontalAlignment="CENTER", verticalAlignment="MIDDLE"
)
left_cell_format = gf.cellFormat(
horizontalAlignment="LEFT", verticalAlignment="MIDDLE"
)
right_cell_format = gf.cellFormat(
horizontalAlignment="RIGHT", verticalAlignment="MIDDLE"
)
for cell_range in middle_cell_ranges:
batch.format_cell_ranges(worksheet, [(cell_range, middle_cell_format)])
for cell_range in left_cell_ranges:
batch.format_cell_ranges(worksheet, [(cell_range, left_cell_format)])
for cell_range in right_cell_ranges:
batch.format_cell_ranges(worksheet, [(cell_range, right_cell_format)])
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 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 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
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
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_wrap_strategy(batch, worksheet, cell_range):
cell_format = gf.cellFormat(wrapStrategy="WRAP")
batch.format_cell_ranges(worksheet, [(cell_range, cell_format)])
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_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_colors(batch, worksheet, color_infos):
for color_info in color_infos:
color = color_info["color"]
cell_range = color_info["range"]
cell_format = gf.cellFormat(backgroundColor=color)
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_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)])
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),
textFormat=gf.textFormat(bold=True, fontSize=base_fontsize, foregroundColor=gf.color(1,1,1))
wks.update_title("Sheet 1")
# 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'
