Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

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": "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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

11 REPLIES 11
Shamatix
Post Partisan
Post Partisan

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

v-yuezhe-msft
Employee
Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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...

 

{
 "sessions": [
   {
     "access_token": "a1b2c3d4",
     "id": 22318,
     "organization_id": 397282,
     "organization_user_guid": "123456a7-ab12-1a23-1ab2-123a45bc6789",
     "organizations_count": 2,
     "username": "user@example.com"
   }
 ]
}

 

Thanks

 

Chris

Hi Chris try adding more options to your first request. Check the documentation maybe something like

 #"Content-Type"="application/json"],

or

 #"Accept"="application/json"],

Sometimes the REST endpoint don't know that the requests have to be JSON so they default to some basic authentication.

After you get your successful response from this request. Use the token from the response to construct another request with token already in the header

 #"Autohrization"="token"],
Anonymous
Not applicable

Thanks for replying guys and gals.

 

I aksed the company who responded with

 

The username and password aren't supposed to be headers, they are supposed to be part of a JSON body sent to the API. The headers required are as follows:

Content-Type:application/json
X-Api-Key:

 

So I think you authenitcate after ?

Hi @Anonymous,

Please check the discussion in this simialr  thread: https://community.powerbi.com/t5/Desktop/Get-data-from-web-error-A-web-API-key-can-only-be-specified-when/m-p/104101.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi,

 

We managed to get to conect to the api using PHP but wanting to do it in PowerBi.  How would I go about translating

 

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.secondstreetapp.com/sessions");
//curl_setopt($ch, CURLOPT_URL, "https://www.google.com");
//$data = array("sessions"=>array("username"=>"****", "password"=>"***"));
$data = '{"sessions":[{"username":"*****", "password": "*******"}]}';
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST"); 
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Accept: application/json','Content-Type: application/json', 'X-Api-Key: *****'));
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS,$data);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_VERBOSE, true);
$session = curl_exec($ch);
echo curl_error($ch);
curl_close($ch);
$sessionDecoded = json_decode($session);
if ($campaignID == null)
{



	$ch = curl_init();
	curl_setopt($ch, CURLOPT_URL, "https://api.secondstreetapp.com/message_campaigns?statusTypeId=1");
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET"); 
	curl_setopt($ch, CURLOPT_HTTPHEADER, array('Accept: application/json','Content-Type: application/json', 'X-Api-Key: *****', 'Authorization:'.$sessionDecoded->sessions[0]->access_token, 'X-Organization-Id:'.$sessionDecoded->sessions[0]->organization_id));
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_VERBOSE, true);
	$newsletters = curl_exec($ch);
	echo curl_error($ch);
	curl_close($ch);

	$newslettersDecoded = json_decode($newsletters);
	echo "<div class='list-group'>";
 	echo "<h1>Select a message</h1>";
	foreach ($newslettersDecoded->message_campaigns as $campaign)
	{
 		echo "<a href='index.php?campaignid=".$campaign->id."&messagetype=".$campaign->message_campaign_type_id."' class='list-group-item'>".$campaign->name."</a>";
 	}

 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

how did you set your credentials? as anonymous?
I have a similar API but it seems it is not accepting my username and password parameters.

When I use anonymous, I get error. When I use basic auth (with pop up box), I get error from web.content only possible when anonymous...

Anonymous
Not applicable

We ended up going down the PHP route

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors