Create dynamic dropdowns with Data Shuttle

Use the offload and upload capabilities of Data Shuttle to create dynamic dropdowns in Smartsheet sheets and forms. 

Who can use this?

Plans:

  • Business
  • Enterprise
  • Advanced Work Management

Permissions:

  • Editor
  • Admin
  • Owner

Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.

Dynamic dropdowns automatically update a dropdown list in one sheet when that list changes in another location.

The example below shows how to create dynamic dropdown using two workflows that work together:

  • Offload workflow: Extracts the dropdown option values from your source sheet and saves them as a CSV file attached to that same sheet.
  • Upload workflow: Reads that CSV attachment and uses it to update the dropdown column choices in your target sheet.

Both workflows must be created and run in order. Once scheduled, they keep your dropdown options automatically in sync.

There are a couple of ways you can access Data Shuttle from the Smartsheet app:

  • Via the Connections menu on the top navigation bar in Grid view. 
  • Via the Data Integrations button on the top navigation bar in Table view.

 

You can also sign in using the button below.

Sign in to Data Shuttle


If you don’t find Data Shuttle in any of the locations described, your System Administrator may not have enabled it in Admin Center. 

Create a dynamic dropdown

To create dynamic dropdowns using Data Shuttle, prepare the following:

  • Your source list can be a sheet within Smartsheet or an external file containing the dropdown options.
  • Your target sheet is the sheet where you update your dropdown options according to changes in your source.

Create the offload workflow 

  1. Select the plus icon on the left rail.
  2. Select Offload Data.

  3. Select your source sheet. Then, select Next. 

    Create a dynamic dropdown with DataShuttle
  4. Select a Target Location from the following:
    • Smartsheet Attachment: Select the sheet where you want the attachment. This is used in this example.
    • OneDrive & Sharepoint: Sign in to your external account.
    • GoogleDrive: Sign in to your external account.
    • Box: Sign in to your external account.

In Smartsheet Gov plans, Microsoft's GCC High offering is also available. However, since most drives aren't accessible by default, you must contact Support to activate access.

Create a dynamic dropdown with DataShuttle
  1. Name your exported file and select CSV as the file type.
  2. Select whether you want to add headers to the destination file. Then, select Next.
  3. (Optional) Add a filter to export only data that meets specific criteria.
  4. Select Next.
  5. Map columns between your selected source and target. Select Next.
  6. Toggle Run on schedule to set a schedule. Select Next.
  7. Name your workflow and select Save.
  8. Hover over the workflow you just created and select Run.

A message confirms your workflow executed correctly. 

Create the upload workflow

  1. Select the plus icon on the left rail.
  2. Select Upload Data.
  3. Select your source location:
    • Smartsheet Attachment: You have to select the sheet where the attachment is. This is used in this example.
    • OneDrive & Sharepoint: Sign in to your external account.
    • GoogleDrive: Sign in to your external account.
    • Box: Sign in to your external account.
  4. Select the sheet where the attachment lives.
  5. Select how Data Shuttle chooses the attachment:
    • Name: You must enter the attachment name (include the attachment format).
    • Most recent: The most recent attachment is selected
  6. Check the box if your attachment has column headers.

  7. Select the first row and the character separator. Then, select Next.

    Create a dynamic dropdown with DataShuttle
  8. Select your target sheet. This is where the dropdown options appear.

  9. Select Update the dropdown choices for the selected columns action. Then, select Next. 

    Create a dynamic dropdown with DataShuttle
  10. (Optional) Add a filter to import only data that meets specific criteria.
  11. Select Next.
  12. Map columns between your selected source and target. Select Next.
  13. Toggle Run on schedule to set a schedule, or toggle Run on attachment to run the workflow based on the attachment. Select Next.
  14. (Optional) Add expressions to include a new function field to your target sheet.
  15. Select Next.
  16. Name your workflow and select Save.
  17. After the confirmation appears, select Done.

You can manually select Run to test your workflows. 

If you set a scheduled cadence for the offload and upload workflows, your workflows automatically run and keep your dropdown options in sync with your source list.