Using REST API (from Prestashop ecommerce CMS)

jfournier · ‎11-16-2017

Hello,

I'm trying to use my Prestashop API as a DataSource in PBI desktop.

I can connect to the API without any problem.

As an example, I will talk about Products !

I want to have a table with all the products information.

I have a URL for that : http://mydomain.com/api/products.

It returns me something like that in a web browser :

<prestashop xmlns:xlink="http://www.w3.org/1999/xlink">
<products>
<product id="66" xlink:href="http://mydomain.com/api/products/66"/>
<product id="307" xlink:href="http://mydomain.com/api/products/307"/>
<product id="407" xlink:href="http://mydomain.com/api/products/407"/>
</products>
</prestashop>

When I browse a product (say the id 66) I have :

<prestashop xmlns:xlink="http://www.w3.org/1999/xlink">
<product>
<id>
<![CDATA[ 66 ]]>
</id>
<id_manufacturer xlink:href="http://mydomain.com/api/manufacturers/7">
<![CDATA[ 7 ]]>
</id_manufacturer>
<id_supplier>
<![CDATA[ 0 ]]>
</id_supplier>
<id_category_default xlink:href="http://mydomain.com/api/categories/2">
<![CDATA[ 2 ]]>
</id_category_default>
<new/>
<cache_default_attribute>
<![CDATA[ 0 ]]>
</cache_default_attribute>
<id_default_image xlink:href="http://mydomain.com/api/images/products/66/1185" notFilterable="true">
<![CDATA[ 1185 ]]>
</id_default_image>
<id_default_combination notFilterable="true"/>
<id_tax_rules_group xlink:href="http://mydomain.com/api/tax_rule_groups/1">
<![CDATA[ 1 ]]>
</id_tax_rules_group>
<position_in_category notFilterable="true">
<![CDATA[ 40 ]]>
</position_in_category>
<quantity notFilterable="true">
<![CDATA[ 0 ]]>
</quantity>
<type notFilterable="true">
<![CDATA[ simple ]]>
</type>
<id_shop_default>
<![CDATA[ 1 ]]>
</id_shop_default>
<reference>
<![CDATA[ COMPO-PERSO ]]>
</reference>
<supplier_reference/>
<location/>
<width>
<![CDATA[ 0.000000 ]]>
</width>
<height>
<![CDATA[ 0.000000 ]]>
</height>
<depth>
<![CDATA[ 0.000000 ]]>
</depth>
<weight>
<![CDATA[ 0.000000 ]]>
</weight>
<quantity_discount>
<![CDATA[ 0 ]]>
</quantity_discount>
<ean13/>
<upc/>
<cache_is_pack>
<![CDATA[ 0 ]]>
</cache_is_pack>
<cache_has_attachments>
<![CDATA[ 0 ]]>
</cache_has_attachments>
<is_virtual>
<![CDATA[ 0 ]]>
</is_virtual>
<on_sale>
<![CDATA[ 0 ]]>
</on_sale>
<online_only>
<![CDATA[ 0 ]]>
</online_only>
<ecotax>
<![CDATA[ 0.000000 ]]>
</ecotax>
<minimal_quantity>
<![CDATA[ 1 ]]>
</minimal_quantity>
<price>
<![CDATA[ 58.333333 ]]>
</price>
<wholesale_price>
<![CDATA[ 0.000000 ]]>
</wholesale_price>
<unity/>
<unit_price_ratio>
<![CDATA[ 0.000000 ]]>
</unit_price_ratio>
<additional_shipping_cost>
<![CDATA[ 0.00 ]]>
</additional_shipping_cost>
<customizable>
<![CDATA[ 0 ]]>
</customizable>
<text_fields>
<![CDATA[ 0 ]]>
</text_fields>
<uploadable_files>
<![CDATA[ 0 ]]>
</uploadable_files>
<active>
<![CDATA[ 1 ]]>
</active>
<redirect_type>
<![CDATA[ 404 ]]>
</redirect_type>
<id_product_redirected>
<![CDATA[ 0 ]]>
</id_product_redirected>
<available_for_order>
<![CDATA[ 1 ]]>
</available_for_order>
<available_date>
<![CDATA[ 0000-00-00 ]]>
</available_date>
<condition>
<![CDATA[ new ]]>
</condition>
<show_price>
<![CDATA[ 1 ]]>
</show_price>
<indexed>
<![CDATA[ 0 ]]>
</indexed>
<visibility>
<![CDATA[ none ]]>
</visibility>
<advanced_stock_management>
<![CDATA[ 0 ]]>
</advanced_stock_management>
<date_add>
<![CDATA[ 2014-12-11 23:21:51 ]]>
</date_add>
<date_upd>
<![CDATA[ 2017-01-27 18:29:02 ]]>
</date_upd>
<pack_stock_type>
<![CDATA[ 3 ]]>
</pack_stock_type>
<name>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ Composition personnalisée ]]>
</language>
</name>
<associations>
<categories nodeType="category" api="categories">
<category xlink:href="http://mydomain.com/api/categories/2">
<id>
<![CDATA[ 2 ]]>
</id>
</category>
</categories>
<combinations nodeType="combination" api="combinations"/>
<product_option_values nodeType="product_option_value" api="product_option_values"/>
<product_features nodeType="product_feature" api="product_features"/>
<tags nodeType="tag" api="tags"/>
<stock_availables nodeType="stock_available" api="stock_availables">
<stock_available xlink:href="http://mydomain.com/api/stock_availables/74">
<id>
<![CDATA[ 74 ]]>
</id>
<id_product_attribute>
<![CDATA[ 0 ]]>
</id_product_attribute>
</stock_available>
</stock_availables>
<accessories nodeType="product" api="products"/>
<product_bundle nodeType="product" api="products"/>
</associations>
</product>
</prestashop>

What I want is to have 1 line per product with all the fields I need (I will take care of relationship manually !).

Is it possible ?

Thanks

v-yuezhe-msft · ‎11-16-2017

@jfournier，

What is the current result do you get when you connect to the API in Power BI Desktop? And could you please post expected result in table format?

Regards,
Lydia

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.

jfournier · ‎11-17-2017

Hello,

When I connect to the API Root, I have :

<prestashop xmlns:xlink="http://www.w3.org/1999/xlink">
<api shopName="MyDomain">
<customers xlink:href="http://mydomain.com/api/customers" get="true" put="true" post="true" delete="true" head="true">
<description xlink:href="http://mydomain.com/api/customers" get="true" put="true" post="true" delete="true" head="true">The e-shop's customers</description>
<schema xlink:href="http://mydomain.com/api/customers?schema=blank" type="blank"/>
<schema xlink:href="http://mydomain.com/api/customers?schema=synopsis" type="synopsis"/>
</customers>
<order_details xlink:href="http://mydomain.com/api/order_details" get="true" put="true" post="true" delete="true" head="true">
<description xlink:href="http://mydomain.com/api/order_details" get="true" put="true" post="true" delete="true" head="true">Details of an order</description>
<schema xlink:href="http://mydomain.com/api/order_details?schema=blank" type="blank"/>
<schema xlink:href="http://mydomain.com/api/order_details?schema=synopsis" type="synopsis"/>
</order_details>
<products xlink:href="http://mydomain.com/api/products" get="true" put="true" post="true" delete="true" head="true">
<description xlink:href="http://mydomain.com/api/products" get="true" put="true" post="true" delete="true" head="true">The products</description>
<schema xlink:href="http://mydomain.com/api/products?schema=blank" type="blank"/>
<schema xlink:href="http://mydomain.com/api/products?schema=synopsis" type="synopsis"/>
</products>
<stocks xlink:href="http://mydomain.com/api/stocks" get="true" put="false" post="false" delete="false" head="true">
<description xlink:href="http://mydomain.com/api/stocks" get="true" put="false" post="false" delete="false" head="true">Stocks</description>
<schema xlink:href="http://mydomain.com/api/stocks?schema=blank" type="blank"/>
<schema xlink:href="http://mydomain.com/api/stocks?schema=synopsis" type="synopsis"/>
</stocks>

</api>
</prestashop>

If I list the prodcuts (http://mydomain.com/products) :

<prestashop xmlns:xlink="http://www.w3.org/1999/xlink">
<products>
<product id="66" xlink:href="http://mydomain.com/api/products/66"/>
<product id="307" xlink:href="http://mydomain.com/api/products/307"/>
<product id="407" xlink:href="http://mydomain.com/api/products/407"/>
<product id="617" xlink:href="http://mydomain.com/api/products/617"/>
<product id="653" xlink:href="http://mydomain.com/api/products/653"/>
<product id="662" xlink:href="http://mydomain.com/api/products/662"/>
<product id="663" xlink:href="http://mydomain.com/api/products/663"/>
<product id="669" xlink:href="http://mydomain.com/api/products/669"/>
<product id="3" xlink:href="http://mydomain.com/api/products/3"/>
<product id="7" xlink:href="http://mydomain.com/api/products/7"/>
<product id="11" xlink:href="http://mydomain.com/api/products/11"/>
<product id="12" xlink:href="http://mydomain.com/api/products/12"/>
<product id="15" xlink:href="http://mydomain.com/api/products/15"/>
<product id="16" xlink:href="http://mydomain.com/api/products/16"/>
<product id="20" xlink:href="http://mydomain.com/api/products/20"/>
<product id="22" xlink:href="http://mydomain.com/api/products/22"/>
<product id="24" xlink:href="http://mydomain.com/api/products/24"/>
<product id="25" xlink:href="http://mydomain.com/api/products/25"/>
<product id="27" xlink:href="http://mydomain.com/api/products/27"/>
<product id="30" xlink:href="http://mydomain.com/api/products/30"/>
<product id="33" xlink:href="http://mydomain.com/api/products/33"/>
<product id="34" xlink:href="http://mydomain.com/api/products/34"/>
<product id="35" xlink:href="http://mydomain.com/api/products/35"/>
<product id="38" xlink:href="http://mydomain.com/api/products/38"/>
<product id="40" xlink:href="http://mydomain.com/api/products/40"/>
<product id="43" xlink:href="http://mydomain.com/api/products/43"/>
<product id="44" xlink:href="http://mydomain.com/api/products/44"/>
<product id="57" xlink:href="http://mydomain.com/api/products/57"/>
<product id="58" xlink:href="http://mydomain.com/api/products/58"/>
<product id="59" xlink:href="http://mydomain.com/api/products/59"/>
<product id="60" xlink:href="http://mydomain.com/api/products/60"/>
<product id="61" xlink:href="http://mydomain.com/api/products/61"/>
<product id="63" xlink:href="http://mydomain.com/api/products/63"/>
<product id="74" xlink:href="http://mydomain.com/api/products/74"/>
<product id="75" xlink:href="http://mydomain.com/api/products/75"/>
<product id="78" xlink:href="http://mydomain.com/api/products/78"/>
<product id="84" xlink:href="http://mydomain.com/api/products/84"/>
<product id="86" xlink:href="http://mydomain.com/api/products/86"/>
<product id="87" xlink:href="http://mydomain.com/api/products/87"/>
<product id="102" xlink:href="http://mydomain.com/api/products/102"/>
<product id="113" xlink:href="http://mydomain.com/api/products/113"/>
<product id="114" xlink:href="http://mydomain.com/api/products/114"/>
</products>
</prestashop>

When I go to a particular product (say the 66) :

<prestashop xmlns:xlink="http://www.w3.org/1999/xlink">
<product>
<id>
<![CDATA[ 66 ]]>
</id>
<id_manufacturer xlink:href="http://mydomain.com/api/manufacturers/7">
<![CDATA[ 7 ]]>
</id_manufacturer>
<id_supplier>
<![CDATA[ 0 ]]>
</id_supplier>
<id_category_default xlink:href="http://mydomain.com/api/categories/2">
<![CDATA[ 2 ]]>
</id_category_default>
<new/>
<cache_default_attribute>
<![CDATA[ 0 ]]>
</cache_default_attribute>
<id_default_image xlink:href="http://mydomain.com/api/images/products/66/1185" notFilterable="true">
<![CDATA[ 1185 ]]>
</id_default_image>
<id_default_combination notFilterable="true"/>
<id_tax_rules_group xlink:href="http://mydomain.com/api/tax_rule_groups/1">
<![CDATA[ 1 ]]>
</id_tax_rules_group>
<position_in_category notFilterable="true">
<![CDATA[ 40 ]]>
</position_in_category>
<manufacturer_name notFilterable="true">
<![CDATA[ Groupe Candy & Co ]]>
</manufacturer_name>
<quantity notFilterable="true">
<![CDATA[ 0 ]]>
</quantity>
<type notFilterable="true">
<![CDATA[ simple ]]>
</type>
<id_shop_default>
<![CDATA[ 1 ]]>
</id_shop_default>
<reference>
<![CDATA[ COMPO-PERSO ]]>
</reference>
<supplier_reference/>
<location/>
<width>
<![CDATA[ 0.000000 ]]>
</width>
<height>
<![CDATA[ 0.000000 ]]>
</height>
<depth>
<![CDATA[ 0.000000 ]]>
</depth>
<weight>
<![CDATA[ 0.000000 ]]>
</weight>
<quantity_discount>
<![CDATA[ 0 ]]>
</quantity_discount>
<ean13/>
<upc/>
<cache_is_pack>
<![CDATA[ 0 ]]>
</cache_is_pack>
<cache_has_attachments>
<![CDATA[ 0 ]]>
</cache_has_attachments>
<is_virtual>
<![CDATA[ 0 ]]>
</is_virtual>
<on_sale>
<![CDATA[ 0 ]]>
</on_sale>
<online_only>
<![CDATA[ 0 ]]>
</online_only>
<ecotax>
<![CDATA[ 0.000000 ]]>
</ecotax>
<minimal_quantity>
<![CDATA[ 1 ]]>
</minimal_quantity>
<price>
<![CDATA[ 58.333333 ]]>
</price>
<wholesale_price>
<![CDATA[ 0.000000 ]]>
</wholesale_price>
<unity/>
<unit_price_ratio>
<![CDATA[ 0.000000 ]]>
</unit_price_ratio>
<additional_shipping_cost>
<![CDATA[ 0.00 ]]>
</additional_shipping_cost>
<customizable>
<![CDATA[ 0 ]]>
</customizable>
<text_fields>
<![CDATA[ 0 ]]>
</text_fields>
<uploadable_files>
<![CDATA[ 0 ]]>
</uploadable_files>
<active>
<![CDATA[ 1 ]]>
</active>
<redirect_type>
<![CDATA[ 404 ]]>
</redirect_type>
<id_product_redirected>
<![CDATA[ 0 ]]>
</id_product_redirected>
<available_for_order>
<![CDATA[ 1 ]]>
</available_for_order>
<available_date>
<![CDATA[ 0000-00-00 ]]>
</available_date>
<condition>
<![CDATA[ new ]]>
</condition>
<show_price>
<![CDATA[ 1 ]]>
</show_price>
<indexed>
<![CDATA[ 0 ]]>
</indexed>
<visibility>
<![CDATA[ none ]]>
</visibility>
<advanced_stock_management>
<![CDATA[ 0 ]]>
</advanced_stock_management>
<date_add>
<![CDATA[ 2014-12-11 23:21:51 ]]>
</date_add>
<date_upd>
<![CDATA[ 2017-01-27 18:29:02 ]]>
</date_upd>
<pack_stock_type>
<![CDATA[ 3 ]]>
</pack_stock_type>
<meta_description>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ ]]>
</language>
</meta_description>
<meta_keywords>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ ]]>
</language>
</meta_keywords>
<meta_title>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ ]]>
</language>
</meta_title>
<link_rewrite>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ composition-personnalisee ]]>
</language>
</link_rewrite>
<name>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ Composition personnalisée ]]>
</language>
</name>
<description>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ ]]>
</language>
</description>
<description_short>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ ]]>
</language>
</description_short>
<available_now>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ ]]>
</language>
</available_now>
<available_later>
<language id="1" xlink:href="http://mydomain.com/api/languages/1">
<![CDATA[ ]]>
</language>
</available_later>
<associations>
<categories nodeType="category" api="categories">
<category xlink:href="http://mydomain.com/api/categories/2">
<id>
<![CDATA[ 2 ]]>
</id>
</category>
</categories>
<images nodeType="image" api="images">
<image xlink:href="http://mydomain.com/api/images/products/66/1185">
<id>
<![CDATA[ 1185 ]]>
</id>
</image>
</images>
<combinations nodeType="combination" api="combinations"/>
<product_option_values nodeType="product_option_value" api="product_option_values"/>
<product_features nodeType="product_feature" api="product_features"/>
<tags nodeType="tag" api="tags"/>
<stock_availables nodeType="stock_available" api="stock_availables">
<stock_available xlink:href="http://mydomain.com/api/stock_availables/74">
<id>
<![CDATA[ 74 ]]>
</id>
<id_product_attribute>
<![CDATA[ 0 ]]>
</id_product_attribute>
</stock_available>
</stock_availables>
<accessories nodeType="product" api="products"/>
<product_bundle nodeType="product" api="products"/>
</associations>
</product>
</prestashop>

What I would like to have is when I connect to the Product API (http://mydomain.com/products) is to automaticly follow the xlink Href and fill the table with products' details.

fields would be (non exhaustive list) :

id
name
reference
price
...

Then, I would like to do the same with Orders, OrderDetails, Customers....

Thanks a lot for your help !

Regards,

jfournier · ‎11-23-2017

Hi,

No one has ever tried to achieve this ?

Thanks a lot,

Using REST API (from Prestashop ecommerce CMS)

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024