Smartsheet ODBC connector not working - but only for Excel

CycleBagEd
CycleBagEd ✭✭
inAdd Ons and Integrations

I have ODBC connector installed. It was working fine with Excel until we switched to single sign on. Then it broke.

I have followed instructions on using an API key for connectivity. These have not worked. I assumed this was an issue at the connector level.

HOWEVER: I have just tried to connect using MS Access. The ODBC connection asked for an API key, then worked immediately, so the connector is working.

In Excel though, I get the message below. I cannot make it work. What am I doing wrong please?

thanks

Ed


image.png


· Share on Facebook Share on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@CycleBagEd

    Thanks for posting screen captures! From this, it looks like you are entering the API token to the “Credential connection string properties” section rather than in the password field.

    Try adding the API key in the “Password” field as it is used to authenticate you the same way as a password would.

    这是ODBC建立文档ishing the connection with Excel specifically: (http://smartsheet-platform.github.io/odbc-docs/#excel).

    Let me know if you're still seeing an error after following the steps in the documentation above!

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter
  • CycleBagEd
    CycleBagEd ✭✭

    Thanks@Genevieve P.

    I can't get it to work from that screen but I have been able to use the (legacy) connection wizard to actually pull the data into Excel finally.

    • Adding the API key "as is" into the password field does not work
    • Entering the API key into the Credentials field does not work
    • Using existing credentials does not work
    • Using the legacy query wizard and entering the API key DOES work but the screen for selecting and ordering data columns is not present, whereas it WAS present prior to single sign on
    • Using Microsoft query also works and does present the screen to select a subset of columns from the report/sheet

    So it looks like the ODBC option is no longer available to use with SSO, or at least I can't get it to work, and I seem to recall it working prior. There is no mention of using the ODBC route on the instructions you have linked to, but this had worked previously.

    The sheet which I want to update has the following query:


    let

    Source = Odbc.DataSource("dsn=My Smartsheet DSN", [HierarchicalNavigation=true]),

    followed by details of the sheet/report and which columns are required. I can't find a way of editing or replacing this query at all - I will have to rebuild the report using one of the other connection methods.

    thanks for your support

    Ed

    · Share on Facebook Share on Twitter

Categories

You are more than welcome!<\/p>

Glad to hear that it worked for your present use-case. <\/p>

✅Remember! <\/strong>Did my post help or answer your question or solve your problem? Please help the Community by <\/em>marking it as the accepted answer\/helpful<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109291,"type":"question","name":"How do I make a global sheet\/report\/dashboard name change","excerpt":"I use Control Center to provision new projects. How do I globally change sheet\/report\/dashboard names' suffix after a project has been provisioned and is underway? For example, if the suffix was \"Enterprise Business Unit\", and now I want it to be \"Enterprise BU\".","snippet":"I use Control Center to provision new projects. How do I globally change sheet\/report\/dashboard names' suffix after a project has been provisioned and is underway? For example, if…","categoryID":343,"dateInserted":"2023-08-22T18:03:46+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T19:36:01+00:00","insertUserID":149249,"insertUser":{"userID":149249,"name":"ShannonTozier","url":"https:\/\/community.smartsheet.com\/profile\/ShannonTozier","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pxvQ6TiroDw!HCDQ32y7iGY!YRwAmRkBQEG","dateLastActive":"2023-08-22T19:51:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-23T01:18:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":20,"score":null,"hot":3385460987,"url":"https:\/\/community.smartsheet.com\/discussion\/109291\/how-do-i-make-a-global-sheet-report-dashboard-name-change","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109291\/how-do-i-make-a-global-sheet-report-dashboard-name-change","format":"Rich","tagIDs":[466,582],"lastPost":{"discussionID":109291,"commentID":391983,"name":"Re: How do I make a global sheet\/report\/dashboard name change","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391983#Comment_391983","dateInserted":"2023-08-22T19:36:01+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-23T01:18:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T19:51:29+00:00","dateAnswered":"2023-08-22T19:36:01+00:00","acceptedAnswers":[{"commentID":391983,"body":"

There is no way to do that through Global Updates. You will need to either do it manually or through the API.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":466,"urlcode":"control-center","name":"Control Center"},{"tagID":582,"urlcode":"global-updates","name":"global updates"}]},{"discussionID":109130,"type":"question","name":"//www.santa-greenland.com/community/discussion/comment/\"Get Sheet\" in Bridge not working","excerpt":"Ok I scrubbed the forum and can't find an answer. I have Bridge workflow that calls \"Get Sheet\" twice. She image. The second is not providing an output, but the Run complete successfully. I specified the SheetID, I am the owner of that sheet.","snippet":"Ok I scrubbed the forum and can't find an answer. I have Bridge workflow that calls \"Get Sheet\" twice. She image. The second is not providing an output, but the Run complete…","categoryID":343,"dateInserted":"2023-08-18T02:50:31+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T08:21:37+00:00","insertUserID":148386,"insertUser":{"userID":148386,"name":"dsmitts.jay","url":"https:\/\/community.smartsheet.com\/profile\/dsmitts.jay","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Asq033UutIg!DAu0A77Sx_w!NYd7SSBcGwv","dateLastActive":"2023-08-20T18:41:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-22T17:41:59+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":49,"score":null,"hot":3384936128,"url":"https:\/\/community.smartsheet.com\/discussion\/109130\/get-sheet-in-bridge-not-working","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109130\/get-sheet-in-bridge-not-working","format":"Rich","lastPost":{"discussionID":109130,"commentID":391563,"name":"Re: \"Get Sheet\" in Bridge not working","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391563#Comment_391563","dateInserted":"2023-08-21T08:21:37+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-22T17:41:59+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2USQVMXJ1B6S\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-21T08:21:28+00:00","dateAnswered":"2023-08-18T16:40:11+00:00","acceptedAnswers":[{"commentID":391426,"body":"

Hi @dsmitts.jay<\/a> <\/p>

Thank you for the screen captures!<\/p>

Would you mind testing something? Can you add a State<\/strong> before the second Get Sheet? It can be named anything, such as \"SecondGetSheet\" or something similar. <\/p>

My current guess at this point is that since you have two modules with the exact same name under the exact same state, the Run Log is only able to register one of them. Adding a State with a unique name above your second Get Sheet should bring the results into the Run Log for you... but let me know if it doesn't!<\/p>

Thanks,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=343&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-dateLastComment&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":643,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • Is there a way to use OR in a Dynamic View filter condition?

    In a View, I can create a custom filter that selects where the Project Lead is "Mike" but I want to also say "OR Debbie." Is that possible? It looks like I can only choose AND.

  • How do I make a global sheet/report/dashboard name change

    I use Control Center to provision new projects. How do I globally change sheet/report/dashboard names' suffix after a project has been provisioned and is underway? For example, if the suffix was "Enterprise Business Unit", and now I want it to be "Enterprise BU".

  • "Get Sheet" in Bridge not working

    Ok I scrubbed the forum and can't find an answer. I have Bridge workflow that calls "Get Sheet" twice. She image. The second is not providing an output, but the Run complete successfully. I specified the SheetID, I am the owner of that sheet.

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video