class SheetListTest(unittest.TestCase):
permalink_start = 'https://app.smartsheet.com/b/home?lx='
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
def test_sheet_list(self):
sheet_list = self.client.fetchSheetList()
self.assertIsInstance(sheet_list, list)
self.logger.debug("SheetList: %s", sheet_list)
sheet_list_2 = self.client.fetchSheetList(use_cache=True)
self.logger.debug("SheetList from cache: %s", sheet_list)
self.assertListEqual(sheet_list, sheet_list_2,
msg='fetchSheetList(use_cache=True) did not get the same list')
for si in sheet_list:
self.assertIsInstance(si, SheetInfo)
self.assertIsInstance(si.name, basestring)
self.assertEqual(si.client, self.client)
self.assertTrue(si.accessLevel in 'OWNER VIEWER EDITOR EDITOR_SHARE ADMIN'.split())
self.assertTrue(type(si.id) == int or type(si.id) == long)
self.assertTrue(si.permalink.startswith(self.permalink_start))
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestRowAddDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber",
type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist",
type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns,
location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestColumnUpdate-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
# col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
# self.columns = [col_1, col_2, col_3, col_4, col_5]
self.columns = [col_1, col_2, col_3, col_4]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns, location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
class ColumnUpdateTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestColumnUpdate-%s' % time_str
col_1 = Column("Col 1 - TextNumber",
type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist",
type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
# col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
# self.columns = [col_1, col_2, col_3, col_4, col_5]
self.columns = [col_1, col_2, col_3, col_4]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns,
location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_update_column_title(self):
'''Test updating a column's title.'''
self.sheet.columns[0].title = 'New Title'
self.sheet.columns[0].update()
self.assertTrue(self.sheet.columns[0].title == 'New Title')
def test_update_column_index(self):
'''Test changing a column's index.'''
self.sheet.columns[1].index = 3
self.sheet.columns[1].update()
self.assertTrue(self.sheet.columns[3].title == "Col 2 - Date")
self.assertTrue(self.sheet.columns[1].title == "Col 3 - Picklist")
class ColumnUpdateTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestColumnUpdate-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
# col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
# self.columns = [col_1, col_2, col_3, col_4, col_5]
self.columns = [col_1, col_2, col_3, col_4]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns, location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_update_column_title(self):
'''Test updating a column's title.'''
self.sheet.columns[0].title = 'New Title'
self.sheet.columns[0].update()
self.assertTrue(self.sheet.columns[0].title == 'New Title')
def test_update_column_index(self):
'''Test changing a column's index.'''
self.sheet.columns[1].index = 3
self.sheet.columns[1].update()
self.assertTrue(self.sheet.columns[3].title == "Col 2 - Date")
self.assertTrue(self.sheet.columns[1].title == "Col 3 - Picklist")
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestCellAccess-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
col_6 = Column("Col 6 - TextNumber", type=CellTypes.TextNumber)
self.columns = [col_1, col_2, col_3, col_4, col_5, col_6]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns, location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("######### Adding Rows to new Sheet ############")
rw = self.sheet.makeRowWrapper()
r = rw.makeRow()
r[0] = "one"
r[1] = '2014-12-25'
r[5] = "one.five"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
r[2] = "Yes"
r[5] = "two.five"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
r[3] = True
r[5] = "three.five"
rw.addRow(r)
self.sheet.addRows(rw)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'testSheetCreateDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber",
type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist",
type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
# TODO: Add some system or autonumber columns.
self.logger.info("########### Test uses Sheet: %s ############",
self.new_sheet_name)
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'testSheetCreateDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
# TODO: Add some system or autonumber columns.
self.logger.info("########### Test uses Sheet: %s ############",
self.new_sheet_name)
class CellAccessTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestCellAccess-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
col_6 = Column("Col 6 - TextNumber", type=CellTypes.TextNumber)
self.columns = [col_1, col_2, col_3, col_4, col_5, col_6]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns, location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("######### Adding Rows to new Sheet ############")
rw = self.sheet.makeRowWrapper()
r = rw.makeRow()
r[0] = "one"
r[1] = '2014-12-25'
r[5] = "one.five"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
r[2] = "Yes"
r[5] = "two.five"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
r[3] = True
r[5] = "three.five"
rw.addRow(r)
self.sheet.addRows(rw)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_cell_access(self):
'''Test the various approaches to Cell access.'''
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[1].getCellByIndex(0).value == "one")
col_0 = self.sheet.getColumnByIndex(0)
self.assertTrue(col_0 is not None)
self.assertTrue(self.sheet[1].getCellByColumnId(col_0.id).value == "one")
row_1 = self.sheet.getRowByRowNumber(1)
self.assertTrue(row_1 is not None)
self.assertTrue(row_1[0] == "one")
self.assertTrue(row_1.getCellByIndex(5).value == "one.five")
self.assertTrue(self.sheet[3][0] == "three")
self.assertTrue(self.sheet[3].getCellByIndex(0).value == "three")
self.assertTrue(self.sheet[3][5] == "three.five")
self.assertTrue(self.sheet[3].getCellByIndex(5).value == "three.five")
def test_add_hyperlink_to_existing_row(self):
'''Test adding a CellHyperlink to an existing Row.'''
link_target = 'http://www.smartsheet.com/developers/api-documentation'
link = CellHyperlink(url=link_target)
self.sheet.enableCache()
cell = self.sheet[1].getCellByIndex(0)
self.assertTrue(cell.value == "one")
cell = cell.assign("API Docs", hyperlink=link)
self.assertTrue(self.sheet[1][0] == "API Docs")
self.assertTrue(self.sheet[1].getCellByIndex(0).value == "API Docs")
self.assertTrue(self.sheet[1].getCellByIndex(0).hyperlink.url == link_target)
cell.save()
self.assertTrue(self.sheet[1][0] == "API Docs")
self.assertTrue(self.sheet[1].getCellByIndex(0).value == "API Docs")
self.assertTrue(self.sheet[1].getCellByIndex(0).hyperlink.url == link_target)
def test_add_hyperlink_to_new_row(self):
'''Test adding a CellHyperlink to a new Row.'''
link_target = 'http://www.smartsheet.com/blog'
link = CellHyperlink(url=link_target)
r = self.sheet.makeRow()
r.getCellByIndex(0).assign('Smartsheet Blog', hyperlink=link)
self.assertTrue(r[0] == 'Smartsheet Blog')
self.assertTrue(r.getCellByIndex(0).value == 'Smartsheet Blog')
self.assertTrue(r.getCellByIndex(0).hyperlink.url == link_target)
self.sheet.addRow(r, position='toTop')
self.assertTrue(self.sheet[1][0] == 'Smartsheet Blog')
self.assertTrue(self.sheet[1].getCellByIndex(0).value ==
'Smartsheet Blog')
self.assertTrue(self.sheet[1].getCellByIndex(0).hyperlink.url ==
link_target)
def test_changing_multiple_cells_on_a_row_list_sytle(self):
self.sheet[1][0] = "blue"
self.sheet[1][5] = "green"
self.sheet[1].save()
self.assertTrue(self.sheet[1][0] == "blue")
self.assertTrue(self.sheet[1][5] == "green")
def test_changing_multiple_cells_on_a_row_oo_style_cache_enabled(self):
# Have to enable cache to be able to change multiple Cells on a Row.
self.sheet.enableCache()
self.sheet.getRowByRowNumber(1).getCellByIndex(0).assign("blue")
self.sheet.getRowByRowNumber(1).getCellByIndex(5).assign("green")
self.sheet.getRowByRowNumber(1).save()
self.assertTrue(self.sheet[1][0] == "blue")
self.assertTrue(self.sheet[1][5] == "green")
def test_changing_multiple_cells_on_a_row_oo_style_without_cache_enabled(self):
row = self.sheet.getRowByRowNumber(1)
row.getCellByIndex(0).assign("blue")
row.getCellByIndex(5).assign("green")
row.save()
self.assertTrue(self.sheet[1][0] == "blue")
self.assertTrue(self.sheet[1][5] == "green")
def test_changing_multiple_cells_on_a_row_but_oo_no_cache_loses_all_but_last_write(self):
'''
In OO access mode, the Row is refetched on each access, as a result,
changes to multiple Cells on the Row will lose all but the first one.
Unless the Row is fetched and then used without refetching (as in the
test case test_changing_multiple_cells_on_a_row_oo_style_without_cache_enabled).
'''
self.sheet.getRowByRowNumber(1).getCellByIndex(0).assign("blue")
self.sheet.getRowByRowNumber(1).getCellByIndex(5).assign("green")
prior_cache_state = self.sheet.forceCache()
self.sheet.getRowByRowNumber(1).save()
self.sheet.restoreCache(prior_cache_state)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[1][5] == "green")
def notest_save_with_vs_without_strict(self):
'''Test assignments with and without 'strict' setting.'''
raise NotImplementedError
class ColumnAddDeleteTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestColumnAddDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
# col_2 = Column("Col 2 - Date", type=CellTypes.Date)
# col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
# options=['Yes', 'No', 'Maybe'])
# col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
# col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
# self.columns = [col_1, col_2, col_3, col_4, col_5]
self.columns = [col_1]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns, location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_add_column_to_empty_sheet(self):
'''Add Columns to an empty Sheet.'''
# Make sure we can add Rows and set Cells afterwards
col_2 = Column('Col 2 - Date', type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
# col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
# col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.logger.debug('Before: Columns: %r', self.sheet.columns)
self.sheet.insertColumn(col_3)
self.logger.debug('After Append: Columns: %r', self.sheet.columns)
self.sheet.insertColumn(col_2, index=1)
self.logger.debug('After Insert @ idx 1: Columns: %r', self.sheet.columns)
self.assertTrue(self.sheet.getColumnByIndex(0).title == "Col 1 - TextNumber")
self.assertTrue(self.sheet.getColumnByIndex(0).type == CellTypes.TextNumber)
self.assertTrue(self.sheet.getColumnByIndex(1).title == "Col 2 - Date")
self.assertTrue(self.sheet.getColumnByIndex(1).type == CellTypes.Date)
self.assertTrue(self.sheet.getColumnByIndex(2).title == "Col 3 - Picklist")
self.assertTrue(self.sheet.getColumnByIndex(2).type == CellTypes.Picklist)
self.assertTrue(self.sheet.getColumnByIndex(-1).title == "Col 3 - Picklist")
self.assertTrue(self.sheet.getColumnByIndex(-1).type == CellTypes.Picklist)
with self.assertRaises(IndexError):
self.sheet.getColumnByIndex(3)
def test_add_column_with_numeric_title(self):
col = Column(17, type=CellTypes.TextNumber)
self.sheet.insertColumn(col)
self.assertTrue(self.sheet.getColumnByIndex(1).title == "17")
self.assertTrue(self.sheet.getColumnByIndex(1).type == CellTypes.TextNumber)
"""
class RowAddDeleteTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestRowAddDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber",
type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist",
type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns,
location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_add_one_row_to_top_of_sheet(self):
'''Add a single Row to the top of the blank Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
def test_add_row_with_multiple_columns(self):
'''Add a Row that has multiple values in multiple Columns.'''
r = self.sheet.makeRow()
r[0] = "one"
r[1] = "2015-05-05"
self.sheet.addRow(r)
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[1][1] == "2015-05-05")
def test_make_a_row_from_a_list_of_values(self):
'''Make a Row from a list of values.'''
row_1_value_list = [
"one", "2015-05-05", "Yes", True, "*****@*****.**"
]
row_2_value_list = [
"two", "2015-05-06", "No", False, "*****@*****.**"
]
row = self.sheet.makeRow(row_1_value_list)
for i in range(len(row_1_value_list)):
self.assertTrue(row[i] == row_1_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_1_value_list)):
self.assertTrue(self.sheet[1][i] == row_1_value_list[i])
rw = self.sheet.makeRowWrapper(position='toBottom')
row = rw.makeRow(row_2_value_list)
for i in range(len(row_2_value_list)):
self.assertTrue(row[i] == row_2_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_2_value_list)):
self.assertTrue(self.sheet[2][i] == row_2_value_list[i])
def test_make_a_row_from_positional_parameters(self):
'''Make a Row from a list of positional parameters.'''
row_1_value_list = [
"one", "2015-05-05", "Yes", True, "*****@*****.**"
]
row_2_value_list = [
"two", "2015-05-06", "No", False, "*****@*****.**"
]
row = self.sheet.makeRow(*row_1_value_list)
for i in range(len(row_1_value_list)):
self.assertTrue(row[i] == row_1_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_1_value_list)):
self.assertTrue(self.sheet[1][i] == row_1_value_list[i])
rw = self.sheet.makeRowWrapper(position='toBottom')
row = rw.makeRow(*row_2_value_list)
for i in range(len(row_2_value_list)):
self.assertTrue(row[i] == row_2_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_2_value_list)):
self.assertTrue(self.sheet[2][i] == row_2_value_list[i])
def test_make_a_row_from_a_dict_keyed_by_index(self):
'''Make a Row from a dict where the keys are column Indexes.'''
row_1_value_list = [
"one", "2015-05-05", "Yes", True, "*****@*****.**"
]
row_1_items = dict(zip(range(len(row_1_value_list)), row_1_value_list))
row_2_value_list = [
"two", "2015-05-06", "No", False, "*****@*****.**"
]
row_2_items = dict(zip(range(len(row_2_value_list)), row_2_value_list))
row = self.sheet.makeRow(row_1_items)
for key, value in row_1_items.items():
self.assertTrue(row[key] == value)
for i in range(len(row_1_value_list)):
self.assertTrue(row[i] == row_1_value_list[i])
self.sheet.addRow(row)
for key, value in row_1_items.items():
self.assertTrue(self.sheet[1][key] == value)
rw = self.sheet.makeRowWrapper(position='toBottom')
row = rw.makeRow(row_2_items)
for key, value in row_2_items.items():
self.assertTrue(row[key] == value)
for i in range(len(row_2_value_list)):
self.assertTrue(row[i] == row_2_value_list[i])
rw.addRow(row)
self.sheet.addRows(rw)
for key, value in row_2_items.items():
self.assertTrue(self.sheet[2][key] == value)
def test_make_a_row_from_a_dict_keyed_by_title(self):
titles = map(lambda x: x.title, self.sheet.columns)
row_1_value_list = [
"one", "2015-05-05", "Yes", True, "*****@*****.**"
]
row_1_items = dict(zip(titles, row_1_value_list))
row_2_value_list = [
"two", "2015-05-06", "No", False, "*****@*****.**"
]
row_2_items = dict(zip(titles, row_2_value_list))
row = self.sheet.makeRow(row_1_items)
for i in range(len(row_1_value_list)):
self.assertTrue(row[i] == row_1_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_1_value_list)):
self.assertTrue(self.sheet[1][i] == row_1_value_list[i])
def test_add_rows_to_top_of_sheet(self):
'''Add Rows, one at a time, to the top of an initially blank Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "one")
r = self.sheet.makeRow()
r[0] = "three"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "three")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "one")
def test_add_rows_to_bottom_of_sheet(self):
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
r = self.sheet.makeRow()
r[0] = "three"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
def test_add_row_between_rows_on_sheet(self):
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
r = self.sheet.makeRow()
r[0] = "one-two"
self.sheet.addRow(r, siblingId=self.sheet[1].id)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "one-two")
self.assertTrue(self.sheet[3][0] == "two")
def test_add_multiple_rows_to_blank_sheet(self):
'''Add multiple Rows at once to the top of a blank/empty Sheet.'''
# Add two rows at once at the very top of a blank Sheet.
row_1 = self.sheet.makeRow()
row_1[0] = 'one'
row_2 = self.sheet.makeRow()
row_2[0] = 'two'
rw = self.sheet.makeRowWrapper(position='toTop')
rw.addRow(row_1)
rw.addRow(row_2)
self.assertTrue(len(rw.rows) == 2)
res = self.sheet.addRows(rw)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == 'one')
self.assertTrue(self.sheet[2][0] == 'two')
def test_add_multiple_rows_to_top_of_one_row_sheet(self):
'''Add multiple Rows at once to the top of a one-row Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
r = self.sheet.makeRow()
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
rw = self.sheet.makeRowWrapper(position='toTop')
r = rw.makeRow()
r[0] = "New Top Row"
rw.addRow(r)
r = rw.makeRow()
r[0] = "Second Row From Top"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "New Top Row")
self.assertTrue(self.sheet[2][0] == "Second Row From Top")
self.assertTrue(self.sheet[3][0] == "one")
def test_add_multiple_rows_below_a_one_row_sheet__without_position_equal_toBottom(
self):
'''
Add multiple Rows at once as a sibling to the Row in a one-row Sheet.
'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[1].id)
r = rw.makeRow()
r[0] = "Second Row From Top"
rw.addRow(r)
r = rw.makeRow()
r[0] = "Third Row From Top"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "Second Row From Top")
self.assertTrue(self.sheet[3][0] == "Third Row From Top")
def test_add_multiple_rows_between_rows(self):
'''
Add multiple Rows at once between the Rows in a Sheet.
'''
rw = self.sheet.makeRowWrapper(position='toBottom')
r = rw.makeRow()
r[0] = "one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[1].id)
r = rw.makeRow()
r[0] = "one-one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "one-two"
rw.addRow(r)
res = self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 5)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "one-one")
self.assertTrue(self.sheet[3][0] == "one-two")
self.assertTrue(self.sheet[4][0] == "two")
self.assertTrue(self.sheet[5][0] == "three")
def test_add_multiple_rows_just_before_the_last_row(self):
rw = self.sheet.makeRowWrapper(position='toBottom')
r = rw.makeRow()
r[0] = "one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[2].id)
r = rw.makeRow()
r[0] = "two-one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two-two"
rw.addRow(r)
res = self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 5)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "two-one")
self.assertTrue(self.sheet[4][0] == "two-two")
self.assertTrue(self.sheet[5][0] == "three")
def test_delete_only_row_in_sheet(self):
'''Test deleting the only Row in a Sheet.'''
r = self.sheet.makeRow()
r[0] = "only"
self.sheet.addRow(r)
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "only")
res = self.sheet[1].delete()
self.assertTrue(len(self.sheet) == 0)
def test_delete_rows_in_sheet_with_multiple_rows(self):
'''Delete Rows from a Sheet with multiple Rows.'''
rw = self.sheet.makeRowWrapper(position='toTop')
r = rw.makeRow()
r[0] = "one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
rw.addRow(r)
r = rw.makeRow()
r[0] = "four"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 4)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
self.assertTrue(self.sheet[4][0] == "four")
self.sheet[1].delete()
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "three")
self.assertTrue(self.sheet[3][0] == "four")
self.sheet[3].delete()
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "three")
self.sheet[-2].delete()
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "three")
self.sheet[-1].delete()
self.assertTrue(len(self.sheet) == 0)
def test_add_row_from_list_of_items(self):
rw = self.sheet.makeRowWrapper(position='toBottom')
row = rw.makeRow('one', '2015-05-05', 'Maybe')
rw.addRow(row)
rw.addRow(rw.makeRow(['two', '2015-05-06', 'Yes']))
def value_generator():
vals = ['three', '2015-05-07', 'No']
for item in vals:
yield item
rw.addRow(rw.makeRow(value_generator()))
rw.addRow(rw.makeRow('four', None, 'Maybe'))
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 4)
self.assertTrue(self.sheet[1][0] == 'one')
self.assertTrue(self.sheet[1][1] == '2015-05-05')
self.assertTrue(self.sheet[1][2] == 'Maybe')
self.assertTrue(self.sheet[2][0] == 'two')
self.assertTrue(self.sheet[2][1] == '2015-05-06')
self.assertTrue(self.sheet[2][2] == 'Yes')
self.assertTrue(self.sheet[3][0] == 'three')
self.assertTrue(self.sheet[3][1] == '2015-05-07')
self.assertTrue(self.sheet[3][2] == 'No')
self.assertTrue(self.sheet[4][0] == 'four')
self.assertTrue(self.sheet[4][1] == None)
self.assertTrue(self.sheet[4][2] == 'Maybe')
class CreateSheetTest(unittest.TestCase):
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'testSheetCreateDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber",
type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist",
type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
# TODO: Add some system or autonumber columns.
self.logger.info("########### Test uses Sheet: %s ############",
self.new_sheet_name)
def test_create_and_delete_sheet(self):
'''
Test the creation of a new Sheet and the deletion of it.
'''
si = self.client.createSheet(self.new_sheet_name,
self.columns,
location='')
self.assertIsNotNone(si)
self.assertIsInstance(si, SheetInfo)
self.assertEqual(si.name, self.new_sheet_name)
self.assertIsNotNone(si.permalink)
# Make sure it shows up on the SheetList by name.
sheet_list = self.client.fetchSheetInfoByName(self.new_sheet_name)
self.assertIsNotNone(sheet_list)
self.assertEqual(1, len(sheet_list))
self.assertEqual(si, sheet_list[0])
# Fetch the Sheet and make sure it corresponds to what we meant to create.
sheet = si.loadSheet()
self.assertIsNotNone(sheet)
self.assertEqual(sheet.name, self.new_sheet_name)
self.assertTrue(len(sheet.columns) == len(self.columns))
self.assertTrue(len(sheet.rows) == 0)
self.assertEqual(sheet.permalink, si.permalink)
for sheet_col, src_col in zip(sheet.columns, self.columns):
self.assertEqual(sheet_col.title, src_col.title)
# Delete the sheet and make sure it is gone.
sheet_id = sheet.id
sheet_permalink = sheet.permalink
sheet.delete()
with self.assertRaises(SmartsheetClientError):
self.client.fetchSheetById(sheet_id)
sheet_list = self.client.fetchSheetInfoByName(self.new_sheet_name)
self.assertTrue(len(sheet_list) == 0)
sheet_info = self.client.fetchSheetInfoByPermalink(sheet_permalink)
self.assertTrue(sheet_info is None)
class ColumnAddDeleteTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestColumnAddDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber",
type=CellTypes.TextNumber,
primary=True)
# col_2 = Column("Col 2 - Date", type=CellTypes.Date)
# col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
# options=['Yes', 'No', 'Maybe'])
# col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
# col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
# self.columns = [col_1, col_2, col_3, col_4, col_5]
self.columns = [col_1]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns,
location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_add_column_to_empty_sheet(self):
'''Add Columns to an empty Sheet.'''
# Make sure we can add Rows and set Cells afterwards
col_2 = Column('Col 2 - Date', type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist",
type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
# col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
# col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.logger.debug('Before: Columns: %r', self.sheet.columns)
self.sheet.insertColumn(col_3)
self.logger.debug('After Append: Columns: %r', self.sheet.columns)
self.sheet.insertColumn(col_2, index=1)
self.logger.debug('After Insert @ idx 1: Columns: %r',
self.sheet.columns)
self.assertTrue(
self.sheet.getColumnByIndex(0).title == "Col 1 - TextNumber")
self.assertTrue(
self.sheet.getColumnByIndex(0).type == CellTypes.TextNumber)
self.assertTrue(self.sheet.getColumnByIndex(1).title == "Col 2 - Date")
self.assertTrue(self.sheet.getColumnByIndex(1).type == CellTypes.Date)
self.assertTrue(
self.sheet.getColumnByIndex(2).title == "Col 3 - Picklist")
self.assertTrue(
self.sheet.getColumnByIndex(2).type == CellTypes.Picklist)
self.assertTrue(
self.sheet.getColumnByIndex(-1).title == "Col 3 - Picklist")
self.assertTrue(
self.sheet.getColumnByIndex(-1).type == CellTypes.Picklist)
with self.assertRaises(IndexError):
self.sheet.getColumnByIndex(3)
def test_add_column_with_numeric_title(self):
col = Column(17, type=CellTypes.TextNumber)
self.sheet.insertColumn(col)
self.assertTrue(self.sheet.getColumnByIndex(1).title == "17")
self.assertTrue(
self.sheet.getColumnByIndex(1).type == CellTypes.TextNumber)
"""
class CreateSheetTest(unittest.TestCase):
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'testSheetCreateDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
# TODO: Add some system or autonumber columns.
self.logger.info("########### Test uses Sheet: %s ############",
self.new_sheet_name)
def test_create_and_delete_sheet(self):
'''
Test the creation of a new Sheet and the deletion of it.
'''
si = self.client.createSheet(self.new_sheet_name, self.columns,
location='')
self.assertIsNotNone(si)
self.assertIsInstance(si, SheetInfo)
self.assertEqual(si.name, self.new_sheet_name)
self.assertIsNotNone(si.permalink)
# Make sure it shows up on the SheetList by name.
sheet_list = self.client.fetchSheetInfoByName(self.new_sheet_name)
self.assertIsNotNone(sheet_list)
self.assertEqual(1, len(sheet_list))
self.assertEqual(si, sheet_list[0])
# Fetch the Sheet and make sure it corresponds to what we meant to create.
sheet = si.loadSheet()
self.assertIsNotNone(sheet)
self.assertEqual(sheet.name, self.new_sheet_name)
self.assertTrue(len(sheet.columns) == len(self.columns))
self.assertTrue(len(sheet.rows) == 0)
self.assertEqual(sheet.permalink, si.permalink)
for sheet_col, src_col in zip(sheet.columns, self.columns):
self.assertEqual(sheet_col.title, src_col.title)
# Delete the sheet and make sure it is gone.
sheet_id = sheet.id
sheet_permalink = sheet.permalink
sheet.delete()
with self.assertRaises(SmartsheetClientError):
self.client.fetchSheetById(sheet_id)
sheet_list = self.client.fetchSheetInfoByName(self.new_sheet_name)
self.assertTrue(len(sheet_list) == 0)
sheet_info = self.client.fetchSheetInfoByPermalink(sheet_permalink)
self.assertTrue(sheet_info is None)
class SmartsheetService(object):
__logger = logging.getLogger(__name__)
def __init__(self, token):
self.__smartsheetClient = SmartsheetClient(token, logger=logging.getLogger(SmartsheetClient.__name__))
self.__smartsheetClient.connect()
def updateCell(self, sheet, rowNumber, columnIndex=None, columnTitle=None, value=None):
if columnIndex is not None and columnTitle is not None:
raise SmartsheetBulkEditError('one but not both "columnIndex" and "columnTitle" must be specified')
elif columnTitle is not None:
columnIndex = sheet.getColumnsInfo().getColumnByTitle(columnTitle).index
elif columnIndex is None:
raise SmartsheetBulkEditError('either "columnIndex" or "columnTitle" must be specified')
row = sheet[rowNumber]
row[columnIndex] = value
row.getCellByIndex(columnIndex).save(propagate=False)
def updateCellInAllSheets(self, rowNumber, workspace=None, columnIndex=None, columnTitle=None, value=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.updateCell(sheet, rowNumber, columnIndex=columnIndex, columnTitle=columnTitle, value=value)
def addColumn(self, sheet, title, index=None, type=None, options=None, symbol=None, isPrimary=None, systemColumnType=None, autoNumberFormat=None, width=None):
params = {}
if sheet is not None:
params["sheet"] = sheet
if index is not None:
params["index"] = index
if type is not None:
params["type"] = type
if options is not None:
params["options"] = options
if symbol is not None:
params["symbol"] = symbol
if isPrimary is not None:
params["primary"] = isPrimary
if systemColumnType is not None:
params["systemColumnType"] = systemColumnType
if autoNumberFormat is not None:
params["autoNumberFormat"] = autoNumberFormat
column = Column(title, **params)
sheet.insertColumn(column, column.index)
def addColumnInAllSheets(self, title, workspace=None, index=None, type=None, options=None, symbol=None, isPrimary=None, systemColumnType=None, autoNumberFormat=None, width=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.addColumn(
sheet,
title,
index=index,
type=type,
options=options,
symbol=symbol,
isPrimary=isPrimary,
systemColumnType=systemColumnType,
autoNumberFormat=autoNumberFormat,
width=width)
def updateColumn(self, sheet, oldTitle, newTitle=None, index=None, type=None, options=None, symbol=None, systemColumnType=None, autoNumberFormat=None, width=None, format=None):
column = sheet.getColumnsInfo().getColumnByTitle(oldTitle)
if newTitle is not None:
column.title = newTitle
if index is not None:
column.index = index
if type is not None:
column.type = type
if options is not None:
column.options = options
if symbol is not None:
column.symbol = symbol
if systemColumnType is not None:
column.systemColumnType = systemColumnType
if autoNumberFormat is not None:
column.autoNumberFormat = autoNumberFormat
if width is not None:
column.width = width
if format is not None:
column.format = format
column.update()
def updateColumnInAllSheets(self, oldTitle, workspace=None, newTitle=None, index=None, type=None, options=None, symbol=None, systemColumnType=None, autoNumberFormat=None, width=None, format=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.updateColumn(
sheet,
oldTitle,
newTitle=newTitle,
index=index,
type=type,
options=options,
symbol=symbol,
systemColumnType=systemColumnType,
autoNumberFormat=autoNumberFormat,
width=width,
format=format)
def addRow(self, sheet, rowDictionary, rowNumber=None):
row = sheet.makeRow(**rowDictionary)
if rowNumber is None:
# add as last row
sheet.addRow(row)
elif rowNumber in (0, 1):
# add as first row
sheet.addRow(row, position=RowPositionProperties.Top)
else:
# new row is inserted below sibling, so the sibling above will be:
# if rowNumber < 0, the row currently at the desired row number
# if rowNumber > 1, the row 1 above the desired row number
siblingAboveRowId = sheet.getRowByRowNumber(rowNumber if rowNumber < 0 else rowNumber - 1).id
sheet.addRow(row, siblingId=siblingAboveRowId)
def addRowInAllSheets(self, rowDictionary, workspace=None, rowNumber=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.addRow(sheet, rowDictionary, rowNumber)
def expandAllRows(self, sheet, isExpanded=True):
# operate only on rows referenced to be parent rows
parentRowNumbers = frozenset([row.parentRowNumber for row in sheet.rows if row.parentRowNumber])
for parentRowNumber in parentRowNumbers:
row = sheet[parentRowNumber]
if row.expanded != isExpanded:
row.expanded = isExpanded
row.save()
def expandAllRowsInAllSheets(self, workspace=None, isExpanded=True):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.expandAllRows(sheet, isExpanded)
def getSheetInfos(self, workspace=None):
# Smartsheet Python SDK cannot filter by workspace
return self.__smartsheetClient.fetchSheetList()
def __getSheetIfInWorkspace(self, sheetInfo, workspace):
""" Returns a Sheet if it belongs to the specified workspace
or if workspace == None. Returns None if the sheet does not
belong to the workspace.
:param sheetInfo: the SheetInfo for the desired sheet
:param workspace: the desired workspace name, or None to
disable workspace checking and always return the associated Sheet.
"""
sheet = sheetInfo.loadSheet()
if (sheet):
sheetWorkspace = sheet.workspace["name"]
isSheetInWorkspace = not workspace or sheetWorkspace == workspace
if (not isSheetInWorkspace):
self.__logger.debug('sheet %s workspace "%s" != "%s"' % (sheetInfo, sheetWorkspace, workspace))
return sheet
def __init__(self, token): self.__smartsheetClient = SmartsheetClient(token, logger=logging.getLogger(SmartsheetClient.__name__)) self.__smartsheetClient.connect()
class RowAddDeleteTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestRowAddDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns, location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_add_rows_to_top_of_sheet(self):
'''Add Rows, one at a time, to the top of an initially blank Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "one")
r = self.sheet.makeRow()
r[0] = "three"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "three")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "one")
def test_add_rows_to_bottom_of_sheet(self):
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
r = self.sheet.makeRow()
r[0] = "three"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
def test_add_row_between_rows_on_sheet(self):
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
r = self.sheet.makeRow()
r[0] = "one-two"
self.sheet.addRow(r, siblingId=self.sheet[1].id)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "one-two")
self.assertTrue(self.sheet[3][0] == "two")
def test_add_multiple_rows_to_blank_sheet(self):
'''Add multiple Rows at once to the top of a blank/empty Sheet.'''
# Add two rows at once at the very top of a blank Sheet.
row_1 = self.sheet.makeRow()
row_1[0] = 'one'
row_2 = self.sheet.makeRow()
row_2[0] = 'two'
rw = RowWrapper(self.sheet, position='toTop')
rw.addRow(row_1)
rw.addRow(row_2)
self.assertTrue(len(rw.rows) == 2)
res = self.sheet.addRows(rw)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == 'one')
self.assertTrue(self.sheet[2][0] == 'two')
def test_add_multiple_rows_to_top_of_one_row_sheet(self):
'''Add multiple Rows at once to the top of a one-row Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
r = self.sheet.makeRow()
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
rw = self.sheet.makeRowWrapper(position='toTop')
r = rw.makeRow()
r[0] = "New Top Row"
rw.addRow(r)
r = rw.makeRow()
r[0] = "Second Row From Top"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "New Top Row")
self.assertTrue(self.sheet[2][0] == "Second Row From Top")
self.assertTrue(self.sheet[3][0] == "one")
def test_add_multiple_rows_below_a_one_row_sheet__without_position_equal_toBottom(self):
'''
Add multiple Rows at once as a sibling to the Row in a one-row Sheet.
'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[1].id)
r = rw.makeRow()
r[0] = "Second Row From Top"
rw.addRow(r)
r = rw.makeRow()
r[0] = "Third Row From Top"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "Second Row From Top")
self.assertTrue(self.sheet[3][0] == "Third Row From Top")
def test_add_multiple_rows_between_rows(self):
'''
Add multiple Rows at once between the Rows in a Sheet.
'''
rw = self.sheet.makeRowWrapper(position='toBottom')
r = rw.makeRow()
r[0] = "one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[1].id)
r = rw.makeRow()
r[0] = "one-one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "one-two"
rw.addRow(r)
res = self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 5)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "one-one")
self.assertTrue(self.sheet[3][0] == "one-two")
self.assertTrue(self.sheet[4][0] == "two")
self.assertTrue(self.sheet[5][0] == "three")
def test_add_multiple_rows_just_before_the_last_row(self):
rw = self.sheet.makeRowWrapper(position='toBottom')
r = rw.makeRow()
r[0] = "one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[2].id)
r = rw.makeRow()
r[0] = "two-one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two-two"
rw.addRow(r)
res = self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 5)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "two-one")
self.assertTrue(self.sheet[4][0] == "two-two")
self.assertTrue(self.sheet[5][0] == "three")
def test_delete_only_row_in_sheet(self):
'''Test deleting the only Row in a Sheet.'''
r = self.sheet.makeRow()
r[0] = "only"
self.sheet.addRow(r)
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "only")
res = self.sheet[1].delete()
self.assertTrue(len(self.sheet) == 0)
def test_delete_rows_in_sheet_with_multiple_rows(self):
'''Delete Rows from a Sheet with multiple Rows.'''
rw = self.sheet.makeRowWrapper(position='toTop')
r = rw.makeRow(); r[0] = "one"; rw.addRow(r)
r = rw.makeRow(); r[0] = "two"; rw.addRow(r)
r = rw.makeRow(); r[0] = "three"; rw.addRow(r)
r = rw.makeRow(); r[0] = "four"; rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 4)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
self.assertTrue(self.sheet[4][0] == "four")
self.sheet[1].delete()
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "three")
self.assertTrue(self.sheet[3][0] == "four")
self.sheet[3].delete()
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "three")
self.sheet[-2].delete()
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "three")
self.sheet[-1].delete()
self.assertTrue(len(self.sheet) == 0)
class RowAddDeleteTest(unittest.TestCase):
logger = None
client = None
columns = []
sheet = None
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
time_str = str(datetime.datetime.utcnow())
self.new_sheet_name = 'TestRowAddDelete-%s' % time_str
col_1 = Column("Col 1 - TextNumber", type=CellTypes.TextNumber,
primary=True)
col_2 = Column("Col 2 - Date", type=CellTypes.Date)
col_3 = Column("Col 3 - Picklist" , type=CellTypes.Picklist,
options=['Yes', 'No', 'Maybe'])
col_4 = Column("Col 4 - Checkbox", type=CellTypes.Checkbox)
col_5 = Column("Col 5 - ContactList", type=CellTypes.ContactList)
self.columns = [col_1, col_2, col_3, col_4, col_5]
self.sheet_info = self.client.createSheet(self.new_sheet_name,
self.columns, location='')
if self.sheet_info is None:
err = "Failed (probably) to create sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.sheet = self.sheet_info.loadSheet()
if self.sheet is None:
err = "Unable to load newly created sheet: %s" % self.new_sheet_name
self.logger.error(err)
raise Exception(err)
self.logger.info("########### Test %s uses Sheet: %s ############",
self.__class__.__name__, self.new_sheet_name)
def tearDown(self):
sheet_name = self.sheet.name
self.sheet.delete()
self.logger.info("Sheet %s deleted", sheet_name)
def test_add_one_row_to_top_of_sheet(self):
'''Add a single Row to the top of the blank Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
def test_add_row_with_multiple_columns(self):
'''Add a Row that has multiple values in multiple Columns.'''
r = self.sheet.makeRow()
r[0] = "one"
r[1] = "2015-05-05"
self.sheet.addRow(r)
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[1][1] == "2015-05-05")
def test_make_a_row_from_a_list_of_values(self):
'''Make a Row from a list of values.'''
row_1_value_list = ["one", "2015-05-05", "Yes", True,
"*****@*****.**"]
row_2_value_list = ["two", "2015-05-06", "No", False,
"*****@*****.**"]
row = self.sheet.makeRow(row_1_value_list)
for i in range(len(row_1_value_list)):
self.assertTrue(row[i] == row_1_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_1_value_list)):
self.assertTrue(self.sheet[1][i] == row_1_value_list[i])
rw = self.sheet.makeRowWrapper(position='toBottom')
row = rw.makeRow(row_2_value_list)
for i in range(len(row_2_value_list)):
self.assertTrue(row[i] == row_2_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_2_value_list)):
self.assertTrue(self.sheet[2][i] == row_2_value_list[i])
def test_make_a_row_from_positional_parameters(self):
'''Make a Row from a list of positional parameters.'''
row_1_value_list = ["one", "2015-05-05", "Yes", True,
"*****@*****.**"]
row_2_value_list = ["two", "2015-05-06", "No", False,
"*****@*****.**"]
row = self.sheet.makeRow(*row_1_value_list)
for i in range(len(row_1_value_list)):
self.assertTrue(row[i] == row_1_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_1_value_list)):
self.assertTrue(self.sheet[1][i] == row_1_value_list[i])
rw = self.sheet.makeRowWrapper(position='toBottom')
row = rw.makeRow(*row_2_value_list)
for i in range(len(row_2_value_list)):
self.assertTrue(row[i] == row_2_value_list[i])
self.sheet.addRow(row)
for i in range(len(row_2_value_list)):
self.assertTrue(self.sheet[2][i] == row_2_value_list[i])
def test_add_rows_to_top_of_sheet(self):
'''Add Rows, one at a time, to the top of an initially blank Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "one")
r = self.sheet.makeRow()
r[0] = "three"
self.sheet.addRow(r, position='toTop')
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "three")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "one")
def test_add_rows_to_bottom_of_sheet(self):
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
r = self.sheet.makeRow()
r[0] = "three"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
def test_add_row_between_rows_on_sheet(self):
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
r = self.sheet.makeRow()
r[0] = "two"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
r = self.sheet.makeRow()
r[0] = "one-two"
self.sheet.addRow(r, siblingId=self.sheet[1].id)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "one-two")
self.assertTrue(self.sheet[3][0] == "two")
def test_add_multiple_rows_to_blank_sheet(self):
'''Add multiple Rows at once to the top of a blank/empty Sheet.'''
# Add two rows at once at the very top of a blank Sheet.
row_1 = self.sheet.makeRow()
row_1[0] = 'one'
row_2 = self.sheet.makeRow()
row_2[0] = 'two'
rw = self.sheet.makeRowWrapper(position='toTop')
rw.addRow(row_1)
rw.addRow(row_2)
self.assertTrue(len(rw.rows) == 2)
res = self.sheet.addRows(rw)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == 'one')
self.assertTrue(self.sheet[2][0] == 'two')
def test_add_multiple_rows_to_top_of_one_row_sheet(self):
'''Add multiple Rows at once to the top of a one-row Sheet.'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
r = self.sheet.makeRow()
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
rw = self.sheet.makeRowWrapper(position='toTop')
r = rw.makeRow()
r[0] = "New Top Row"
rw.addRow(r)
r = rw.makeRow()
r[0] = "Second Row From Top"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "New Top Row")
self.assertTrue(self.sheet[2][0] == "Second Row From Top")
self.assertTrue(self.sheet[3][0] == "one")
def test_add_multiple_rows_below_a_one_row_sheet__without_position_equal_toBottom(self):
'''
Add multiple Rows at once as a sibling to the Row in a one-row Sheet.
'''
r = self.sheet.makeRow()
r[0] = "one"
self.sheet.addRow(r, position='toBottom')
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "one")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[1].id)
r = rw.makeRow()
r[0] = "Second Row From Top"
rw.addRow(r)
r = rw.makeRow()
r[0] = "Third Row From Top"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "Second Row From Top")
self.assertTrue(self.sheet[3][0] == "Third Row From Top")
def test_add_multiple_rows_between_rows(self):
'''
Add multiple Rows at once between the Rows in a Sheet.
'''
rw = self.sheet.makeRowWrapper(position='toBottom')
r = rw.makeRow()
r[0] = "one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[1].id)
r = rw.makeRow()
r[0] = "one-one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "one-two"
rw.addRow(r)
res = self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 5)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "one-one")
self.assertTrue(self.sheet[3][0] == "one-two")
self.assertTrue(self.sheet[4][0] == "two")
self.assertTrue(self.sheet[5][0] == "three")
def test_add_multiple_rows_just_before_the_last_row(self):
rw = self.sheet.makeRowWrapper(position='toBottom')
r = rw.makeRow()
r[0] = "one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two"
rw.addRow(r)
r = rw.makeRow()
r[0] = "three"
rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
rw = self.sheet.makeRowWrapper(siblingId=self.sheet[2].id)
r = rw.makeRow()
r[0] = "two-one"
rw.addRow(r)
r = rw.makeRow()
r[0] = "two-two"
rw.addRow(r)
res = self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 5)
self.assertTrue(res is not None)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "two-one")
self.assertTrue(self.sheet[4][0] == "two-two")
self.assertTrue(self.sheet[5][0] == "three")
def test_delete_only_row_in_sheet(self):
'''Test deleting the only Row in a Sheet.'''
r = self.sheet.makeRow()
r[0] = "only"
self.sheet.addRow(r)
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "only")
res = self.sheet[1].delete()
self.assertTrue(len(self.sheet) == 0)
def test_delete_rows_in_sheet_with_multiple_rows(self):
'''Delete Rows from a Sheet with multiple Rows.'''
rw = self.sheet.makeRowWrapper(position='toTop')
r = rw.makeRow(); r[0] = "one"; rw.addRow(r)
r = rw.makeRow(); r[0] = "two"; rw.addRow(r)
r = rw.makeRow(); r[0] = "three"; rw.addRow(r)
r = rw.makeRow(); r[0] = "four"; rw.addRow(r)
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 4)
self.assertTrue(self.sheet[1][0] == "one")
self.assertTrue(self.sheet[2][0] == "two")
self.assertTrue(self.sheet[3][0] == "three")
self.assertTrue(self.sheet[4][0] == "four")
self.sheet[1].delete()
self.assertTrue(len(self.sheet) == 3)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "three")
self.assertTrue(self.sheet[3][0] == "four")
self.sheet[3].delete()
self.assertTrue(len(self.sheet) == 2)
self.assertTrue(self.sheet[1][0] == "two")
self.assertTrue(self.sheet[2][0] == "three")
self.sheet[-2].delete()
self.assertTrue(len(self.sheet) == 1)
self.assertTrue(self.sheet[1][0] == "three")
self.sheet[-1].delete()
self.assertTrue(len(self.sheet) == 0)
def test_add_row_from_list_of_items(self):
rw = self.sheet.makeRowWrapper(position='toBottom')
row = rw.makeRow('one', '2015-05-05', 'Maybe')
rw.addRow(row)
rw.addRow(rw.makeRow(['two', '2015-05-06', 'Yes']))
def value_generator():
vals = ['three', '2015-05-07', 'No']
for item in vals:
yield item
rw.addRow(rw.makeRow(value_generator()))
rw.addRow(rw.makeRow('four', None, 'Maybe'))
self.sheet.addRows(rw)
self.assertTrue(len(self.sheet) == 4)
self.assertTrue(self.sheet[1][0] == 'one')
self.assertTrue(self.sheet[1][1] == '2015-05-05')
self.assertTrue(self.sheet[1][2] == 'Maybe')
self.assertTrue(self.sheet[2][0] == 'two')
self.assertTrue(self.sheet[2][1] == '2015-05-06')
self.assertTrue(self.sheet[2][2] == 'Yes')
self.assertTrue(self.sheet[3][0] == 'three')
self.assertTrue(self.sheet[3][1] == '2015-05-07')
self.assertTrue(self.sheet[3][2] == 'No')
self.assertTrue(self.sheet[4][0] == 'four')
self.assertTrue(self.sheet[4][1] == None)
self.assertTrue(self.sheet[4][2] == 'Maybe')
def setUp(self):
self.logger = logging.getLogger()
self.client = SmartsheetClient(api_token, logger=self.logger)
class SmartsheetService(object):
__logger = logging.getLogger(__name__)
def __init__(self, token):
self.__smartsheetClient = SmartsheetClient(token, logger=logging.getLogger(SmartsheetClient.__name__))
self.__smartsheetClient.connect()
def updateCell(self, sheet, rowNumber, columnIndex=None, columnTitle=None, value=None):
if columnIndex is not None and columnTitle is not None:
raise SmartsheetBulkEditError('one but not both "columnIndex" and "columnTitle" must be specified')
elif columnTitle is not None:
columnIndex = sheet.getColumnsInfo().getColumnByTitle(columnTitle).index
elif columnIndex is None:
raise SmartsheetBulkEditError('either "columnIndex" or "columnTitle" must be specified')
row = sheet[rowNumber]
row[columnIndex] = value
row.getCellByIndex(columnIndex).save(propagate=False)
def updateCellInAllSheets(self, rowNumber, workspace=None, columnIndex=None, columnTitle=None, value=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.updateCell(sheet, rowNumber, columnIndex=columnIndex, columnTitle=columnTitle, value=value)
def updateCellInSheetList(self, rowNumber, columnIndex=None, columnTitle=None, value=None, sheetInfoList=None):
'''
Update the specified Cell in the list of sheets.
The Cell is identified by rowNumber and columnIndex or columnTitle.
Two lists are returned, the first is a list of the SheetInfo objects
for the Sheets that were successfully updated.
The second returned list is a list of 3-tuples:
(SheetInfo, Exception, stacktrace) for the sheets that were NOT
updated successfully.
'''
good, bad = [], []
if columnIndex is not None and columnTitle is not None:
raise SmartsheetBulkEditError('one but not both "columnIndex" and "columnTitle" must be specified')
elif columnTitle is not None:
columnIndex = sheet.getColumnsInfo().getColumnByTitle(columnTitle).index
elif columnIndex is None:
raise SmartsheetBulkEditError('either "columnIndex" or "columnTitle" must be specified')
for sheetInfo in sheetInfoList:
try:
sheet = sheetInfo.loadSheet()
self.updateCell(sheet, rowNumber=rowNumber, columnIndex=columnIndex, columnTitle=columnTitle, value=value)
good.append(sheet)
except Exception as e:
bad.append((sheetInfo, e, sys.exc_info()[2]))
return good, bad
def addColumn(self, sheet, title, index=None, type=None, options=None, symbol=None, isPrimary=None, systemColumnType=None, autoNumberFormat=None, width=None):
params = {}
if sheet is not None:
params["sheet"] = sheet
if index is not None:
params["index"] = index
if type is not None:
params["type"] = type
if options is not None:
params["options"] = options
if symbol is not None:
params["symbol"] = symbol
if isPrimary is not None:
params["primary"] = isPrimary
if systemColumnType is not None:
params["systemColumnType"] = systemColumnType
if autoNumberFormat is not None:
params["autoNumberFormat"] = autoNumberFormat
column = Column(title, **params)
sheet.insertColumn(column, column.index)
def addColumnInAllSheets(self, title, workspace=None, index=None, type=None, options=None, symbol=None, isPrimary=None, systemColumnType=None, autoNumberFormat=None, width=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.addColumn(
sheet,
title,
index=index,
type=type,
options=options,
symbol=symbol,
isPrimary=isPrimary,
systemColumnType=systemColumnType,
autoNumberFormat=autoNumberFormat,
width=width)
def addColumnInSheetList(self, title, workspace=None, index=None, type=None, options=None, symbol=None, isPrimary=None, systemColumnType=None, autoNumberFormat=None, width=None, sheetInfoList=None):
good, bad = [], []
for sheetInfo in sheetInfoList:
try:
sheet = sheetInfo.loadSheet()
self.addColumn(
sheet,
title,
index=index,
type=type,
options=options,
symbol=symbol,
isPrimary=isPrimary,
systemColumnType=systemColumnType,
autoNumberFormat=autoNumberFormat,
width=width)
good.append(sheetInfo)
except Exception as e:
bad.append((sheetInfo, e, sys.exc_info()[2]))
return good, bad
def updateColumn(self, sheet, oldTitle, newTitle=None, index=None, type=None, options=None, symbol=None, systemColumnType=None, autoNumberFormat=None, width=None, format=None):
column = sheet.getColumnsInfo().getColumnByTitle(oldTitle)
if newTitle is not None:
column.title = newTitle
if index is not None:
column.index = index
if type is not None:
column.type = type
if options is not None:
column.options = options
if symbol is not None:
column.symbol = symbol
if systemColumnType is not None:
column.systemColumnType = systemColumnType
if autoNumberFormat is not None:
column.autoNumberFormat = autoNumberFormat
if width is not None:
column.width = width
if format is not None:
column.format = format
column.update()
def updateColumnInAllSheets(self, oldTitle, workspace=None, newTitle=None, index=None, type=None, options=None, symbol=None, systemColumnType=None, autoNumberFormat=None, width=None, format=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.updateColumn(
sheet,
oldTitle,
newTitle=newTitle,
index=index,
type=type,
options=options,
symbol=symbol,
systemColumnType=systemColumnType,
autoNumberFormat=autoNumberFormat,
width=width,
format=format)
def updateColumnInSheetList(self, oldTitle, workspace=None, newTitle=None, index=None, type=None, options=None, symbol=None, systemColumnType=None, autoNumberFormat=None, width=None, format=None, sheetInfoList=None):
good, bad = [], []
for sheetInfo in sheetInfoList:
try:
sheet = sheetInfo.loadSheet()
self.updateColumn(
sheet,
oldTitle,
newTitle=newTitle,
index=index,
type=type,
options=options,
symbol=symbol,
systemColumnType=systemColumnType,
autoNumberFormat=autoNumberFormat,
width=width,
format=format)
good.append(sheetInfo)
except Exception as e:
bad.append((sheetInfo, e, sys.exc_info()[2]))
return good, bad
def addRow(self, sheet, rowDictionary, rowNumber=None):
row = sheet.makeRow(**rowDictionary)
if rowNumber is None:
# add as last row
sheet.addRow(row)
elif rowNumber in (0, 1):
# add as first row
sheet.addRow(row, position=RowPositionProperties.Top)
else:
# new row is inserted below sibling, so the sibling above will be:
# if rowNumber < 0, the row currently at the desired row number
# if rowNumber > 1, the row 1 above the desired row number
siblingAboveRowId = sheet.getRowByRowNumber(rowNumber if rowNumber < 0 else rowNumber - 1).id
sheet.addRow(row, siblingId=siblingAboveRowId)
def addRowInAllSheets(self, rowDictionary, workspace=None, rowNumber=None):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.addRow(sheet, rowDictionary, rowNumber)
def addRowInSheetList(self, rowDictionary, rowNumber=None, sheetInfoList=None):
good, bad = [], []
for sheetInfo in sheetInfoList:
try:
sheet = sheetInfo.loadSheet()
self.addRow(sheet, rowDictionary, rowNumber=rowNumber)
good.append(sheetInfo)
except Exception as e:
bad.append((sheetInfo, e, sys.exc_info()[2]))
return good, bad
def expandAllRows(self, sheet, isExpanded=True):
# operate only on rows referenced to be parent rows
parentRowNumbers = frozenset([row.parentRowNumber for row in sheet.rows if row.parentRowNumber])
for parentRowNumber in parentRowNumbers:
row = sheet[parentRowNumber]
if row.expanded != isExpanded:
row.expanded = isExpanded
row.save()
def expandAllRowsInAllSheets(self, workspace=None, isExpanded=True):
for sheetInfo in self.getSheetInfos(workspace):
sheet = self.__getSheetIfInWorkspace(sheetInfo, workspace)
if sheet is not None:
self.expandAllRows(sheet, isExpanded)
def expandAllRowsInSheetList(self, isExpanded=True, sheetInfoList=None):
good, bad = [], []
for sheetInfo in sheetInfoList:
try:
sheet = sheetInfo.loadSheet()
self.expandAllRows(sheet, isExpanded=isExpanded)
good.append(sheetInfo)
except Exception as e:
bad.append((sheetInfo, e, sys.exc_info()[2]))
return good, bad
def getSheetInfos(self, workspace=None):
# Smartsheet Python SDK cannot filter by workspace
return self.__smartsheetClient.fetchSheetList()
def getSheetInfosInWorkspace(self, workspaceID=''):
'''
Get a list of the SheetInfo objects in the specified workspace.
'''
# Uses the Smartsheet Python SDK client directly, since this
# functionality isn't yet implemented in the high-level Python SDK.
sheetInfoList = []
try:
workspacePath = '/workspace/%s' % workspaceID
workspace = self.__smartsheetClient.GET(workspacePath)
for sheet_fields in workspace['sheets']:
sheetInfoList.append(SheetInfo(sheet_fields, self.__smartsheetClient))
return sheetInfoList
except Exception as e:
self.__logger.exception("Error getting list of sheets from workspace ID: %s: %r", workspaceID, e)
raise
raise (SmartsheetBulkEditError, ("Error getting list of sheets from workspace ID: %s: %r" % (workspaceID, e)), sys.exc_info()[2])
def getWorkspacesByName(self, workspaceName):
'''
Get a list of workspaces that have the given name.
The returned workspaces are Python dicts, not objects from the
Smartsheet client library -- it does not yet support workspaces.
'''
# Uses the Smartsheet Python SDK client directly, since this
# functionality isn't yet implemented by the high-level Python SDK.
workspaces = []
try:
for workspace in self.__smartsheetClient.GET('/workspaces'):
if workspace['name'] == workspaceName:
workspaces.append(workspace)
return workspaces
except Exception as e:
raise (SmartsheetBulkEditError, ("Error getting workspace list: %r" % e), sys.exc_info()[2])
raise SmartsheetBulkEditError("Unable to find workspace named: '%s'" % workspaceName)
def __getSheetIfInWorkspace(self, sheetInfo, workspace):
""" Returns a Sheet if it belongs to the specified workspace
or if workspace == None. Returns None if the sheet does not
belong to the workspace.
:param sheetInfo: the SheetInfo for the desired sheet
:param workspace: the desired workspace name, or None to
disable workspace checking and always return the associated Sheet.
"""
sheet = sheetInfo.loadSheet()
if (sheet):
sheetWorkspace = sheet.workspace["name"]
isSheetInWorkspace = not workspace or sheetWorkspace == workspace
if (not isSheetInWorkspace):
self.__logger.debug('sheet %s workspace "%s" != "%s"' % (sheetInfo, sheetWorkspace, workspace))
return sheet