We will introduce the API Service Tools that Cloudsmart has developed to use Smartsheet.
What is an API service?
Smartsheet APIadd functionality not found in the original Smartsheet functionality, or continue to programmatically iterate Smartsheet functionality (e.g. apply column functions, paste images into cells, get attachments) It will be possible to use.
Tools in operation
The tools we are currently using are as follows, and we will add explanations and release how to use them one by one.
From what I added additional commentaryput it in bold, change the link destination to the article on this website.
- Column information creation service
- A service that retrieves sheet column information and displays it in the Column Info Table
Used for column management when there are dozens to hundreds of columns, or when there are complex column expressions.
- A service that retrieves sheet column information and displays it in the Column Info Table
- Column information update service
- Service to edit the columns of the sheet based on the sheet of column information created by the above service
For example, for bulk editing of column names, widths, column functions, options, etc.
- Service to edit the columns of the sheet based on the sheet of column information created by the above service
- Image pasting service in cell
- A service that pastes an image file attached to a row into a cell in a specific column
Used to automatically paste photos attached by mobile apps, etc. to cells, and to automatically paste images requested to be pasted by external users in update requests to cells.
- A service that pastes an image file attached to a row into a cell in a specific column
- Cell link destination display service
- A service that pastes the sheet name of the cell link destination and its URL into the row conversation
Since the number of cell links displayed is currently limited to XNUMX, it is used to access more than that, for example, XNUMX or more cell link destination information.
- A service that pastes the sheet name of the cell link destination and its URL into the row conversation
Tools under consideration for development
We are planning to develop the following tools.
Especially for Japanese organizations, it is important to control access on an organizational basis, so tools related to group and workspace sharing are important.
Also, automating the refresh of dropdown lists with other lists is a key feature that Smartsheet is missing, so we'd like to consider early development.
- Organization management/group management
- A service that automatically updates the group to which a user belongs when a user moves from one organization to another due to personnel changes, etc.
- Automatic update of dropdown list
- A service that allows you to update the drop-down list of a sheet's columns, such as within the same workspace, by updating the master sheet's options.
- Unification of column names
- A service that unifies the column names of sheets within the same workspace
For example, if multiple column names such as customer, customer, etc. are used, unify them to XNUMX
- A service that unifies the column names of sheets within the same workspace
- Cross-sheet reference update automation
Usage common to all services
Consultation on using the service
Please refer toFormPlease select "API Service" from the list and contact us.
request by form
Access is done through a form familiar to Smartsheet users.
For example, in the case of cell link information acquisition, the sheet ID that displays the outbound link information in the form and the ID of the workspace where the linked sheet is stored are entered.
Granting Required Privileges
of the target user, as follows:seat,Workspacesheet inviewing rights foras well as the When updating columns, pasting images, commenting (conversation) recording of cell link-out information, etc., the sheet OfEdit permission is required.
- Sheets, workspaces: read permission
- Viewing authority for the sheet to be provided with the service or the workspace in which the sheet exists (Check the required information (eg cell link) for each sheet.)
- Target sheet: Edit permission
- For example, for the cell linkout information service, you need permission to write linkout information as row conversations (comments) in the sheet.
Reason for sharing
OAuth authentication risks
When providing services using API for user sheets etc., it is common to display the OAuth screen like the image below and grant permissions such as viewing and editing. , Editing, etc. applies to all sheets for which the grantor has authority.
Therefore, for example, there are services that automatically update drop-down lists for a fee.
In order to avoid this risk, it is conceivable to use an account for service use and give that account authority to view, edit, etc. only the sheets etc. necessary for that service use, but there is another method. There is a problem that it is necessary to prepare an account for
Risk mitigation through sharing
Restricted Scope of Granted Objects
The shared method has the same risks as OAuth authentication, but the major difference from OAuth authentication is thatPermissions granted are limited to the shared sheet or workspaceabout it.
The minimum authority to be granted is sufficient for using the service
In addition, since it is sufficient if the authority to be given is the minimum necessary for using the service, for example,If it is an example of writing information in a comment (conversation), only "comment creator access" is sufficient.Therefore, there is no fear that the data in the sheet body will be edited.,Also,Unnecessary information can be hidden in columns, so the risk of confidential information being seen is also reduced.Available
Auditing through activity logs, etc.
Also, in the case of OAuth authentication, activity logs and cell histories are recorded as the activity of the person who gave the authentication, but in the sharing method, it is recorded with the account name of the sharing destination (e.g. app@cloudsmart.jp). Therefore, viewing the sheet is unavoidable as long as the work is done, but it is possible to audit whether unnecessary editing etc. is performed.
Risk avoidance by providing dedicated services
Even if the risk is reduced by the sharing method, a certain degree of risk remains, so in that case, a custom service dedicated to the user will be built.
Cloudsmart builds an API service on GCP (Google Cloud Platform), but in the case of providing a custom service, we build a similar user-dedicated service and use your API tokenSecret Managerto avoid the risks of other methods.