使用Apps Script跨Google账号克隆带层级结构的文件夹及文件

需要将另一个Google账号中包含多级子文件夹和文件的文件夹，完整复制到当前活跃账号，保持原有的层级结构。但现有脚本存在以下问题：

• 重复创建文件夹和文件
• 生成以文件夹URL命名的异常新文件夹

操作流程：

1. 获取原文件夹的Folder ID
2. 在表格中列出原文件夹及所有子项
3. 依据列表克隆文件夹和文件
4. 在表格中更新已克隆的文件夹及文件信息

原脚本如下：

function listFilesAndFolders() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet() ;
  var sheet = spreadsheet.getSheetByName("LIST SHEET")
  var sheetSetting = spreadsheet.getSheetByName("SETTING")
  var folderId = sheetSetting.getRange(11,2).getValue();
  var folder = DriveApp.getFolderById(folderId);
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear(); 
  sheet.appendRow(["Name", "Type", "Date Created", "Last Updated", "URL", "Parent Folder"]);
  
  // Call the function to start logging files and folders
  processFolder(folder, sheet, folder.getName());
}

function processFolder(folder, sheet, parentFolderName) {
  var folders = folder.getFolders();
  var files = folder.getFiles();
  
  // Process all sub-folders
  while (folders.hasNext()) {
    var subFolder = folders.next();
    sheet.appendRow([subFolder.getName(), "Folder", subFolder.getDateCreated(), subFolder.getLastUpdated(), subFolder.getUrl(), parentFolderName]);
    processFolder(subFolder, sheet, subFolder.getName()); // Recursion for sub-folders
  }
  
  // Process all files in the folder
  while (files.hasNext()) {
    var file = files.next();
    sheet.appendRow([file.getName(), "File", file.getDateCreated(), file.getLastUpdated(), file.getUrl(), parentFolderName]);
  }

processFileAndFolderList()

}


 function processFileAndFolderList() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet() ;
  var sheet = spreadsheet.getSheetByName("LIST SHEET")
  var data = sheet.getDataRange().getValues();
  var parentFolders = {}; // To track folders that have been created
  
  // Starting from the second line (first line is the header)
  for (var i = 1; i < data.length; i++) {
    var name = data[i][0]; // File or Folder Name
    var type = data[i][1]; // Type (File or Folder)
    var url = data[i][4]; // File URL (for type "File")
    var parentFolderName = data[i][5]; // Parent Folder

    // Step 1: Create a Parent Folder if it doesn't already exist
    if (parentFolderName) {
      if (!parentFolders[parentFolderName]) {
        var parentFolder = getOrCreateFolder(parentFolderName); // Buat folder induk jika belum ada
        parentFolders[parentFolderName] = parentFolder;
      }
    }

    // Step 2: Folder creation process
    if (type === "Folder") {
      var folder = getOrCreateFolder(name, parentFolders[parentFolderName]);
      sheet.getRange(i + 1, 6).setValue(folder.getUrl()); // Update URL di spreadsheet setelah folder dibuat
    }

    // Step 3: File copying process
    if (type === "File") {
      var file = DriveApp.getFileById(extractFileId(url));
      if (parentFolders[parentFolderName]) {
        file.makeCopy(name, parentFolders[parentFolderName]); // Salin file ke folder induk
      }
    }
  }
}


// Function to create a folder or retrieve a folder if it already exists
function getOrCreateFolder(folderName, parentFolder) {
  var folder;
  if (parentFolder) {
    var folders = parentFolder.getFoldersByName(folderName);
    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = parentFolder.createFolder(folderName); // Buat sub-folder di dalam folder induk
    }
  } else {
    var rootFolders = DriveApp.getFoldersByName(folderName);
    if (rootFolders.hasNext()) {
      folder = rootFolders.next();
    } else {
      folder = DriveApp.createFolder(folderName); // Buat folder di root Google Drive
    }
  }
  return folder;
}


// Function to get file ID from URL
function extractFileId(url) {
  var id = url.match(/[-\w]{25,}/);
  return id ? id[0] : null;
}

1. 重复调用克隆函数：processFolder函数在递归处理每个子文件夹后都会调用processFileAndFolderList，导致克隆逻辑被多次执行，重复创建内容。
2. 文件夹追踪逻辑缺陷：parentFolders以文件夹名称为键，若存在同名文件夹会导致错误关联；同时未处理根文件夹的创建与追踪。
3. 表格列错误更新：将新创建的文件夹URL写入了Parent Folder列（第6列），后续读取时会把URL当作文件夹名称，生成异常命名的新文件夹。
4. 根文件夹未纳入克隆：原脚本未处理初始的根文件夹，直接从子项开始克隆，导致层级缺失。

function listFilesAndFolders() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("LIST SHEET");
  var sheetSetting = spreadsheet.getSheetByName("SETTING");
  var folderId = sheetSetting.getRange(11, 2).getValue();
  var sourceRootFolder = DriveApp.getFolderById(folderId);
  
  sheet.clear();
  // 新增"New URL"列存储克隆后的资源地址，保留原"Parent Folder"列存储父文件夹名称
  sheet.appendRow(["Name", "Type", "Date Created", "Last Updated", "Original URL", "Parent Folder", "New URL"]);
  
  // 先处理根文件夹本身
  sheet.appendRow([
    sourceRootFolder.getName(), 
    "Folder", 
    sourceRootFolder.getDateCreated(), 
    sourceRootFolder.getLastUpdated(), 
    sourceRootFolder.getUrl(), 
    "", // 根文件夹无父文件夹
    ""
  ]);
  
  // 递归处理子项
  processFolder(sourceRootFolder, sheet, sourceRootFolder.getName());
  
  // 仅在所有列表完成后调用一次克隆函数
  processFileAndFolderList();
}

function processFolder(folder, sheet, parentFolderName) {
  var folders = folder.getFolders();
  var files = folder.getFiles();
  
  // 处理子文件夹
  while (folders.hasNext()) {
    var subFolder = folders.next();
    sheet.appendRow([
      subFolder.getName(), 
      "Folder", 
      subFolder.getDateCreated(), 
      subFolder.getLastUpdated(), 
      subFolder.getUrl(), 
      parentFolderName, 
      ""
    ]);
    processFolder(subFolder, sheet, subFolder.getName());
  }
  
  // 处理文件
  while (files.hasNext()) {
    var file = files.next();
    sheet.appendRow([
      file.getName(), 
      "File", 
      file.getDateCreated(), 
      file.getLastUpdated(), 
      file.getUrl(), 
      parentFolderName, 
      ""
    ]);
  }
}

function processFileAndFolderList() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("LIST SHEET");
  var data = sheet.getDataRange().getValues();
  // 改用"父文件夹名称->文件夹名称"作为键，避免同名冲突；同时存储文件夹对象
  var folderMap = {};
  
  // 先创建根文件夹
  var rootFolderName = data[1][0];
  var rootFolder = getOrCreateFolder(rootFolderName);
  folderMap[rootFolderName] = rootFolder;
  // 更新根文件夹的New URL
  sheet.getRange(2, 7).setValue(rootFolder.getUrl());
  
  // 从第二行开始处理（第一行是表头，第二行是根文件夹）
  for (var i = 2; i < data.length; i++) {
    var name = data[i][0];
    var type = data[i][1];
    var originalUrl = data[i][4];
    var parentFolderName = data[i][5];
    
    // 确保父文件夹已存在于映射中
    if (!folderMap[parentFolderName]) {
      continue; // 若父文件夹未创建，跳过当前项（理论上不会出现，因为按层级顺序处理）
    }
    
    if (type === "Folder") {
      var folder = getOrCreateFolder(name, folderMap[parentFolderName]);
      // 用"父文件夹名称->当前文件夹名称"作为唯一键
      folderMap[`${parentFolderName}->${name}`] = folder;
      // 更新New URL列
      sheet.getRange(i + 1, 7).setValue(folder.getUrl());
    }
    
    if (type === "File") {
      var fileId = extractFileId(originalUrl);
      if (!fileId) continue;
      
      var sourceFile = DriveApp.getFileById(fileId);
      var copiedFile = sourceFile.makeCopy(name, folderMap[parentFolderName]);
      // 更新文件的New URL
      sheet.getRange(i + 1, 7).setValue(copiedFile.getUrl());
    }
  }
}

function getOrCreateFolder(folderName, parentFolder) {
  var folder;
  if (parentFolder) {
    var existingFolders = parentFolder.getFoldersByName(folderName);
    if (existingFolders.hasNext()) {
      folder = existingFolders.next();
    } else {
      folder = parentFolder.createFolder(folderName);
    }
  } else {
    var existingRootFolders = DriveApp.getFoldersByName(folderName);
    if (existingRootFolders.hasNext()) {
      folder = existingRootFolders.next();
    } else {
      folder = DriveApp.createFolder(folderName);
    }
  }
  return folder;
}

function extractFileId(url) {
  var idMatch = url.match(/[-\w]{25,}/);
  return idMatch ? idMatch[0] : null;
}

1. 调整克隆函数调用时机：将processFileAndFolderList移至listFilesAndFolders末尾，仅在所有文件和文件夹列表生成完成后调用一次，避免重复执行。
2. 优化文件夹追踪机制：
   • 新增folderMap，使用父文件夹名称->当前文件夹名称作为唯一键，解决同名文件夹的冲突问题
   • 优先创建根文件夹并加入映射，确保后续子项能正确关联父文件夹
3. 修正表格列结构：
   • 新增New URL列存储克隆后的资源地址，原Parent Folder列仅保留父文件夹名称，避免URL被当作文件夹名称创建异常文件夹
   • 根文件夹的父文件夹字段设为空，明确层级关系
4. 完善根文件夹处理：在列表中先添加根文件夹条目，确保克隆时完整复制整个层级结构
5. 增加异常判断：对文件ID提取失败、父文件夹不存在的情况添加跳过逻辑，提升脚本稳定性

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