Sync Smartsheet and External Systems With Data Uploader

Automatically merge or replace data from a third-party application, like Excel, into Smartsheet without the need to manually copy and paste. The Data Uploader works with Excel files, or virtually any CRM, ERP, LDAP, database or project management tool that can export to .csv. 

With the Data Uploader, you have the ability to:

  • Update existing rows and insert new rows in Smartsheet from other applications that can export .csv, Excel, or Google Sheets
  • Replace all rows in an existing sheet in Smartsheet with the latest uploaded file
  • Schedule workflows to run as often as necessary to meet business needs

This is a premium add-on that is available for purchase. For more information about how to obtain Smartsheet Premium Apps, please contact Smartsheet Sales.

Before You Begin

Data Uploader can leverage data from other systems that allow exporting to Google Sheets, Excel, or comma separated values (.csv) text files. It can also consume data from files in Google Drive.

To begin creating your first Data Uploader workflow, you'll need to have the data that you want to sync in one of the following locations:

  • The file (.csv or .xlsx) file must be attached to a sheet in Smartsheet
    -or-
  • The file (.csv, .xls, or Google Sheet) must be located in Google drive in an account to which you have access

Connect the Data Uploader App With Smartsheet

Click the Launcher to access the App.

Smartsheet Launcher

 

If you have trouble accessing the app from the Launcher, please see Access Premium Apps and Templates With the Launcher.

That’s it! Once you’re logged in, you’ll see the Data Uploader dashboard screen, which will show you any available workflows that have been created under your account.

Create a Data Uploader Workflow

Create a new workflow by clicking the Create New button in the upper-right corner of the Data Uploader Dashboard.

create new

Name and Configure Source Input for Your Workflow

  1. In the Name box, type a descriptive name for your workflow (so that it's easy for you to remember what the workflow does later on).
  2. Select the one of the following source locations:
    • Smartsheet Attachment—Use this option if the file you want to link to is attached to a sheet in Smartsheet. Your account will already be authenticated and 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, more specifically, an attachment by a specific name:
      • Attachment Name—The Data Uploader will only use the attachment that is specified in your workflow. The attachment name is case sensitive and will find an exact match in your sheet.
      • Most Recent—Your latest attachment will be synced, which is based on the schedule set you’ve set for your workflow, or on the latest manual run of your workflow on the Data Uploader dashboard.
         
    • Google Drive—Use this option if the file that you want to sync is stored in Google Drive.

      If you choose this option, you'll be prompted to allow smartsheetapps.com to view files in your Google Drive account to use for the Smartsheet Data Uploader. After authentication is successful, you'll be able to search or browse your synced Google Drive to locate the file (.xls, .csv, or Google Sheet) that you would like to use with Data Uploader.
       
    • OneDrive—Use this option if the file that you want to sync is stored in Microsoft OneDrive.

      If you choose this option, you'll be prompted to allow smartsheetapps.com to view files in your OneDrive account to use for the Smartsheet Data Uploader. After authentication is successful, you'll be able to search or browse your synced OneDrive to locate the file (.xls, or .csv) that you would like to use with Data Uploader.
       
    • 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 that you want is to be merged (the first sheet in the Excel workbook is  tab 1).
       
    • Click Next to move to the next step of the workflow.

      NOTE: When you've entered all the necessary information for a particular stage of the workflow setup, you'll see a green check mark next to that stage in the left panel. 

    Select a Target Sheet and Upload Action

    In this step of the workflow, you will configure how you would like the results uploaded into Smartsheet.

    1. Select the target sheet to be updated. (If you are using Smartsheet Attachments as a source, this can be the same sheet or a different sheet from the source sheet.)
    2. Choose how you want to import the data into Smartsheet:
      • Replace—Delete all existing data and replace it with the newest data

        IMPORTANT: Choosing replace will delete all context added including cell links.
         
      • Merge—Updates data based on a primary key with an option for you to insert new rows. If you choose this option, you'll also need to select whether to add new rows.
         
      • Update Dropdowns—Replace Smartsheet dropdown values in Column Properties, from values within your Excel or .csv file

    Set Filters to Control Which Input Rows Are Imported

    In the left panel of the workflow wizard, select Filters to define which data should be imported into Smartsheet based on certain criteria (for example, Status equals “In Progress”).

    Use filters if you are 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 the Filter Logic box empty to start.
    2. To create a filter, click Create New.
    3. Select the column from the file to filter on, the comparison operator, and the value.

      TIP: As you create multiple filters, you can use the Filter Logic box to connect each filter together with additional AND/OR operators.

      Use the filter IDs (the number below the # sign of the Input Filters section) as you 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.

    Map Columns Between the Two Systems 

    In this step of the workflow, all Smartsheet columns will be pre-filled and you will be able to choose the desired Input columns from your source file. You also can create custom expressions, which can contain a constant value or formula used in each row, instead of using data from the input file.

    column mapping

    • Input Column—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.

      NOTE: Data Uploader will automatically map input columns to Smartsheet columns if the column names are exactly the same.
       
    • Expression—To set a formula for the target sheet, you can build an expression with the parameters of the formula. Scroll to the bottom of the available Columns/Expressions and click </> New Expression. A form will appear where you can enter a static value.

      TIP: Create the formula in the target sheet with no references to other rows, then copy and paste into the expression builder.

      NOTE: Expressions can be created from the Mapping section of a workflow, and can be created or modified from the Expressions section, which is the last step of the Data Uploader workflow.

    Schedule Your Workflow

    Click Schedule in the left panel to set how often you'd like your workflow to run.

    Run On Attachment—Uses Smartsheet Attachment as the file source. As long as this is 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 for the Smartsheet Attachment option and will not be visible when Google Drive is used as the source location.

    Run on Schedule—To run the workflow on a schedule, click Create New. Select the desired option under Days of Week, Start Time, Repeat Interval, and End Time. Once you’ve selected the desired schedule, switch the the Run On Schedule button to on.

    View All Expressions Used in Mappings

    In this step of the workflow, you can view any Input Expressions that were created in target column mappings. You can also create new expressions from this screen and use them in your current mappings.

    Values that begin with an equal sign are formulas.

    Review and Finalize Your New Workflow

    When all steps of the workflow are complete (marked with a green checkmark in the left panel), you can click Close at the bottom-right of your workflow. This will save and store your new workflow. on the Data Uploader Dashboard. The changes should now be reflected on the related sheet.

    When you click Next, your workflow will automatically be saved.

    Edit, Manually Run, or Delete Workflows 

    You can manage your workflows from the main Data Uploader screen.
     

    • Edit Workflow—You can make changes to an existing workflow (for example, you would like to change the frequency of a workflow schedule).

      NOTE: If there was an error on the last run of the workflow, or there is an error with configuration of the workflow (the source sheet has been deleted), the Last Run section and Edit icon will appear red.

      Edit Workflow Red Icon
       
    • Execute workflow—You can manually run your workflow to check for any new changes.
    • Delete workflow—Deletes your workflow if no longer needed.
    • View Log Messages—With the View Log messages button, you can see a log of errors and how many rows were updated, added, or skipped.

      TIP: You can also click the Download All Logs button, which will download a .csv file to your machine containing all Data Uploader syncs for this workflow.

    Edit and Add New Columns

    You may need to change the structure of your source file as your process evolves. When this occurs, take the following steps to ensure that this structure is maintained in your target sheet:

    1. Temporarily disable your workflow.
    2. Edit your source file with your new column headers or additional columns.
    3. If using an Excel or CSV file, upload the new source file to your source location. 
    4. Edit the target sheet with updated column headers or additional columns.
    5. In your Data Uploader workflow settings (Source > Source Location), select a different source location.

      (For example, if your source is Google Drive, switch to Smartsheet.)
       
    6. Then switch the source location back to your original selection.

      NOTE: You may need to reselect your Attachment Sheet or File Name.
       
    7. Click the Next button until you get to the Mapping page. Here, you can map all of your Input Columns and Expressions again.

      NOTE: You may need to map all of your columns again, not just the columns you added and edited.

    That’s it! You can now enable and run your workflow.


    Tips and Best Practices

    Keep the following in mind as you work with Data Uploader:

    • With Data Uploader Smartsheet limitations still apply.  For more information on sheet and imported file size maximums, see Smartsheet System Requirements and Guidelines.
    • If you need to pull data from a source that contains more than 5,000 rows, you can use filters and a limited set of column mappings (as described below) or you can create multiple workflows using a common data category to distribute the data into multiple Sheets.
    • If someone builds a workflow and sets it to run every time a new attachment is added, anyone who adds the attachment will cause the workflow to run.
       
    Was this article helpful?
    YesNo