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

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](https://developers.google.com/admin-sdk/directory/v1/guides/manage-users#json-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](https://docs.google.com/spreadsheets/d/1ESQ6NvkmUkoYX5rayvfWnvhMxdZjWW3f_IyAyjq11Cw/copy))

```

// @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](https://developers.google.com/workspace/explore) lets you see just how much is possible.