def one_of_condition_column_from_a1(worksheet, categories, start_cell, count):
validation_rule = gf.DataValidationRule(
gf.BooleanCondition("ONE_OF_LIST", categories), showCustomUi=True, strict=True
)
start_row, start_col = gspread.utils.a1_to_rowcol(start_cell)
end_cell = gspread.utils.rowcol_to_a1(start_row + count - 1, start_col)
cell_range = start_cell + ":" + end_cell
gf.set_data_validation_for_cell_range(worksheet, cell_range, validation_rule)
def checkbox_condition(worksheet, cell_range):
first_cell = worksheet.range(cell_range)[0].address
validation_rule = gf.DataValidationRule(
gf.BooleanCondition("BOOLEAN", []),
showCustomUi=True,
strict=True,
inputMessage="Použijte prosím zašrktávací políčko.",
)
gf.set_data_validation_for_cell_range(worksheet, cell_range, validation_rule)
def at_most_n_chars_condition(worksheet, n, cell_range, input_message):
first_cell = worksheet.range(cell_range)[0].address
validation_rule = gf.DataValidationRule(
gf.BooleanCondition(
"CUSTOM_FORMULA", ["=(LEN(" + first_cell + ") <= " + str(n) + ")"]
),
showCustomUi=True,
strict=True,
inputMessage=input_message,
)
gf.set_data_validation_for_cell_range(worksheet, cell_range, validation_rule)
def create_access_worksheets(self):
colors = {
"ADMINS": (0, 0.5, 0.5),
"STUDENTS": (0.4, 0.8, 0.1),
"INVENTORY": (0.5, 0.5, 0),
}
headers = {
"ADMINS": ["Admin", "RFID", "ACCESS"],
"STUDENTS": ["Student", "RFID", "Class", "ACCESS"],
"INVENTORY": ["Item", "RFID"]
}
for sheet_name, sheet_cols in headers.items():
num_cols = len(sheet_cols)
worksheet = self.ACCESS.add_worksheet(title=sheet_name,
rows=1,
cols=num_cols)
worksheet.insert_rows([sheet_cols], 1)
worksheet.format(
f"A1:{string.ascii_uppercase[num_cols - 1]}1", {
"backgroundColor": {
"red": colors[sheet_name][0],
"green": colors[sheet_name][1],
"blue": colors[sheet_name][2]
},
"horizontalAlignment": "LEFT",
"textFormat": {
"foregroundColor": {
"red": 1,
"green": 1,
"blue": 1
},
"fontSize": 12,
"bold": True
}
})
if sheet_name == "STUDENTS":
# Add a column which specified which class the student is in
class_col = string.ascii_uppercase[sheet_cols.index("Class")]
set_data_validation_for_cell_range(worksheet, class_col,
class_validation_rule)
if sheet_name != "INVENTORY":
# Add a Yes/No column to grant/deny access
access_col = string.ascii_uppercase[sheet_cols.index("ACCESS")]
set_data_validation_for_cell_range(worksheet, access_col,
access_validation_rule)
def createSheetFromFile(filePath):
# create the spreadsheet
gc = gspread.service_account()
spreadsheet = gc.create(filePath)
spreadsheet.share(ownerEmail, perm_type='user', role='writer')
# edit the spreadsheet
items = gc.open(filePath).sheet1
# creating/configuring headers
items.update('A1', 'Item:')
gsf.set_column_width(items, 'A', 450)
items.update('B1', 'Status:')
gsf.set_column_width(items, 'B', 200)
# data validation for status column
defaultItemStatus = '❌ INCOMPLETE'
validation_rule = gsf.DataValidationRule(gsf.BooleanCondition(
'ONE_OF_LIST', [defaultItemStatus, '⚠️ WIP', '✅ COMPLETE']),
showCustomUi=True)
toAdd = getItems(filePath)
# how many cells large is the gap between title and list
listUpperPadding = 1
columnACounter = listUpperPadding
columnBCounter = listUpperPadding
# create the list
for i in range(0, len(toAdd)):
columnACounter += 1
columnBCounter += 1
itemCell = str('A' + str(columnACounter + 1))
statusCell = str('B' + str(columnBCounter + 1))
items.update(itemCell, toAdd[i])
items.update(statusCell, defaultItemStatus)
columnBRange = str('B3:B' + str(columnBCounter + 1))
gsf.set_data_validation_for_cell_range(items, columnBRange,
validation_rule)
def box(self, db, data):
sheet = self.get_sheets(db)
values = self.get_sheets(db, mode='data')
rows = len(values)
validation_rule = DataValidationRule(
BooleanCondition(
'BOOLEAN'
), # condition'type' and 'values', defaulting to TRUE/FALSE
showCustomUi=True)
set_data_validation_for_cell_range(sheet,
f'{data[0]}2:{data[0]}{rows}',
validation_rule)
set_data_validation_for_cell_range(sheet,
f'{data[1]}2:{data[0]}{rows}',
validation_rule)
value = []
for i in range(rows - 1):
for j in range(values[i]):
value.append(values[i][j])
print(values)
def ends_with_str_condition(worksheet, ending, cell_range, input_message):
first_cell = worksheet.range(cell_range)[0].address
validation_rule = gf.DataValidationRule(
gf.BooleanCondition(
"CUSTOM_FORMULA",
[
"=IF(ISBLANK("
+ first_cell
+ "),TRUE, IF(ISNUMBER("
+ first_cell
+ "),REGEXMATCH(TEXT("
+ first_cell
+ ',"0"),".*'
+ ending
+ '$"), FALSE))'
],
),
showCustomUi=True,
strict=True,
inputMessage=input_message,
)
gf.set_data_validation_for_cell_range(worksheet, cell_range, validation_rule)
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))