Forum Discussion
Trello rest api as a data source authentication issue
Hi All,
I have managed to get data from Trello rest api to my desctop report but when I published I get authentication 400 error - The credentials provided for the Web source are invalid. Not sure why and how can I fix it. Could you please support me.
let
Source = Json.Document(Web.Contents("https://api.trello.com/1/boards/cExjoJEB/cards?key=<my key>a&token=<my token>")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}, {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}),
#"Expanded {0}1" = Table.ExpandListColumn(#"Expanded {0}", "idMembersVoted"),
#"Expanded {0}2" = Table.ExpandListColumn(#"Expanded {0}1", "idLabels"),
#"Expanded {0}3" = Table.ExpandRecordColumn(#"Expanded {0}2", "badges", {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due", "dueComplete"}, {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due.1", "dueComplete.1"}),
#"Expanded {0}4" = Table.ExpandListColumn(#"Expanded {0}3", "idChecklists"),
#"Expanded {0}5" = Table.ExpandListColumn(#"Expanded {0}4", "idMembers"),
#"Expanded {0}6" = Table.ExpandListColumn(#"Expanded {0}5", "labels"),
#"Expanded {0}7" = Table.ExpandRecordColumn(#"Expanded {0}6", "labels", {"id", "idBoard", "name", "color"}, {"id.1", "idBoard.1", "name.1", "color"}),
#"Expanded {0}8" = Table.ExpandRecordColumn(#"Expanded {0}7", "attachmentsByType", {"trello"}, {"trello"}),
#"Expanded {0}9" = Table.ExpandRecordColumn(#"Expanded {0}8", "trello", {"board", "card"}, {"board", "card"}),
#"Expanded {0}10" = Table.ExpandRecordColumn(#"Expanded {0}9", "descData", {"emoji"}, {"emoji"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded {0}10",{"checkItemStates", "closed", "desc", "emoji", "dueReminder", "idBoard", "idMembersVoted", "idAttachmentCover", "idLabels", "manualCoverAttachment", "pos", "shortLink", "board", "card", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "due.1", "dueComplete.1", "dueComplete", "idChecklists", "id.1", "idBoard.1", "name.1", "color", "shortUrl", "subscribed"})
in
#"Removed Columns"The first parameter to Web.Contents must be static
In your case try setting it to "https://api.trello.com/", then use the relative path option to add "1/boards/cExjoJEB/cards", and query option for the paremeters "key=<my key>a&token=<my token>"
The below reference helped me out quite a bit
Good luck
5 Replies
The first parameter to Web.Contents must be static
In your case try setting it to "https://api.trello.com/", then use the relative path option to add "1/boards/cExjoJEB/cards", and query option for the paremeters "key=<my key>a&token=<my token>"
The below reference helped me out quite a bit
Good luck
Many thanks for you support. I'm trying to use your advance but I'm getting credential error - credential are incorrect. Actually it works on descope report till I refreshed the data.
let Source = Json.Document(Web.Contents("https://api.trello.com", [ RelativePath="/1/boards/cExjoJEB/cards", Query=[key="<my key>", token="<my token>"] ])), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}, {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}), #"Expanded {0}1" = Table.ExpandListColumn(#"Expanded {0}", "idMembersVoted"), #"Expanded {0}2" = Table.ExpandListColumn(#"Expanded {0}1", "idLabels"), #"Expanded {0}3" = Table.ExpandRecordColumn(#"Expanded {0}2", "badges", {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due", "dueComplete"}, {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due.1", "dueComplete.1"}), #"Expanded {0}4" = Table.ExpandListColumn(#"Expanded {0}3", "idChecklists"), #"Expanded {0}5" = Table.ExpandListColumn(#"Expanded {0}4", "idMembers"), #"Expanded {0}6" = Table.ExpandListColumn(#"Expanded {0}5", "labels"), #"Expanded {0}7" = Table.ExpandRecordColumn(#"Expanded {0}6", "labels", {"id", "idBoard", "name", "color"}, {"id.1", "idBoard.1", "name.1", "color"}), #"Expanded {0}8" = Table.ExpandRecordColumn(#"Expanded {0}7", "attachmentsByType", {"trello"}, {"trello"}), #"Expanded {0}9" = Table.ExpandRecordColumn(#"Expanded {0}8", "trello", {"board", "card"}, {"board", "card"}), #"Expanded {0}10" = Table.ExpandRecordColumn(#"Expanded {0}9", "descData", {"emoji"}, {"emoji"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}10",{"checkItemStates", "closed", "desc", "emoji", "dueReminder", "idBoard", "idMembersVoted", "idAttachmentCover", "idLabels", "manualCoverAttachment", "pos", "shortLink", "board", "card", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "due.1", "dueComplete.1", "dueComplete", "idChecklists", "id.1", "idBoard.1", "name.1", "color", "shortUrl", "subscribed"}) in #"Removed Columns"Yes, RESTful web services are kind of a pain to work with
If you are using anonymous authentication, then the root url must support anonymous (some providers support this and others do not)
I don't know if it is an option for you, but I've used the following post to get around this
Actually, after some small changes the RelativePath solved the issue. Many thanks for your support.
Just to add onto what blopez11 said (so it does work)
Ive managed to get this working with Authentication
I have setup parameters in powerbi for
- The API Key
- The API Token
- The board key you want to get information from
This is an example of the code block for the M code
let
Source = Json.Document(Web.Contents("https://api.trello.com/1/boards/"&TrelloBoardKey&"/cards?key="&TrelloAPIKey&"&token="&TrelloAPIToken&"")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),If you want to get Card information you can use the
https://api.trello.com/1/boards/"&TrelloBoardKey&"/cards?
and for lists..
https://api.trello.com/1/boards/"&TrelloBoardKey&"/lists?
and there are others.
To get your API Key go to https://trello.com/app-key
To get your API Token, you get this once you have an API Key you can get a token from the same page
This sums it up.. https://developers.trello.com/page/authorization
