Auto Generate Google Users List

This script will auto-generate a list of all your Google Workspace users, regardless of their domain (if you have multiple domains). The script will be run as scheduled, so your list will be updated regularly. This way, you don’t need to always go to the Google Workspace dashboard to check the users.

Step 1: Create Google Sheet and get the id. We will need the id later.

Step 2: Go to Google Apps Script https://script.google.com

Step 3: Create a new project. Let’s name it “List All Users”

Step 4: Copy and paste this script.

Replace <Google Sheet ID> with the ID you copied earlier at Step 1.

Replace <Sheet Name> with the sheet name where you want to use for this script. This will be useful if you have multiple sheets in a worksheet.

function listAllUserRR() {
 

  var sh = '<Google Sheet ID>'; //This line will specify which Spreadsheet to Open//
  var sheet = SpreadsheetApp.openById(sh);
  var sheet1 = sheet.getSheetByName('<Sheet Name>');
  
  var sheet1range = sheet1.getRange("A:F")
  sheet1range.clear()
 
  var data = [];// array to store values
  data.push(['Email' ,'Firstname', 'Lastname',  'Suspended', 'LastLoginTime','Creation']);// store headers
  var pageToken, page;
  do {
    page = AdminDirectory.Users.list({
    
      customer: 'my_customer',
     
      pageToken: pageToken
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {
        
        var user = users[i];
        
        
        data.push([user.primaryEmail, user.name.givenName, user.name.familyName, user.suspended, user.lastLoginTime , user.creationTime  ]);//store in an array of arrays (one for each row)
      }
    } else {
    Logger.log('No users found.');
       }
    pageToken = page.nextPageToken;
  } while (pageToken);
  
  sheet1.getRange(1,1,data.length,data[0].length).setValues(data);

//Search and replace 
var to_replace = ".000Z";
var replace_with = ""; //Leave blank to delete Text, or enter text in quotes to add string.

//var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var lastRow = sheet1.getLastRow();
var ranges = ['E2:F' + lastRow];
sheet1.getRangeList(ranges).getRanges().forEach(r => 
    r.createTextFinder(to_replace).matchEntireCell(false).replaceAllWith(replace_with)
);

}

Step 5: Add the “Admin SDK API” Service

Step 6: Save and Run the script. On initial run it will prompt authentication to allow App Script to access the Google Workspace list of users.

Once successful it will return an “Execution Completed” log and it will list all the users in the Google Sheet.

Step 7: Let create a schedule so the script will run everyday between 1AM to 2AM. Go to Trigger > Add Trigger

Make sure the right function is selected, which is the name of your script. Select the type of time-based trigger as a Day timer, and Select the time of day as 1 AM to 2 AM. You can select or setup the schedule that fits your requirements.

Please note that every time the script runs, it will overwrite the existing data on the Google Sheet.