Create an upload workflow in Data Shuttle

Prerequisites

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

If you can access Smartsheet University, you can find this information with interactive instructions in the Data Shuttle Upload Workflows course.

Sign in to Data Shuttle

This article provides an overview of importing data to a sheet.

To learn how to upload to a DataTable, refer to Create and populate a DataTable.

Key terms

• Source: Where you import data from. For example, the source can be an XLSX file from another system you want to import into Smartsheet. 
• Target: Where you import the data to. Data Shuttle can import data to a sheet or a DataTable.
• Unique identifier: Unique identifiers are unique IDs (key column values) that must not be duplicates within a specific column on a sheet. For example, if you use a sheet to track your contacts, you might designate the Email Address as the unique identifier to avoid having the same email address appear twice. Other unique identifier examples are part numbers, student ID numbers, or employee codes.

Basics of Data Shuttle upload workflows

With Data Shuttle you can upload CSV files, XLSX files, and Google Sheets into a Smartsheet from the following sources: 

• Google Drive

• Google Sheets (for Google Sheet uploads only)

• Box

• OneDrive or SharePoint

Make sure to meet the following requirements based on your file and environment:

• CSV files and XLSX files must be attachments in a sheet in Smartsheet or reside in a Google Drive, OneDrive, or Box account that you have access to.
• Google Sheets must live in a Google Drive that you have access to.
• If your files are on a Google Drive, one of your Data Shuttle Workflow Managers must have at least Viewer access to them.

Limitations

• Sheet upload workflows are supported. You can build workflows that upload data directly into sheets. (Reports, WorkApps, dashboards, and attachments aren’t included.)
• Merge workflows support up to 20,000 rows per run.
• Replace workflows support up to 5,000 rows per run.
• Workflows can process up to 400 columns per sheet.
• Merge workflows need a unique identifier column and don't support the following:
  • System-generated columns
  • Multi-Select Dropdown columns
  • Column formulas
  • Blank cells
  • Duplicates within the column

Step 1: Create a workflow

1. Log into Data Shuttle or access the feature by going to Launcher > Data Shuttle in the left navigation bar.
2. On the left navigation bar, select  Brandfolder Image  to create a workflow.

3. Select Upload Data.

   Brandfolder Image

   

Pro tip: Copy from an existing workflow 

You can also make a copy of an existing workflow and edit it to create a new one: 

1. Hover over the workflow that you’d like to copy. Then select  Brandfolder Image  on the right side of the screen on that row.
2. Select Create a copy and give the new workflow a name.

3. Hover over the row created for the new workflow and select  Brandfolder Image  again to edit the workflow with your desired changes.

   Brandfolder Image

   

Step 2: Select a source file

Notes regarding sources

• You must have at least Editor permissions for any shared drives used as a source. For OneDrive, Smartsheet only supports connecting with personal drives.
• Data Shuttle doesn’t support open XML-based XLSX files. If you're generating your data file using an API, consider generating a CSV file rather than an XLSX file.
• Your source file shouldn't be larger than 1 GB.
   

To set a source

1. Select your Source Location. Your source file may live in one of the following locations:

   • Smartsheet Attachment: Since you’re logged in, you can search or select a file to locate the sheet where the file is attached. You must have at least Viewer permissions for the sheet. Choose whether you always want to import the most recent attachment or an attachment with a specific name.

     If you have difficulty attaching files to your sheets, contact your Smartsheet System Admin.

   • OneDrive or SharePoint: Follow the prompts to allow smartsheetapps.com to view files in your OneDrive or SharePoint account. After successful authentication, search or browse your synced OneDrive/SharePoint to locate your file.

     If you choose OneDrive as your source/target location, your SharePoint sites show on the left pane under your OneDrive files.

   • 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).
      
   • 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 your file (XLSX or CSV). You can upload Box files you own and Box files shared with you.
2. Ensure the This file has column headers box if your file contains column headers.
3. If your data doesn’t begin on row 1 or isn’t on tab 1 of your workbook, specify the First Row of the data and Tab Number.

4. Select Next.

   Brandfolder Image

   

Step 3: Select the target and workflow actions

Configure how you want results uploaded into Smartsheet.

1. Select the target — where your data is going. For this example, we cover how to upload data to a sheet.

   • If you’d like to learn how to upload to a DataTable, follow the steps in Create and populate a DataTable.
   • If you’re using Smartsheet Attachments as a source, your target sheet can be the same as your source sheet.
   Brandfolder Image

   

2. Select how you want to import the data into Smartsheet. You can only select one of these options:

   • Replace all target sheet rows with the data from the input file. This option scans the source sheet to find the first change. When a change is detected, Data Shuttle replaces all data in the target sheet from that row onward.
   • Merge data into the target based on a key column value. Note that you must have a unique identifier column to use this functionality. When you choose to merge data, you also select row options. You can select any of these combinations:

     • Add rows to the sheet as they are added to the source file. This option adds new rows for unique identifiers not already included in the target.

       Example

       If you have a list of contacts and use email addresses as the unique identifier and Data Shuttle finds a new email address on your source (for example: Hiro Senjima, hiro.senjima@mbfcorp.com) that isn’t already on your sheet, it would add a new row for Hiro and populate his details. If Hiro has two different email addresses, this could result in a situation where he is listed twice on your target sheet.

     • Update rows as the change in the source file. This is part of a true “merge” functionality. If there’s an existing row of data in your sheet for a unique ID and any changes are made to that record in the source, it updates that row in the target.

       Example

       If your Unique Identifier column is email addresses and Sally Smith (sally.smith@mbfcorp.com) changes her phone number to (111) 222-3333, Data Shuttle would look for the row where the email address is sally.smith@smartsheet.com and update the phone number for this row.

     • Delete rows that no longer match the filter criteria. This option deletes rows that no longer match the filters you set up in the next step.

       Example

       If you set a filter to only import records from your source where the Location column has the value of United States and Hiro Senjima moves to Canada, this would delete the row with Hiro’s data from your target sheet.

   • Update the dropdown choices for the selected columns. This option replaces the dropdown column options in your target sheet with values within your Excel or CSV file. Use this feature to create dynamic dropdowns with Data Shuttle.
   Brandfolder Image

   

Step 4: Build filters (optional)

• If you don't want to build a filter, select Next and continue to Step 5.

Creating filters is optional, but you can use them to determine the data you want to bring into Smartsheet. For example, you can create a filter to import rows with a value of In Progress in the Status column.

Filters are helpful when working with large data sets that exceed the sheet limit of 20,000 rows. For data sets that exceed sheet limitations, consider using DataTable. For more information on sheet and imported file size maximums, see Smartsheet System Requirements and Guidelines.

Filters are case-sensitive. Ensure they're an exact match to the values in your file.

1. On the Filter tab, select Add another filter.

   Brandfolder Image

   
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 you’ve created with the AND or OR operators. 

   • Use the filter IDs, which are the numbers next to each filter, to define relationships between them.

     Brandfolder Image

     
4. Select Next.

Define more complex logic

You can define more complex filter logic with parentheses and the AND or OR operators.

Example

(1 AND 2) OR 3 combines 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 source and target

Designate which columns from the source file should map to the target columns. Here are a few things to note:

• Your source and target column names don’t need to match.

• If there are column names from the source that match the column names in the target sheet, they map automatically. You can add or edit any of these mappings. 

• You don’t need to map all columns of the source or target. 

• You must select the Unique Identifier column if you merged data into the target based on a key column value. Refer to the key terms for more information.

• If you experience issues with formatting of your fields after import, you can select the Column Type formatter to change the column format. This option is a hidden column menu between Source field and Sheet Column.

• If you modify the underlying sheet, select the Refresh button to update the source and target columns. Refreshing overwrites any manual changes you make, so ensure your columns are mapping correctly.

When mapping, you see the columns of your source file and the sheet columns.

1. Select the dropdown on the Source field column and choose the correct mapping for the Smartsheet column on the right side.

2. After you've completed all mapping, select Next.

   Brandfolder Image

   

Step 6: Schedule your workflow

There are two ways to schedule your workflow to run automatically:

• Run on attachment
• Run on schedule

You can choose one, both, or neither set. You must run your workflow manually if you don’t choose a setting.

Brandfolder Image

Run on attachment

Use this option if your source is a Smartsheet attachment. Note that you can't use it if the target is DataTable.

When active, every time someone adds a new attachment to the source sheet, the workflow runs if the new attachment fits the criteria. The criteria for matching depend on whether it aligns with the indicated name you set on the source page. If you select Most Recent, it consistently imports new data if the columns in your attachment match the column names in the mapping step.

Run on schedule

If you select Run on attachment, you can still choose Run on schedule. Note that you can also activate them separately.

When you enable Run on schedule, Data Shuttle looks at the source location on a recurring basis and automatically updates the target sheet with any changes found. Source locations include shared drives such as Google Drive, OneDrive/SharePoint, and Box.

1. Toggle Run on schedule to on. 
2. To set up the schedule, fill in the following fields:
   • Days of the week: Days you want to run the workflow 
   • Start time: What time you want the workflow to run
   • Execute every: The frequency of the workflow schedule. For example, every 15 minutes.
   • End time (UTC): What time you want the workflow to stop running

3. Select Next.

   Data Shuttle uses UTC.

Run on manual

This option isn't selectable, but you can manually run any Data Shuttle workflow once it's built, even if it has Run on attachment or Run on schedule set.

Once you publish a workflow, you can manually run your workflow from the dashboard by hovering over the desired workflow and selecting Run.

You can see the workflow execution status in the dashboard's Last Run (UTC) column.

Brandfolder Image

Step 7: Use input expressions (optional)

As an optional step, you can use input expressions to apply a Smartsheet formula to data brought into your sheet via Data Shuttle. You may often forgo this step and instead create column formulas once you’ve uploaded the data to your target sheet.

Example

Let’s say you have several source files feeding into a sheet in Smartsheet. You want to distinguish which data source a particular row of data comes from. If one file comes from Netsuite and another from Oracle, you can use an input expression to identify the source during the Data Shuttle upload.

Tag the data with the source location information

1. Your target sheet must already contain the column you use for the input expression. In this example, the column is Source.

   Brandfolder Image

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

Brandfolder Image

3. On the Expressions tab, select Add another expression.
4. Enter the new column name for the Unique Field Name. This example uses Source.

5. Enter a formula in the Function Syntax field. This example uses =”Netsuite”.

   Brandfolder Image

   

6. Return to the Mapping tab and map this expression field to the corresponding column. For this example, Source maps onto Source.

   Brandfolder Image

   

After you finish building and publishing the workflow, the formula is placed into the uploaded rows when Data Shuttle runs.In this example, when you select a cell in the Source column, you see the =”Netsuite” formula.

Brandfolder Image

You can use other Smartsheet functions as expressions, including those referencing values in different columns.

As some examples, you could use:

• =TODAY(). This formula would be placed in the TODAY() function, updating whenever someone opens the sheet.
• =RIGHT(Location@row,3). This formula would pull the right three characters of the string in the Location column and corresponding row.

Step 8: Share your workflow

You can share your workflows with other users in your Smartsheet plan. When you share a workflow, those users become Workflow Managers. As Workflow Managers, they can edit, run, archive, and share the workflow as long as they have at least Editor permissions on both the source and target files. Admins can also add or remove Workflow Managers for any workflow in their plan.

1. In the Sharing settings tab, type the user name you want to share the workflow with in the text field provided.
2. Select Next.

Brandfolder Image

Step 9: Publish your new workflow

1. Finish your work on the Input Expressions page and select Next.

2. Enter a name for your workflow and select Publish. You can now access your workflow on the Data Shuttle dashboard anytime you wish to run, edit, or delete it.

   • Setting the Data Shuttle workflow to Run on a schedule runs it at the designated time.
   • If you chose Run on attachment or neither option, you may need to run the workflow manually by selecting Run when hovering over your workflow in the Data Shuttle dashboard.
   Brandfolder Image