Google Shared Drive权限提取脚本问题：仅显示单条权限

你的脚本核心问题是遍历共享驱动器权限时，仅将最后一条权限记录添加到输出数组，导致每个驱动器只显示一条权限。

问题根源 #

在job_get_permissions_for_drives函数中，遍历thisDrivePermissions.items时，每次循环都覆盖newRow变量，但未及时将每条权限记录加入rowsToWrite数组，最后仅把最后一次赋值的newRow推入数组，因此只输出一条记录。

修正后的完整脚本 #

var GOOGLE_SHEET_URL = "https://docs.google.com/spreadsheets/";
var GOOGLE_SHEET_RESULTS_TAB_NAME_DRIVES = "Drive";
var GOOGLE_SHEET_RESULTS_TAB_NAME_PERMISSIONS = "Permissions";
const GOOGLEAPI_MAX_RESULTS_PER_PAGE = 5;
const NUMBER_OF_ROWS_TO_LOOKUP_PERMISSIONS_PER_LOOP = 4; 
const USE_DOMAIN_ADMIN_ACCESS = true;
//const EMAIL_RECIPIENT_ADDRESS = 'test@gmail.com';
const EMAIL_SUBJECT_LINE = 'Appscript Google Shared Drive Audit Complete';

/*
* 设置表格表头
*/
function job_set_sheet_headers() {
  
  var drives_sheet = SpreadsheetApp.openByUrl(GOOGLE_SHEET_URL).getSheetByName(GOOGLE_SHEET_RESULTS_TAB_NAME_DRIVES);
  drives_sheet.appendRow(["AUDIT_DATE", "ID", "NAME"]);

  var permissions_sheet = SpreadsheetApp.openByUrl(GOOGLE_SHEET_URL).getSheetByName(GOOGLE_SHEET_RESULTS_TAB_NAME_PERMISSIONS);
  permissions_sheet.appendRow(["AUDIT_DATE", "ID", "NAME", "EMAIL_ADDRESS", "TYPE", "ROLE"]);
  
}

/*
* 获取所有共享驱动器列表并写入表格
*/
function job_get_shared_drives_list() {

  var startTime = new Date().getTime();

  var audit_timestamp = Utilities.formatDate(new Date(), "UTC", "yyyy-MM-dd'T'HH:mm:ss'Z'");

  var newRow = [];
  var rowsToWrite = [];
  
  let sharedDrives = Drive.Drives.list(
        {
          maxResults: GOOGLEAPI_MAX_RESULTS_PER_PAGE,
          useDomainAdminAccess: USE_DOMAIN_ADMIN_ACCESS,
          hidden: false,
        }
      );

  let sharedDrivesItems = sharedDrives.items;
   
  while(sharedDrives.nextPageToken){
    sharedDrives = Drive.Drives.list(
      {
        pageToken:sharedDrives.nextPageToken,
        maxResults: GOOGLEAPI_MAX_RESULTS_PER_PAGE,
        useDomainAdminAccess: USE_DOMAIN_ADMIN_ACCESS,
        hidden: false,
      }
    );

    sharedDrivesItems = sharedDrivesItems.concat(sharedDrives.items)

  }

  sharedDrivesItems.forEach(function(value) {
    var newRow = [audit_timestamp, value.id, value.name];
    rowsToWrite.push(newRow);
  });

  var ss = SpreadsheetApp.openByUrl(GOOGLE_SHEET_URL).getSheetByName(GOOGLE_SHEET_RESULTS_TAB_NAME_DRIVES);
  ss.getRange(ss.getLastRow() + 1, 1, rowsToWrite.length, rowsToWrite[0].length).setValues(rowsToWrite);

  var endTime = new Date().getTime();

  var elapsed = (endTime-startTime)/1000;
  console.log('耗时秒数: ' + elapsed);

}

/*
* 遍历共享驱动器列表并提取每个驱动器的权限
*/
function job_get_permissions_for_drives() {
  
  var startTime = new Date().getTime();
  
  var drives_sheet = SpreadsheetApp.openByUrl(GOOGLE_SHEET_URL).getSheetByName(GOOGLE_SHEET_RESULTS_TAB_NAME_DRIVES);
  var rangeData = drives_sheet.getDataRange();
  var lastColumn = rangeData.getLastColumn();
  var lastRow = rangeData.getLastRow();
  var searchRange = drives_sheet.getRange(2,1, NUMBER_OF_ROWS_TO_LOOKUP_PERMISSIONS_PER_LOOP, lastColumn);
  
  // 获取搜索范围内的数值数组
  var values = searchRange.getValues();
  
  // 遍历数组并处理权限查询
  for ( i = 0; i < NUMBER_OF_ROWS_TO_LOOKUP_PERMISSIONS_PER_LOOP; i++) {

    if(values[0][0] == '') {
      console.log('源行已空，脚本执行完成。');
      //send_email_simple_(EMAIL_RECIPIENT_ADDRESS,EMAIL_SUBJECT_LINE,'Source row empty for Shared Drive Audit process. The script probably complete now.');
      return;
    }
    
    var rowsToWrite = [];
    
    this_audit_date = values[i][0];
    this_drive_id = values[i][1];
    this_drive_name = values[i][2];

    console.log('正在获取驱动器权限: ' + this_drive_name);

    var thisDrivePermissions = Drive.Permissions.list(
      this_drive_id,
      {
        useDomainAdminAccess: USE_DOMAIN_ADMIN_ACCESS,
        supportsAllDrives: true
      } 
    );

    // 遍历每条权限记录
    if(thisDrivePermissions.items.length >= 1) {
        thisDrivePermissions.items.forEach(function(value) {
          var newRow = [this_audit_date, this_drive_id, this_drive_name, value.emailAddress, value.type, value.role];
          rowsToWrite.push(newRow); // 每条权限都添加到输出数组
        });
    } else {
        console.log('该驱动器无权限记录');
        var newRow = [this_audit_date, this_drive_id, this_drive_name, 'ORPHAN', 'ORPHAN', 'ORPHAN'];
        rowsToWrite.push(newRow);
    }
    
    // 将权限记录写入表格
    var ss = SpreadsheetApp.openByUrl(GOOGLE_SHEET_URL).getSheetByName(GOOGLE_SHEET_RESULTS_TAB_NAME_PERMISSIONS);
    ss.getRange(ss.getLastRow() + 1, 1, rowsToWrite.length, rowsToWrite[0].length).setValues(rowsToWrite);

    // 删除已处理的驱动器行，避免重复处理
    var rowToDelete = 2; 
    drives_sheet.deleteRow(rowToDelete);
    
  };

  var endTime = new Date().getTime();

  var elapsed = (endTime-startTime)/1000;
  console.log('耗时秒数: ' + elapsed);
 
};

/*function send_email_simple_(recipient,subject,plain_text_body) {

  MailApp.sendEmail(
    recipient,
    subject,
    plain_text_body
  );

}
*/

关键修改点 #

1. 权限遍历逻辑调整：将rowsToWrite.push(newRow)移到forEach循环内部，确保每条权限记录都被添加到输出数组
2. 变量初始化优化：移除多余的newRow重复声明，确保数组初始化清晰
3. 无权限分支处理：直接将无权限的记录推入数组，保持逻辑一致性

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