如何通过AppScript从Admin SDK>>Reports API将已删除、已归档、已停用用户数据导入Google表格

Hey there! Let's get you set up with pulling user status change data (deleted, archived, suspended) from the Admin SDK Reports API into Google Sheets using Apps Script. I'll walk you through every step, including the code and key considerations.

Step 1: Enable Required APIs & Services #

First, you need to make sure the necessary APIs are enabled and accessible in your Apps Script project:

• Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
• Click Resources > Advanced Google Services, then find and enable the Admin Reports API.
• Next, click the link to your Google Cloud Platform (GCP) project at the top of that window. In the GCP console, search for "Admin SDK" and enable it (this is required for the Reports API to work properly).
• Make sure you're signed in as a Google Workspace administrator—you won't have permissions to access this data otherwise.

Step 2: The Apps Script Code #

Here's a complete script that fetches the required data, processes it, and writes it to your sheet. I've added comments to explain each part:

function fetchUserStatusChanges() {
  // Configure your target sheet
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = spreadsheet.getSheetByName("UserStatusLogs"); // Update this to your sheet name
  
  // Set up header row
  const headers = ["用户邮箱", "删除日期", "组织单元路径", "归档日期", "停用日期"];
  targetSheet.clearContents();
  targetSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  
  // Define API parameters
  const appName = "admin";
  const targetEvents = ["delete_user", "archive_user", "suspend_user"];
  // Adjust startTime to your desired date range (example: last 12 months)
  const startTime = new Date(Date.now() - 365 * 24 * 60 * 60 * 1000).toISOString();
  
  try {
    let pageToken;
    let processedData = [];
    
    // Paginate through API results (handles large datasets)
    do {
      const apiResponse = AdminReports.Activities.list(appName, "all", {
        eventName: targetEvents.join(","),
        startTime: startTime,
        pageToken: pageToken
      });
      
      if (apiResponse.items && apiResponse.items.length > 0) {
        apiResponse.items.forEach(event => {
          const userEmail = event.actor.email;
          const eventTimestamp = new Date(event.id.time).toLocaleString(); // Format date for readability
          const orgUnit = event.parameters?.find(param => param.name === "org_unit_path")?.value || "";
          
          // Initialize row with empty values
          let userRow = ["", "", "", "", ""];
          userRow[0] = userEmail;
          userRow[2] = orgUnit;
          
          // Populate the correct date field based on event type
          switch (event.eventName) {
            case "delete_user":
              userRow[1] = eventTimestamp;
              break;
            case "archive_user":
              userRow[3] = eventTimestamp;
              break;
            case "suspend_user":
              userRow[4] = eventTimestamp;
              break;
          }
          
          // Merge data for the same user (if multiple events exist)
          const existingUserIndex = processedData.findIndex(row => row[0] === userEmail);
          if (existingUserIndex !== -1) {
            // Only update empty fields to preserve existing data
            for (let i = 1; i < userRow.length; i++) {
              if (userRow[i] && !processedData[existingUserIndex][i]) {
                processedData[existingUserIndex][i] = userRow[i];
              }
            }
          } else {
            processedData.push(userRow);
          }
        });
      }
      
      pageToken = apiResponse.nextPageToken;
    } while (pageToken);
    
    // Write processed data to the sheet
    if (processedData.length > 0) {
      targetSheet.getRange(2, 1, processedData.length, processedData[0].length).setValues(processedData);
      SpreadsheetApp.getUi().alert(`Success! Imported ${processedData.length} user status records.`);
    } else {
      SpreadsheetApp.getUi().alert("No user status change records found in the specified date range.");
    }
    
  } catch (error) {
    SpreadsheetApp.getUi().alert(`Error fetching data: ${error.message}`);
    console.error("API Error:", error);
  }
}

Step 3: Run & Authorize the Script #

• Save the script with a name (like UserStatusReporter).
• Click the run button ▶️ next to the function name fetchUserStatusChanges.
• You'll be prompted to authorize the script. Follow the prompts:
  1. Click "Review Permissions".
  2. Select your administrator account.
  3. When you see the "Google hasn’t verified this app" warning, click Advanced > Go to [Your Script Name] (this is safe since you wrote the script).
  4. Grant the required permissions.

Key Notes & Customizations #

• Date Range: Modify the startTime variable to adjust how far back you want to pull data. For example, use new Date("2023-01-01").toISOString() to get records from January 1, 2023 onwards.
• Sheet Name: Update targetSheet to match the name of your sheet in the Google Sheet.
• Data Merging: The script automatically merges multiple events for the same user (e.g., if a user was suspended then deleted, both dates will appear in the same row).
• Error Handling: The script includes basic error handling and alerts to let you know if something goes wrong.

内容的提问来源于stack exchange，提问作者Mohammed Zaid