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 __format_mark(self): # Mark Formatting (centered 'X') mark_format = CellFormat(horizontalAlignment="CENTER") format_cell_ranges(self.worksheet, [("C2:{}{}".format( self.__colnum_string(self.__get_highest_column()), self.__get_highest_row()), mark_format)])
def GetData(LoLaNid): Query_String = {'tagId': LoLaNid, "property_equal": property_equal, "timeformat": timeformat} from_msFromEpoch=request.query.from_msFromEpoch if from_msFromEpoch: Query_String["from_msFromEpoch"]=from_msFromEpoch else : return "Wrong URL" #Query and resoponse response = requests.get(Api, Query_String)#Send the request and recieve the response data=response.json()# convert the response body into JSON creds = ServiceAccountCredentials.from_json_keyfile_name('account_key.json', scope) client = gspread.authorize(creds) sheet = client.open(sheet_name).sheet1 try: sheet.get_all_records() #if it is empty it will throw an exception except: sheet.append_row(['tagID','timeStamp','acceX','acceY','acceZ','gX','gY','gZ','mX','mY','mZ','t','p']) fmt = CellFormat( backgroundColor=Color(1, 0.9, 0.9), textFormat=TextFormat(bold=True), horizontalAlignment='CENTER') format_cell_ranges(sheet, [('A1:M1', fmt)]) for x in range(len(data)): accX=int(round( (data[x]['value'][0]*1000))) accY = int(round((data[x]['value'][1] * 1000))) accZ = int(round((data[x]['value'][2] * 1000))) sheet.append_row([data[x]['tagId'],(data[x]['updatedAt_msFromEpoch']),accX,accY,accZ,0,0,0,0,0,0,0,0]) return "done, data are in saved on "+sheet_name+"google sheet "
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_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
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 format_with_dataframe(worksheet, dataframe, formatter=None, row=1, col=1, include_index=False, include_column_header=True): """ Modifies the cell formatting of an area of the provided Worksheet, using the provided DataFrame to determine the area to be formatted and the formats to be used. :param worksheet: the gspread worksheet to set with content of DataFrame. :param dataframe: the DataFrame. :param formatter: an optional instance of ``DataFrameFormatter`` class, which will examine the contents of the DataFrame and assemble a set of ``gspread_formatter`` operations to be performed after the DataFrame contents are written to the given Worksheet. The formatting operations are performed after the contents are written and before this function returns. Defaults to ``DEFAULT_FORMATTER``. :param row: number of row at which to begin formatting. Defaults to 1. :param col: number of column at which to begin formatting. Defaults to 1. :param include_index: if True, include the DataFrame's index as an additional column when performing formatting. Defaults to False. :param include_column_header: if True, format a header row before data. Defaults to True. """ if not formatter: formatter = DEFAULT_FORMATTER formatting_ranges = [] columns = [dataframe[c] for c in dataframe.columns] index_column_size = _determine_index_or_columns_size(dataframe.index) column_header_size = _determine_index_or_columns_size(dataframe.columns) if include_index: # allow for multi-index index if index_column_size > 1: reset_df = dataframe.reset_index() index_elts = [ reset_df[c] for c in list(reset_df.columns)[:index_column_size] ] else: index_elts = [dataframe.index] columns = index_elts + columns for idx, column in enumerate(columns): column_fmt = formatter.format_for_column(column, col + idx, dataframe) if not column_fmt or not column_fmt.to_props(): continue range = '{}:{}'.format( rowcol_to_a1(row, col + idx), rowcol_to_a1(row + dataframe.shape[0], col + idx)) formatting_ranges.append((range, column_fmt)) if include_column_header: # TODO allow for multi-index columns object elts = list(dataframe.columns) if include_index: # allow for multi-index index if index_column_size > 1: index_names = list(dataframe.index.names) else: index_names = [dataframe.index.name] elts = index_names + elts header_fmt = formatter.format_for_header(dataframe.index, dataframe) if header_fmt: formatting_ranges.append(('{}:{}'.format( rowcol_to_a1(row, col), rowcol_to_a1(row + dataframe.shape[0], col + index_column_size - 1)), header_fmt)) header_fmt = formatter.format_for_header(elts, dataframe) if header_fmt: formatting_ranges.append(('{}:{}'.format( rowcol_to_a1(row, col), rowcol_to_a1(row + column_header_size - 1, col + len(elts) - 1)), header_fmt)) freeze_args = {} if row == 1 and formatter.should_freeze_header(elts, dataframe): freeze_args['rows'] = column_header_size if include_index and col == 1 and formatter.should_freeze_header( dataframe.index, dataframe): freeze_args['cols'] = index_column_size row += column_header_size values = [] for value_row, index_value in zip_longest(dataframe.values, dataframe.index): if include_index: if index_column_size > 1: index_values = list(index_value) else: index_values = [index_value] value_row = index_values + list(value_row) values.append(value_row) for y_idx, value_row in enumerate(values): for x_idx, cell_value in enumerate(value_row): cell_fmt = formatter.format_for_cell(cell_value, y_idx + row, x_idx + col, dataframe) if cell_fmt: formatting_ranges.append((rowcol_to_a1(y_idx + row, x_idx + col), cell_fmt)) row_fmt = formatter.format_for_data_row(values, y_idx + row, dataframe) if row_fmt: formatting_ranges.append(('{}:{}'.format( rowcol_to_a1(y_idx + row, col), rowcol_to_a1(y_idx + row, col + dataframe.shape[1])), row_fmt)) if formatting_ranges: formatting_ranges = [ r for r in formatting_ranges if r[1] and r[1].to_props() ] format_cell_ranges(worksheet, formatting_ranges) if freeze_args: set_frozen(worksheet, **freeze_args)
def __format_data(self): # NPM Formatting (right-aligned) npm_format = CellFormat(horizontalAlignment="RIGHT") # Name Formatting (left-aligned) name_format = CellFormat(horizontalAlignment="LEFT") format_cell_ranges(self.worksheet, [("A2:A", npm_format), ("B2:B", name_format)]) # Do column resizing, sorting, and bordering after update body = { "requests": [ { "setBasicFilter": { # Sort the value "filter": { "range": { "sheetId": 0, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": self.__get_highest_row(), "endColumnIndex": self.__get_highest_column(), }, "sortSpecs": [{ "dimensionIndex": 0, "sortOrder": "ASCENDING" }] } } }, { "clearBasicFilter": { # Clean the sort residue "sheetId": 0 } }, { "autoResizeDimensions": { "dimensions": { "sheetId": 0, "dimension": "COLUMNS", "startIndex": 0, "endIndex": self.__get_highest_column() } }, }, { "updateBorders": { "range": { "sheetId": 0, "startRowIndex": 0, "endRowIndex": self.__get_highest_row(), "startColumnIndex": 0, "endColumnIndex": self.__get_highest_column() }, "top": { "style": "SOLID" }, "left": { "style": "SOLID" }, "right": { "style": "SOLID" }, "bottom": { "style": "SOLID" }, "innerVertical": { "style": "SOLID" }, "innerHorizontal": { "style": "SOLID" }, } } ] } self.spreadsheet.batch_update(body)
def __format_headers(self): # Header format (centered and bold) header_format = CellFormat(textFormat=TextFormat(bold=True), horizontalAlignment="CENTER") format_cell_ranges(self.worksheet, [("1", header_format)])
gf.set_row_height(ws, '1:' + str(height), 10) gf.set_column_width(ws, 'A:' + i2s(width), 10) formats = [] h_scale = im.height // height w_scale = im.width // width for i in range(height): for j in range(width): r_s, g_s, b_s = 0, 0, 0 cnt = 0 for x in range(i * h_scale, (i + 1) * h_scale): for y in range(j * w_scale, (j + 1) * w_scale): r, g, b = im.getpixel((x, y)) r_s += r g_s += g b_s += b cnt += 1 cnt *= 255 fmt = gf.CellFormat(backgroundColor=gf.Color(r_s / cnt, g_s / cnt, b_s / cnt)) formats.append((c2s(j + 1, i + 1) + ':' + c2s(j + 1, i + 1), fmt)) gf.format_cell_ranges(ws, formats)