LinkedIn
Twitter
As an IT admin, you might come across a task where you have to download the list of all the Google Chat Spaces and its members. This scenario is very common for mergers and acquisitions, as well as divestiture cases.
Problem
Currently, the Google Admin console does not allow super admins to simply download a list of Chat Spaces along with their members. Hence, it's impossible for an admin to get that data without using a Google Chat API.
Solution
This article will focus on how to use the Chat API with Apps Script (Google’s Own IDE for Google Workspace). By the end of this article, you'll be able to list all the Google Chat Spaces, along with their members. This has been a long pending task from a lot of customers of all sizes.
Note: Currently we can’t list the external members, as well as any groups that are part of the Chat Space using the Chat API and the method described below.
Using a Chat API is not relatively straightforward and we have to perform multiple steps in order to get the required data. We will follow the below steps:
- Create a service account in a Google Cloud project
- Enable domain wide delegation to that service account
- Configure a Apps Script Project and deploy it
- Enable and Configure the Chat API
Let’s get started with the first task, which is to create a service account.
Create a service account
We'll be using this service account with a domain wide delegation so that we can list the Chat Spaces for all of the users that they're a part of. Follow the below steps to create a service account.
1. Go to the console.cloud.google.com → Select the organization and Create a project.
2. Once the project is created, go to IAM and admin → Service accounts.
3. Create a service account by clicking on + Create service account on the top of the page.
4. Once done, open the service account and go to Keys from the top menu → Add Key → Create new Key → JSON.
5. It will automatically download the key in your local system. Save the key - we will need it in the coming steps.
Provide domain wide delegation
We'll have to provide a domain wide delegation to the service account created in the previous step so that it can list the Spaces for each of your domain users.
1. Go back to the service account page, copy the 21 digit OAuth 2 Client ID of the service account that you created in step 3 (Create a Service Account section).
2. Go to the Domain wide delegation settings page and login with your super admin account, click on Add new.
3. Paste the Client ID in the ‘Client id’ field.
4. Add below scope in OAuth scope field. The scope allows read only access to the Chat Spaces.
https://www.googleapis.com/auth/chat.spaces.readonly
5. Click on the Authorize button.
Configure an Apps Script project
Since we'll be using Apps Script to list the Chat Spaces data, the next step is to create an Apps Script project, where we'll be storing and executing the required code.
1. Log in with your Google Workspace account and go to the Google Chat Spaces sheet.
2. Make a copy of the above file by going to Files → Make a Copy → Make a Copy.
3. Once a new sheet is created from the menu above, click on the Extensions → Apps Script. This will create a new Apps Script project and it will be automatically attached to your Google Sheet.
4. Once done, you can see the Apps Script project. Give it a name and add a service of Admin SDK API.
5. Add an OAuth Library using the below code.
1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
6. Create a new Script file with name OAuth and copy paste the below code in it. Remove <<<<<<<<<<<<<<< Your JSON Key goes here in single {} >>>>>>>>>>>>>>> and add your JSON key in a single brackets { }.
var json = <<<<<<<<<<<<<<< Your JSON Key goes here in single {} >>>>>>>>>>>>>>>
function getOAuthService(user) {
return OAuth2.createService('Service Account')
.setTokenUrl('https://accounts.google.com/o/oauth2/token')
.setPrivateKey(json.private_key)
.setIssuer(json.client_email)
.setSubject(user)
.setPropertyStore(PropertiesService.getScriptProperties())
.setParam('access_type', 'offline')
.setScope('https://www.googleapis.com/auth/chat.spaces.readonly');
}
function reset() {
var service = getOAuthService();
service.reset();
}
7. Create another Script file with the name List all the users and copy paste the below code. This will list all the users of the domain.
function listAllUser() {
var customerId = AdminDirectory.Customers.get('my_customer').id;
var SS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('User data');
var sheetrange = SS.getRange("A2:A")
sheetrange.clearContent()
var data = []; // array to store values
var pageToken, page;
do {
page = AdminDirectory.Users.list({
customer: customerId,
pageToken: pageToken
});
var users = page.users;
if (users) {
for (var i = 0; i < users.length; i++) {
var user = users[i];
data.push([user.primaryEmail]);//store in an array of arrays (one for each row)
}
} else {
Logger.log('No users found.');
}
pageToken = page.nextPageToken;
} while (pageToken);
SS.getRange(2, 1, data.length, data[0].length).setValues(data);
}
8. Add the Last script file with name List all Spaces and copy paste the below code.
function listGoogleChatSpaces() {
var userData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('User data').getDataRange().getValues()
var spaceData = []
var spaceDataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Space data');
for (var a = 1; a < userData.length; a++) {
var user = userData[a]
user = String(user)
var service = getOAuthService(user)
service.reset()
/** Set the API endpoint URL **/
var apiUrl = 'https://chat.googleapis.com/v1/spaces';
/** Make a request to list Chat spaces for all the users which are part of 'User data' sheet **/
try {
var response = UrlFetchApp.fetch(apiUrl, {
method: 'get',
headers: {
'Authorization': 'Bearer ' + service.getAccessToken(),
}
});
var data = JSON.parse(response.getContentText());
if (data.spaces && data.spaces.length > 0) {
for (var i = 0; i < data.spaces.length; i++) {
spaceData.push([data.spaces[i].name, data.spaces[i].displayName, data.spaces[i].spaceType, data.spaces[i].type, data.spaces[i].spaceThreadingState, data.spaces[i].spaceHistoryState, data.spaces[i].externalUserAllowed, user])
}
} else {
Logger.log('No Google Chat spaces found for ' + user);
}
} catch (error) {
Logger.log('Error for '+ user + ' : ' + error.toString());
}
}
/** Put data in 'Space Data' sheet **/
spaceDataSheet.getRange(2, 1, spaceData.length, spaceData[0].length).setValues(spaceData);
}
9. Deploy this code as a Library and copy the deployment ID.
Configure a Chat API
1. In the previously created Google Cloud project, go to API and Services → Library Search for Google Chat API → Enable it.
2. Once done, it will take you to the Google Chat API page. Go to the Configuration and add the below values in the field.
|
App Name |
List Chat Spaces |
|
Avatar URL |
https://developers.google.com/chat/images/quickstart-app-avatar.png |
|
Description |
List Chat Spaces |
|
Interactive features |
Enable |
|
Connection settings → Apps Script project → Deployment ID |
Add your Apps Script deployment ID here that you copied in Step 9 of Create a Apps Script file section. |
|
Visibility |
Uncheck the box which says |
|
Logs → Log errors to Logging |
Enable |
Once done, we're all set with the configuration. Now you can simply run the code in the sequence provided below.
- ▶ Run the code on List all the users File - It will list all the users of your domain.
- ▶ Run the code on List all Spaces File - It will fetch the users from the User Sheet and will iterate through each user to list the Spaces that they're part of.
Please authorize your super admin/user admin account when you see the OAuth authorization prompt in Apps Script.
Hope this was helpful. I will continue posting stuff around Apps Script so that you can derive more value for your organization.
If you have any questions, please leave a comment below. Thank you - see you again in the next article!