1

I have a script that is able to populate a sheet with the info required but I am unsure how to retrieve the data from the Employee Information section in the user profile. I would like the below 4 items, what am I missing?? I have also taken a look at the Admin SDK Directory API but have had no luck in adding externalIds[] please help point in the right direction if you can

  1. Employee ID
  2. Job Title
  3. Type of Employee
  4. Department

function listAllUsers() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Users');
  sheet.getRange(1, 8).setValue(Utilities.formatDate(new Date(), "GMT", "HH:mm dd/MM/yyyy"));
 
  var pageToken;
  var page;
  n = 0;
  do {
    page = AdminDirectory.Users.list({
      domain: 'domain.co.uk',
      orderBy: 'givenName',
      maxResults: 100,
      pageToken: pageToken
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {
        var user = users[i];
        sheet.getRange(n + i + 2, 1).setValue(user.name.fullName);      
        sheet.getRange(n + i + 2, 2).setValue(user.name.givenName); 
        sheet.getRange(n + i + 2, 3).setValue(user.name.familyName); 
        sheet.getRange(n + i + 2, 4).setValue(user.primaryEmail);  
        sheet.getRange(n + i + 2, 5).setValue(user.orgUnitPath);
        SpreadsheetApp.flush();
      }
    } else {
      Logger.log('No users found.');
    }
    n+=100;
    pageToken = page.nextPageToken;
  } while (pageToken);
   
}
N8B123
  • 13
  • 2

1 Answers1

1

I am assuming you want the following data:

enter image description here enter image description here

Both externalIds and organizations are arrays. They allow for multiple sets of data for the same person.

Assuming your database has just one set for each person, you could try:

user.externalIds[0].value

user.organizations[0].title

user.organizations[0].type

user.organizations[0].department

PS: I have not used this API, and these suggestions are based on my reading of the docs. Let me know if it works. Best of luck.

PPS: I would also suggest sending all the data to the sheet in one go to minimize calls to the sheet

sheet.getRange(n + i + 2, 1, 1, 5).setValues([user.name.fullName, user.name.givenName, user.name.familyName, user.primaryEmail, user.orgUnitPath]);

It may be even more efficient to put all user data in an array and post to the sheet in one go.

And you could also avoid calling SpreadsheetApp.flush() for each user. Best to do it once at the end of posting data.

Edit

To avoid fails from missing externalId, you could try:

  if (user.externalIds) {
    var extID = user.externalIds[0].value;
  } else {
    extID = null;
  }

and then post the extId to the sheet.

ADW
  • 4,177
  • 1
  • 14
  • 22
  • Thanks for the info. However, I receive the error below when trying user.externalIds[0].value TypeError: Cannot read property '0' of undefined I have not looked at optimizing the script yet with your other suggestions – N8B123 Mar 13 '20 at 11:38
  • The error suggests that `externalIds` does not exist. This may be an issue for a specific user or for all users. Try looking at the data by logging it: `Logger.log([i, user.externalIds])`. – ADW Mar 13 '20 at 13:09
  • The field won't exist unless the user has it. You can check at this [post](https://stackoverflow.com/questions/27860653/how-to-update-a-users-externalid-using-java) to add it. – Jescanellas Mar 13 '20 at 13:11
  • I've edited my answer to suggest a way to avoid failure from the occasional missing `exertnalIds`. You could use the same idea if the company details may be missing for some users. – ADW Mar 13 '20 at 13:21