Need to pull confluence space name and admin list in excel.

mhari_krishna February 20, 2024

Are there any possible ways to pull confluence space and admin list using plugin or any rest API in jira software (cloud). any suggestions is appreciated.

 

Thanks,

Hari

3 answers

1 vote
Hariharan Iyer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
February 20, 2024

Hi @mhari_krishna ,

 

You can use the get spaces and get space permissions APIs as documented here -https://developer.atlassian.com/cloud/confluence/rest/v2/api-group-space-permissions/#api-spaces-id-permissions-get

 

In the permissions API, principals who have the administer operation assigned to them will be space admins.

Hope that helps!

0 votes
Kuan Yi Ming _ServiceRocket_ February 20, 2024

Hi @mhari_krishna 

JMWE has the ability to run scripts that query the Confluence Cloud REST API and execute these scripts as part of workflow post-functions, event-based actions, or on a timed schedule.

Perhaps you can share with us further details on your requirements, so we can provide you with a more detailed answer?

Regards
Yi Ming

mhari_krishna February 20, 2024

Hi @Kuan Yi Ming _ServiceRocket_

Thanks for the answer but where do i need to use this code or query to get this information done.

thanks 

Hari

0 votes
Najeeb Shah
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 20, 2024

To pull Confluence space names and their respective admin lists into Excel, you can use Confluence's REST API along with Excel's Power Query feature. Here's a general outline of the steps you can follow:

1. **Get Space Information from Confluence**: Use Confluence's REST API to retrieve information about all spaces, including their names.

2. **Get Space Admins**: For each space, use the REST API to retrieve the list of administrators.

3. **Import Data into Excel**: Use Excel's Power Query feature to import the data obtained from the API into an Excel worksheet.

Here's how you can do it in more detail:

### Step 1: Get Space Information

You can use Confluence's REST API to get a list of all spaces. You'll need to make a GET request to the `/wiki/rest/api/space` endpoint. This will return JSON data containing information about all spaces.

### Step 2: Get Space Admins

For each space, you can make another API call to get the list of administrators. You'll need to make a GET request to `/wiki/rest/api/space/{spaceKey}/permission/admin` where `{spaceKey}` is the key of each space you obtained in Step 1.

### Step 3: Import Data into Excel

1. In Excel, go to the "Data" tab.
2. Select "From Web" or "From JSON" depending on the format of the API response.
3. Enter the URL of the Confluence REST API endpoint.
4. Power Query Editor will open where you can transform and load the data into Excel.

### Example Power Query for Space Information:

```excel
let
Source = Json.Document(Web.Contents("https://your-confluence-url/rest/api/space")),
Spaces = Source[results],
ConvertToTable = Table.FromList(Spaces, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandColumns = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"key", "name", "type", "description", "metadata"}, {"Key", "Name", "Type", "Description", "Metadata"}),
RemovedColumns = Table.RemoveColumns(ExpandColumns,{"Type", "Description", "Metadata"})
in
RemovedColumns
```

### Example Power Query for Space Admins:

```excel
let
Source = Json.Document(Web.Contents("https://your-confluence-url/rest/api/space/{spaceKey}/permission/admin")),
Admins = Source[results],
ConvertToTable = Table.FromList(Admins, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandColumns = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"type", "username"}, {"Type", "Username"}),
RemovedColumns = Table.RemoveColumns(ExpandColumns,{"Type"})
in
RemovedColumns
```

Make sure to replace `"https://your-confluence-url/rest/api/space"` with the actual URL of your Confluence instance.

This is a basic outline of the process. Depending on your specific requirements and the structure of your Confluence instance, you may need to adjust the API calls and data handling in Excel accordingly.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events