Listing Domain User Names in a Spreadsheet

Post date: Nov 30, 2014 11:42:11 PM

I saw a question on G+ today:

"Interesting question today asking if it's possible to feed the users from the OU's in the Admin control panel or to feed the users from the Google Groups for Business groups into a spreadsheet to use with formRanger? " ~ Chris Webb

While the Apps Script for this is only 10 lines long there are other steps that can make this a bit tricky for a first timer.

1. First thing you want to do is to ensure your domain has API access enabled.

The instructions from Google:

To enable the API, log in to your admin account and select Security. If you do not see Security listed, select More controls and then Security from the options shown in the gray box. Select API reference, and then select the checkbox to Enable API access. Save your changes.

This link should get you there: https://admin.google.com/AdminHome?pli=1&fral=1#SecuritySettings:

2. Open up a Google Apps Script from Drive or from the Tools menu in your spreadsheet.

3. Turn on the Admin Directory API

Click on Resources and select Advanced Google Services

Click the Switch to turn on the service

4. Click the link at the bottom to open the Google Developers Console.

Click the switch to turn on the Admin SDK

5. Add some code to your script.

We are using the Admin SDK and here is a link to the docs

There are many things we can get from a users item, this is the list

CODE

function writeToSpreadsheet(){

var values = [];

var users = AdminDirectory.Users.list({domain:'REPLACE WITH YOUR DOMAIN'}).users; //example: ignitesynergy.com

for (var i=0; i<users.length; i++){

values.push([users[i].name.fullName, users[i].primaryEmail]); //Look in the docs or use auto complete to see what you can access

}

var spreadsheetUrl = 'https://docs.google.com/a/YOUR_DOMAIN.com/spreadsheets/d/YOUR_ID';

SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0].getRange(1, 1, values.length, values[0].length).setValues(values);

}

//Red indicates the places you need to use your info

The quick run down:

We use an array (values) that will store each row of a spreadsheet.

AdminDirectory.Users.list({domain:'<REPLACE WITH YOUR DOMAIN>'}).users; gets all the user information as an array

Iterate through this array and create a row with the info you want. In this case we push the array [fullName, email] into the values array.

Now that the values are in a format the spreadsheet can accept, call up your sheet and set the values.

That's it, run the script, grant some permissions and your users will show up in the spreadsheet.

To make this run on a trigger click Resources and select Current project's triggers and set a time based trigger to run the writeToSpreadsheet function.