Unable to use the export API, getting 500 error

So I got a Matomo on-premise installation and I tried getting more dimensions dimensions in the tables in order to check the source and medium within a campaign, learnt that it isn’t possible. So went over to import the data into Looker Studio and all documentations refer to getting a token and using it with the API to populate a google spreadsheet.

My issue is that I can create the token but it seems to only be able to retrieve data from 1 of my websites, unfortunately it’s for the staging/test site which is a mirror of the real one. I’ve checked and the user I’m using is the superadmin user but it seems that I don’t have view access on the real site. I’m at a loss how to fix this and trying to find a proper guide to how to give Looker direct access to the SQL database instead but everywhere Looker is mentioned there is the google spreadsheet solution.

Which path should I go here and what’s the next step for me?