Exporting group members emails as csv

Is there a way to easily export the email addresses of group members as a csv? I am aware that it is possible to export such list from the /admin/users/list page but I can’t seem to find a way to do this for a particular group.

Bonus point for being able to export emails only.

The goal here is to make it easy to synchronise the emails from our beta group on discourse with the lists required to setup beta testing on Google Play and the App Store.

2 Likes

Sure, @techapj can assist you with this.

1 Like

Hi @phi,

Yes, this can be done by running a query from /admin/plugins/explorer once you have discourse-data-explorer plugin installed.

Here is the query you are looking for:

-- [params]
-- group_id :group

WITH user_groups AS (
    SELECT gu.user_id
    FROM groups g
    JOIN group_users gu ON gu.group_id = g.id
    WHERE g.id = :group
    GROUP BY gu.user_id
)
SELECT u.email
FROM users u
INNER JOIN user_groups ug ON u.id = ug.user_id
WHERE u.id > 0
ORDER BY u.id DESC

The group name will be entered in group field, and once you run the query you will have the option to export the result, which will only contain user emails from the group you specified.

I hope this helps.

9 Likes

where was this when i need this the most? :smiley:

thanks for this.

1 Like

Is there a way to do this via the API?

http://docs.discourse.org/#tag/Admin%2Fpaths%2F~1groups~1{group_name}~1members.json%2Fget

I see that I can get a group’s members, but there’s no parameter to request email. For getting a list of users, I can set the email parameter as true.

My use case: I moderate a company forum. We have a group that we use to automatically apply to our employees based on their email domain. When an employee posts, we’d like to be able to reconcile the username with an email address. I know that moderators and admins can click to show the email, but that’s tedious. Also, we want all of our employees to be able to see this list. My plan is to export the users and their emails via the groups endpoint and then regularly update a forum post that is private to our employees with this information.

If there are other ways that you think would be more efficient, I’d love to hear them, too.

1 Like

@codinghorror, any idea? Thank you!

Are you looking for paid, hosted support?

Since the email address was moved to the user_emails table, this query has changed.

The working query is now

-- [params]
-- group_id :group

WITH user_groups AS (
    SELECT gu.user_id
    FROM groups g
    JOIN group_users gu ON gu.group_id = g.id
    WHERE g.id = :group
    GROUP BY gu.user_id
)
SELECT ue.email
FROM users u
INNER JOIN user_emails ue ON u.id = ue.user_id
INNER JOIN user_groups ug ON u.id = ug.user_id
WHERE u.id > 0
ORDER BY u.id DESC
5 Likes