I want to copy folders into a Master Folder within Google Drive. My main issue is creating a function when one column is not blank and another column is blank. Once, a new folder is created it has to paste in the folder URL in one of the columns.

This is what I have so far:

function addData(){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var lr = activeSheet.getLastRow();
  var lc = activeSheet.getLastColumn();
  var sourceData = sourceSheet.getRange(1, 1, lr, lc).getValues();
  for (row in sourceData) {
    if (sourceData[row][0] !== "" && sourceData[row][19] !== "") {
      var rowNum = parseInt(row);

     //code will go here for Folder copying and renaming and getting the new folder url

     activeSheet.getRange(rowNum+1,lc).setValue("Completed"); //we will eventually change Completed with the new Folder URL
  }
 }
}

I want to copy folders into a Master Folder within Google Drive. My main issue is creating a function when one column is not blank and another column is blank. Once, a new folder is created it has to paste in the folder URL in one of the columns.

This is what I have so far:

function addData(){
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var lr = activeSheet.getLastRow();
  var lc = activeSheet.getLastColumn();
  var sourceData = sourceSheet.getRange(1, 1, lr, lc).getValues();
  for (row in sourceData) {
    if (sourceData[row][0] !== "" && sourceData[row][19] !== "") {
      var rowNum = parseInt(row);

     //code will go here for Folder copying and renaming and getting the new folder url

     activeSheet.getRange(rowNum+1,lc).setValue("Completed"); //we will eventually change Completed with the new Folder URL
  }
 }
}

• javascript
• google-apps-script
• directory-structure
• google-drive-shared-drive

• Unfortunately, I cannot understand about I'm stuck with my coding on automating folder creation in Google Drive. I want to copy one folder to the next all within Google Drive. My main issue is creating a function when column A is not blank and column S is blank. Once, the new folder is created it pastes in the folder URL in column S.. I apologize for this. Can I ask you about the detail of your current issue and your goal? – Tanaike Commented Mar 4, 2021 at 0:25
• @Tanaike, my current issue is copying folders in Google Drive and adding them to a master folder that I've set up. – Coach212 Commented Mar 4, 2021 at 0:35
• Thank you for replying. I would like to confirm my understading of your actual question. In your current issue, you want to copy the specific folder which has subfolders and files to the other specific folder using Google Apps Script. In this case, you don't want to move them. You want to copy them. And, you have the permissions for retrieving the source folder. Is my understanding correct? – Tanaike Commented Mar 4, 2021 at 0:38
• @Tanaike, yes that is correct. – Coach212 Commented Mar 4, 2021 at 1:04
• Thank you for replying. I could understand that my understanding of I want to copy folders into a Master Folder within Google Drive. is correct. I have one more question. I cannot understand the relationship between I want to copy folders into a Master Folder within Google Drive. and My main issue is creating a function when one column is not blank and another column is blank. Once, a new folder is created it has to paste in the folder URL in one of the columns.. I apologize for this. Can I ask you about the detail of your goal? – Tanaike Commented Mar 4, 2021 at 1:07

2 Answers 2

The main idea would be to iterate files using Folder.getFiles() and folders with Folder.getFolders(). Make new folders with Folder.createFolder(name) and copy files using File.makeCopy(name, destination):

/**
 * Copies all the files in a folder to another one.
 */
function copyFolderContents_(source, target) {
  // Iterate files in source folder
  const filesIterator = source.getFiles()
  while (filesIterator.hasNext()) {
    const file = filesIterator.next()

    // Make a copy of the file keeping the same name
    file.makeCopy(file.getName(), target)
  }
}

/**
 * Recursivelly copies a folder and all its subfolders with all the files
 */
function copyFolder_(toCopy, copyInto) {
  // Makes the new folder (with the same name) into the `copyInto`
  const newFolder = copyInto.createFolder(toCopy.getName())

  // Copy the contents
  copyFolderContents_(toCopy, newFolder)

  // Iterate any subfolder
  const foldersIterator = toCopy.getFolders()
  while (foldersIterator.hasNext()) {
    const folder = foldersIterator.next()

    // Copy the folder and it's contents (recursive call)
    copyFolder_(folder, newFolder)
  }
}

/**
 * Entry point to execute with the Google Apps Script UI
 */
function copyFolder() {
  // Get the folders (by ID in this case)
  const toCopy = DriveApp.getFolderById('')
  const copyInto = DriveApp.getFolderById('')

  // Call the function that copies the folder
  copyFolder_(toCopy, copyInto)
}

You need to add the ids of the folder to be copied and the folder to copy it from on copyFolder().

foldersIterator and filesIterator are Google Apps Script Iterators. They have a method hasNext() that returns if there is another item, and a next() that retrieves it.

Note that copyFolder_(toCopy, copyInto) uses itself to copy its subfolders.

References

• Folder.getFiles() (Google Apps Script reference)
• Folder.getFolders() (Google Apps Script reference)
• Folder.createFolder(name) (Google Apps Script reference)
• File.makeCopy(name, destination) (Google Apps Script reference)
• Iterators (Google Ads Script concepts) (same implementation as Google Apps Script)

This solution is a variation that checks to see if the file or folder has already been copied.

Useful if you have to restart the execution.

It uses includes to check if a file/folder has been copied:

• https://developer.mozilla/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/includes

It goes only one level deep, so won't copy subfolders of subfolders.

I've added a sort using Intl.Collator because it helps me read the console output. Neither sort nor console are needed but may be useful. references:

• https://stackoverflow./a/54427214/16465606
• https://developer.mozilla/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/Collator
• https://tc39.es/ecma402/#sec-collator-parestrings

/** Use with copyFolderContents */
function copyFolder() {
  const topFolderFrom = DriveApp.getFolderById(''); // enter folder ID
  const topFolderTo   = DriveApp.getFolderById(''); // enter folder ID

  // Get the subfolder names and IDs
  const ffTopFolder = topFolderFrom.getFolders();
  const subfolders  = [];
  while (ffTopFolder.hasNext()) {
    let folder      = ffTopFolder.next();
    let ffnameid    = [];
    ffnameid.push(folder.getName(), folder.getId());
    subfolders.push(ffnameid);
  }
  subfolders.sort((a, b) => collator.pare(a[0], b[0]));

  // Get target subfolder names
  const folderstarget = topFolderTo.getFolders();
  const listtarget    = [];
  while (folderstarget.hasNext()) {
    let foldertarget  = folderstarget.next();
    let ffname        = [];
    ffname.push(foldertarget.getName());
    listtarget.push(ffname);
  }
  listtarget.sort(collator.pare);

  // Check if subfolder has already been created
  const alreadycreated = listtarget.flat();
  const stilltocreate  = subfolders.filter(e => !alreadycreated.includes(e[0]));
  console.log('subfolders already created:\n', alreadycreated);
  console.log('subfolders still to create:\n', stilltocreate.map(e => e[0]));

  // Create subfolders
  for (let i = 0, len = stilltocreate.length; i < len; i++) {
    topFolderTo.createFolder(stilltocreate[i][0]);
  }

  // Get the name and ID of subfolders in target folder (needed in case subfolders have been created)
  const ffTopFolderTo = topFolderTo.getFolders();
  const subfoldersTo  = [];
  // Get the subfolders
  while (ffTopFolderTo.hasNext()) {
    let folder        = ffTopFolderTo.next();
    let ffnameid      = [];
    ffnameid.push(folder.getName(), folder.getId());
    subfoldersTo.push(ffnameid);
  }
  subfoldersTo.sort((a, b) => collator.pare(a[0], b[0]));

  // Add the top level folders to arrays to copy files in top level
  subfolders.push([topFolderFrom.getName(), topFolderFrom.getId()]);
  subfoldersTo.push([topFolderTo.getName(), topFolderTo.getId()]);
  console.log('subfolders and top folder:\n', subfolders);
  console.log('subfolders and top folder (target):\n', subfoldersTo);

  for (let i = 0, len = subfolders.length; i < len; i++) {
    let copyFrom = DriveApp.getFolderById(subfolders[i][1]);
    let indx      = subfoldersTo.map(e => e[0]).indexOf(subfolders[i][0]);
    let copyTo    = DriveApp.getFolderById(subfoldersTo[indx][1]);
    // Call the function that copies the folder
    copyFolderContents_(copyFrom, copyTo);
  }
}

/** Copies all the files in a folder to another one, checking for duplicates */
function copyFolderContents_(source, target) {
  Logger.log('copy from: ' + source);
  Logger.log('copy to: ' + target);

  // Get source filenames and IDs
  const files      = source.getFiles();
  const listsource = [];
  while (files.hasNext()) {
    let file       = files.next();
    let ssnameid   = [];
    ssnameid.push(file.getName(), file.getId());
    listsource.push(ssnameid);
  }
  listsource.sort((a, b) => collator.pare(a[0], b[0]));

  // Get target filenames
  const filestarget = target.getFiles();
  const listtarget  = [];
  while (filestarget.hasNext()) {
    let filetarget  = filestarget.next();
    let ssname      = [];
    ssname.push(filetarget.getName());
    listtarget.push(ssname);
  }
  listtarget.sort(collator.pare);

  // Check if file has already been copied
  const alreadycopied = listtarget.flat();
  const stilltocopy   = listsource.filter(e => !alreadycopied.includes(e[0]));
  console.log('files already copied:\n', alreadycopied);
  console.log('files still to copy:\n', stilltocopy.map(e => e[0]));

  // Copy files still to copy
  for (let i = 0, len = stilltocopy.length; i < len; i++) {
    let fileid = stilltocopy[i][1];
    let ss     = DriveApp.getFileById(fileid);
    Logger.log(i + ' - ' + ss);
    ss.makeCopy(stilltocopy[i][0], target);
  }
}

/** Used for sorting alpha/numeric arrays */
const collator = new Intl.Collator('en', {
  numeric: true,
  sensitivity: 'base'
})