How ChatGPT Helped Me Extract 12 Years of Google User Data

My work often involves processing large amounts of data. Recently, I needed to retrieve information about users of a corporate Google Workspace email system. I had to extract data spanning 12 years to clean up inactive accounts and update their information for future use.

How ChatGPT Helped

I turned to ChatGPT to solve this task. I explained that I needed to extract user data, including email addresses and account creation dates. Instead of searching for solutions manually or relying on outdated tools, I decided to automate the process.

ChatGPT suggested a script that could run in Google Apps Script and work with the Google Directory API. It allowed me to extract 12 years of information down to each individual account.

Project Stages

• Preparing the data: I prepared a Google spreadsheet to store the data. It was a simple table with two columns: users' email addresses and the dates they registered in the system.
• Writing the script: With ChatGPT's help, I wrote a Google Apps Script that connected to the Google Directory API and extracted user data, including email addresses and account creation dates.
• Logging and error handling: The script included logging to track the data extraction process. All errors were recorded, so I could quickly resolve any issues.
• Running the script: Once the script was configured, I ran it. It automatically processed the data and wrote it to the spreadsheet without any further input from me. All 12 years of user data were retrieved and saved in the required format.
• Removing unused accounts: With all the information available, I could start removing inactive accounts. Since Google does not provide a direct way to delete accounts in bulk, I could manually filter the data and delete only the records that were no longer needed.

Conclusion

This experience showed me the value of automation when working with large amounts of data. Instead of relying on standard tools and collecting data manually, I used a script to extract the information quickly and accurately. ChatGPT helped me configure the process efficiently and solve a task that initially seemed quite complex.

JS:

function listUsersWithCreationDate() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Users");
  const props = PropertiesService.getScriptProperties();
  const pageSize = 500;
  let pageToken = props.getProperty('pageToken');
  const isFirstRun = !pageToken;

  if (!sheet) {
    Logger.log('Error: Sheet "Users" not found.');
    throw new Error('Sheet "Users" not found');
  }

  if (isFirstRun) {
    sheet.clear();
    sheet.appendRow(['Email', 'Creation date', 'Status']);
    Logger.log('First run: cleared the sheet and added the header');
  }

  try {
    // Clear an invalid token
    if (pageToken && pageToken.toLowerCase().includes('invalid')) {
      Logger.log('Invalid pageToken removed');
      props.deleteProperty('pageToken');
      pageToken = null;
    }

    const response = AdminDirectory.Users.list({
      customer: 'my_customer',
      maxResults: pageSize,
      orderBy: 'email',
      pageToken: pageToken,
      projection: 'full',
      viewType: 'admin_view', // or 'admin_view' with the required permissions
    });

    const users = response.users;
    Logger.log('Users retrieved: ' + (users ? users.length : 0));

    if (users && users.length > 0) {
      const rows = users.map(user => [
        user.primaryEmail || 'no email',
        user.creationTime || 'no date',
        '✓ added',
      ]);
      sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 3).setValues(rows);
      Logger.log('Rows added to the sheet: ' + rows.length);
    } else {
      sheet.appendRow(['-', '-', 'no users found']);
      Logger.log('No users on this page');
    }

    if (response.nextPageToken) {
      props.setProperty('pageToken', response.nextPageToken);
      Logger.log('Next page saved: ' + response.nextPageToken);
    } else {
      props.deleteProperty('pageToken');
      Logger.log('Loading complete, token removed');
    }
  } catch (e) {
    Logger.log('Error: ' + e.message);
    sheet.appendRow(['-', '-', 'error: ' + e.message]);
    props.deleteProperty('pageToken'); // clear the token as a precaution
    throw e;
  }
}

function resetPageToken() {
  PropertiesService.getScriptProperties().deleteProperty('pageToken');
  Logger.log('Token reset');
}