By @mhawksey

Google Apps Script Patterns: Keeping a gam generated users report up-to-date with Google Apps Script

Martin Hawksey

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:

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.

Exit mobile version