def create_shoebox_worksheet(self, box_name):
worksheet = self.LOG.add_worksheet(title=box_name,
rows=MAX_LOG_LENGTH - 1,
cols=len(LOG_COLS))
worksheet.insert_rows([LOG_COLS], 1)
cols_num = len(LOG_COLS)
worksheet.format(
f"A1:{string.ascii_uppercase[cols_num - 1]}1", {
"backgroundColor": {
"red": 0.2,
"green": 0.2,
"blue": 0.7
},
"horizontalAlignment": "LEFT",
"textFormat": {
"foregroundColor": {
"red": 1,
"green": 1,
"blue": 1
},
"fontSize": 12,
"bold": True
}
})
# Make the "timestamp" column wider
time_col = LOG_COLS.index("timestamp")
time_col = string.ascii_uppercase[time_col]
set_column_width(worksheet, time_col, 250)
return worksheet
async def add_quick_links_worksheet(
self, spreadsheet: gspread_asyncio.AsyncioGspreadSpreadsheet,
puzzle: PuzzleData, settings: GuildSettings):
worksheet = await spreadsheet.add_worksheet(title="Quick Links",
rows=10,
cols=2)
cell_range = await worksheet.range(1, 1, 10, 2)
self.update_cell_row(cell_range, 1, "Hunt URL", puzzle.hunt_url)
self.update_cell_row(cell_range, 2, "Drive folder",
urls.drive_folder_url(puzzle.google_folder_id))
nexus_url = urls.spreadsheet_url(
settings.drive_nexus_sheet_id
) if settings.drive_nexus_sheet_id else ""
self.update_cell_row(cell_range, 3, "Nexus", nexus_url)
resources_url = urls.docs_url(
settings.drive_resources_id) if settings.drive_resources_id else ""
self.update_cell_row(cell_range, 4, "Resources", resources_url)
self.update_cell_row(cell_range, 5, "Discord channel mention",
puzzle.channel_mention)
self.update_cell_row(
cell_range, 6, "Reminders",
"Please create a new worksheet if you're making large changes (e.g. re-sorting)"
)
self.update_cell_row(
cell_range, 7, "",
"You can use Ctrl+Alt+M to leave a comment on a cell")
await worksheet.update_cells(cell_range)
# Not async
gspread_formatting.set_column_width(worksheet.ws, "B", 1000)
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 create_intro_sheet(sheet):
# Create the Introductory Worksheet
worksheet = sheet.add_worksheet(title="SMART CABINET", rows=1, cols=1)
sheet.del_worksheet(sheet.sheet1)
worksheet.insert_rows(INTRO_SHEET, 1)
set_column_width(worksheet, 'A', 1000)
set_row_height(worksheet, '1:4', 70)
set_row_height(worksheet, '5:28', 34)
worksheet.format(
"A1:A1", {
"backgroundColor": {
"red": 0,
"green": 0,
"blue": 0
},
"horizontalAlignment": "CENTER",
"textFormat": {
"foregroundColor": {
"red": 0,
"green": 0.9,
"blue": 1
},
"fontSize": 30,
"bold": True
}
})
worksheet.format(
"A2:A5", {
"backgroundColor": {
"red": 0,
"green": 0,
"blue": 0
},
"horizontalAlignment": "CENTER",
"textFormat": {
"foregroundColor": {
"red": 1,
"green": 1,
"blue": 1
},
"fontSize": 30,
"bold": True
}
})
worksheet.format(
"A6:A7", {
"backgroundColor": {
"red": 0,
"green": 0,
"blue": 0
},
"horizontalAlignment": "LEFT",
"textFormat": {
"foregroundColor": {
"red": 1,
"green": 0,
"blue": 0
},
"fontSize": 14,
"bold": True
}
})
worksheet.format(
"A8:A8", {
"backgroundColor": {
"red": 0,
"green": 0,
"blue": 0
},
"horizontalAlignment": "LEFT",
"textFormat": {
"foregroundColor": {
"red": 0,
"green": 0.9,
"blue": 1
},
"fontSize": 12,
"bold": True
}
})
worksheet.format(
"A9:A14", {
"backgroundColor": {
"red": 0,
"green": 0,
"blue": 0
},
"horizontalAlignment": "LEFT",
"textFormat": {
"foregroundColor": {
"red": 1,
"green": 1,
"blue": 1
},
"fontSize": 12,
"bold": True
}
})
worksheet.format(
"A15:A25", {
"backgroundColor": {
"red": 0,
"green": 0,
"blue": 0
},
"horizontalAlignment": "CENTER",
"textFormat": {
"foregroundColor": {
"red": 0.9,
"green": 0,
"blue": 0
},
"fontSize": 14,
"bold": False
}
})
worksheet.format(
"A26:A27", {
"backgroundColor": {
"red": 0.2,
"green": 0.2,
"blue": 0.2
},
"horizontalAlignment": "LEFT",
"textFormat": {
"foregroundColor": {
"red": 1,
"green": 0,
"blue": 0
},
"fontSize": 12,
"bold": False
}
})
worksheet.delete_rows(28)
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
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("tutorial")
ws = sheet.worksheet('Picture')
im = Image.open("pic.png").convert('RGB')
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