Hi, I am a complete Newbie with regards to Resftul services etc, I am trying to connect to a Second Street Api using the below. It seems you need to use a Post first to get an access toke, how would you go about this in POWERBI. Thanks OverviewSecond Street’s Messaging Analytics API allows partners to get detailed information about message campaigns sent via the Second Street Platform. If you do not need the level of detail offered by direct access to a JSON API, Second Street also provides a series of Dashboards which will help you visualize some of this data. This document outlines what you will need in order to build your own data visualizations, or otherwise use data about the Message Campaigns you’ve sent through our Platform. API BasicsSecond street’s API is located at:https://api.secondstreetapp.com Second Street’s API is a RESTful api that accepts and returns JSON. As a RESTful API, the HTTP method used on a request is meaningful so please only use GETs when you intend to retrieve a record, POST to create a record, etc. More information will be provided about when it is appropriate to GET or POST in the discussions of the different endpoints below.Required HeadersThe following headers are meaningful when communicating with the API HeaderReq’d?Expected ValuesAcceptYesapplication/json Accept-LanguageYesAny valid language codeExample: en-US,en;q=0.8X-Api-KeyYesAny valid API KeyWill be provided by Second StreetX-Organization-IdYesAny valid organization IDWill be provided by Second StreetX-Referring-UrlYesThe URL the form will be hosted onAuthorizationYesAn access token generated via sessions Data ContextsWhen requesting Categorical Charts or Messaging Statistics from the Second Street API, you can include one of the following query strings in your GET request to get data for different contexts:To get data for a single Schedule Instance in a Newsletter Campaign, use ?scheduleInstanceId={{id}}You can get the ID of a Schedule Instance from the URL you see when looking at its dashboard. For example, the Schedule Instance at https://partners.secondstreet.com/p/app/#/o/397282/message-campaigns/198/schedule-instances/441/dashboard has an ID of 441.To get data for a single Message in a Drip Campaign, use ?messageId={{id}}You can get the ID of a Message from the URL you see when looking at its dashboard. For example, the Message at https://partners.secondstreet.com/p/app/#/o/397282/message-campaigns/198/messages/319/dashboard has an ID of 319.To get data for an entire Campaign, use ?messageCampaignId={{id}}You can get the ID of a Message Campaign from the URL you see when looking at its dashboard. For example, the Message Campaign at https://partners.secondstreet.com/p/app/#/o/397282/message-campaigns/198/dashboard has an ID of 198.PagingWhen requesting data from certain endpoints of the Second Street API, you can include the following query strings in your GET request to page through data:?pageSize={{size}}How many records to return in a single request. If unspecified, defaults to 1000.?pageIndex={{index}}Which page of records to show, 1-indexed. For example, a pageSize of 1000 and a pageIndex of 2 will return the second thousand records. If unspecified, defaults to 1. Paged endpoints will have a second JSON root object, meta. The meta object will have page_index, page_size, and total_records properties.page_index is the same value you sent through the GET request.page_size is the same value you sent through the GET request.total_records is the total count of records that can be accessed, and is your clue for whether you should request more pages of data.Logging InTo begin a session that has the authority to access messaging analytics, perform a POST on /sessions: POST to: https://api.secondstreetapp.com/sessions With body: { "sessions": [ { "username": "user@example.com", "password": "password" } ]} The username should be the email address of a Second Street user that has all roles. The password should be their password. Be sure you’re using HTTPS in the request URL, as you don’t want to send passwords over plain text! If successful, the server will respond with { "sessions": [ { "access_token": "a1b2c3d4", "id": 22318, "organization_id": 397282, "organization_user_guid": "123456a7-ab12-1a23-1ab2-123a45bc6789", "organizations_count": 2, "username": "user@example.com" } ]} The most important value here is session.access_token. The access_token should be added to your headers to be used for all future requests as this identifies the user.

Anonymous

Not applicable

9 years ago

Solved

Connect to restful api data source multiple times

Hi,

I am a complete Newbie with regards to Resftul services etc, I am trying to connect to a Second Street Api using the below.

It seems you need to use a Post first to get an access toke, how would you go about this in POWERBI. Thanks

Overview

Second Street’s Messaging Analytics API allows partners to get detailed information about message campaigns sent via the Second Street Platform.

If you do not need the level of detail offered by direct access to a JSON API, Second Street also provides a series of Dashboards which will help you visualize some of this data.

This document outlines what you will need in order to build your own data visualizations, or otherwise use data about the Message Campaigns you’ve sent through our Platform.

API Basics

Second street’s API is located at:

https://api.secondstreetapp.com

Second Street’s API is a RESTful api that accepts and returns JSON. As a RESTful API, the HTTP method used on a request is meaningful so please only use GETs when you intend to retrieve a record, POST to create a record, etc. More information will be provided about when it is appropriate to GET or POST in the discussions of the different endpoints below.

Required Headers

The following headers are meaningful when communicating with the API

Header	Req’d?	Expected Values
Accept	Yes	application/json
Accept-Language	Yes	Any valid language code Example: en-US,en;q=0.8
X-Api-Key	Yes	Any valid API Key Will be provided by Second Street
X-Organization-Id	Yes	Any valid organization ID Will be provided by Second Street
X-Referring-Url	Yes	The URL the form will be hosted on
Authorization	Yes	An access token generated via sessions

Data Contexts

When requesting Categorical Charts or Messaging Statistics from the Second Street API, you can include one of the following query strings in your GET request to get data for different contexts:

To get data for a single Schedule Instance in a Newsletter Campaign, use ?scheduleInstanceId={{id}}

You can get the ID of a Schedule Instance from the URL you see when looking at its dashboard. For example, the Schedule Instance at https://partners.secondstreet.com/p/app/#/o/397282/message-campaigns/198/schedule-instances/441/dashboard has an ID of 441.

To get data for a single Message in a Drip Campaign, use ?messageId={{id}}

You can get the ID of a Message from the URL you see when looking at its dashboard. For example, the Message at https://partners.secondstreet.com/p/app/#/o/397282/message-campaigns/198/messages/319/dashboard has an ID of 319.

To get data for an entire Campaign, use ?messageCampaignId={{id}}

You can get the ID of a Message Campaign from the URL you see when looking at its dashboard. For example, the Message Campaign at https://partners.secondstreet.com/p/app/#/o/397282/message-campaigns/198/dashboard has an ID of 198.

Paging

When requesting data from certain endpoints of the Second Street API, you can include the following query strings in your GET request to page through data:

?pageSize={{size}}

How many records to return in a single request. If unspecified, defaults to 1000.

?pageIndex={{index}}

Which page of records to show, 1-indexed. For example, a pageSize of 1000 and a pageIndex of 2 will return the second thousand records. If unspecified, defaults to 1.

Paged endpoints will have a second JSON root object, meta. The meta object will have page_index, page_size, and total_records properties.

page_index is the same value you sent through the GET request.
page_size is the same value you sent through the GET request.
total_records is the total count of records that can be accessed, and is your clue for whether you should request more pages of data.

Logging In

To begin a session that has the authority to access messaging analytics, perform a POST on /sessions:

POST to:

https://api.secondstreetapp.com/sessions

With body:

{
"sessions": [
   {
     "username": "[email protected]",
     "password": "password"
   }
]
}

The username should be the email address of a Second Street user that has all roles. The password should be their password. Be sure you’re using HTTPS in the request URL, as you don’t want to send passwords over plain text!

If successful, the server will respond with

{
"sessions": [
   {
     "access_token": "a1b2c3d4",
     "id": 22318,
     "organization_id": 397282,
     "organization_user_guid": "123456a7-ab12-1a23-1ab2-123a45bc6789",
     "organizations_count": 2,
     "username": "[email protected]"
   }
]
}

The most important value here is session.access_token. The access_token should be added to your headers to be used for all future requests as this identifies the user.

Need Help

Anonymous

9 years ago

FYI we got it connecting using

let
     apiUrl = "https://api.secondstreetapp.com/sessions",
     data ="{'sessions':[{'username':'**', 'password': '**'}]}",
     options = [
     Headers =[#"Content-Type"="application/json", #"X-Api-Key"="*****"],
     Content = Text.ToBinary(data)
     ],
     Value = Web.Contents(apiUrl,options),

in
     Value

11 Replies

Anonymous

Not applicable

9 years ago

Hi Anonymous,

Add a new blank query in Power BI Desktop, then paste one of the following code in Advanced Editor and check if you get expected result.

let
    Source = Json.Document(Web.Contents("your API address"), [Headers=[#" Authorization "="Your token"]])
in
    Source

let    
    Source = Web.Contents("URL", [Headers=[#" Authorization  " = "Your token"]])
in
    Source

There are some blogs for your reference.
https://jessedotnet.com/2016/06/24/power-bi-connect-to-your-secure-api/
https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/

Thanks,
Lydia Zhang

Anonymous

Not applicable

9 years ago

Hi Lydia,

Thanks for getting back to me, the thing is though in order to get the token you have to first POST to the api.

Its like a two step authentication. How would this translate?

let
     apiUrl = "https://api.secondstreetapp.com/sessions",
     options = [Headers =[#"username"="***"],[#"password"="****"]],
     result = Web.Contents(apiUrl , options)
in
     result

When I put in the above I get Access to the resource is forbidden and then taken to a login screen, what I am needing it to return is...

Thanks

Chris

Anonymous
Not applicable
9 years ago
Hi Anonymous,

What do you mean by "two step authentication"? If you refer to 2 factor authentication, as far as I know, Power BI doesn't support this auth method.

Thanks,
Lydia Zhang

Shamatix
Post Partisan
7 years ago
Hey fellow users,

I need something similar.
I have a post request on https://xxxxxxxx.dk/oauth2/token
I need to set two parameters in the body (Email and password), how can I do this?

Best regards

Forum Discussion

Connect to restful api data source multiple times

Overview

API Basics

Required Headers

Data Contexts

Paging

Logging In

11 Replies

Related content

Connecting to a Rest API source - pipeline vs dataflow gen2

Connecting Multiple Smartsheets

Rest API Informations

Issues with Fabric Connection REST API and On-Prem Connections

Rest API connecting to Power BI