Dear @lubos, I was trying to access the API page using excel as follows:
But after inputting credentials I keep getting this error message:
Is there something more that I should do to get excel to log in to the api page?
Note: I am using an administrator account and use the password saved in my wallet.
I made some experiments with Excel and manager last summer. Working with the old APIs is completely impractical. It would be better to have an additional different authentication system throughout the whole software in order to allow Excel to get data through webtables.
Finally got it ro work. I logged in using chrome and in the console of chrome >> network you will get the key after login. I used it in the authentication without the “==” at the end and it worked.
Can you post some screenshots?
Here are the steps:
Step 1.
Step 2.
Step 3.
Step 4.
Step 5. Success!
Then you need to write your code to navigate through the api and retrieve data using power query code.
That’s up to where I got this summer. The problem is that what you get is completely useless since each JSON report points to a single record and not to a whole table of records.
I am working on it and if I get it to work I am going to share the code, but nothing is certain.
Here is the query to get the JSON data:
Connect to data source Manager Cloud
Set authentication method, use the method mentioned earlier, or basic authentication will work just fine (make sure you apply the authentication on the level of Manager Cloud)
Create a text query for API URL:
Create a text query for company key:
Obtain the list of object types and their links:
In excel, you can either manually manipulate the source of the business page in API to get the hrefs and the a in a table, or if you cannot do that, just copy and paste links manually.
For PowerBI object type link table See query in notes.
Use the following query to obtain the index for object types as well as the json records
you should change the value of objectKey to match the key for the object type you want (e.g. BalanceSheetAccount)
Your data table should look like this:
click on the 
button inside the header of the “Object column” to expand the column, you can choose the fields you like to expand into from there.
Note: Query for object type link table in PowerBI (Will not work in Excel)
I found this thread once before and used it’s info to gain access to manager’s api so that I could use the data with python. I always had the intention to post back with some examples but they were all pretty poor, so instead I just want to say thanks to @Ealfardan for posting the original question and the update and @Davide for your input too. With what you guys put together I was able to extract what I wanted the way I wanted it and use it for covid submissions.
Thanks. I’ll test tomorrow
@ealfardan, when trying this out, in step 3 in the inspection window the jsuid part of the cookie was missing, see picture. Clearing out cookies with the Clear browsing data in Chrome didn’t have any affect. So I tried connecting to Manager cloud Api without the cookie and it worked. That is maybe ok if it works or is there some security implication skipping out the cookie part ?
All the JSON records in Manager were there, which was by the way excellent, except for reports like Trial balance where only the date definition of the report seemed to be visible. Is there any way to get also this JSON data out of Manager somehow ? I tried this in Excel but is Power BI maybe more capable in this regard ?
Some websites store some kind of session validations in a cookie so if you gave them a key that doesn’t match it will void the session. I just didn’t want to waste any time to figure out how manager authentication works so I used the cookie just in case.
Apparently as you said, manager doesn’t use the cookie.
As far as I know, if the site requires authentication, both excel and power bi will only allow you to use data that is readily provided by the site. This means you cannot web scrape info if you have to login.
I am not an expert on the matter, but I think you would have to write your own scraping code. But, if you found a native function that does it, please let me know.
Just wanted to share this updated Power Query connector function for Excel and Power BI.
let
_getObjectIndex = (url as text, optional business as text, optional object as text) =>
let
objectURL = url &
(if business <> null then "/" & business else "") &
(if object <> null then "/" & object else "") & ".json",
result = try Json.Document(Web.Contents(objectURL)) otherwise null
in
result,
_getObjectTable = (#"API Endpoint" as text, optional #"Business Key" as text, optional #"Object Key" as text) =>
let
indexTable = Table.FromRecords(_getObjectIndex(#"API Endpoint", #"Business Key", #"Object Key")),
mergedObject = if #"Object Key" <> null
then
Table.AddColumn(
indexTable,
"Object",
each Json.Document(
Web.Contents((#"API Endpoint" & "/" & #"Business Key" & "/" & #"Object Key" & "/" & [Key] & ".json"))
)
)
else
indexTable
in
if Table.IsEmpty(mergedObject)
then
Table.FromRecords({[Key =" ", Name = "" , Timestamp = 0, FormType = "", Object = "Error: No Records found!"]})
else
try mergedObject otherwise
Table.FromRecords({[Key =" ", Name = "" , Timestamp = 0, FormType = "", Error = "Error: Can't connect!"]}),
getQueryTable = _getObjectTable
in
getQueryTable
Everyone is welcome to share their own improvements to this code.
Enjoy!
I moved a few months ago to n8n to connect to Manager and never looked back. I’m now able to sync data to external databases, use it with BI tools and dashboards, sync it with e-invoices and so on.
How much does n8n cost?
https://n8n.io/ It is using fair code license https://faircode.io/ i.e.:
Send me a private message. However I would prefer to answer to a public thread in order to help everyone.
I An unable to see cokki
It is just showing language against cokki on my api page.
Can You help me @Ealfardan please
I used this before because there was a problem with api authentication. But It’s not necessary anymore.
You just create your connector and once you load the query excel will prompt you with the following:
You can enter you credentials and make sure that you set your level to:
https://{ your domain }/api
When i am doing the same
It says Unable to connect.