def main() -> None:
with Smartsheet(TOKEN) as smartsheet:
create_index_sheet_if_not_exists(smartsheet, delete_existing=False)
sheet = smartsheet.sheets.get(INDEX_SHEET_NAME)
sheet.build_index(INDEXES)
pprint(sheet.indexes)
# >
# {('Company',): {'index': {('ACME',): [Row(id=8113413857011588, num=1),
# Row(id=5298664089905028, num=3)],
# ('Globex',): [Row(id=795064462534532, num=2)]},
# 'unique': False},
# ('Company', 'Full Name'): {
# 'index': {('ACME', 'Bob Lee'): Row(id=8113413857011588, num=1),
# ('ACME', 'Charlie Brown'): Row(id=5298664089905028, num=3),
# ('Globex', 'Alice Smith'): Row(id=795064462534532, num=2)},
# 'unique': True},
# ('Email address',): {
# 'index': {('*****@*****.**',): Row(id=795064462534532, num=2),
# ('*****@*****.**',): Row(id=8113413857011588, num=1),
# ('*****@*****.**',): Row(id=5298664089905028, num=3)},
# 'unique': True}}
pprint(sheet.as_list())
# >
# [{'Company': 'ACME',
# 'Email address': '*****@*****.**',
# 'Full Name': 'Bob Lee'},
# {'Company': 'Globex',
# 'Email address': '*****@*****.**',
# 'Full Name': 'Alice Smith'},
# {'Company': 'ACME',
# 'Email address': '*****@*****.**',
# 'Full Name': 'Charlie Brown'}]
print("\nRow where email address is '*****@*****.**':")
pprint(
sheet.get_row(filter={
"Email address": "*****@*****.**"
}).as_dict())
# >
# {'Company': 'ACME',
# 'Email address': '*****@*****.**',
# 'Full Name': 'Charlie Brown'}
print(
"\nRow where full name is 'Bob Lee' and the company name is 'ACME':"
)
pprint(
sheet.get_row(filter={
"Full Name": "Bob Lee",
"Company": "ACME"
}).as_dict())
# >
# {'Company': 'ACME', 'Email address': '*****@*****.**', 'Full Name': 'Bob Lee'}
print("\nRows where the company name is 'ACME':")
pprint([
row.as_dict() for row in sheet.get_rows(filter={"Company": "ACME"})
])
def remove_all_rw_objects(custom_vcr):
with custom_vcr.use_cassette("remove_all_rw_objects.yaml"):
with Smartsheet(SMARTSHEET_TOKEN) as smartsheet:
for sheet in smartsheet.sheets.list():
if sheet.name.startswith("[TEST]") and not any(
pattern in sheet.name for pattern in ("[TEST] Report",)
):
smartsheet.sheets.delete(id=sheet.id)
def create_session_objects(vcr):
with vcr.use_cassette("create_session_objects.yaml"):
with Smartsheet(SMARTSHEET_TOKEN) as smartsheet:
read_only_sheet = Sheet(name="[TEST] Read-only Sheet",
columns=columns_gen())
result = smartsheet.sheets.create(read_only_sheet)
read_only_sheet = result.obj
rows = [
Row(to_top=True, cells=read_only_sheet.make_cells(row_data))
for row_data in rows_data_gen()
]
smartsheet.sheets.add_rows(read_only_sheet.id, rows)
def main() -> None:
with Smartsheet(TOKEN) as smartsheet:
# retrieve a list of reports (limited set of attributes)
reports = smartsheet.reports.list()
pprint(reports)
# get the report by the name and build an index
report = smartsheet.reports.get("[TEST] Read-only Report")
report.build_index([{"columns": ("Full Name", ), "unique": True}])
# print a list of dictionaries containing column titles and values for each row
pprint(report.as_list())
# print built indexes
pprint(report.indexes)
# use the index to retrieve a row
print("\nRow where the full name is 'David Ward':")
pprint(report.get_row(filter={"Full Name": "David Ward"}).as_dict())
def get_course_choice(day_of_play):
TOKEN = "0b2269ovtwyvi1bmbqm15c2kg9"
smartsheet = Smartsheet(TOKEN)
# id=8164839852926852 'Tee Times'
sheet = smartsheet.sheets.get(id=8164839852926852)
for row in sheet.rows:
day = row.get_cell("Day").value
date_of_play = row.get_cell("Date").value
tee_times_data = row.get_cell("Course / Tee Times").value
print(f"tee_times_data = {tee_times_data}")
if 'CWV' in tee_times_data:
print('CWV to be played')
course_choice = 'C'
else:
print('TPC to be played')
course_choice = 'T'
return (course_choice)
def _fetch_exclusion_list(self) -> None:
api_key = self.config["Smartsheet"]["api_key"]
sheet_name = self.config["Smartsheet"]["sheet_name"]
column_name = self.config["Smartsheet"]["column_name"]
try:
smartsheet = Smartsheet(api_key)
sheet = smartsheet.sheets.get(sheet_name)
except SmartsheetError:
raise ValueError("Could not download smartsheet")
self.exclusion_list = list(
{
row[column_name]
for row in sheet.as_list()
if row[column_name] is not None
}
)
def create_sheets_for_reports(vcr):
with vcr.use_cassette("setup_sheets_for_reports.yaml"):
with Smartsheet(SMARTSHEET_TOKEN) as smartsheet:
report_sheet1 = Sheet(name="[TEST] Report Sheet 1", columns=columns_gen())
result = smartsheet.sheets.create(report_sheet1)
report_sheet1 = result.obj
rows = [
Row(to_top=True, cells=report_sheet1.make_cells(row_data))
for row_data in rows_data_gen()
]
smartsheet.sheets.add_rows(report_sheet1.id, rows)
report_sheet2 = Sheet(name="[TEST] Report Sheet 2", columns=columns_gen())
result = smartsheet.sheets.create(report_sheet2)
report_sheet2 = result.obj
row = Row(
to_top=True, cells=report_sheet2.make_cells(additional_row_data_gen())
)
smartsheet.sheets.add_row(report_sheet2.id, row)
def update_tee_times(display_day):
"""[Open "Tee Times" smartsheet and ]
Arguments:
display_day {[string]} -- [Day of Week Playing]
"""
TOKEN = "0b2269ovtwyvi1bmbqm15c2kg9"
smartsheet = Smartsheet(TOKEN)
# id=8164839852926852 'Tee Times'
sheet = smartsheet.sheets.get(id=8164839852926852)
for row in sheet.rows:
day = row.get_cell("Day").value
date_of_play = row.get_cell("Date").value
tee_times_data = row.get_cell("Course / Tee Times").value
tee_times = f"{date_of_play}, {tee_times_data}"
# print(f"{display_day} / {day} {tee_times}")
if (day + " Players") == display_day:
# print(f"Returning {tee_times}")
return tee_times
import os
from simple_smartsheet import Smartsheet
from pprint import pprint
TOKEN = os.getenv("SMARTSHEET_API_TOKEN")
smartsheet = Smartsheet(TOKEN)
INDEX_KEYS = [
{
"columns": ("Company Name", ),
"unique": False
},
{
"columns": ("Company Name", "Full Name"),
"unique": True
},
{
"columns": ("Email Address", ),
"unique": True
},
]
sheet = smartsheet.sheets.get("Index Test Sheet", index_keys=INDEX_KEYS)
pprint(sheet.indexes)
# >
# defaultdict(<class 'dict'>,
# {('Company Name',): {('ACME',): [Row(id=525791232583556, num=1),
# Row(id=5029390859954052, num=2)],
# ('Globex',): [Row(id=2777591046268804, num=3)]},
def update_player_status(day_of_play):
TOKEN = "0b2269ovtwyvi1bmbqm15c2kg9"
smartsheet = Smartsheet(TOKEN)
# id=1798700143011716 'Jack Sign-Up'
sheet = smartsheet.sheets.get(id=1798700143011716)
# create list of players for specific date
monday_list = []
tuesday_list = []
wednesday_list = []
thursday_list = []
friday_list = []
road_trip_list = []
# Inefficient, processing list of players every day
for row in sheet.rows:
player = row.get_cell("Player").value
monday_play = row.get_cell("Mon?").value
if monday_play:
monday_list.append(player)
tuesday_play = row.get_cell("Tues?").value
if tuesday_play:
tuesday_list.append(player)
wednesday_play = row.get_cell("Wed?").value
if wednesday_play:
wednesday_list.append(player)
thursday_play = row.get_cell("Thurs?").value
if thursday_play:
thursday_list.append(player)
friday_play = row.get_cell("Fri?").value
if friday_play:
friday_list.append(player)
road_trip_play = row.get_cell("Road Trip?").value
if road_trip_play:
road_trip_list.append(player)
if day_of_play == "M" or day_of_play == "m":
display_day = "Monday Players"
for player in player_list:
if player.signup_name in monday_list:
player.playing = True
if day_of_play == "T" or day_of_play == "t":
display_day = "Tuesday Players"
for player in player_list:
if player.signup_name in tuesday_list:
player.playing = True
elif day_of_play == "W" or day_of_play == "w":
display_day = "Wednesday Players"
for player in player_list:
if player.signup_name in wednesday_list:
player.playing = True
elif day_of_play == "H" or day_of_play == "h":
display_day = "Thursday Players"
for player in player_list:
if player.signup_name in thursday_list:
player.playing = True
elif day_of_play == "F" or day_of_play == "f":
display_day = "Friday Players"
for player in player_list:
if player.signup_name in friday_list:
player.playing = True
elif day_of_play == "R" or day_of_play == "r":
for player in player_list:
if player.signup_name in road_trip_list:
player.playing = True
else:
print("Bad choice of Day")
return display_day
def main() -> None:
with Smartsheet(TOKEN) as smartsheet:
# retrieve a list of sheets (limited set of attributes)
sheets = smartsheet.sheets.list()
pprint(sheets)
# delete the test sheet if already exists
for sheet in sheets:
if sheet.name == SHEET_NAME:
smartsheet.sheets.delete(id=sheet.id)
# create a new Sheet
new_sheet_skeleton = Sheet(
name=SHEET_NAME,
columns=[
Column(primary=True,
title="Full Name",
type=ColumnType.TEXT_NUMBER),
Column(title="Number of read books",
type=ColumnType.TEXT_NUMBER),
Column(title="Birth date", type=ColumnType.DATE),
Column(title="Library member", type=ColumnType.CHECKBOX),
],
)
# print the sheet object attributes used by the Smartsheet API (camelCase)
pprint(new_sheet_skeleton.dump())
# add the sheet via API
result = smartsheet.sheets.create(new_sheet_skeleton)
sheet = result.obj
print(f"ID of the created sheet is {sheet.id!r}")
# retrieve a sheet by name
# this object is exactly the same as result.obj
sheet = smartsheet.sheets.get(SHEET_NAME)
# get columns details by column title (case-sensitive)
full_name_column = sheet.get_column("Full Name")
pprint(full_name_column.__dict__)
num_books_column = sheet.get_column("Number of read books")
pprint(num_books_column.__dict__)
# add rows (cells are created using different ways)
# second way is the easiest
new_rows = [
Row(
to_top=True,
cells=[
Cell(column_id=full_name_column.id, value="Alice Smith"),
Cell(column_id=num_books_column.id, value=5),
],
),
Row(
to_top=True,
cells=sheet.make_cells({
"Full Name": "Bob Lee",
"Number of read books": 2
}),
),
Row(
to_top=True,
cells=[
sheet.make_cell("Full Name", "Charlie Brown"),
sheet.make_cell("Number of read books", 1),
sheet.make_cell("Birth date", date(1990, 1, 1)),
],
),
]
smartsheet.sheets.add_rows(sheet.id, new_rows)
# sort rows by column "Full Name" descending / returns updated sheet
sheet = smartsheet.sheets.sort_rows(sheet, [{
"column_title": "Full Name",
"descending": True
}])
print("\nSheet after adding rows:")
# print a list of dictionaries containing column titles and values for each row
pprint(sheet.as_list())
# get a specific cell and updating it:
row_id_to_delete = None
rows_to_update = []
for row in sheet.rows:
full_name = row.get_cell("Full Name").value
num_books = row.get_cell("Number of read books").value
print(f"{full_name} has read {num_books} books")
if full_name.startswith("Charlie"):
updated_row = Row(
id=row.id,
cells=[sheet.make_cell("Number of read books", 15)])
rows_to_update.append(updated_row)
elif full_name.startswith("Bob"):
row_id_to_delete = row.id # used later
# update rows
smartsheet.sheets.update_rows(sheet.id, rows_to_update)
# or a single row
# smartsheet.sheets.update_row(sheet.id, rows_to_update[0])
# get an updated sheet
sheet = smartsheet.sheets.get(id=sheet.id)
print("\nSheet after updating rows:")
pprint(sheet.as_list())
# delete a row
smartsheet.sheets.delete_row(sheet.id, row_id_to_delete)
# get an updated sheet
sheet = smartsheet.sheets.get(id=sheet.id)
print("\nSheet after deleting rows:")
pprint(sheet.as_list())
# delete a sheet by name
smartsheet.sheets.delete(SHEET_NAME)
sheets = smartsheet.sheets.list()
pprint(sheets)
def smartsheet():
with Smartsheet(SMARTSHEET_TOKEN) as smartsheet:
yield smartsheet