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