能否通过Google Script记录Google Drive特定文件的打开者邮箱至表格？

Absolutely, you can build this solution with Google Apps Script! Let’s break down two approaches depending on whether you need to track users with edit access only, or all users (including those with view-only permissions).

Approach 1: Track Users with Edit Access (Simple On-Open Trigger) #

This method works for Google’s native files (Docs, Sheets, Slides, Forms) and uses a built-in onOpen trigger that runs when a user opens the file.

Step 1: Set Up Your Log Spreadsheet #

• Create a new Google Sheet (name it something like "File Access Log").
• Add these headers in the first row: Timestamp, User Email, File Name, File ID.

Step 2: Bind Script to Your Target File #

1. Open the specific Drive file you want to track.
2. Go to Extensions > Apps Script to open the script editor.
3. Replace the default code with this:

function onOpen() {
  // Get the current user's email address
  const userEmail = Session.getActiveUser().getEmail();
  
  // Skip if the user is anonymous (no logged-in Google account)
  if (!userEmail) return;
  
  // Grab details about the file being opened
  const file = DocumentApp.getActiveDocument(); 
  // For Sheets: use SpreadsheetApp.getActiveSpreadsheet()
  // For Slides: use SlidesApp.getActivePresentation()
  
  const fileName = file.getName();
  const fileId = file.getId();
  const timestamp = new Date().toLocaleString();
  
  // Replace with your log sheet's ID (find this in the sheet's URL)
  const logSheetId = "YOUR_LOG_SHEET_ID";
  const logSheet = SpreadsheetApp.openById(logSheetId).getSheetByName("Sheet1");
  
  // Add the access entry to the log sheet
  logSheet.appendRow([timestamp, userEmail, fileName, fileId]);
}

4. Replace YOUR_LOG_SHEET_ID with the ID from your log sheet’s URL (the long string between /d/ and /edit).
5. Save the script (click the floppy disk icon) and name it something like "FileAccessTracker".

Step 3: Authorize the Script #

• Run the onOpen function once manually (click the play button next to the function name).
• Follow the prompts to authorize the script—you’ll need to allow it to access your Drive files and spreadsheets.

Approach 2: Track All Users (Including View-Only Access) #

The onOpen trigger only works for users with edit access (since view-only users can’t run bound scripts). To track everyone, use the Drive Activity API to pull access logs periodically.

Step 1: Enable the Drive Activity API #

1. In the Apps Script editor, click Services > + Add Service.
2. Search for "Drive Activity API", select it, and click Add.

Step 2: Add the Tracking Code #

Replace the script editor code with this:

function logFileAccess() {
  // Replace with your target file's ID and log sheet's ID
  const targetFileId = "YOUR_TARGET_FILE_ID";
  const logSheetId = "YOUR_LOG_SHEET_ID";
  
  const logSheet = SpreadsheetApp.openById(logSheetId).getSheetByName("Sheet1");
  const existingEntries = logSheet.getDataRange().getValues();
  
  // Fetch recent view activity for the file
  const activity = DriveActivity.Activity.query({
    itemName: `items/${targetFileId}`,
    filter: "detail.action_detail_case:VIEW",
    pageSize: 100
  });
  
  // Process each activity entry and avoid duplicates
  activity.activities.forEach(act => {
    const timestamp = new Date(act.timestamp).toLocaleString();
    const userEmail = act.actors[0]?.user?.knownUser?.personName;
    
    if (userEmail) {
      const isDuplicate = existingEntries.some(row => row[0] === timestamp && row[1] === userEmail);
      if (!isDuplicate) {
        logSheet.appendRow([timestamp, userEmail, act.targets[0].title, targetFileId]);
      }
    }
  });
}

// Create a time-based trigger to run the log function automatically
function createTimeTrigger() {
  ScriptApp.newTrigger("logFileAccess")
    .timeBased()
    .everyHours(1) // Adjust frequency as needed (every 1 hour, every day, etc.)
    .create();
}

Step 3: Set Up the Trigger #

• Run the createTimeTrigger function once to set up an automatic schedule (it will pull access logs every hour by default).
• Authorize the script when prompted—this gives it permission to access Drive activity data.

Important Notes #

• Privacy Compliance: Make sure you have a legitimate reason to track user emails, and comply with privacy laws like GDPR or CCPA.
• Anonymous Users: Both methods skip users who open the file without logging into a Google account (since their email can’t be retrieved).
• Duplicate Entries: The second approach includes a check to avoid logging the same access event multiple times.

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