Create an upload workflow in Data Shuttle

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

Log in to Data Shuttle

Prerequisites

Data Shuttle is a premium application. To check your access level, log in at datashuttle.smartsheet.com.

To use Data Shuttle you must:

• Be a Licensed User
• Have Owner, Admin, or Editor permissions on the target sheet
• Have Data Shuttle Premium Application permissions enabled in User Management by your Smartsheet System Admin.

Step 1: Create an upload workflow

1. Log in to Data Shuttle.
2. In Data Shuttle, on the left Navigation Bar, select the plus icon. 
3. Select Upload Data > Next.

Copy an existing workflow to save time in creating workflows

You can copy a workflow to use as is. You can also edit a copied workflow to make a new one without starting from scratch. To copy an existing workflow:

1. Go to the tab where the workflow is located.
2. Hover the pointer over the workflow. 
3. Select More . 
4. Select Create a copy.

Once you've created a copy of the workflow, you can edit it. To edit a workflow:

1. Hover the pointer over the row of the workflow copy.
2. Select More . 
3. Select Edit workflow.

Once your upload workflow template is ready, you can edit the details.

Step 2: Select a source file 

1. Locate your source file in one of the following locations:
   • Smartsheet Attachment: Because you're logged in, you can search for or browse to 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 is 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 successful authentication, search or browse your synced Google Drive to locate the file (.xlsx, .csv, or Google Sheet) you want to use.
      
   • OneDrive or Sharepoint: Follow the prompts to allow smartsheetapps.com to view files in your smartsheetapps.com to view files in your OneDrive or Sharepoint account. After successful authentication, search or browse your synced OneDrive/Sharepoint 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 successful authentication, 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 with you.
2. 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).
3. 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.

Step 3: 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 or different from the source sheet.
   
   If you have difficulty attaching files to your sheets, please contact your Smartsheet System Administrator regarding your organization's attachment settings.
   
   Your source file should be no larger than 1GB. Anything larger will likely time out.
   
   You can also upload data to an existing DataTable via Data Shuttle. Learn more about using DataTable here.
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 previous 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.

Step 4: Set filters to control which input rows are imported

Use filters to define which data the system should push into Smartsheet. For example, you can create a filter to import only rows with In Progress as the value in the Status column.

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

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

To set filters:

1. On the Filter tab, select Add another filter.
2. In the filter setup, select an input column, a comparison operator (for example, equals or contains), and the value.
3. In the Filter logic box, connect each filter with the AND or OR operator. 
   • To define relationships between each filter, use the filter IDs (the number next to each filter).
     
4. Select Next.

Define more complex logic

You can define more complex filter logic using parentheses and 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.

Step 5: Map columns between the two systems 

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

1. 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.
2. Select a column type to make sure your fields are correctly formatted. 

If you've changed the underlying sheet, select Refresh to update the mapping. Refresh will overwrite any manual changes you've made, so double-check to make sure your columns are mapped correctly.

Step 6: Schedule your workflow

There are two ways to schedule your Workflow to run automatically; Run on Attachment or Run on Schedule. If you do not need your data to update on a set cadence, you can also run your workflow manually at any time via the dashboard.

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 and when the Target is not DataTable. 

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. You can add multiple scheduling options. 
3. Click Next.

Data Shuttle uses GMT.

Run on manual

Skip this step if you do not need your workflow to run on a set schedule. To manually run your workflow, select Run from the dashboard. If you decide later that you would like a workflow to start running on a set schedule, you can edit the workflow to add the schedule. Workflows running on a schedule on 'On Attachment' can also be run manually at any point.

Step 7: Create input expressions

Input expressions in Data Shuttle are a way to apply a Smartsheet formula to data brought into your sheet via Data Shuttle. 

For example, let's say you had several source files feeding into a sheet in Smartsheet. You want to distinguish which data source a particular row of data had come from. 

If one file was coming from Netsuite and another from Oracle, you can use an Input Expression to identify the source during the Data Shuttle upload. 

To tag the data with the source location information: 

1. Create the sheet where the data will be uploaded via Data Shuttle and include a column that will display the source location, for example, Source.

2. Save your sheet. 
3. Skip the Source column in the mapping step when you build your Data Shuttle workflow. 

4. On the Expressions tab, select Add another expression.
5. Enter the new column name for the Unique Field Name. This example uses Source.
6. Enter a formula in the Function Syntax field. This example uses =”Netsuite”

7. Return to the mapping tab and map this expression field to the corresponding column. For this example, Source to Source. 

8. Finish building your Data Shuttle Workflow. 

When the workflow runs, it will place the formula into the uploaded rows. In this example, when you select a cell in the Source column, you will see the =" Netsuite" formula. 

You can use any other Smartsheet functions as expressions, including those that reference values in other columns. 

As some examples, you could use:

• =TODAY() [this would place in the TODAY() function, which would update any time the sheet was opened]
• =RIGHT(Location@row,3) [this would pull the correct three characters of the string in the "Location" column and corresponding row]

Step 8: Publish your workflow

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