如何从Excel功能查询访问Smartsheet API ?

你好,

我有Excel的ODBC连接器,但需要在这个实例中使用API。

我想创建一个API连接,可以由任何人访问Excel文档运行。

我有一个安全令牌,但无法配置电源查询中的参数,以便智能表API能够正常工作并提供访问。

我目前有:

数据源设置:https://api.smartsheet.com/

凭证:匿名

隐私级别:组织

然后,我尝试将我的查询配置为web源。

在URL部分我已经尝试过了https://api.smartsheet.com/2.0/sheets/ (sheetid)

我想看一份报告我想是https://api.smartsheet.com/2.0/reports /

我尝试过“ApiKeyName”和“值”作为具有适当值的请求标头。

我还尝试在全局设置中插入API密钥。

有人能告诉我如何配置这些设置访问API提取报告数据到Excel通过电源查询?

谢谢

image.png


最佳答案

答案

Hi @cabbsman<\/a> <\/p>

I see you've received a response in the StackOverflow Community on this thread:<\/p>

\n \n https:\/\/stackoverflow.com\/questions\/76417418\/python-sdk-api-to-add-rows-with-multiple-columns-to-update-to-smartsheet\n <\/a>\n<\/div>

I would suggest following-up on that thread as it looks like there are some good tips\/tricks!<\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"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":227,"urlcode":"api-and-developers","name":"API and Developers"}]},{"discussionID":105822,"type":"question","name":"Smartsheet IP Addresses for Network Restrictions","excerpt":"Hi! I am using Smartsheet webhooks to stream events of updates on items Along with hmac authorization, I also want to restrict network access so that requests coming from Smartsheet server will be catered, and all other go unauthorized can I get the set of IPs of Smartsheet?","categoryID":320,"dateInserted":"2023-05-31T04:25:53+00:00","dateUpdated":null,"dateLastComment":"2023-06-01T12:28:16+00:00","insertUserID":161903,"insertUser":{"userID":161903,"name":"Shravan A","url":"https:\/\/community.smartsheet.com\/profile\/Shravan%20A","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-06T18:35:00+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-06-15T12:58:48+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":73,"score":null,"hot":3371130249,"url":"https:\/\/community.smartsheet.com\/discussion\/105822\/smartsheet-ip-addresses-for-network-restrictions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105822\/smartsheet-ip-addresses-for-network-restrictions","format":"Rich","lastPost":{"discussionID":105822,"commentID":378281,"name":"Re: Smartsheet IP Addresses for Network Restrictions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/378281#Comment_378281","dateInserted":"2023-06-01T12:28:16+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-06-15T12:58:48+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-06T18:36:38+00:00","dateAnswered":"2023-06-01T12:28:16+00:00","acceptedAnswers":[{"commentID":378281,"body":"

Hi @Shravan A<\/a> <\/p>

Smartsheet publishes a DNS A record at aws.relay.smartsheet.com which can be added to the Allowlist in your firewall. We recommend using the DNS record and not<\/strong> IP addresses because our IP addresses may change in the future. <\/p>

Here's the link to Authenticating Webhooks<\/a> in the API documentation. <\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"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":105733,"type":"question","name":"Reseting a row parentId","excerpt":"Hi, For some needs, i would like to reset the parentId value of a given row. what value should i be using to force the parentId to be reset ? I tried :0; null; \"0\"; \"null\" and none of them made any positive result :","categoryID":320,"dateInserted":"2023-05-26T14:22:47+00:00","dateUpdated":"2023-05-26T14:40:07+00:00","dateLastComment":"2023-05-26T20:12:12+00:00","insertUserID":105856,"insertUser":{"userID":105856,"name":"Etienne Mermillod","url":"https:\/\/community.smartsheet.com\/profile\/Etienne%20Mermillod","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mjjbH0nZWJQ!cu9Yh9XS-Xo!wwrdU3Cq-QN","dateLastActive":"2023-06-13T11:24:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":91566,"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-06-15T12:44:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":55,"score":null,"hot":3370244699,"url":"https:\/\/community.smartsheet.com\/discussion\/105733\/reseting-a-row-parentid","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105733\/reseting-a-row-parentid","format":"Rich","lastPost":{"discussionID":105733,"commentID":377787,"name":"Re: Reseting a row parentId","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/377787#Comment_377787","dateInserted":"2023-05-26T20:12:12+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-06-15T12:44:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8JN94KOJFPH3\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-05-26T19:50:49+00:00","dateAnswered":"2023-05-26T19:47:30+00:00","acceptedAnswers":[{"commentID":377776,"body":"

Are you trying to outdent a row?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":320,"name":"API & Developers","url":"https:\/\/community.smartsheet.com\/categories\/api-developers","allowedDiscussionTypes":[]},"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=320&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&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":198,"limit":3},"title":"Trending in API & Developers","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

API和开发者趋势