Dan Bliss
New member
- Joined
- Dec 7, 2016
- Messages
- 45
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365
From an online CRM, I hope to extract the names of contacts associated with each sales opportunity. The CRM has an API command to extract "parties" (contacts) associated with a given opportunity using curl. Here's the code which runs from a command prompt on a Windows 10 PC:
where:
{key} is a unique token which the CRM assigns to an account for secure access.
{partyId} is the unique key field identifying a party.
First I loaded the Party table, which has a unique field, party.ID (also name fields, address fields, email, phone, etc.)
Using the party.ID, I had hoped to write a Power Query expression, which included something like the following:
...
prestring = "curl -H ""Authorization: Bearer {Key}"" -H ""Accept: application/json"" https://api.capsulecrm.com/api/v2/parties/",
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", Expression.Evaluate("prestring & each [party.ID] & ""/opportunities""", #shared))
in
#"Added Custom"
Not surprising that #Shared environment would not be able to access a cmd.exe environment: = Expression.Evaluate("echo", #shared) does not work.
Please note the quotes in the Expression.Evaluate(string) are not correct, but I ran out of patience trying to get them right.
Any thoughts on how to do this?
Maybe can't be done in PowerQuery?
Code:
curl -H "Authorization: Bearer {Key}" -H "Accept: application/json" https://api.capsulecrm.com/api/v2/parties/{partyId}/opportunities
{key} is a unique token which the CRM assigns to an account for secure access.
{partyId} is the unique key field identifying a party.
First I loaded the Party table, which has a unique field, party.ID (also name fields, address fields, email, phone, etc.)
Using the party.ID, I had hoped to write a Power Query expression, which included something like the following:
...
prestring = "curl -H ""Authorization: Bearer {Key}"" -H ""Accept: application/json"" https://api.capsulecrm.com/api/v2/parties/",
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", Expression.Evaluate("prestring & each [party.ID] & ""/opportunities""", #shared))
in
#"Added Custom"
Not surprising that #Shared environment would not be able to access a cmd.exe environment: = Expression.Evaluate("echo", #shared) does not work.
Please note the quotes in the Expression.Evaluate(string) are not correct, but I ran out of patience trying to get them right.
Any thoughts on how to do this?
Maybe can't be done in PowerQuery?
Last edited: