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'