开始

Overview

The Smartsheet Live Data Connector (also known as the Smartsheet ODBC Driver) provides industry-standard connectivity between Smartsheet and third-party analytics tools. It enables business users to interact with and visualize data stored in Smartsheet using business analytics and data visualization software such as Tableau, Spotfire, QlikView / Qlik Sense, Excel and others.

Benefits of using the Live Data Connector include, but not limited to, the following:

  • Industry-standard ODBC-based connectivity to Smartsheet. The Connector is compatible with version 3.8 of the ODBC specification.
  • Smartsheet-wide access control (ACL) is enforced and data access and retrieval via ODBC is subject to all Smartsheet’s existing security mechanisms.
  • Interact with Smartsheet data dynamically, using Smartsheet as a live data source.
  • Create charts, graphs and other visualizations with data stored in one or more Smartsheet sheets or reports.

快速启动

This section provides a fast track to getting you up and running with the Smartsheet Live Data Connector as quickly as possible.


Download the installer Run the installer Configure your software
  • Tableau Desktop
  • Tableau Server
  • Tibco Spotfire
  • Qlik Sense
  • QlikView
  • Excel
    		•


    Requirements

    In order to use the Live Data Connector you will need:

    • Live Data Connector license (included in some plans - seepricing)
    • A Smartsheet license
    • Microsoft Windows 7, Windows 8, or Windows Server 2012

    Additionally, to use the driver you will need access to at least one of these products:

    • Tableau Desktop Professional Edition 8.2/8.3/9.x or Tableau Server 8.3.3/9.x
    • QlikView 11.2, Qlik Sense 1.1
    • Tibco Spotfire Desktop, Server, Web Player 7.0.1+
    • Microsoft Excel 2010, 2013, 2016 - Data Connection Wizard, MSFT Query, Power Query
    • Microsoft Access 2013 and 2016

    Support

    If you experience any difficulties, have questions or feature requests, please visit theSmartsheet Help Center．

    如果你有技术问题,请提供detailed information on the issue to our Support team at ourSupport Contactpage. To best assist you with the issue, we will likely need the following information:

    • Screenshot of the error message
    • Exact steps to reproduce the issue
    • Attach a copy of logs generated by the Live Data Connector (follow the steps inthis sectionto enable logging.)

    Download

    Download the most recent installer:

    Configuration

    Windows Proxy Configuration (optional)

    Some organizations use aproxy serverto access web content. If you are using a proxy to access web content, the Smartsheet Live Data Connector will use your proxy server as well. You can check your proxy settings fromInternet Optionsin your Control Panel.

    By default the Smartsheet Live Data Connector will use the Windows / Internet Explorer’s proxy settings for the currently logged-in user. You can find more information onoverriding proxy settingsin theAdvanced Options Menusection.

    To check your settings, select theConnectionstab, and then click onLAN settingsat the bottom of the window:



    … and if you have a proxy configured, you will see it in your LAN settings (example):



    The Smartsheet Live Data Connector will use either the automatic configuration script in theAutomatic configurationgroup box or the proxy server address in theProxy servergroup box as valid proxy server configurations.

    If the Smartsheet Live Data Connector is running on a Windows Server where it is not impersonating a logged-in user (for example when it is running under the Tableau Server service) only the system default proxy settings will be used. Ask your Network Administrator for more information onconfiguring Windows Server for proxy access．

    Enabling logging (optional)

    If you run into any issues with the Connector, we may ask you to send us your log file to help identify and address the problem. Enabling logging simply creates a text file on your computer and logs the Connector activity.

    To enable logging, please locate the logging utility by navigating to:

    1. Start>Programs>Smartsheet Live Data Connector
    2. Click onLive Data Connector Settings



    1. Click onDriver Properties
    2. CheckEnable logging
    3. Click onFolder…to specify the log destination:



    Pick a destination folder and clickOK:



    Once the destination is selected, clickOKto finish:



    Configuring a Smartsheet DSN (optional)

    If you need to set up a Data Source:

    1. Navigate toStart>Programs>Smartsheet Live Data Connector
    2. RunODBC Data Source Admin



    ClickAdd…to add a data source:



    SelectSmartsheet Live Data Connectorand clickFinish:



    …and then fill in the details. If your Smartsheet account does not permit using email and password for login, you can use theUse API Token for SSO loginoption instead.



    Windows 10

    If you are usingWindows 10, you may have noticed that the option for the ODBC Data Source Admin in your Start Menu is missing. Please follow the steps below to add the Smartsheet DSN on Windows 10.

    在你的任务栏,点击searchbox and type inControl Panel:



    Once the Control Panel opens, click onSystem and Security:



    Scroll down and click onAdministrative Tools:



    Double click on theappropriate shortcut that matches the driver version you installed(32bit or 64bit):



    After that, you’ll see theODBC Data Source Adminwindow appear, and you can head back to theprevious sectionto create theSmartsheet DSN．

    Advanced Options Menu

    The Advanced Options menu provides controls to include additional row metadata, manage data conversion modes, enable driver logging, and manually override proxy settings.

    The menu can be accessed either at the time of using the Connector (e.g., from within Tableau):



    …when setting up an ODBC Data Source:



    …or by navigating toStart>Smartsheet Live Data Connector>Live Data Connector Settings:



    TheAdvanced Optionsmenu (calledLive Data Connector Settingsin the Start Menu) has options for including extended metadata (such as Gantt attributes, outline level, and discussions text) as additional columns. It also provides controls for advanced features and logging.





    The following tabs and options are available in this menu:

    1. Connection Properties
      • Optional Content - extended metadata as additional columns
      • Data retrieval mode
      • SQL table names
    2. Driver Properties
      • Enabling logging
      • Report cache - auto refresh settings
      • Manually Override Proxy Settings

    Data retrieval mode has 3 options:

    • Always return text- returns all results as text
    • Usemajorityrule - returns numeric results only if more than half the column inputs are numeric, discarding all non-numeric input
    • Useentiretyrule - returns numeric results only if 100% of the column inputs are numeric, otherwise returns all results as text

    The SQL table names section is available to shorten names. By default the full path is appended to the sheet name. Unchecking this option will cause only the sheet/report name to be returned.

    Custom SQL Functions (optional)

    In the Advanced Options menu, scroll down to the bottom of theOptional contentlist and you will see anEnable custom SQL functionscheckbox:



    Checking this option will enable the following SQL functions:

    Function Tableau Example
    attachments_count() -returns integer RAWSQL_INT(‘attachments_count(%1)’,[_Rowlocator])
    discussions_count() -returns integer RAWSQL_INT('discussions_count(%1)’,[_Rowlocator])
    discussions_text() -returns text RAWSQL_STR('discussions_text(%1)’,[_Rowlocator])
    parent_id() -returns text RAWSQL_STR('parent_id(%1)’,[_Rowlocator])
    child_ids() -returns text(comma separated values) RAWSQL_STR('child_ids(%1)’,[_Rowlocator])
    outline_level() -returns integer RAWSQL_INT('outline_levle(%1)’,[_Rowlocator])
    sheet_id() -returns text RAWSQL_STR('sheet_id(%1)’,[_Rowlocator])
    sheet_name() -returns text RAWSQL_STR('sheet_name(%1)’,[_Rowlocator])
    predecessor_ids() -returns text(comma separated values) RAWSQL_STR('predecessor_ids(%1)’,[_Rowlocator])
    predecessor_labels() -returns text RAWSQL_STR('predecessor_labels(%1)’,[_Rowlocator])
    duration_seconds() -returns double RAWSQL_REAL('duration_seconds(%1)’,[_Rowlocator])
    workspace_id() -returns text RAWSQL_STR('workspace_id(%1)’,[_Rowlocator])
    workspace_name() -returns text RAWSQL_STR('workspace_name(%1)’,[_Rowlocator])

    Column Type Conversion Logic

    The connector uses the following logic to process and convert Smartsheet data:

    • Text/Number: The Text/Number column defaults to the majority rule. This means that numeric values are returned if more than half of the column inputs are numeric. All other values are discarded. This rule can be changed in theAdvanced Optionssection during login.

    • Date, time, anddatetime: Date, time, and datetime columns have a storage class of TEXT, which means they are not converted to a date until a query is ran.

    • Picklists: Picklists probe for types but they use a precise rule (100%). So, one text string in a list of integers would cause the picklist to display text. All of the state picklists resolve to text (5-State Signal, 6-State Hearts, etc..).

    • Checkbox: Checkbox (or any on/off column) values are stored as a Boolean and uses an integer storage class. Any text data in a checkbox column would be lost. An exception to this rule would be a list to what equates to Boolean data (on/off, true/false, 0/1) i.e. Stars and Flags. These would be stored in a Boolean and the same rules apply.

    Usage

    Tableau Desktop

    Connect to the Smartsheet Data Source by choosing theTo a server>Other Databases (ODBC)option. If you do not see the ODBC option listed here, click on theMore Servers…and choose it from the list from additional options:



    UnderConnect UsingpickDriver, and then selectSmartsheet Live Data Connectorfrom the Driver drop-down menu:



    … and clickConnect．

    Connect using Email Address and Password

    Connect to Smartsheet by entering your Smartsheet credentials into theSmartsheet Logindialog box:



    Connect using API Access Token (for SSO users)

    First, you need to create a token - it’s easy with just a couple of clicks.

    In Smartsheet, navigate toAccount>Personal Settings:



    In Personal Settings, selectAPI Accessand click onGenerate new access token:



    Give the new token an arbitrary, descriptive name and clickOK:





    Now go back to yourSmartsheet Loginwindow and click on theUse API token for SSO loginoption:



    … and paste your access token:



    Upon successful login, confirm connection information and clickOK:



    Creating Workbooks and Reports

    Browse or search Sheets. You can search by Sheet or Report name, or by any portion of their path (name of parent Workspace or Folder).



    Then drag and drop each Sheet or Report you need to the upper right pane:



    If you are using multiple sheets or reports, and they have columns in common, Tableau will try automatically figure out how to map the selected Sheets and will create a default relationship between them. You can modify the type of relationship (JOIN) at this time.

    Once you are done, clickGo to Worksheetat the bottom of the application to proceed to the visualization design step:



    The next step is to design and configure your report:



    Finally, use Tableau Dashboard to create a compelling visual representation of the data:



    Re-opening Saved Workbooks

    Tableau Desktop does not save login credentials by design (for security reasons), so the next time you launch Tableau Desktop and re-open a previously saved workbook, in Tableau 8, you will see an error message:



    ClickYesand re-enter your Smartsheet password. Or, if you are using anAPI Access tokento connect, paste the token into the password field instead:



    In Tableau 9 you will see a different message, re-enter your login credentials then clickOK．Or, if you are using anAPI Access tokento connect, paste the token into the password field instead.



    Tableau Server

    Installation

    The process for installing the Smartsheet Live Data Connector is the same for both server and desktop. You can visit theDownloadsection to find the latest installer packages.

    聚氨酯blishing Workbooks to Tableau Server

    A typical Tableau Server workflow starts withcreating a workbook / report in Tableau Desktop．Once you create a workbook in Tableau Desktop, you can publish it to a Tableau Server for others to use.

    Open the服务器menu option and select聚氨酯blish Workbook…:



    If you are not already logged in to your Tableau Server, you will be prompted to do so.

    Enter your server:



    Then enter your login credentials:



    Once logged in, at the next step you will have an opportunity to name the workbook, specify where to save the new workbook on the server, and manage the permissions and access for the published workbook. In terms of access to workbook data, you have two options:

    1. Have users provide their own credentials (which requires they have a Smartsheet login and have permissions in Smartsheet to access the data you included in your workbook).

    2. Access the data you have published without having to provide Smartsheet credentials.

    The second option is likely to be the most common one．表服务器可以存储你的login credentials with your published workbook. To choose this option, click theAuthentication…button:



    At the next step, you may have to scroll to the right to see the Authentication options:



    Finally, in the Authentication dropdown, selectEmbedded password:



    To save and finish, clickOK．

    Tibco Spotfire Desktop

    Spotfire installation instructions can be foundhere．Additional resources for using Spotfire can be foundhere．


    Launch Spotfire and then navigate toAdd Data>Add Data Connection>Other:



    Click onAddthen select数据库……at the bottom of the list:



    ChooseOdbc Data Providerthen clickConfigure…:



    Select theDSN you configured earlier．Fill in your Smartsheet credentials to log in. Or, if you are using anAPI Access token连接,将电子邮件字段留空,粘贴the token into the password field instead:



    Select a Table and all columns needed, set a Data Source Name, then clickOK:



    Select the Data table you just set up and clickOKto import the data (the Name will be the same as theData source nameyou entered in the last step):



    Finally, use Spotfire to create a nice visualization of the data:



    Tibco Spotfire Server

    Connect using Email Address and Password

    When you open Tibco Spotfire Analyst, you’ll be prompted to log in and select a Server. If you haven’t configured it already, click onManage Servers…:



    On the Manage Servers window, click onAdd:



    On the next screen, enter theserver addressfor your Tibco Spotfire server:



    开放from Spotfire Library

    Click on theAdd Datatab, then click onOpen from Library:



    Select a file from the list, then click onOpen:



    Saving to Spotfire Library

    You can save any changes back to the Spotfire library by clicking onFile>Save As>Library Item…:


    Spotfire Web Player

    To access your content on the Spotfire Web Player, you’ll first need to log in:



    After logging in, you can view current files uploaded to your Spotfire server. You can also upload files from your computer by clicking onOpen analysis:



    Thenbrowsefor the file on your computer:



    Qlik Sense

    Open up Qlik then click onCreate a new appon the dialog that appears



    Enter a name for your app and clickCreate:



    Click onOpen app:



    Click onAdd data:



    Then click onConnect my data, then clickODBC:



    Create a new connection by selecting theDSN you configured earlier．Fill in your Smartsheet credentials to log in. Or, if you are using anAPI Access tokento connect, paste the token into the password field instead:



    Select the data you want, then clickLoad and finish:



    After the data is loaded, click onClose:



    Then click on the new data sheet you just created:



    Finally, use the data to create your dashboard in Qlik Sense:



    QlikView

    Open up ClickView and navigate toFile>New:



    After the开始Wizard:dialog starts, click theXin the upper right-hand corner:



    After the dialog is closed, click onFileagain, then click onEdit Script…:



    On the next screen, selectODBCin theDatabasedropdown, then click onConnect…(you can click onSelect…as well, they open the same dialog box):



    On the next dialog, select the Data Source you set up (If it doesn’t show, click theShow User DSNscheckbox). Fill in your Smartsheet credentials to log in. Or, if you areusing an API Access token to connect, paste the token into the password field instead:



    Click onSelect:



    Select the Tables and Columns you would like then clickOK:



    After you’re finishing adding tables and your script is ready, clickOK:



    Your data is now loaded, but before you can begin making charts and graphs you’ll need to save your project:



    Then you’ll need to start the Chart Wizard:


    Click onFile>Reload:



    Excel

    This section covers the various ways to use the Smartsheet Live Data Connector with Excel.

    Excel - Data Connection Wizard

    Open the Data Connection Wizard by navigating toData>Get External Data>From Other Sources:



    SelectODBC DSNand clickNext:



    Select the name of the DSN you created earlier, and clickNext:



    当出现提示时,填写内容登录“ntials. If your Smartsheet account requires a single sign-on and you are unable to use email and password for login, you can use theUse API Token for SSO loginoption instead and paste your API token into the password field:



    Once connected to Smartsheet, select your sheet or report and clickFinish:



    Finally, specify the destination for the imported data:



    Excel - Microsoft Query

    Once a DSN has been configured, navigate to Microsoft Query by navigating toData>From Other Sources>From Microsoft Query:



    Select the Data Source you created earlier, then clickOK:



    当出现提示时,填写内容登录“ntials. If your Smartsheet account requires a single sign-on and you are unable to use email and password for login, you can use theUse API Token for SSO loginoption instead and paste your API token into the password field:



    Once connected, select your desired sheets or reports:



    Specify the destination for your data:



    Select how you want to view the data:



    Saving Credentials

    To save credentials, click on theDATAtab, then click onConnections:



    Choose the Connection for your query, then click onProperties:




    Click on theDefinitiontab, then check theSave passwordoption.

    After you’ve checked the option to save your password, you’ll want to copy the following string to the end of theConnection String(ReplacingAPI_TOKENwith theAPI access tokenyou generated):

    PWD=API_TOKEN

    Finally, click onOK:







    Excel - Power Query

    Follow the早些时候instructions for Microsoft Query, but instead of returning data to Excel, selectView data or edit data query in Microsoft Query:



    Once in query editor, click onSQLand copy the SQL statement in the window that opens:



    Now, launch Power Query from the main Excel menu and selectFrom other sources>From ODBC:



    At the next step, paste the string below intoConnection string, and paste the query you copied / saved from an earlier step intoSQL Statement:

    Driver={Smartsheet Live Data Connector}



    Finally, enter your Smartsheet credentials when prompted:



    FAQ

    The following questions are answered in this section:

    Why am I seeing this error - “Unexpected error”, when expanded says that “ODBC is not enabled for your account”?





    This is because ODBC access has not been enabled for your Smartsheet account. Please see theRequirementssection to ensure your account is eligible to use the Live Data Connector.

    Back to FAQ



    My date columns contain mixed milestone names (text) and dates, but on import into Tableau all text data is lost. How can I see this information?

    Date and Time columns have a storage class of text in the Live Data Connector. This means that they are plain text strings until imported into Tableau or other ODBC applications. If you wish to see the non-date/time content of these columns, you must modify the Tableau column type prior to import. You can do this by a right clicking on the calendar icon in the Tableau table selection screen and selecting theStringtype. All cell contents will now be displayed as raw, unformatted text.



    Back to FAQ



    Does my Smartsheet data need to be formatted in a certain way to be compatible with Tableau?

    Spreadsheet data may require some formatting to work with Tableau. Tableau has created a guide for Excel users, and much of it apply to Smartsheet as well because of similarities in how data may be structured in Excel and Smartsheet. SeePreparing your Excel data for Tableauknowledge base article.

    Back to FAQ



    When I use Tableau Desktop, why do I have to re-enter my Smartsheet credentials every time I re-open Tableau?

    Tableau Desktop does not save user login credentials by design, as a security precaution. It is a feature of Tableau Desktop, not a feature of the Smartsheet Live Data Connector. To re-open previously saved Tableau Desktop workbooks, seethis section．

    Back to FAQ



    Why am I getting an “invalid username or password” error when trying to re-open a previously saved Tableau Desktop workbook?



    Please seethis FAQ item．

    Back to FAQ



    What is the max number of tables that the Smartsheet Live Data Connector will support in a join?

    The Smartsheet Live Data Connector will support up to 64 tables (sheets) in a single join.

    Back to FAQ



    What SQL dialect does the Smartsheet Live Data Connector support?

    The Smartsheet Live Data Connector uses an implementation of SQLite to locally store and query Smartsheet sheets and reports. As such, the Live Data Connector can interpret native SQL as understood by SQLite. The SQL syntax used by SQLite is well documented on the SQLite website:https://www.sqlite.org/lang.html．In addition to the SQLite core functionshttps://www.sqlite.org/lang_corefunc.html, the Live Data Connector also supports a variety of functions which expose sheet, column and cell metadata through native SQL. Those functions are described above.

    Back to FAQ



    Why doesn’t Tableau’s Fiscal Calendar Start functionality work correctly?

    Any time I select a different fiscal year start month, it loses the year and quarter data for any month after the start month, through the end of the calendar year. For example, if I choose August as the start of the fiscal year, the months of August through December show Null values for year and quarter. January through July, however, show up correctly.

    When communicating with the Smartsheet Live Data Connector, Tableau expects (as an example) that if you ask for the 15th month of 2014 that the Connector will return a date of the 3rd month of 2015. The Connector’s date function, however, sees the 15th month as a bad value and returns NULL instead. To work around this limitation, create a calculated field in Tableau:

    1. Right click anywhere in the Data pane and selectCreate Calculated Field
    2. Let’s call itFY Order Date
    3. In the Functions column selectRAWSQL_DATE
    4. Then in the Formula window typeRAWSQL_DATE("date(%1, '+6 month'), [Order Date]), whereOrder Dateshould be replaced with the name of the date column you will use from your table.

    In this example the Fiscal Year starts on July 1st - hence ’+6 month’. If your Fiscal Year starts on a different month, you would need to adjust accordingly. For instance, if it starts in March, your calculation should say ‘+2 month’.

    The result should look like this:



    Back to FAQ



    I tried publishing a Smartsheet-based Workbook and got a message that the functions TODAY() and NOW() will work differently because the server and Smartsheet are in the different timezones.

    Tableau appears to be returning incorrect information when executing theNOW()function, and there appear to be a number ofonline forum postsconfirming this.

    Luckily, as withDATEADDandDATEDIFFwe can modify these calculations to return UTC or local time. TheNOW()UTC function isRAWSQL_DATETIME("datetime('now')")．And theNOW()local time function isRAWSQL_DATETIME(“datetime(‘现在’,‘作用’)”)．

    The way most other Tableau users address this is by picking a common time zone and modifying the calculations to always use that time zone.Our recommendation is to replaceNOW()with your own function that returns UTC if you need to be consistent across time zones(all other Smartsheet dates will also get returned in UTC).

    Back to FAQ



    My Smartsheet data does not appear to refresh on Tableau Server.

    There is a known issue with Tableau Server prior to version 8.3.3 where the manual refresh (using F5) does not refresh any data from Smartsheet. The issue has been addressed in Tableau Server 8.3.3.

    Back to FAQ



    I am seeing “Tableau identified limitations…” warning message.

    The Smartsheet Live Data Connector comes with a Tableau configuration file designed to optimize the Tableau integration experience. The file gets installed into C:\My Documents\My Tableau Repository\Datasources directory and is called “smartsheet.tdc”.

    If for some reason Tableau is not able to find this file, you will see the following warning:


    This may happen for one of two reasons:

    1. The Smartsheet Live Data Connector was installed before Tableau was installed, so the .tdc file was never copied to the Tableau directory because it didn’t exist at the time.

    2. Both 32-bit and 64-bit Connectors were installed and then one of them was uninstalled, removing the .tdc file.

    In both instances, the solution is to uninstall your Smartsheet Live Data Connector(s) and do a clean install of the Connector, insuring that the .tdc file is now installed into the Tableau directory.

    Back to FAQ



    How do I get Excel to Auto-Refresh Smartsheet Data?

    To start, navigate toData>Refresh All>Connection Properties…



    After that, a dialog will open with several settings for refreshing. After you’ve finished setting the options to your liking, clickOK:



    Back to FAQ



    I am getting an error message trying to configure or delete a DSN - “The setup routines for the … could not be found. Please reinstall the driver.”

    If you removed the Live Data Connector from your computer, or have upgraded it from version 1.0.20.3 (or earlier when it used to be called Smartsheet ODBC Driver), but still have Data Source Names (DSNs) set up with the old driver, you will not be able to configure or delete them.

    When you attempt to configure or delete a DSN set up for an ODBC driver that has been removed from your computer, you will receive the following error message:



    Please follow the instructions described inthis online postto remove any references to the old driver.

    First, clickStart, typeregeditand launch the Windows registry editor:



    If your DSN is auserDSN (most likely) configured for a 64-bit driver, navigate toHKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INIand delete the data source key underODBC.INIand the entry underODBC.INI\ODBC Data Sources:

    Back to FAQ



    I am getting an error message when attempting to open a saved workbook - “Invalid username or password for data source”

    This error is likely caused by character limitations in Tableau. Because of the way data is passed in Tableau when a workbook is re-opened, the following characters cannot be used in your password:

    {} ; =

    If you have any of these characters in your password, you will need to change it and remove them.


    Back to FAQ