Introduction
We have created a Dropdown List Update Tool which uses Smartsheet API and Google Cloud Platform Functions to update sheets in a workspace with a source sheet’s column information.
Before providing this tool as a Smartsheet partner service, we would like to offer the Smartsheet community user to test the tool and get feedback from users.
How you can use the tool
- You need to give app@cloudsmart.jp a read-only access to your source sheet.
- Why: to read column information, especially type of the column and dropdown options.
- You also need to give app@cloudsmart.jp an admin access to the target workspace.
- Why: to update the dropdown columns of sheets in the workspace.
- After you have completed the 1. and 2. steps, please fill in the following form with your source sheet’s id and target sheet’s workspace id.
https://app.smartsheet.com/b/form/e4d7b74b7c614194b7ee4f3148d095ef
What this tool does
- First, it views the source sheet and gets the column information.
- Then, the tool records the title (or name) and options of the column whose column types are one of the follwings: (Here, we call them as “dropdown columns”)
- Pick List
- Mutiple Pic List
- Contact List
- Multiple Contact List
- Symbol
- Then, the tool collects the sheet id information of the sheets in the workspace, including sheets in folders.
- Finally, the tool updates the above dropdown columns of the sheet with same column names* with the source sheet’ dropdown or contact list or symbol specification (* case ignored)
Security
- Sharing method, not Oauth
- This tool does not use the typical OAuth but sheet and workspace share methods.
- The reason why we opt out of the OAuth is that if you give OAuth of an account to a tool, an administrator access, for example, you will give the tool the administrator access to the all sheets the account has access to.
- We doubt your organization’s system administrator will permit the use of this kind of OAuth access.
- Compared to OAuth methods, the sharing methods give only access to the shared sheets, directly or through workspace access.
- How you can improve security
- The last thing you want to do is to give access to some tool and get your organization’s confidential information leaked or your sheets modified or deleted by the tool.
- To avoid the above, we advise you to share workspace with sheets that are only used as form inputs.
- To do that, you can add an automation to move a row to another not-shared sheet as soon as the form adds a row to the input sheet.
- You could remove the admin access to the workspace as soon as the tool updates dropdown lists. (You need to give the tool admin access again when you update the list again.)
- That way, you can avoid the risk of information leaks and unauthorized modification.
Limitation
- The number of sheets that can be updated within a workspace is limited by the number of dropdown columns and number of folders in the workspace.
- For example, 179 if the source sheet has 9 dropdown columns and 21 folders in the workspace.
- It depends on the number of the Pick List etc. columns at the source sheet and number of folders at the workspace.
- The limit comes from the following two.
- Google Cloud Platform Function times out in 540 seconds
- Smartsheet API needs to rest 60 seconds after every 300 calls
- Example (Source Sheet’s : 9 dropdown columns)
- Set up takes 23 calls
- Number of Smartsheet API Calls
- Access source sheet 1 call
- Access target workspace 1 call
- Access folders in the workspace 21 call
- Note, If the workspace has nested folders, the tool needs to make more API calls.
- Number of Smartsheet API Calls
- Update a sheet takes 10 calls
- Sheet Access 1
- Columns Updates 9
- Smartsheet API needs 1 API call per 1 column update
- 1st round
- (300-23) /10 = 28 sheets (note: updating takes a few seconds)
- 60 seconds sleep
- 2nd- rounds
- 300 /10 = 30 sheets (note: updating takes a few seconds)
- 60 seconds sleep
- If the tool have 6 rounds, it can update 28 + 30*5 = 178 sheets.
- Set up takes 23 calls