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
japolo
Frequent Visitor

Connect to Confluence (Atlassian)

Hi,

I want to connect to one or multiple pages in Confluence where other teams update tables that are visible for anyone. I don't want to pay for custom connectors that I know are available.

I'm trying to figure out the best way of doing it but have ran into the following limitations:

- Web API or Web Page doesn't work on either Desktop nor Service ( Have tried Anonymous, Basic and Organizational, is it supposed to work properly with Basic, if yes, is the password supposed to be the one that I have to Confluence? Because that returns "Invalid credentials"

- Start -> ODBC Data Sources 32/64 bit does not give the options that are available in instructions that I find (System DSN is empty)

japolo_0-1660832021086.png

- And obviously neither ODATA or ODBC works

If anyone has any good ideas i'd greatly appreciate it.

 

Kindly

/J

10 REPLIES 10
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @japolo - sorry my instruction were unclear.  I didn't mean to swap the order of the Web and Json functions.

I have update my example to show you how the Web.Page function should work instead of the Json.Document function:

In my original example, I am connecting to the Rest API, so the result is a JSON file.  The last step will work, while the Web.Page fails.  I split the steps up to show this. I also added an extra step to show "Text" string included in the JSON file. 

 

I suspect your Web.Contents using the updated #"Relative Path" step return HTML format instead of JSON.  So, #"Web Page" step will work, while the #"Json Document" step will fail. 

If both are failing, hopefully, the #"View Context Text" will show something meaningful to help us figure out the actual format of the Web Contents.  If we still have an error message, you may need to delete the highlighted below.

let
    #"Base URL" = "https://your-domain.atlassian.net/",
    #"Relative Path" = "wiki/rest/api/user/current",
    #"Get Contents" = 
            Web.Contents(#"Base URL", 
                [
                    RelativePath=#"Relative Path", 
                    Headers=[Accept="application/json"]  //delete this row
                ]
            ),
    #"View Content Text" = Text.FromBinary(#"Get Contents"),
    #"Web Page" = Web.Page(#"Get Contents"),
    #"Json Document" = Json.Document( #"Get Contents")
in
    #"Json Document"

 

Hi,

Thank you for such thorough explanations.

Unfortunately I can't make it work. I have tried to both remove and leave the row you suggested to delete and none worked, instead it returns the error message that the expected type isn't HTML. I also tried switching the last 2 steps from "Get Contents" to the previous step.

japolo_0-1661857884951.png

japolo_1-1661858124173.png

And authorization wise, I'm using the generated token from Atlassian together with the e-mail I use in Confluence & Power BI through Basic Authentication

japolo_2-1661858184022.png

 

 

@japolo - Can I check that you are getting a result when you run it for the API?

"wiki/rest/api/user/current"

Do you know that you can preview each of the "Applied Steps" in Power Query.  What you can do is switch between the each step in the below to see when the error occurs.


Actually meant this:

let
    #"Base URL" = "https://your-domain.atlassian.net/",
    #"Relative Path" = "wiki/rest/api/user/current",
    #"Get Contents" = 
            Web.Contents(#"Base URL", 
                [
                    RelativePath=#"Relative Path", 
                    Headers=[Accept="application/json"]  //delete this row
                ]
            ),
    #"View Content Text" = Text.FromBinary(#"Get Contents"),
    #"Web Page" = Web.Page(#"Get Contents")
in
     #"Web Page" 

 I think you need to stop using the Json.Document step.  

 

 

Hi,

Yes, i've tested the different steps. The base URL and Relative Path returns what has already been added after the "=" symbol.

japolo_0-1661864999636.png

 

Testing the Get Content Step returns the base URL with the web symbol:

japolo_2-1661865086537.png

The View Content step returns the HTML code:

japolo_3-1661865151315.png

The Web page step prompts me to sign in with credentials. I use my e-mail and the created token in atlassian but it tells me the credentials are invalid so I guess the problem starts here. I've tried the "normal" name I have there as well and all kinds of other usernames and passwords as well but none works. Should it not be the username/e-mail with the created token? And i'm using the Basic one.

japolo_4-1661865924275.png

And the last step prompts that an error reason could be invalid credentials.

japolo_5-1661866000065.png

 

Sorry @japolo - I get the same error when trying to read an HTML page from Confluence.  So, it looks like there is no access to read the Web.Page Contents when in HTML format.    I can see the Binary Text in #"View Content Text" step, but this does not correlate to the objects on the screen because many of these are Java Scriyt(?) objects.

 

I think Confluence wants you extract the data via the REST API call only.  I don't know what syntax you need to extract the page you are calling.  Could you please post your question on the Altassian Developer forum (The Atlassian Developer Community).  Hopefully, someone can then explain how to translate the Web URL into the Get Content API.

Hi @Daryl-Lynch-Bzy 

I'll give that a try, thank for trying things and helping out, much appreciated!

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @japolo 
You can use Web.Contents to access Confluence API.  I going to show you an example based on the following:

api-wiki-rest-api-user-current-get 

The following should work to retrieve results because the User and Token can be stored in the Data Source Credentials (Basic).  However you need to generate token using this Manage API tokens for your Atlassian account | Atlassian Support

 

 

let
    #"Base URL" = "https://your-domain.atlassian.net/",
    #"Relative Path" = "wiki/rest/api/user/current",
    #"Get Data" = 
        Json.Document(
            Web.Contents(#"Base URL", 
                [
                    RelativePath=#"Relative Path", 
                    Headers=[Accept="application/json"]
                ]
            )
        )
in
    #"Get Data"

 

 

 

Hi @Daryl-Lynch-Bzy ,

I've tried your instructions but I can't make it work. I've generated a token and tried the it but it doesn't work, i'm getting the following error:

japolo_0-1661846834907.png

 

And i replaced the base URL and relative path with the confluence page I wanted to connect to

japolo_1-1661847013168.png

Not sure what is missing or if it's something else.

 

Kind regards,

J

Hi @japolo - it appears that you trying to pull a Web.Page instead of Json.Document.   Try swapping the these functions to see if this works.

Hi,

I placed web.contents first instead but that returns an error as well:

japolo_0-1661852324429.png

 

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