Fetch data from custom report via API in Excel

API & integrations
1

Hi

First of all sorry if this question is rather stupid, but I can’t seem to find a solution to my problem, and I’m an absolute beginner when it comes to API and integrating data into Excel. I hope you guys can help me out.

Is it possible to fetch data from a custom report into Excel? And if yes, then how?

I’ve managed to get a power query up and running, which will fetch a bearer token, but for some reason it won’t allow me to connect to the query and comes up with this message:

datasource.error web.contents failed to get contents from (500) internal server error

I’ve included my code here:

piwik api excel.json (1.5 KB)

/Martin

2

Hi. Do you really need to do it through api? What’s the end goal? Do you want to fetch that data regularly in an automated way? I’m asking because the other option is to simply download a CSV from the UI:

image

3

My end goal is to make a dashboard in excel, which fetches multiple datasources and analyses them automaticly (this method is coincidentally based on a framework based on Piwik PRO - Web analytics for governments: The framework for setting the right KPIs - Piwik PRO)

Right now it’s a proof-of-concept which I don’t intend to share with others, which is one reason I’m using Excel. I expect to migrate it to Tableau eventually, but right now Excel is the better option.

I figured out what went wrong. For some reason the json was wrong and I simply inserted a file-link to the “body”: body = Json.Document(File.Contents(“C:\Users\XXXXXXXXX\Documents\piwik-file.json”)),

If there’s a smarter way to automatically fetch the data, please let me know.

4

Thanks for the context. As for the Tableau, it would be much easier since we have a dedicated integration for that: Tableau integration | Piwik PRO help center

5

I know, but there’s a whole proces, which involves a few people to just add a data source in Tableau in my organization. Excel just fits the Proof-of-concept need better right now.

I’ll probably use Tableau later.

6

Hello @martho,

I wanted to update this thread to let you know that Piwik PRO data is now available in the Supermetrics connector for Excel:

7

Thanks for the update! As you mention in the documentation, Postman is also a viable alternative.