This is best way to create folders from google sheet to google drive

Question

Hey I am kind of stuck here, can we use regex or something to search folder names? How do I add wildcards? eg: In this script it will generate a folder name as "Ticket Number - Ticket Name". Script works well, though if we modify the cell that contains "Ticket name" it generates another folder.

So, our solution is to only search for "Ticket Number" then if it doesn't exist, create a folder with "Ticket Number - Ticket Name".

Possibly we could also Find any folder beginning with "Ticket Number" and append " - Ticket name" if this cell was changed. Not sure how to do this.

var googleFolderID = ('Add Folder ID Here');
var ss = SpreadsheetApp.getActive();



function createTicketFolder() {
 
  
// identify the sheet where the data resides
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Tickets");
  

  
//Select the column we will check for the first blank cell
var columnToCheck = sheet.getRange("A:I").getValues();
  
// Get the last row based on the data range of a single column.
var lastRow = getLastRowSpecialTicket(columnToCheck);

 //identify the cell that will be used to name the folder 
 var ticketNumber = sheet.getRange(lastRow,1,1,1);
  var ticketName = sheet.getRange(lastRow,9,1,1);
  var dataValues = ticketNumber.getValues() + " - " + ticketName.getValues();
  

// Find Customer folder within parentFolder, formatted as "Lastname Firstname - CustomerNumber"
//IMPORTANT: retrieve the value!
  // old var ticketNumber = sheet.getRange(lastRow,1).getValue();
  var customerNumber = sheet.getRange(lastRow,2).getValue();  
  //identify the parent folder the new folder will be in
  var parentFolder=DriveApp.getFolderById(googleFolderID);
  var customerSheet = ss.getSheetByName("Customer Data");
  var data = customerSheet.getDataRange().getValues();
  // the following line retrieves all customerIds from the 2-D value range and saves them in the 1-D array "customerIds" - necessary for using indexOf()
  var customerIds = data.map(function(e){return e[0];}); 
  var index = customerIds.indexOf(customerNumber);
  // if the customer number has been found
  if(index >= 0){
    // Lastname Firstname - Customernumber"
    var folderName = data[index][3] + " " + data[index][2] + " - " + data[index][0]; 
    //  retrieve the first folder with this name - important: avoid duplicates!
    var customerFolder = parentFolder.getFoldersByName(folderName);//.getFoldersByName(folderName);
    if(customerFolder.hasNext()){
      var customerFolder = customerFolder.next();
      //check if ticket folder exists
      var ticketFolder=customerFolder.getFoldersByName(dataValues); // Old ticketNumber, New dataValues
      if(ticketFolder.hasNext()){
       // var newFolder=ticketFolder.next();
       } else {
        // if it does not exist, create the new folder
        var newFolder=customerFolder.createFolder(dataValues); // Old ticketNumber, New dataValues
        var addLink=sheet.getRange(lastRow, 14).setFormula('=HYPERLINK("' + newFolder.getUrl() + '")');
         Logger.log(addLink);
         var newFolder1=newFolder.createFolder("Production");
         var newFolder2=newFolder.createFolder("Finished Pictures");
         var newFolder3=newFolder.createFolder("Source");

       }
    }    
  }
};


function getLastRowSpecialTicket(range){
  var rowNum = 0;
  var blank = false;
  for(var row = 0; row < range.length; row++){
 
    if(range[row][0] === "" && !blank){
      rowNum = row;
      blank = true;
    }else if(range[row][0] !== ""){
      blank = false;
    };
  };
  return rowNum;
};

Our sheets looks like this:

Ticket Sheet

Ticket Number	Customer Number	Customer name						Ticket Name
10000	1000							Example ticket

Customer Sheet

Customer Number	FirstName LastName	First Name	LastName
1000	John Smith	John	Smith

What we need

We are looking at around line 49, For this to first check the google folders for "ticket number", to match with last row on the sheet. Then if no matches create folder as "Ticket Number - Ticket Name".

If there is a match, append "ticket name" to make folder formatted right. Otherwise do nothing.

What we tried

Row 49

 var ticketFolder=customerFolder.getFoldersByName(dataValues); 

We changed this to,

 var ticketFolder=customerFolder.getFoldersByName(ticketNumber);

But looking to create a wildcard to only search tickenumber ignore rest of folder name.

 var ticketFolder=customerFolder.getFoldersByName(/.*ticketNumber().*/);

Something like this. I am guessing formatting like this is wrong. Whow do we do this?

Thank you for your help.

Answer 1

Here's a simple example:

function findmyfiles() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0')
  const r = Drive.Files.list({q:"title contains 'Ticket'"});
  let itmA = ["id","Title","MimeType","Path"];
  JSON.parse(r).items.forEach(itm => {
    itmA.push([itm.id,itm.title,itm.mimeType,getPathFromId(itm.id)]);
  })
  sh.getRange(1,1,itmA.length,3).setValues(itmA);
}

function getPathFromId(id) {
  try {
    var file = DriveApp.getFileById(id)
    var pA = [];
    pA.push(file.getName());
    var folder = file.getParents();
    while (folder.hasNext()) {
      var f = folder.next();
      pA.push(f.getName());
      folder = f.getParents()
    }
    var r = pA.reverse().join(' / ');
  }
  catch (e) {
    return e;
  }
  return r;
}

If you have a lot of file you may have to use pagetoken