Google Apps Script : Populate Sheet with Gmail User listing

0
votes

I am trying to create a Google Apps Script that will compile a listing of all Gmail Users in our domain and populate the info in a Google Sheet.

Some of the User info that I want to retrieve is as follows:

  • Full name
  • Email address
  • Organizational Unit
  • Department
  • Last login time
  • Suspended
  • 2-Step verification status

Does anyone have a GAS that does something similar or one that could help me get started?

Thank you!

Amit Desai.

2
google-apps-scriptgoogle-admin-sdk

2 Answers

0
votes

Here is a script that does almost all you need. It includes a couple of helper functions to get a better layout & readability.

Note : AdminDirectory is an advanced Google service that you have to activate (script editor>ressources>Advanced Google Services + Admin SDK in console as explained)

Code :

function listAllUsers() {
  var ss = SpreadsheetApp.getActive();
  var pageToken, page, count=0;
  var listArray = [];
  listArray.push(['num','first name','last name','email','alias []','creation date','is connected','last connection','ID'])
  do {
    page = AdminDirectory.Users.list({
      domain: 'insas.be',
      orderBy: 'givenName',
      maxResults: 100,
      pageToken: pageToken
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {
        var user = users[i];
        count++;
        listArray.push([count, user.name.givenName, user.name.familyName, user.primaryEmail,user.aliases!=null?user.aliases:'',new Date(user.creationTime),user.agreedToTerms==true?"oui":"non",new Date(user.lastLoginTime),user.id,]);
      }
    } else {
      Logger.log('No users found.');
    }
    pageToken = page.nextPageToken;
  } while (pageToken);
    try{
      var outputSheet = ss.getSheetByName('allMembers');
      outputSheet.getDataRange();
      }catch(err){
        var outputSheet = ss.insertSheet('allMembers',2);
      }
  outputSheet.getDataRange().clear();
  outputSheet.getRange(1,1,listArray.length, listArray[0].length).setValues(listArray);
  outputSheet.getRange(1,6,outputSheet.getLastRow(),4).setHorizontalAlignment("center");
  outputSheet.getRange(1,1,outputSheet.getLastRow(),1).setHorizontalAlignment("center");
  var width = [40,150,180,250,250,95,60,125,160];
  formatSheet(outputSheet,width);
}

function formatSheet(outputSheet,width){
  SpreadsheetApp.flush();
  if(outputSheet.getMaxRows() > outputSheet.getLastRow()){
    outputSheet.deleteRows(outputSheet.getLastRow()+1, outputSheet.getMaxRows()-outputSheet.getLastRow());
  };
  SpreadsheetApp.flush();
  if(outputSheet.getMaxColumns() > outputSheet.getLastColumn()){
    outputSheet.deleteColumns(outputSheet.getLastColumn()+1, outputSheet.getMaxColumns()-outputSheet.getLastColumn());
  };
  outputSheet.getRange(1,1,1,outputSheet.getLastColumn()).setBorder(true,true,true,true,true,true).setBackground("#CCA").setFontWeight("bold").setHorizontalAlignment("center");
  outputSheet.setFrozenRows(1);
  SpreadsheetApp.flush();
  for(var w=0;w < width.length;w++){
    outputSheet.setColumnWidth(w+1,width[w]);
  }
  shader(outputSheet);
}

function shader(sh) {
  sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).setBackground(null)
  var L=sh.getLastRow();
  var C = sh.getLastColumn();
  for(var n=3;n<L;n=n+2){
    sh.getRange(n,1,1,C).setBackground("#DDD");
  }
}
0
votes

You need to replace Domain with customer

function writeToSpreadsheet(){
var values = [],
users = [],
userListQuery = {},
nextPageToken = '',
listObject = {

  customer : 'customer ID' //you need to replace this with your customer ID
  maxResults: 500,        
},
i = 0,
activeSpreadsheet;
do {
if (nextPageToken && nextPageToken !== '') {
listObject.pageToken = nextPageToken;
}  

userListQuery = AdminDirectory.Users.list(listObject);

// if there are more users than fit in the query a nextPageToken is returned
 nextPageToken = userListQuery.nextPageToken;

// Add the query results to the users array
 users = users.concat(userListQuery.users);

} while (nextPageToken);

 for (i = 0; i < users.length; i += 1) {
 values.push([users[i].name.fullName, users[i].primaryEmail]);   
 }

 SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(2, 1,      values.length, values[0].length).setValues(values);
  }