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

[mhari.krishna]

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

[Hariharan Iyer]

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!

[Kuan Yi Ming _ServiceRocket_]

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]

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

[Najeeb Shah]

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.