def sheet_formatter(self, sheetname):
working_sheet = self.sh.worksheet(sheetname)
sheetId = working_sheet.id
body = {
"requests": [{
"autoResizeDimensions": {
"dimensions": {
"sheetId": sheetId,
"dimension": 'COLUMNS',
"startIndex": 1,
"endIndex": 5
}
}
}]
}
self.sh.batch_update(body)
header_fmt = f.CellFormat(
backgroundColor=f.Color(0.043137256, 0.3254902, 0.5803922),
textFormat=f.TextFormat(bold=True,
foregroundColor=f.Color(1, 1, 1)),
horizontalAlignment='CENTER',
verticalAlignment='MIDDLE')
f.set_frozen(
working_sheet, rows=1,
cols=2) # freeze the headers and hotel_id and status column
f.format_cell_range(working_sheet, '1', header_fmt)
def save_to_google(filename, bad_list, exp_from, exp_to):
""" Выгружает список вакансий в google-таблицу с известным ID, полностью затирая таблицу """
sh, client = connect_to_google()
# Записать таблицу из tsv в первый лист
content = open(filename, 'r', newline='', encoding='utf-8').read()
client.import_csv(sh.id, content.encode('utf-8'))
# Закрепить первую строку, иначе сортировка и фильтрация будут ломать таблицу
sheet = sh.sheet1
set_frozen(sheet, rows=1)
fill_bad_sheet(sh, bad_list)
fill_stats_sheets(sh, exp_from, exp_to)
def load(
self,
data: list,
raw: bool = False,
drop: bool = True,
freeze: list = [1, 1],
):
self.spreadsheet = self.ga.open(self.spreadsheet_name)
rows = len(data)
cols = len(data[0])
size = rows * cols
speed = 0.00007
print(f"loading {rows}x{cols}={size:_}")
print(f"estimated {round(10+size*speed,0)} sec")
if drop:
try:
self.worksheet = self.spreadsheet.worksheet(
self.worksheet_name)
self.spreadsheet.del_worksheet(self.worksheet)
except:
pass
self.spreadsheet.add_worksheet(
self.worksheet_name,
rows=rows,
cols=cols,
)
self.worksheet = self.spreadsheet.worksheet(self.worksheet_name)
step = 4096 if rows > 4096 else rows
for row in range(0, rows, step):
range_name = (f""
f"{rowcol_to_a1(row+1,0+1)}"
f":{rowcol_to_a1(row +step+1,cols+1)}")
ans = self.worksheet.update(
range_name,
data[row:row + step],
raw=raw,
)
self.worksheet.set_basic_filter()
try:
gsf.set_frozen(
self.worksheet,
rows=freeze[1],
cols=freeze[0],
)
except:
pass
return ans
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 write_to_sheet(
gc,
sheet_id,
worksheet_name,
df,
column_width_dict=None,
header_row_height=None,
no_borders=False,
percent_cols=[],
wrap_cols=[],
cols_to_colors={},
):
sh = gc.open_by_key(sheet_id)
# may fail if sheet isn't created
try:
clear_worksheet(sh, worksheet_name)
except Exception as _:
pass
wk = sh.worksheet(worksheet_name)
header_letters = [
'A',
'B',
'C',
'D',
'E',
'F',
'G',
'H',
'I',
'J',
'K',
'L',
'M',
'N',
'O',
'P',
'Q',
'R',
'S',
'T',
'U',
'V',
'W',
'X',
'Y',
'Z',
]
column_ids = header_letters.copy()
# 26**2 columns max in spreadsheet
for letter_A in header_letters:
for letter_B in header_letters:
column_ids.append(letter_A + letter_B)
# writes df to sheet
set_with_dataframe(wk, df)
header_letter = column_ids[df.shape[1] - 1]
# set all font to 11
format_obj = {
"textFormat": {
"fontSize": 11
},
}
# freeze the header layer
set_frozen(wk, rows=1)
# remove borders
if no_borders:
format_obj['borders'] = {
"top": {
"style": "NONE",
"width": 0
},
"bottom": {
"style": "NONE",
"width": 0
},
"left": {
"style": "NONE",
"width": 0
},
"right": {
"style": "NONE",
"width": 0
},
}
wk.format(f'A1:Z1000', format_obj)
# set cols as percent cols
for col in percent_cols:
wk.format(
f'{header_letters[col]}1:{header_letters[col]}2000',
{'numberFormat': {
'pattern': '0.0%',
'type': "PERCENT"
}},
)
# writes a header
wk.format(
f'A1:{header_letter}1',
{
"backgroundColor": {
"red": 0.95,
"green": 0.95,
"blue": 0.95
},
"horizontalAlignment": "CENTER",
"textFormat": {
"fontSize": 12,
"bold": True
},
"wrapStrategy": "WRAP",
},
)
# wrap columns
for col in wrap_cols:
wk.format(
f'{header_letters[col]}1:{header_letters[col]}2000',
{
"wrapStrategy": "WRAP",
},
)
# column widths
for idx in range(df.shape[1]):
if idx % WRITE_THRESHOLD == 0:
time.sleep(2)
if column_width_dict is not None and idx in column_width_dict:
set_column_width(wk, column_ids[idx], column_width_dict[idx])
else:
set_column_width(wk, column_ids[idx], 150)
time.sleep(2)
# row heights
if header_row_height:
set_row_height(wk, '1', header_row_height)
conditional_formatter(wk, df, cols_to_colors, header_letters)
# clear sheet1 if it exists
try:
clear_sheet1(sh)
except Exception as _:
pass
def add_to_sheet(target, data):
print('Add sheet processing: {}'.format(target))
# key name order
key_order = [
'Proposed Title', 'Type', 'Current URL', 'Proposed URL',
'Primary Keywords', 'Monthly Searches', 'Proposed Word Count',
'Secondary Keywords'
]
if data == []:
empty_dict = {}
for k in key_order:
empty_dict[k] = ''
data.append(empty_dict)
df = pd.DataFrame(data)
df = df[key_order]
secondary = df['Secondary Keywords'].apply(pd.Series)
secondary = secondary.rename(columns=lambda x: ''.format(x))
df.drop('Secondary Keywords', axis=1, inplace=True)
secondary.columns.values[0] = 'Secondary Keywords'
df = pd.concat([df[:], secondary[:]], axis=1)
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'content-map-id.json', scope)
gc = gspread.authorize(credentials)
# Checking if exists
list_sheet = gc.openall(title=target)
ids = [sheet.id for sheet in list_sheet]
if len(ids) > 0:
for id in ids:
gc.del_spreadsheet(id)
gc.create(target, "sheet file id")
ws = gc.open(target).worksheet("Sheet1")
set_with_dataframe(ws, df)
get_as_dataframe(ws)
ws.format(
"A1:H1", {
"horizontalAlignment": "CENTER",
"textFormat": {
"fontSize": 10,
"bold": True
}
})
set_column_widths(ws, [('A', 500), ('B', 100), ('C', 300), ('D', 300),
('E', 300), ('F', 200), ('G', 200), ('H:AZ', 300)])
set_frozen(ws, rows=1, cols=1)
count_row = df.shape[0] + 2
validation_rule = DataValidationRule(BooleanCondition(
'ONE_OF_LIST', [
'Home Page', 'Blog Category', 'Blog Home', 'Blog Post', 'Page',
'Product'
]),
showCustomUi=True)
set_data_validation_for_cell_range(ws, 'B2:B{}'.format(count_row),
validation_rule)
print('Add sheet finished: {}'.format(target))