Create new Folder in Drive if it doesn't already exist

Question

CONTEXT

In my Drive there's the following structure:

Ops folder || Sales folder

Ops contains 3 folders: Process1 / Process2 / Process3

Sales contains 3 folders: Customer1 / Customer2 / Customer3

TARGET

I'm asking for the names of the folders in Drive > SALES

In a Sheet I have a list of Names of some Prospects. I'm checking one by one if the name of the prospect matches with any folder in my Drive.

In case there's a folder that matches the prospect name then do nothing.

In case there's no folder for that Prospect I want to create a new folder in Sales with the prospect name

function prepararPropuesta() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var ssAS = ss.getActiveSheet()
  var ssProspects = ss.getSheetByName('Prospects')

  var lr = ssProspects.getLastRow()
  var lc = ssProspects.getLastColumn()  



  // I'm looking for the column 'Prospect' in the Sheet
  for(i=2; i<lc; i++){  

    var columnas = ssProspects.getRange(2, i).getValue()
    Logger.log(columnas)

  if(columnas == 'Nombre Prospect'){

     var colProspect = ssProspects.getRange(2, i).getColumn()
     Logger.log(colProspect)
  }
  }


/*
     Now I'm looking for the names of the folders in my Drive
     This is looking in all my Drive. It would be nice to filter the 
search and look just in SubFolders(Sales)
*/
   var folders = DriveApp.searchFolders('"me" in owners');
    while (folders.hasNext()) {
   var folder = folders.next();
   Logger.log(folder.getName());

/*       The first Prospect Name is in row 2 and I'm creating a loop to 
get each Name 
         If the name matches an existing Folder then ends the function
         If the Prospect Name is not found in the Folders list then creates a new Folder under the name prospectName
*/
      for(i=2; i<lr; i++){
      var prospectName = ssProspects.getRange(i, colCliente).getValue()

            var folderName = folder.getName()
            if(folderName = prospectName){

              return

            }  
      }


      DriveApp.createFolder(prospectName)


}
}

In between the lines in the code you will find a description step by step

(1) I'm finding the column in which the prospect names are -> This works fine

(2) I'm using WHILE to get all the name folders in my Drive This log

Logger.log(folder.getName())

Returns

[18-04-21 06:33:35:985 PDT] FolderIterator

Is it getting the names correctly?

(3) I created the variables folderName and prospectName and looped them to check if there's a match -> This part doesn't work correctly. I guess that my I'm not using the right methods although I researched in developers.google. How could it be done?

Thnx!

Now I'm more specific about the question: (1) Am I using the right method the list the folder names from Drive?; (2) The loop in the end that check the prospectNames and the folderNames is not correct cause is not creating the folders for new prospects. I'd love to add more info but my knowledge is limited and I tried it everything. Where am I missing the point? Thanks — Gonzalo

JPV JPV · Accepted Answer · 2018-04-21T14:07:52

It's not a good idea to use .getValue() inside a loop because in that case you make one call to the spreadsheet for every row. It's considered to be good practice to get all values in one call and then process the returning array in the script.

I'm not sure why it is necessary to loop through the columns to find the column with the name? Assuming you know what that column is, you can just get the values (names) of that column directly.

Here's an example that assumes the names in column B (change range to suit) of sheet 'Prospects'. For every name found the function checkIfFolderExistElseCreate() is called. That function checks if a folder with the name (second paramater) exists. If it does, nothing happens else a folder with that name is created.

The first paramater of the function is the 'parent' folder in which the new folders will be created.

function prepararPropuesta() {
var parent = DriveApp.getFolderById("id_of_parentFolder")
SpreadsheetApp.getActive().getSheetByName('Prospects').getRange('B2:B').getValues()
    .forEach(function (r) {
        if(r[0]) checkIfFolderExistElseCreate(parent, r[0]);
    })
}

function checkIfFolderExistElseCreate(parent, folderName) {
var folder;
try {
    folder = parent.getFoldersByName(folderName).next();
} catch (e) {
    folder = parent.createFolder(folderName);
}
}

I hope this helps?

Create new Folder in Drive if it doesn't already exist

1 Answers