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
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
