import string
from pprint import pprint
import gspread
from gspread import Cell, Client, Spreadsheet, Worksheet
from gspread.utils import rowcol_to_a1
from gspread_formatting import *
from openpyxl.utils.cell import get_column_letter
# from gspread.utils import get_addr_int
import requests
import time
SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/1Mgx2KLM2ODqVfSR6Xlv0fZOaqypRipMYxGXiKs6Ysas/"
def show_available_worksheets(sh: Spreadsheet):
worksheets = sh.worksheets()
for ws in worksheets:
print("Worksheet with title", repr(ws.title), "and id", ws.id)
def show_main_ws(sh: Spreadsheet):
main_ws = sh.sheet1
print("Main ws:", main_ws)
def create_ws_fill_and_del(sh: Spreadsheet):
another_worksheet = sh.add_worksheet("another", rows=15, cols=10)
print(another_worksheet)
input("enter to fill ws")
another_worksheet.insert_row(["hello", "world"])
input("enter to fill ws again")
another_worksheet.insert_row(list(range(1, 16)))
input("enter to delete ws")
sh.del_worksheet(another_worksheet)
def insert_some_data(ws: Worksheet):
ws.insert_rows([
list(range(1, 40)),
list(string.ascii_lowercase),
list(string.ascii_uppercase),
list(string.punctuation),
list("hello world and OTUS!"),
])
def insert_some_columns(ws: Worksheet):
ws.insert_cols([
list(range(1, 40)),
list(string.ascii_lowercase),
list(string.ascii_uppercase),
list(string.punctuation),
list("hello world and OTUS!"),
])
def append_rows(ws: Worksheet):
ws.append_rows([
list(reversed(string.ascii_uppercase)),
list(reversed(string.ascii_lowercase)),
list(range(50, 1, -3)),
])
def update_table_by_cells(ws: Worksheet):
ws.update_cell(2, 3, "Hello OTUS!!!")
rows = 3
cols = 4
row = 4
col = 1
range_start = rowcol_to_a1(row, col)
range_end = rowcol_to_a1(row + rows - 1, col + cols - 1)
cells_range = f"{range_start}:{range_end}"
print("update range", cells_range)
values = [[""] * cols] * rows
print("values", values)
print("cells_range", cells_range)
# worksheet.update([['', ''], ['', ''], ['', '']], 'A1:C3')
ws.update(values, cells_range)
def show_all_values_in_ws(ws: Worksheet):
list_of_lists = ws.get_all_values()
print(list_of_lists)
print("===" * 20)
for row in list_of_lists:
print(row)
def show_worksheet(ws: Worksheet):
list_of_dicts = ws.get_all_records()
pprint(list_of_dicts)
def find_comment_by_author(ws: Worksheet):
cell: Cell = ws.find("Hayden@althea.biz")
print("Found something at row %s and col %s" % (cell.row, cell.col))
row = ws.row_values(cell.row)
print(row)
def column_to_letter(column):
"""
Convert a column number to a corresponding letter.
Example: 1 -> A, 2 -> B, 27 -> AA, etc.
"""
letter = ""
while column > 0:
column, remainder = divmod(column - 1, 26)
letter = chr(65 + remainder) + letter
return letter
def find_comment_by_diches(ws: Worksheet, name: str):
cell: Cell = ws.find(name)
if cell:
print("Found something at row %s and col %s" % (cell.row, cell.col))
addr_from_unmerge = rowcol_to_a1(cell.row, cell.col)
addr_to_unmerge = rowcol_to_a1(cell.row, cell.col + 40)
data_range_unmerge = f"{addr_from_unmerge}:{addr_to_unmerge}"
ws.unmerge_cells(data_range_unmerge)
print("Cell unmerged.")
row = ws.row_values(cell.row)
# print(row)
addr_from = rowcol_to_a1(cell.row, cell.col+3)
addr_to = rowcol_to_a1(cell.row, cell.col + 3 + 36)
NEW_VALUES = [i for i in range(1, 36)]
data_range = f"{addr_from}:{addr_to}"
batches = []
batch = {
"range": data_range,
"values": [NEW_VALUES],
}
batches.append(batch)
ws.batch_update(batches)
else:
print("Cell with name %s not found!" % (name))
def split_by_vertical_cells(ws: Worksheet, name: str):
cell: Cell = ws.find(name)
print("Found something at row %s and col %s" % (cell.row, cell.col))
row = ws.row_values(cell.row)
# print(row)
addr_from = rowcol_to_a1(cell.row + 1, cell.col+3)
addr_to = rowcol_to_a1(cell.row + 5, cell.col + 3 + 36)
startRowIndex = cell.row + 1
endRowIndex = cell.row + 5
startColumnIndex = cell.col+3
endColumnIndex = cell.col + 3 + 36
print("split_by_vertical(ws, ws.id, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex) = ", ws, ws.id, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex)
split_by_vertical(ws, ws.id, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex)
# NEW_VALUES = [i for i in range(1, 36)]
# data_range = f"{addr_from}:{addr_to}"
# batches = []
# batch = {
# "range": data_range,
# "values": [NEW_VALUES],
# }
# batches.append(batch)
# ws.batch_update(batches)
def add_black_solid_border(ws: Worksheet):
# Define the cell format and border style
cell_format = CellFormat(
backgroundColor=Color(1, 0, 0), # Replace with your desired RGB values for background color
)
border_style = Border(
style='SOLID',
color=Color(0, 0, 0), # Replace with your desired RGB values for border color
)
# Define the range of cells you want to format
cell_range = 'G4:AO228' # Replace with your desired range
# Create a list of requests to apply the formatting
requests = [
{
'repeatCell': {
'range': {
'sheetId': ws.id,
'startRowIndex': cell_range.start[0] - 1,
'endRowIndex': cell_range.end[0],
'startColumnIndex': cell_range.start[1] - 1,
'endColumnIndex': cell_range.end[1],
},
'cell': {
'userEnteredFormat': {
'backgroundColor': {
'red': cell_format.backgroundColor.red,
'green': cell_format.backgroundColor.green,
'blue': cell_format.backgroundColor.blue,
},
'borders': {
'top': border_style,
'bottom': border_style,
'left': border_style,
'right': border_style,
},
},
},
'fields': 'userEnteredFormat(backgroundColor,borders)',
},
},
]
# Apply the formatting using the batch_update() method
batch_update(ws, requests)
def find_comment_by_diches_and_color_column(ws: Worksheet, name: str):
cell: Cell = ws.find(name)
if cell:
print("Found something at row %s and col %s" % (cell.row, cell.col))
# start_column = get_column_letter(cell.col +1) #'A'
start_column = cell.col + 4 #'A'
# end_column = get_column_letter(cell.col + 36) #'Z'
end_column = cell.col + 36 #'Z'
step = 2
# create a list of column names every third column
column_names = []
for i in range(start_column, end_column + 1, step):
column_names.append(get_column_letter(i)+':'+get_column_letter(i))
print(' '.join(column_names))
# Call the function twice with different parameters
format_column_color(ws, column_names, '#d9d2e9')
start_column = cell.col + 4 #'A'
# end_column = get_column_letter(cell.col + 36) #'Z'
end_column = cell.col + 36 #'Z'
step = 4
# create a list of column names every third column
column_names = []
for i in range(start_column, end_column + 1, step):
column_names.append(get_column_letter(i)+':'+get_column_letter(i))
print(' '.join(column_names))
format_column_color(ws, column_names, '#fff2cc')
else:
print("Cell with name %s not found!" % (name))
def format_column_color(ws, column_names, hex_value):
red = int(hex_value[1:3], 16) / 255
green = int(hex_value[3:5], 16) / 255
blue = int(hex_value[5:], 16) / 255
rgb_value = (red, green, blue)
pprint(rgb_value)
cell_format = {
"backgroundColor": {
"red": red,
"green": green,
"blue": blue
}
}
ws.format(column_names, cell_format)
# cell_format = CellFormat(
# backgroundColor=Color(1, 0, 0), # Replace with your desired RGB values for background color
# )
# border_style = Border(
# style='SOLID',
# color=Color(0, 0, 0), # Replace with your desired RGB values for border color
# )
# Define the range of cells you want to format
cell_range = column_names # Replace with your desired range
# Define the cell format with a solid border
def find_itogo_by_diches(ws: Worksheet):
cell: Cell = ws.find("ИТОГО:")
print("Found something at row %s and col %s" % (cell.row, cell.col))
row = ws.row_values(cell.row)
# print(row)
set_border(ws, cell)
# set_border(ws, cell, cell.row + 1)
# ws.delete_rows(start_row = cell.row + 2 )
start_index = cell.row + 1
# ws.delete_rows(start_index, end_index=None)
ws.resize(rows = start_index)
def find_menu(ws: Worksheet):
cell: Cell = ws.find("МЕНЮ")
# Check if the cell is found
if cell:
# Do something if the cell is found
print("Cell found at row", cell.row, "and column", cell.col)
print("Found something at row %s and col %s" % (cell.row, cell.col))
# Set the value of the cell to "New Value"
cell.value = "Левое крыло"
# Update the worksheet to save changes
ws.update_cells([cell])
else:
# Do something if the cell is not found
print("Cell not found")
# start_column = cell.col
# end_column = cell.col
# step = 1
# column_names = []
# for i in range(start_column, end_column + 1, step):
# column_names.append(get_column_letter(i)+':'+get_column_letter(i))
# print('find_menu: '.join(column_names))
# format_column_color(ws, column_names, '#d9d2e9')
def set_border(ws, cell):
addr_from = rowcol_to_a1(cell.row, cell.col -35)
addr_to = rowcol_to_a1(cell.row + 1, cell.col -1)
NEW_VALUES = [i for i in range(1, 36)]
data_range = f"{addr_from}:{addr_to}"
batches = []
batch = {
"range": data_range,
"values": [NEW_VALUES],
}
batches.append(batch)
ws.batch_update(batches)
addr_from = rowcol_to_a1(4, cell.col -35)
data_range = f"{addr_from}:{addr_to}"
cell_format = {
"borders": {
"top": {
"style": "SOLID",
"width": 1,
"color": {
"red": 0,
"green": 0,
"blue": 0
}
},
"bottom": {
"style": "SOLID",
"width": 1,
"color": {
"red": 0,
"green": 0,
"blue": 0
}
},
"left": {
"style": "SOLID",
"width": 1,
"color": {
"red": 0,
"green": 0,
"blue": 0
}
},
"right": {
"style": "SOLID",
"width": 1,
"color": {
"red": 0,
"green": 0,
"blue": 0
}
}
}
}
ws.format(data_range, cell_format)
def do_batch_update(ws: Worksheet):
batches = []
for i in range(1, 20, 1):
items_count = i + 1
addr_from = rowcol_to_a1(i, 1)
addr_to = rowcol_to_a1(i, items_count)
data_range = f"{addr_from}:{addr_to}"
print("add range", data_range)
batch = {
"range": data_range,
"values": [[i] * items_count],
}
batches.append(batch)
ws.batch_update(batches)
def apply_formatting(ws: Worksheet):
cell_format = {
"horizontalAlignment": "CENTER",
"backgroundColor": {
"red": 0.5,
"green": 1.0,
"blue": 0.3,
},
"textFormat": {
"foregroundColor": {
"red": 0.2,
"green": 0.7,
"blue": 1,
},
"fontSize": 12,
"bold": True,
},
}
ws.format("B11:C13", cell_format)
def insert_column(sh: Spreadsheet, sheet_id: int):
# sa = gspread.service_account(filename="service_account.json")
# sh = sa.open("NAME")
# wks = sh.worksheet("Class Data") # In this script, this line is not used.
# sheet_id = '1571557445'
# sh.insert_cols(values, col=1, value_input_option='RAW', inherit_from_before=False)
data = {
"requests": [
{
"insertDimension": {
"range": {
"sheetId": sheet_id, # <--- Please set the sheet ID of "Class Data" sheet.
"dimension": "COLUMNS",
"startIndex": 15,
"endIndex": 30
},
"inheritFromBefore": True
}
},
],
}
sh.batch_update(data)
def insert_empty_column(ws: Worksheet):
ws.insert_cols([None] * 15, col=15, value_input_option='RAW', inherit_from_before=False)
def add_column_to_worksheets(sh: Spreadsheet):
worksheets = sh.worksheets()
sheetsName = ('ПН', 'ВТ', 'СР', 'ЧТ', 'ПТ')
for ws in worksheets:
if ws.title in sheetsName:
print("Worksheet with title", repr(ws.title), "and id", ws.id)
#insert_column(sh, ws.id)
insert_empty_column(ws)
def add_number_to_worksheets(sh: Spreadsheet):
worksheets = sh.worksheets()
# sheetsName = ('ПН')
sheetsName = ('ПН', 'ВТ', 'СР', 'ЧТ', 'ПТ')
for ws in worksheets:
if ws.title in sheetsName:
# clear_filter(sh, ws.id)
add_number(ws)
print("Worksheet with title", repr(ws.title), "and id", ws.id)
def clear_filter(sh: Spreadsheet, sheet_id: int):
data = {
"requests": [
{
"clearBasicFilter": {
"sheetId": sheet_id }
},
],
}
sh.batch_update(data)
def add_number(ws: Worksheet):
names = ('Салаты', 'Салаты ПРЕМИУМ', 'Холодные закуски', 'Первые блюда', 'Вторые блюда', 'Гарниры', 'Выпечка', 'Разливные напитки, вода, соки', 'Снэк-бар', 'ПРАЗДНИЧНЫЕ БЛЮДА', 'Дополнительно', 'Комплексное меню')
num_second = 10
print("Sleep %s seconds" % (num_second))
time.sleep(num_second)
# find_comment_by_diches(ws, "Цена")
for name in names:
find_comment_by_diches(ws, name)
# add_black_solid_border(ws)
find_comment_by_diches_and_color_column(ws, 'Салаты')
find_itogo_by_diches(ws)
# find_menu(ws)
# split_by_vertical_cells(ws, 'Комплексное меню')
def split_by_vertical(sh: Spreadsheet, sheet_id: int, startRowIndex: int,endRowIndex: int,startColumnIndex: int,endColumnIndex: int):
body = {
"requests": [
{
"mergeCells": {
"mergeType": "MERGE_COLUMNS",
"range": { # In this sample script, all cells of "A1:C3" of "Sheet1" are merged.
"sheetId": sheet_id,
"startRowIndex": startRowIndex,
"endRowIndex": endRowIndex,
"startColumnIndex": startColumnIndex,
"endColumnIndex": endColumnIndex
}
}
}
]
}
res = sh.batch_update(body)
pprint(res)
def main():
gc: Client = gspread.service_account("./service_account.json")
sh: Spreadsheet = gc.open_by_url(SPREADSHEET_URL)
# show_main_ws(sh)
# show_available_worksheets(sh)
# ws = sh.sheet1
# ws.insert_cols(15,1)
# print(sh)
# 1)
add_column_to_worksheets(sh)
# add_number_to_worksheets(sh)
# show_main_ws(sh)
# create_ws_fill_and_del(sh)
# ws = sh.sheet1
# insert_some_data(ws)
# append_rows(ws)
# update_table_by_cells(ws)
# show_all_values_in_ws(ws)
# create_and_fill_comments_ws(sh)
# dishes_ws = sh.worksheet("ПН")
# add_number(dishes_ws)
# 2)
add_number_to_worksheets(sh)
# find_comment_by_diches(dishes_ws)
# show_worksheet(comments_ws)
# find_comment_by_author(comments_ws)
# do_batch_update(ws)
# apply_formatting(ws)
if __name__ == '__main__':
main()
