I have a Folder which has several Sub Folders and Files in another Google Accounts. I want to duplicate the folder to active account while maintaining the folder - subfolder and file arrangement.
Unfortunately, with the script that I created, there is still a repetitive process in creating a new folder and the files in it, and there is another new folder with a name using the URL of the folder created.
The process I did:
Here is the script that I use:
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;
}
I have seen quite a few lapses with your existing code, and I intend to correct them for that. I crafted a code which you can try as I believe that this is more reliable and efficient in terms of copying the data while maintaining your folder hierarchy. Instead of relying on the data of your spreadsheet, which may cause trouble when you have a lot of data to copy as it involves multiple iteration of reading the data, and whenever that happens you constantly call the API which possibly be the reason why you may hit the limit of API calls, and more importantly can be the reason of slow copying process.
This code also provides the same output as what your current code does, but it now based the copying of data and folder structure directly in the drive. To make this work, run the main() function.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName("LIST SHEET")
const sheetSetting = spreadsheet.getSheetByName("SETTING")
const folderId = sheetSetting.getRange(11, 2).getValue();
const folder = DriveApp.getFolderById(folderId);
function main(){
listFolderContents();
createRootFolder();
}
function listFolderContents() {
sheet.clear();
sheet.appendRow(["Name", "Type", "Date Created", "Last Updated", "URL", "Parent Folder"]);
processFolder(folder, sheet, folder.getName());
}
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 createRootFolder() {
var parentFolder = DriveApp.createFolder(folder.getName());
copyProcess(folder, parentFolder);
}
function copyProcess(folder, parentFolder) {
var folders = folder.getFolders();
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
file.makeCopy(file.getName(), parentFolder);
}
while (folders.hasNext()) {
var subFolder = folders.next();
var newSubFolder = parentFolder.createFolder(subFolder.getName());
var data = sheet.getDataRange().getValues();
for (var i = 0; i < data.length; i++) {
if (data[i][0] === subFolder.getName()) {
sheet.getRange(i + 1, 6).setValue(newSubFolder.getUrl());
break;
}
}
copyProcess(subFolder, newSubFolder);
}
}
How the process works: List all the folder and files of the source drive Copy the data directly from one drive to another Updates the URL of the newly copied folders of the destination
Note: I have tried my best to split each function to each part of your project to make sure that it is readable and going to be easily modified into the project, due to limited information about what is important for you and what is just created just to get to the expected goal.