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)
