In Google Workspace gam is probably the ‘go to’ tool command line tool which allows administrators to easily manage domain and user settings.
With great power, comes great responsibility
GAM is very powerful and you can do extreme damage to your Google Workspace domain.
Recently I was asked about how you can keep gam generated reports up-to-date using Google Apps Script. It’s worth remember that gam uses public Google Workspace APIs when it performs actions and reports. One approach to this problem is to think like gam. More often Google’s APIs will return a responses object. These objects will vary in structure. For example, below is an excerpt from the Google Admin SDK for a user response:
{
"kind": "directory#user",
"id": "the unique user id",
"primaryEmail": "liz@example.com",
"name": {
"givenName": "Liz",
"familyName": "Smith",
"fullName": "Liz Smith"
},
"organizations": [
{
"name": "Google Inc.",
"title": "SWE",
"primary": true,
"customType": "",
"description": "Software engineer"
}
],
...
}
As gam can output as .csv and directly to Google Sheets this structured data is flattened is a ‘.’ path notation making it easy to transpose into column headings and rows:
{
"kind": "directory#user",
"id": "the unique user id",
"primaryEmail": "liz@example.com",
"name.givenName": "Liz",
"name.familyName": "Smith",
"name.fullName": "Liz Smith",
"organizations.0.name": "Google Inc.",
"organizations.0.title": "SWE",
"organizations.0.primary": true,
"organizations.0.customType": "",
"organizations.0.description": "Software engineer",
...
}
Using a similar approach in Google Apps Script means with minimum effort and code we can take an existing gam export and refresh the report contents. Take, for example, the following gam command:
gam print users name ou organizations relations todrive
Using the following Apps Script code we can replicate the gam command and keep a Google Workspace account list up-to-date (also available if you make a copy of this Google Sheet)
// @OnlyCurrentDoc
/**
* This function is designed to read a heading row and update the data from the Admin SDK
*/
function updateSheet(){
const doc = SpreadsheetApp.getActiveSpreadsheet();
const sheet = doc.getSheetByName('Directory');
// this will return two arrays one with the column headings, the other with all the other rows
const [heads, ...data] = sheet.getDataRange().getValues();
// get a flattened list of accounts
const accounts = listAllUsers_();
// if we just want to update the entire sheet with the new data we can create a 2d array
const tr = accounts.map(row => heads.map(key => row[String(key)] || ''));
// clear any existing rows (except the header)
if (sheet.getLastRow() > 1) sheet.getRange(2,1, sheet.getLastRow()-1, tr[0].length).clearContent();
// write the data we got back from the Admin SDK
sheet.getRange(2, 1, tr.length, tr[0].length).setValues(tr);
}
/**
* Lists all the users in a domain sorted by first name.
* @see https://developers.google.com/admin-sdk/directory/reference/rest/v1/users/list
* Based on https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/adminSDK.gs
*/
function listAllUsers_() {
let pageToken = null;
let users = [];
do {
let page = AdminDirectory.Users.list({
customer: 'my_customer',
orderBy: 'givenName',
maxResults: 500,
pageToken: pageToken,
fields: 'users(primaryEmail,name,orgUnitPath,relations,organizations),nextPageToken'
});
// here we flatten the user response
users = users.concat(page.users.map(d => flatten_(d)));
pageToken = page.nextPageToken;
} while (pageToken);
return users
}
/**
* This function will take take a tree object and flatten it into so it's only 1 deep
* @see https://hawksey.info/blog/2020/04/google-apps-script-patterns-writing-rows-of-data-to-google-sheets-the-v8-way/#V8_Version
*
*/
const flatten_ = (obj, prefix = '', res = {}) =>
Object.entries(obj).reduce((r, [key, val]) => {
const k = `${prefix}${key}`;
if(typeof val === 'object' && val !== null){
flatten_(val, `${k}.`, r);
} else {
res[k] = val;
}
return r;
}, res);
gam has more super powers and depending on the public APIs you need may require a little more work to get up and running. The key aspects of this design pattern are:
- Using column headings that match the flattened response object means the column order isn’t important (changing the column heading text will break the script); and
- the function
listAllUsers_()is a common pattern for getting paged responses from Google APIs
Finally, if you’d like a little more inspiration with what is possible the new Google Workspace API Explorer lets you see just how much is possible.
Henry Blanchard
Thanks for creating this example. This is exactly what I was looking for and has got me about 90% of the way of displaying all the fields I need in my Google Sheets Directory. In your provided script I’ve updated the fields as fields: ‘users(primaryEmail,name,orgUnitPath,relations,organizations, addresses, phones),nextPageToken’ . As you can see I’ve added addresses, phones and also added these headers to the spreadsheet: addresses.0.formatted & phones.0.value. My issue is that each user has multiple values for both of these properties. The spreadsheet is displaying the first property but I can’t figure out how to get the second one. For example, let’s say one user has the following:
“addresses”: [
{
“type”: “work”,
“formatted”: “123 Main St”
},
{
“type”: “work”,
“formatted”: “Almer, GA 40947”
}
“phones”: [
{
“value”: “:123-456-7890”,
“type”: “work”
},
{
“value”: “101-123-1314”,
“type”: “mobile”
}
My spreadsheet will show “123 Main St” for the address & “123-456-7890” for the phone number. How do I get the other 2 values? I tried duplicating the header column names but that just gives me duplicate results in the spreadsheet.
Martin Hawksey
Hi Henry – glad you found this example useful. To get the other values you should be able to add additional column names incrementing the number in the middle e.g. phones.1.value, phones.2.value and so on 🙂
Henry Blanchard
Yes, that did the trick. Exactly what I needed. Thanks for your help. This script is going to save me a lot of time. 🙂