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

你好,

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

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

我有一个安全令牌,但无法在Power Query中配置参数,以使Smartsheet 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,通过Power Query将报告数据提取到Excel ?

谢谢

image.png


最佳答案

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    @CycleBagEd

    我对此有几个问题…你能解释一下为什么不使用ODBC连接器吗?你在找添加内容的报告或得到数据?

    当你注意到你想要它的时候,知道你的意思也会很有帮助。”任何能访问Excel文档的人都可以运行。”

    您需要添加一个Header名称=授权,值为承载.关于原始令牌请求的Smartsheet API文档将提供更多信息:https://smartsheet.redoc.ly/#section/API-Basics/Raw-Token-Requests

    我还发现了一个StackOverflow上的旧线程通过设置PowerQuery和Smartsheet,如果有帮助的话。

    欢呼,

    吉纳维芙

答案

In case anyone finds this thread, I posted this to stackoverflow and received this answer:<\/p>
\n \n https:\/\/stackoverflow.com\/a\/73194270\/6770704\n <\/a>\n<\/div>


<\/p>

Apparently in the OperationErrorResult class, there are a few errors which undergo an auto-retry:<\/p>

4003: {\n    'name': 'RateLimitExceededError',\n    'recommendation': ('Retry using exponential backoff. Hint: '\n                       'Reduce the rate at which you are sending '\n                       'requests.'),\n    'should_retry': True},\n<\/pre>

So the error is handled and the request is automatically retried.<\/p>


<\/p>

If anyone wishes for further details, please see the linked post to stackoverflow<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-08-02 11:49:33","updateUser":{"userID":150873,"name":"I_B","url":"https:\/\/community.smartsheet.com\/profile\/I_B","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-08-02T11:45:33+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":227,"urlcode":"API Developers","name":"API and Developers"}]},{"discussionID":93567,"type":"question","name":"Is there a change log for the Smartsheet API Reference?","excerpt":"The Smartsheet API Reference shows that it was updated on 2022-07-13. Is there a way to see what changed?","categoryID":320,"dateInserted":"2022-07-25T17:55:57+00:00","dateUpdated":null,"dateLastComment":"2022-07-26T14:20:48+00:00","insertUserID":127476,"insertUser":{"userID":127476,"name":"Leibel Shuchat","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Leibel%20Shuchat","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-08-04T14:11:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":127476,"lastUser":{"userID":127476,"name":"Leibel Shuchat","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Leibel%20Shuchat","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-08-04T14:11:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3317618205,"url":"https:\/\/community.smartsheet.com\/discussion\/93567\/is-there-a-change-log-for-the-smartsheet-api-reference","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/93567\/is-there-a-change-log-for-the-smartsheet-api-reference","format":"Rich","tagIDs":[227],"lastPost":{"discussionID":93567,"commentID":337455,"name":"Re: Is there a change log for the Smartsheet API Reference?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/337455#Comment_337455","dateInserted":"2022-07-26T14:20:48+00:00","insertUserID":127476,"insertUser":{"userID":127476,"name":"Leibel Shuchat","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Leibel%20Shuchat","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-08-04T14:11:48+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-07-26T14:20:50+00:00","dateAnswered":"2022-07-26T12:18:44+00:00","acceptedAnswers":[{"commentID":337424,"body":"

Hi,<\/p>

I checked with and older version of the swagger documentation from the API reference: the changes are mostly typos.<\/p>

there are also some changes regarding the commenter level access on reports and notes about the US-GOV version of smartsheet.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-07-26 14:20:50","updateUser":{"userID":127476,"name":"Leibel Shuchat","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Leibel%20Shuchat","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-08-04T14:11:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}}},"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 Developers","name":"API and Developers"}]},{"discussionID":92735,"type":"question","name":"Python get_columns response.","excerpt":"I am trying to find specific columns through the python api. Running something like listColumns = smartAPI.Sheets.get_columns(83053825###, include_all=True) gives me the object and not the Field Name. [, Hey @MikeG_O<\/a> <\/p>

It looks like you may just need to resolve the response (i.e. columns = listColumns.data<\/code>). Here's the API Documentation and example: List Columns<\/a><\/p>

Let me know if this has helped!<\/p>

Cheers,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-07-06 16:35:25","updateUser":{"userID":149319,"name":"MikeG_O","title":"","url":"https:\/\/community.smartsheet.com\/profile\/MikeG_O","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-07-19T16:04:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in API & Developers","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">