Export Google Workspace Users to Sheets with Apps Script

Introduction
Managing a Google Workspace domain efficiently often starts with knowing exactly who has access to its resources. While the Admin console provides a visual list of users, many administrators need that data in a manipulable format for reporting, audits, or bulk operations. Exporting every user into a Google Sheet offers real‑time visibility, easy sharing, and the power of spreadsheet functions for analysis. In this article we will explore the step‑by‑step process to list all users of a Google Workspace domain directly in Google Sheets, from enabling the necessary API permissions to writing a compact Apps Script that pulls the directory data. By the end, you’ll have a reliable, automated solution that keeps your user inventory up‑to‑date with just a click.

Why Exporting Users to Sheets Matters
Having a live spreadsheet of domain users brings several strategic advantages:

• Instant reporting: Combine user data with usage metrics, licensing costs, or department tags without leaving the sheet.
• Collaboration: Share the list with managers, auditors, or HR while preserving read‑only or edit permissions.
• Automation: Use Google Sheets formulas or add‑ons to trigger alerts when a new user is added or a license expires.
• Backup: Create periodic snapshots for compliance or disaster‑recovery purposes.

Setting Up Google Workspace API Access
Before any script can query the directory, you must grant it the proper scopes:

• Sign in to the Google Cloud Console and create a new project (or reuse an existing one).
• Navigate to APIs & Services → Library and enable the Admin SDK API.
• Under APIs & Services → OAuth consent screen, configure a internal consent screen and add the scopes https://www.googleapis.com/auth/admin.directory.user.readonly and https://www.googleapis.com/auth/spreadsheets.
• Create credentials of type OAuth client ID (Web application) and note the client ID and secret – they will be used automatically when the Apps Script runs for the first time.
• In the Admin console, assign the service account (or the user running the script) the Directory Reader role.

Creating the Apps Script to Pull User Data
With the API ready, open a new Google Sheet and launch Extensions → Apps Script. Paste the following concise script, which queries the Admin SDK and writes results into the active sheet:

• function listAllUsers() {
•   var pageToken, users = [];
•   do {
•     var response = AdminDirectory.Users.list({
•       domain: 'yourdomain.com',
•       maxResults: 500,
•       pageToken: pageToken
•     });
•     users = users.concat(response.users || []);
•     pageToken = response.nextPageToken;
•   } while (pageToken);
•   var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
•   sheet.clear();
•   sheet.appendRow(['Full Name','Email','Org Unit','Status']);
•   users.forEach(function(u){
•     sheet.appendRow([u.name.fullName, u.primaryEmail, u.orgUnitPath, u.suspended ? 'Suspended' : 'Active']);
•   });
• }

Run listAllUsers once, authorize the requested scopes, and the sheet will populate with every account in the domain, including name, email, organizational unit, and status.

Automating Updates and Managing Permissions
To keep the list current without manual intervention, set a time‑driven trigger:

• In the Apps Script editor, go to Triggers → Add Trigger.
• Select listAllUsers, choose “Time‑driven”, and pick a frequency (daily, hourly, etc.) that matches your reporting cadence.
• Ensure the spreadsheet’s sharing settings reflect the intended audience – grant Viewer rights to stakeholders who only need to read the data, and Editor rights to admins who may run the script manually.

This automation guarantees that any newly created accounts, deletions, or status changes appear in the sheet shortly after they happen, supporting compliance audits and capacity planning.

Best Practices and Common Pitfalls
While the solution is straightforward, observing a few best practices will save time and avoid errors:

• Limit columns to only what you need. Adding unnecessary fields can increase script execution time and may hit the 6‑minute Apps Script limit for large domains.
• Handle pagination carefully. The script above uses a do…while loop; forgetting the nextPageToken check will result in incomplete data.
• Monitor quota usage. The Admin SDK enforces a daily request quota; batching requests (maxResults = 500) reduces the total count.
• Secure credentials. Do not embed client secrets in the script; rely on the built‑in OAuth flow provided by Apps Script.
• Test with a small OU first. Running the script on a single organizational unit helps verify column mapping before scaling to the entire domain.

Conclusion
Exporting every Google Workspace user into a Google Sheet transforms a static directory into a dynamic, shareable resource that powers reporting, auditing, and automation across your organization. By first enabling the Admin SDK, then crafting a concise Apps Script, and finally scheduling regular updates, you create a self‑maintaining inventory that reflects real‑time changes. Applying the outlined best practices—such as pagination handling, quota awareness, and proper permission management—ensures the solution remains reliable and secure. Armed with this approach, administrators can focus on strategic tasks rather than manual data extraction, delivering clearer insight and stronger governance for their Google Workspace domain.