Google App Script to copy and rename folder structure, including updating fields within a doc and sheet contained within the folders?

Question

I must first apologise as I've never done any coding before. I've been doing a lot of googling and looking at other peoples codes to try and work out whats going on. I have the logic, but I don't know all the classes, triggers or even the right terminology! I can map out in my mind what needs to happen but need to learn the language first, so hoping you can point me in the right direction for learning materials, and help with this particular project.

I would like to automate something that we do a lot in our office, which is creating a new customer folder containing pre-defined sub-folders, and taking a template doc and sheet, filling out name and address fields, and saving them with the customer name appended with the doc or sheet name into relevant newly copied/created folders. I'm struggling to coherently explain this, so I've drawn something which will hopefully help: sketch of folder structure with doc and sheet location

I've made a Google Form that posts results to a spreadsheet, and I've put a script in that sheet. I've worked out how to take a template sheet and doc, copy and rename them into a folder, but can't figure out what I need to do with the folders so they can be filed into unique places on drive.

Here is the code that I'm testing to copy the templates, add fields and rename and save:

function autoFillGoogleDocFromForm(e) {
  var timestamp = e.values[0];
  var title = e.values[1];
  var firstName = e.values[2];
  var lastName = e.values[3];
  var emailAddress = e.values[4];
  var premisesType = e.values[6];

  
  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('1QjYas1erxtQjLPduNMoY1EIEAjBOI5qbAMeX59qPib4'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('18GWERZFqgf9TbPfKDRX8gK02kbKpRLch')
  var copy = file.makeCopy(lastName + ', ' + firstName, folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId()); 
  
  //Since everything we need to change is in the body, we need to get that
  var body = doc.getBody();
  var header = doc.getHeader();
  
  //Then we call all of our replaceText methods
  header.replaceText('{{Premises type}}', premisesType);
  body.replaceText('{{First Name}}', firstName); 
  body.replaceText('{{Surname}}', lastName);  
  body.replaceText('{{Salutation}}', title); 
  body.replaceText('{{email}}', emailAddress);
  body.replaceText('{{Premises type}}', premisesType); 
  
  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); }

// REPLICATE ABOVE WITH SHEETS, fills out cell G2 with test info

function autoFillGoogleSheetFromForm(e) {
  var timestamp = e.values[0];
  var title = e.values[1];
  var firstName = e.values[2];
  var lastName = e.values[3];
  var emailAddress = e.values[4];
  var premisesType = e.values[6];

  
  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('1yvOtPcnGMQnE7Hc0d6vVMSG2Q5nY9u-MijaDZ51jlOU'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('18GWERZFqgf9TbPfKDRX8gK02kbKpRLch')
  var copy = file.makeCopy(lastName + ', ' + firstName, folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var ss = SpreadsheetApp.openById(copy.getId()); 
  // ss is now the spreadsheet the script is associated with
  var sheet = ss.getSheets()[0]; 
  // sheets are counted starting from 0
  // sheet is the first worksheet in the spreadsheet
  var cell = sheet.getRange("G2"); 
  cell.setValue(premisesType);
}

Hoping in anticipation someone can help without flaming me. I'm embarrassed enough posting this! I promise I am trying to learn and not going for the easy option! Vince.

Edit: thanks to @iansedano I've almagamted some code which gets me whaere I need to be, except on some occasions I have the folder structer created multiple times, as though the smae form has been resubmitted several times. Here is the code I am now testing:

function autoCreateFoldersSheetDoc(e) {
  var title = e.values[1];
  var firstName = e.values[2];
  var lastName = e.values[3];
  var emailAddress = e.values[4];
  var premisesType = e.values[6];

   // This is the root directory where all the client folders would be stored
  const customerRoot = DriveApp.getFolderById('1s4fD0wk8Mj_YiCkEOj7hUZedOv_AyPdl');
  const mainFolder = customerRoot.createFolder(lastName + ', ' + firstName);

   // Creating the sub folders
   // Some are assigned to variables so that later children can be added
  mainFolder.createFolder("1. Customer Correspondence")
  const costsFolder = mainFolder.createFolder("2. Costs")
  const proposalsFolder = mainFolder.createFolder("3. Proposals")
  mainFolder.createFolder("4. Drawings")
  mainFolder.createFolder("5. Testing & Commissioning")

   // Creating children
  costsFolder.createFolder("1. Suppliers")
  const dcaFolder = costsFolder.createFolder("2. DCA")

   // Take the template proposal doc and save a copy to the proposals folder
  var fileA = DriveApp.getFileById('1QjYas1erxtQjLPduNMoY1EIEAjBOI5qbAMeX59qPib4');
  var copyA = fileA.makeCopy(lastName + ', ' + firstName, proposalsFolder); 

   // Open new proposal doc and replace text
  var doc = DocumentApp.openById(copyA.getId());
  var body = doc.getBody();
  var header = doc.getHeader();
  header.replaceText('{{Premises type}}', premisesType);
  body.replaceText('{{First Name}}', firstName); 
  body.replaceText('{{Surname}}', lastName);  
  body.replaceText('{{Salutation}}', title); 
  body.replaceText('{{email}}', emailAddress);
  body.replaceText('{{Premises type}}', premisesType);
  doc.saveAndClose();

   // Take the template costs sheet and save a copy to the DCA folder
  var fileB = DriveApp.getFileById('1yvOtPcnGMQnE7Hc0d6vVMSG2Q5nY9u-MijaDZ51jlOU'); 
  var copyB = fileB.makeCopy(lastName + ', ' + firstName, dcaFolder); 

   // Open new costs sheet and replace text
  var ss = SpreadsheetApp.openById(copyB.getId()); 
  var sheet = ss.getSheets()[0]; 
  var cellA = sheet.getRange("G2"); 
  var cellB = sheet.getRange("H5");
  var cellC = sheet.getRange("H6");
  var cellD = sheet.getRange("H4");
  var cellE = sheet.getRange("I2");

  cellA.setValue(premisesType);
  cellB.setValue(firstName);
  cellC.setValue(lastName);
  cellD.setValue(title);
  cellE.setValue(emailAddress);

}

Any ideas how I can prevent the duplicates?

Answer 1

Sample function

/**
 * This function takes three arguments, the client name,
 * the costing sheet id, and the proposal doc id.
 * It will then create the folders and place the documents in the
 * right place, wherever they are in drive.
 */
function createClientAccountFolder(clientName, costingSheetId, ProposalDocId) {
  // This is the root directory where all the client folders would be stored
  const customerRoot = DriveApp.getFolderById('[YOUR_ROOT_FOLDER_ID]');

  if (clientName) {
    // This creates the main folder with the customer name
    const mainFolder = customerRoot.createFolder(clientName);

    // Creating the sub folders
    // Some are assigned to variables so that later children can be added
    mainFolder.createFolder("1. Customer Correspondence")
    const costsFolder = mainFolder.createFolder("2. Costs")
    const proposalsFolder = mainFolder.createFolder("3. Proposals")
    mainFolder.createFolder("4. Drawings")
    mainFolder.createFolder("4. Testing & Commissioning")

    // Creating children
    costsFolder.createFolder("1. Suppliers")
    const dcaFolder = costsFolder.createFolder("2. DCA")

    // Getting the documents
    const costingSheet = DriveApp.getFileById(costingSheetId)
    const proposalDoc = DriveApp.getFileById(ProposalDocId)

    // Moving the documents to the respective folders
    costingSheet.moveTo(dcaFolder)
    proposalDoc.moveTo(proposalsFolder)

  } else {
    Logger.log('No Client Name Specified')
  }
}

function test(){
  createClientAccountFolder("John Smith", "[SHEET_ID]", "[DOC_ID]")
}

I was not sure how exactly to integrate it into your other script, since I don't have the data to be able to test it. Though, as a suggestion, you might add a line to the end of the functions autoFillGoogleDocFromForm and autoFillGoogleSheetFromForm.

return id

You would need to get the id of the documents first, and assign it to an id variable. Be sure to assign it as a string if you want to use the function above without any changes.

So all you would need to do is call all the functions from a main functions like this:

function main(){
    const clientName = "John Smith"
    const docId = autoFillGoogleDocFromForm()
    const sheetId = autoFillGoogleSheetFromForm()
    createClientAccountFolder(clientName, sheetId, docId)
}

You might also want a way to get the clientName in a different way, so this could all be automated easier. Though hopefully you get an idea of the kind of thing that would need to be done and you can modify it to your needs.

Let me know if anything is unclear!

References

• createFolder(name)
• getFolderById(id)
• getFileById(id)
• moveTo(destination)