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 test_dataframe(self, mocked_sheet: Sheet) -> None:
df = mocked_sheet.as_dataframe()
assert len(df) == 3
assert df.loc[0]["Full Name"] == "Bob Lee"
assert df.loc[1]["Email address"] == "*****@*****.**"
assert df.loc[2]["Company"] == "ACME"
assert set(df.loc[2]["Maintains"]) == {"napalm", "netmiko", "nornir"}
def _sort_rows_data(sheet: Sheet,
order: List[Dict[str, Any]]) -> Dict[str, Any]:
# TODO: add validation schema for sorting order
normalized_order = []
for item in order:
normalized_item = {}
if "column_id" in item:
normalized_item["columnId"] = item["column_id"]
elif "column_title" in item:
column_title = item["column_title"]
column = sheet.get_column(column_title)
normalized_item["columnId"] = column.id
else:
raise ValueError(
"Sorting key must have either column_id or column_title")
descending = item.get("descending", False)
if descending:
normalized_item["direction"] = "DESCENDING"
else:
normalized_item["direction"] = "ASCENDING"
normalized_order.append(normalized_item)
data = {"sortCriteria": normalized_order}
return data
def sort_rows(self, sheet: Sheet, order: List[Dict[str, Any]]) -> "Sheet":
"""Sorts rows in the sheet with the specified order.
Args:
sheet: Sheet object where the rows should be sorted
order: List of dictionaries containing column_title or column_id and
(optional) descending bool (default is ascending). Example:
[
{"column_title": "Birth date", "descending": True},
{"column_title": "Full Name"}
]
Returns:
Sheet object
"""
data = self._sort_rows_data(sheet, order)
endpoint = self._sort_rows_endpoint.format(sheet=sheet)
response = self.smartsheet._post(endpoint, data, result_obj=False)
updated_sheet = Sheet.load(cast(Dict[str, Any], response))
return updated_sheet
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)
"unique": False
},
{
"columns": ("Company", "Full Name"),
"unique": True
},
{
"columns": ("Email address", ),
"unique": True
},
]
INDEX_SHEET_NAME = "[TEST] Index Sheet"
INDEX_SHEET = Sheet(
name=INDEX_SHEET_NAME,
columns=[
Column(primary=True, title="Full Name", type=ColumnType.TEXT_NUMBER),
Column(title="Email address", type=ColumnType.TEXT_NUMBER),
Column(title="Company", type=ColumnType.TEXT_NUMBER),
],
)
ROWS_DATA = [
{
"Full Name": "Bob Lee",
"Email address": "*****@*****.**",
"Company": "ACME"
},
{
"Full Name": "Alice Smith",
"Email address": "*****@*****.**",
"Company": "Globex",
},
async def main() -> None:
async with AsyncSmartsheet(TOKEN) as smartsheet:
# SHEETS
# retrieve a list of sheets (limited set of attributes)
sheets = await smartsheet.sheets.list()
pprint(sheets)
# delete the test sheet if already exists
for sheet in sheets:
if sheet.name == SHEET_NAME:
await 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 = await 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 = await 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)),
],
),
]
await smartsheet.sheets.add_rows(sheet.id, new_rows)
# sort rows by column "Full Name" descending / returns updated sheet
sheet = await 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
await smartsheet.sheets.update_rows(sheet.id, rows_to_update)
# or a single row
# await smartsheet.sheets.update_row(sheet.id, rows_to_update[0])
# get an updated sheet
sheet = await smartsheet.sheets.get(id=sheet.id)
print("\nSheet after updating rows:")
pprint(sheet.as_list())
# delete a row
await smartsheet.sheets.delete_row(sheet.id, row_id_to_delete)
# get an updated sheet
sheet = await smartsheet.sheets.get(id=sheet.id)
print("\nSheet after deleting rows:")
pprint(sheet.as_list())
# delete a sheet by name
await smartsheet.sheets.delete(SHEET_NAME)
sheets = await smartsheet.sheets.list()
pprint(sheets)
# REPORTS
# retrieve a list of reports (limited set of attributes)
reports = await smartsheet.reports.list()
pprint(reports)
# get the report by the name and build an index
report = await smartsheet.reports.get("[TEST] Read-only Report")
report.build_index([{"columns": ("Full Name", ), "unique": True}])
# print the report object attributes
pprint(report.__dict__)
# or print the report object attributes used by the Smartsheet API (camelCase)
pprint(report.dump())
# or 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 tl_ss(self, sheet_name, df, key):
"""
process: (back end of ss is quirky, so these steps are necessary)
1. delete sheet name if exists
2. create new sheet skeleton, specified by list of columns
3. iteratively add new rows using dataframe data
params:
sheet_name: smartsheet name for column
df: pandas dataframe which we are placing into smartsheets
key: establish key column
returns:
writes dataframe to smartsheet under specified sheet name if completed
"""
# isolate df columns
cols = list(df.dtypes.index)
skeleton = [Column(title=x, type=ColumnType.TEXT_NUMBER) if x != key else Column(primary=True, title=x, type=ColumnType.TEXT_NUMBER) for x in cols]
# get sheets associated with token
sheets = self.smart.sheets.list()
# 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 skeleton
new_sheet_skeleton = Sheet(name=sheet_name, columns=skeleton)
# add the blank sheet via API
result = smartsheet.sheets.create(new_sheet_skeleton)
sheet = result.obj
print(f"ID of the created sheet is {sheet.id!r}")
# isolate sheet
sheet = self.smart.sheets.get(sheet_name)
# create new row list
new_rows = []
for i in range(0,len(df)):
new_rows.append(Row(to_top=True,
cells=sheet.make_cells({x: df.loc[i,x] for x in cols})))
# write to ss
smartsheet.sheets.add_rows(sheet.id, new_rows)
# sort
sheet = smartsheet.sheets.sort_rows(
sheet, [{"column_title": key, "descending": False}]
)
def placeholder_sheet() -> Sheet:
return Sheet(name="[TEST] Placeholder", columns=columns_gen())
def mocked_sheet(pytestconfig) -> Sheet:
path = Path(pytestconfig.rootdir) / "tests/sandbox/data/mocked_sheet.json"
with open(path) as f:
data = json.load(f)
sheet = Sheet.load(data)
return sheet
# getting a simplified view of sheets
sheets = smartsheet.sheets.list()
pprint(sheets)
sheet_name = "My New Sheet"
# Delete the test sheet if already exists
for sheet in sheets:
if sheet.name == sheet_name:
smartsheet.sheets.delete(sheet_name)
# creating new Sheet
new_sheet = Sheet(
name=sheet_name,
columns=[
Column(primary=True, title="Full Name", type="TEXT_NUMBER"),
Column(title="Number of read books", type="TEXT_NUMBER"),
Column(title="Birth date", type="DATE"),
],
)
# print the sheet object as a dictionary which will be used in REST API
pprint(new_sheet.dump())
# adding the sheet via API
smartsheet.sheets.create(new_sheet)
# getting a simplified view of sheets
sheets = smartsheet.sheets.list()
pprint(sheets)
# getting the sheet by name