def _add_or_update_row_data(row: Row, schema: RowSchema):
updated_row = row.copy(deep=False)
updated_row.cells = [
cell for cell in row.cells if cell.value is not None
or cell.formula is not None or cell.object_value is not None
]
result = schema.dump(updated_row.unstructured)
return result
def create_sheet_with_rows(smartsheet, target_sheet, rows_data) -> Sheet:
result = smartsheet.sheets.create(target_sheet)
new_sheet = cast(Sheet, result.obj)
rows = [
Row(to_top=True, cells=new_sheet.make_cells(row_data))
for row_data in rows_data
]
smartsheet.sheets.add_rows(result.obj.id, rows)
return new_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)
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 create_index_sheet_if_not_exists(smartsheet: Smartsheet,
delete_existing: bool = False) -> None:
for sheet in smartsheet.sheets.list():
if sheet.name == INDEX_SHEET.name:
if delete_existing:
smartsheet.sheets.delete(id=sheet.id)
else:
return
result = smartsheet.sheets.create(INDEX_SHEET)
rows = [
Row(to_bottom=True, cells=result.obj.make_cells(row_data))
for row_data in ROWS_DATA
]
smartsheet.sheets.add_rows(result.obj.id, rows)
async def test_sheet_add_row_async(
self,
async_smartsheet: AsyncSmartsheet,
sheet_to_update: Sheet,
additional_row_data: Dict[str, Any],
) -> None:
sheet = await async_smartsheet.sheets.get(name=sheet_to_update.name)
new_row = Row(cells=sheet.make_cells(additional_row_data))
result = await async_smartsheet.sheets.add_row(sheet.id, new_row)
assert result.message == "SUCCESS"
updated_sheet = await async_smartsheet.sheets.get(id=sheet.id)
assert len(updated_sheet.rows) == 4
assert (updated_sheet.rows[-1].get_cell("Email address").value ==
"*****@*****.**")
async def test_sheet_add_rows_async(
self,
async_smartsheet: AsyncSmartsheet,
sheet_to_update: Sheet,
additional_rows_data: List[Dict[str, Any]],
) -> None:
sheet = await async_smartsheet.sheets.get(name=sheet_to_update.name)
new_rows = [
Row(to_bottom=True, cells=sheet.make_cells(row_data))
for row_data in additional_rows_data
]
result = await async_smartsheet.sheets.add_rows(sheet.id, new_rows)
assert result.message == "SUCCESS"
updated_sheet = await async_smartsheet.sheets.get(id=sheet.id)
assert len(updated_sheet.rows) == 5
assert (updated_sheet.rows[-1].get_cell("Email address").value ==
"*****@*****.**")
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}]
)
pprint(sheet.__dict__)
# or printing the sheet object as a dictionary which will be used in REST API
pprint(sheet.dump())
# getting 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__)
# adding rows (cells created using different ways):
sheet.add_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),