Create an Upload Workflow in Data Shuttle

Applies to

Smartsheet Advance Package (Requires Enterprise Plan)

Capabilities

Who can use this capability

Anyone with active Smartsheet license with any paid user role, internal permissions to use cloud drives where your data is located and Admin, Owner, or Editor access on the target sheet can create and own Data Shuttle workflows. A license is required to own any source and target Smartsheet used in Data Shuttle configurations.

Create an upload workflow  to connect an external data source and upload, edit, and write it to to Smartsheet.

You can copy an existing workflow and then edit it to create a new workflow. Hover over the menu next to the Run button, select Create a copy. Hover over the menu on the copy, click Edit workflow, and make your changes in the copy. 

Create an upload workflow

  1. Click the Launcher on the left Navigation Bar and then select Data Shuttle. If this is your first time using Data Shuttle, you may need to first log in at datashuttle.smartsheet.com.
  2. Select your source file. Your source file can be in one of the following locations:
     
    • Smartsheet Attachment:  Because you’re logged in,  you can search or browse to find the sheet where the file is attached. Choose whether you want to import the most recent attachment every time the workflow runs or, an attachment with a specific name:
      • Most Recent:  Your latest attachment sync based on the schedule in your workflow. Or, you can run the workflow manually. 
      • Attachment Name:  Data Shuttle will only use the attachment specified in your workflow.
         
    • Google Drive: Follow the prompts to allow smartsheetapps.com to view files in your Google Drive account. After authentication is successful, search or browse your synced Google Drive to locate the file (.xlsx, .csv, or Google Sheet) you want to use.
       
    • OneDrive: Follow the prompts to allow smartsheetapps.com to view files in your smartsheetapps.com to view files in your OneDrive account. After authentication is successful, search or browse your synced OneDrive to locate the file  (.xlsx, or .csv) you want to use.
       
    • Box:  Follow the prompts to allow smartsheetapps.com to view files in your Box account After authentication is successful, search or browse your synced Box to locate the file (.xlsx, or .csv) you want to use. You can upload Box files you own and Box files shared to you.
       
  3. Use the Does the input file have column headers toggle to confirm whether the data source includes headers.

    Excel files only: Indicate which sheet tab contains the data you want to merge (the first sheet in the Excel workbook is  tab 1).
     
  4. Click Next

Data Shuttle does not support OpenXML-based .xlsx files. When generating your file data using an API, consider generating a .csv file rather than an .xlsx file.

Select a Target Sheet and Workflow Actions

Configure how you want results uploaded into Smartsheet.

  1. Select the target sheet -- where your data is going. If you are using Smartsheet Attachments as a source, this can be the same sheet or a different sheet from the source sheet.
  2. Select how you want to import the data into Smartsheet:

    Replace all Target sheet rows with the data from the input file. IMPORTANT: This option completely overwrites your data. Your target location may store previous versions so you can recover prior offloads as needed.

    or

    Merge data into the target based on a key column value. When you choose to merge data, you will also select row options:
    • Add rows to the sheet as they are added to the source file.
    • Update rows as the change in the source file.
    • Delete rows that no longer match the filter criteria.
  3. Update the dropdown choices for the selected columns. This will replace Smartsheet dropdown values in Column Properties with values within your Excel or .csv file.

Set Filters to Control Which Input Rows Are Imported

Filters define which data should be imported into Smartsheet based on certain criteria (for example, Status equals “In Progress”).

Filters are also useful in working with large data sets that may cause the import to exceed Smartsheet row limits. For more information on sheet and imported file size maximums, see Smartsheet System Requirements and Guidelines.

  1. Leave  Filter Logic empty. You can order your filters after you create them. 
  2. In the filter set up, select an input column, a comparison operator (for example equals or greater than), and the value.
  3. Use the Filter Logic box to connect each filter together with additional AND/OR operators.

    Use the filter IDs (the number next to each filter) to define relationships between each filter.

    You can define more complex filter logic using parentheses along with AND or OR. For example, (1 AND 2) OR 3 combine logic to include all data where the (Department = “Services” AND Widget Size = “Large”) OR the Department = “Marketing.” In this example, only large widgets for the services department would be imported but all marketing widgets would also be imported.

Filters are case sensitive. Make sure they are an exact match to the information in your file.

Map Columns Between the Two Systems 

Data Shuttle column mapping interface

Smartsheet columns are pre-populated; you select the Input columns from your source file. 

  • To manually map a column, click the dropdown next to the input column, scroll, and choose the correct mapping for the Smartsheet column indicated on the right side.

Schedule Your Workflow

There are two ways to schedule your Workflow to run automatically; Run on Attachment or Run on Schedule. You can use one, both, or neither and run the Workflow manually. 

It’s best to allow more than six hours between scheduled runs. More frequent runs can: 

  • Create a very high amount of writes to their cloud source. 
  • Create multiple versions that are hard to differentiate. 
  • Cause errors when the workflow attempts to access files in use. 

Run On Attachment

When enabled, any time a new attachment is added to the source sheet, the workflow will assess and run if the new attachment fits the criteria. Run On Attachment is only available when your Workflow uses Smartsheet Attachment as the source. 

Keep in mind, if you build a workflow and set it to run every time a new attachment is added, anyone who adds the attachment will cause the workflow to run.

Run on Schedule

  1. Toggle Run on Schedule to on. 
  2. Select your options under Days of Week, Start Time, Repeat Interval, and End Time. Once you’ve selected the desired schedule, switch Run On Schedule to on. You can add multiple scheduling options. 
  3. Click Next.

Data Shuttle uses GMT.

Run on Manual

If you leave this step blank, you can run your workflow at any time. Go to the workflow and click Run.  

Create Custom Expressions

Expressions allow you to create a formula in your workflow; the formula runs when you run the workflow. For example, an expression could price and sales tax and output them as a new column on the target sheet. 

This is an optional step; it may be better to use column formulas in the target sheet instead. Expressions can be useful when you need to know which rows were added or updated by Data Shuttle.
 

  • To set a formula for the target sheet, create the formula in the field provided. You may find it easier to create the formula in the target sheet with no references to other rows, then copy and paste into the expression builder. For more information about using @row, see Create Efficient Formulas with @cell and @row.
  • Click Add Another Expression to add more rows. 

Name Your New Workflow

Enter a name for your Workflow and click Next. You can now access your Workflow anytime you want to run, edit, or delete it.