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
  • Smartsheet Advance Package

Permissions:

  • Editor
  • Admin
  • Owner

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

Overview

Dynamic dropdowns can automatically update a dropdown list in one location when that list has changed in another location. 

Use Data Shuttle to upload a list of items to update dropdown selections from a list in the same sheet, a different sheet, or a CSV or XLSX file.

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.

Prerequisites

To use Data Shuttle, you must:

  • Have Editor, Admin, or Owner permissions on the target sheet.
  • Have Data Shuttle Premium Application permissions enabled in Admin Center by your Smartsheet System Admin.

Create dynamic dropdowns

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 where you can update your dropdown options according to changes in your source sheet.

The example shows how to automate dropdown values using two workflows:

  • Offload workflow: Extracts data from your source sheet, maps the relevant columns, and saves it as a CSV file.
  • Upload workflow: Uses the generated CSV file to update the dropdown options in your target sheet by mapping the CSV content to the target sheet's dropdown columns.

Create an offload workflow 

  1. Go to Create a workflow > Offload Data.

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

    Create a dynamic dropdown with DataShuttle
  3. Select a Target Location from the following:
    • Smartsheet Attachment: Select the sheet where you want the attachment.
    • 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, the drive for 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 exported 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 if you want to set a schedule, and set the 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 an upload workflow

  1. Go to Create a workflow > Upload Data.
  2. Select your source location:
    • Smartsheet Attachment: You have to select the sheet where the attachment is.
    • OneDrive & Sharepoint: Sign in to your external account.
    • GoogleDrive: Sign in to your external account.
    • Box: Sign in to your external account.
  3. Select the sheet where the attachment lives.
  4. 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
  5. Check the box if your attachment has column headers.

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

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

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

    Create a dynamic dropdown with DataShuttle
  9. (Optional) Add a filter to import only data that meets specific criteria.
  10. Select Next.
  11. Map columns between your selected source and target. Select Next.
  12. Toggle Run on schedule if you want to set a schedule, or toggle Run on attachment if you want the workflow to run when the attachment is uploaded. Select Next.
  13. (Optional) Add expressions to include a new function field to your target sheet.
  14. Select Next.
  15. Name your workflow and select Save.
  16. 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.