Before I explain my suggestions, I would first like to give some context on how I use the Smartsheet API. I develop implementations for SQL connectivity interfaces such as JDBC, ODBC, ADO.NET, etc. for various sources (one of those sources being Smartsheet). I use the Smartsheet API to get data for the main resources in Smartsheet such as Sheets, Dashboards, Templates, Reports, Workspaces, Folders, etc. This allows me to expose these API resources as SQL tables/views in my connectors.
With that in mind, I have a very simple mental model in mind when developing my connectors: I want to expose as much of my end-users' Smartsheet data/information through my connectors.
At the moment, I'm interested in getting as much information possible for Sheets, Dashboards, and Reports (although my suggestions would probably apply for other resources, which can be nested in workspaces/folders). Particularly, I'm working with the following endpoints:
1> The first problem that I have is that List <OBJECT_TYPE>s endpoints return only the most basic/important properties for each record, which are only a subset of the full set of properties that describe the object. The Get <OBJECT_TYPE> endpoints on the other hand, return all the properties for the object corresponding to the id specified.
I'm not sure if I am the only one, or if I'm wrong, but I find this design very confusing (of course I'm sure Smartsheet must have their reasons for it). In most API-s I would expect that both endpoints return the full set of properties for the resource, and the endpoint which accepts an id (or some sort of key/identifier attribute) input/parameter would be used simply for the purpose of filtering the results.
The way that things are currently; in order to get all the properties for all the records, I have to do something like the following:
- List all objects by using the
List <OBJECT_TYPE>s endpoint. Save the id value for each record. - For each
id collected, call the Get <OBJECT_TYPE> endpoint to get the full set of properties.
#2 in particular can be very expensive in terms computing resources. For example, if I have 10.000 sheets in my account, I need to make 10.000 requests, which as you can imagine will take 'forever', even with the best HTTP client out there.
My first suggestion is why not make the List <OBJECT_TYPE>s endpoints return the full set of properties like the Get <OBJECT_TYPE> endpoints?
2> The second problem that I have is that I want to get the workspace id and folder id in which the object is located. I find this piece of information very important for my end-users. To give a simple idea, consider the following use case:
SELECT s.SheetId, s.SheetName, f.FolderId, f.FolderName
FROM Sheets s
JOIN Folders f
ON s.FolderId = f.FolderId
WHERE f.FolderName = 'My Archived Sheets'
Currently, the only way to get this data is by recursively navigating the workspace/folder Smartsheet hierarchy through the List workspace children and List folder children endpoints. This approach has already been explained in previous posts:
I don't really mind this, however just like the List <OBJECT_TYPE>s endpoints, the List workspace children and List folder children endpoints don't return the full set of properties either.
My first second suggestion is why not make these endpoints return the full set of properties like the Get <OBJECT_TYPE> endpoints? Or alternatively, make the List <OBJECT_TYPE>s endpoints return the full set of properties (including the workspaceId and folderId).
