Using Excel as primary data source

Ken Hankoff

I've been reading about the Live Data Connector feature in Smartsheet, but it doesn't sound as though it will solve my dilemma.  I have a situation where highly confidential data in an Excel worksheet cannot (per policy) be stored in the cloud.  However, not all of the data in the worksheet is highly confidential, and I would like to leverage a Smartsheet to collaborate on it.  In other words, I would like to link to a couple of data elements in the Highly Confidential Excel worksheet, and then add (in Smartsheet) related data attributes, so they can be collaboratively maintained.  

When I read about the Live Data Connector, it seems as though the Use Case is for Smartsheet to be the data source, and Excel (or Tableau, or other tools) the display mechanism.

Can I use an Excel worksheet maintained in secure storage as the data source, and link to it with Smartsheet?

Ken Hankoff

I'm wondering if something as simple as a vlookup formula (I know Smartsheet calls it something else, and the functionality is slightly different), but think that might be the way to achieve this.  Wouldn't be a live connection, but if the two were inter-connected, then maybe it would be made to work??

Kate French

Hi Ken,

 

The Live Data Connector currently only allows read-only use of sheets in Smartsheet, and isn't able to write data into your sheets. I've added your vote for read/write functionality between Smartsheet and programs connected via the Live Data Connector to our Enhancement Request List to be considered in future development. We appreciate your input!

 

Best,

Kate

Ken Hankoff

Hi Kate.  Maybe I'm not explaining it properly.  I don't need to have the connected sheet write data into Smartsheet, but have Smartsheet read data from the connected Excel.  If I could do that, then I could add and edit attributes in Smartsheet that are related to the data read from Excel.  For example, if I had sensitive employee data in Excel (e.g. a physical home address) that I didn't want in the cloud, but I wanted to keep track of a laptop selection for that employee in Smartsheet, how to achieve that?  How do I connect the laptop selection data in Smartsheet to the employee ID in Excel?  Again - some data in Excel is sensitive, but some now.  So if I could maintain a lookup of some sort on a key field, that might work.

 

Thanks for the response.

Joseph Miller

Hi Ken,

 

To add onto Kate's response, she was originally correct in that the driver is only able to read data from Smartsheet. However, based on your description it sounds like that's what you're ultimately wanting to do.

 

The ODBC driver allows you to connect to Sheets/Reports in Smartsheet while in Excel and pull the data down from Smartsheet into an Excel file. This information can also be kept in sync. 

 

You can find more information here.

 

Cheers,

Joseph

 

Ken Hankoff

Thank you, Joseph.  It sounds as though this may still be something I can use, so I'll give it a go.  I don't need to write to Smartsheet from the Excel sheet to which I will establish the ODBC connection.  However, I gather (and will find out soon enough) that having a connection from SS to Excel will not preclude me from updating the SS manually with various attribute data, which is my ultimate objective.  

 

Fact is, I just need to associate data (that I want to edit in Smartsheet) with data in Excel (which I don't want exposed in the cloud).  If I link properly and identify a non-sensitive Key field in Excel, this should work (I hope).

 

Thanks.

Joseph Miller

My pleasure, Ken. It sounds like the ODBC driver should work, as you can use it simply to pull (and sync) data from Smartsheet to Excel. 

 

Feel free to email api@smartsheet.com if you run into any issues. I'd also suggest utilizing our ODBC Documentation, if you haven't already seen it.

 

Cheers,

Joseph

Jennifer Dewhurst

Ken, 

Have you considered keeping ALL the data (including the sensitive data) in Smartsheet then creating a "report" in smartsheet that contains only the non-sensitive data that can be shared?  This is a great way to protect your information yet benefit from collaboration.  

 

I work for a public utility and we also have classified data but Smartsheet meets the security protocols.

Luis Vega

Wanted to bring this back up because I think I have a similar issue.  I am looking to take data from excel spreadsheets and have them link to and populate a smartsheet.  I have leveraged ODBC to use smartsheets as the data source, that populates a spreadsheet and didn't notice a way to reverse this.  Issue I am trying to solve is I recieve spreadsheets from a contractor and would like to "automate" updates so as to not have to manually transfer the data into smartsheet.  My hope is to simply establish the link, and when a new spreadsheet arrives (with updated data) I can ust copy replace the existing spreadsheet data source or just re-link and smartsheet updates the data?  is this possible or should I just be doing it manually?

Kennedy Stomps

Hi Luis-- Currently, the ODBC can only be used to pull data from Smartsheet into Excel, it isn't possible to write data from Excel into a sheet in Smartsheet. I'm just curious--is there a reason why your contractors are sending spreadsheets and not updating sheets in Smartsheet? Using Update Requests wouldn't require the contractor to log in to Smartsheet at all, and there are other ways you could collaborate as well. 

 

If it wouldn't be possible to transition this contractor into Smartsheet, you'd need to perform this information transfer manually, at this point. 

Heather Dixon

Hi all,

I was wondering if there was any changes to the current limitations with using the ODBC to pull data from excel into smartsheet.

 

Thanks,

Heather

Anna1

Hi,

Like Heather, I would like to know if any changes have been made to the current limitations with using the ODBC to pull data from excel into smartsheet.

Thanks,

Anna

Keith Tham

Please add my vote to have a Smartsheet read(pull) or reference from a Data Source such as Excel, Access, etc...  

 

My 3 Sons KY

Please add my vote to have a Smartsheet read(pull) or reference from a Data Source such as Excel, Tableau, etc...

Ken Arends

Add my vote to PULL DATE FROM EXCEL INTO SMARTSHEETS! We have a an Excel spreadsheet that runs through our SAP system and cannot be changed. I need to pull data from this Excel spreadsheet and have it live on my Smartsheet. Somebody please send me a hack for this...

Luis Vega

Folks, note that Smartsheet does have a "Data Uploader" functionality now.

https://help.smartsheet.com/articles/2478361-data-uploader?_ga=2.62270188.1742860479.1551704615-449753565.1539367053

It's not cheap but does 'solve' this issue, etc.  I'm currently putting together a use case and validation to my company to see if we can add the feature to our enterprise account.