Create document and save file in folder found by name-CodePudding

Thanks in advance for any assistance yall can give me.

I have a spreadsheet which I use to create documents based on a template. The names of the clients exists in D2:D.

Here's what I want my scripts to do:

Check parentfolder for folders with the names from D2:D.
If a folder does not exist, create one.
Create new document(s) based on template
Save in the corresponding folder. So where D2:D for the row matches, place in that folder.

So far I have managed to do the top three functions but am struggling to save the file in the corresponding folder.

I want column 3 (D) to provide the name of the folder and where each document is saved

Create folders script `

function clientFolder() {
var parent = DriveApp.getFolderById("folder ID here")
SpreadsheetApp.getActive().getSheetByName('Engagement Letter').getRange('D2:D').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);
}
}

Create doc script `

function createEngagementLetter() {
  //This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('Template ID Here');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('Want Folder Name instead of ID')
  //Here we store the sheet as a variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Engagement Letter')
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getValues();
  
  //Start processing each spreadsheet row
  rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //if (index === 1) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[0]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[3]}, Letter of Engagement` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText('{{Client Email}}', row[1]);
    body.replaceText('{{Date}}', row[2]);
    body.replaceText('{{Client}}', row[3]);
    body.replaceText('{{Primary Point of Contact}}', row[4]);
    body.replaceText('{{PPOC Role}}', row[5]);
    body.replaceText('{{PPOC Email}}', row[6]);
    body.replaceText('{{PPOC First Name}}', row[7]);
    body.replaceText('{{Supervisor}}', row[8]);
    body.replaceText('{{Issues overview and what we will do}}', row[9]);
    body.replaceText('{{Discount}}', row[10]);
    body.replaceText('{{Initial Rate}}', row[11]);  
    body.replaceText('{{Reason for Discount}}', row[12]);
    body.replaceText('{{Discount Amount}}', row[13]);
    body.replaceText('{{Overall Rate}}', row[14]);
    body.replaceText('{{Include Estimate}}', row[15]);
    body.replaceText('{{Estimate of Overall Cost}}', row[16]);

    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index   1, 1).setValue(url)
    
  })
  
}

CodePudding user response：

In your showing script, how about modifying your createEngagementLetter() as follows?

From:

const copy = googleDocTemplate.makeCopy(`${row[3]}, Letter of Engagement` , destinationFolder)

To:

const folders = DriveApp.getFoldersByName(row[3]);
if (!folders.hasNext()) return;
const copy = googleDocTemplate.makeCopy(`${row[3]}, Letter of Engagement`, folders.next());

In this case, const destinationFolder = DriveApp.getFolderById('Want Folder Name instead of ID') is not used.

Note:

From your question, this modification supposes that your expected destination folders have already been existing. Please be careful about this.

CodePudding user response：

Try it this way:

function clientFolder() {
  var folder = DriveApp.getFolderById("folder ID here")
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Engagement Letter");
  const vs = sh.getRange("D2:D"   sh.getLastRow()).getValues()
  vs.forEach(function (r) {
    if (r[0]) {
      let n = 0;
      let folders = folder.getFoldersByName(r[0]) 
      while(folders.hasNext()) {
        n  ;
        let f = folders.next();
        createEngagementLetter(f.getId())
      } 
      if(!n) {
        createEngagementLetter(folder.createFolder(r[0]).getId())
      }
    }
  })
}
function createEngagementLetter(fid) {
  const googleDocTemplate = DriveApp.getFileById('Template ID Here');
  const destinationFolder = DriveApp.getFolderById(fid)
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Engagement Letter')
  const rows = sheet.getDataRange().getValues();
  rows.forEach(function(row, index){
    if (index === 0) return;
    if (row[0]) return;
    const copy = googleDocTemplate.makeCopy(`${row[3]}, Letter of Engagement` , destinationFolder)
    const doc = DocumentApp.openById(copy.getId())
    const body = doc.getBody();
    body.replaceText('{{Client Email}}', row[1]);
    body.replaceText('{{Date}}', row[2]);
    body.replaceText('{{Client}}', row[3]);
    body.replaceText('{{Primary Point of Contact}}', row[4]);
    body.replaceText('{{PPOC Role}}', row[5]);
    body.replaceText('{{PPOC Email}}', row[6]);
    body.replaceText('{{PPOC First Name}}', row[7]);
    body.replaceText('{{Supervisor}}', row[8]);
    body.replaceText('{{Issues overview and what we will do}}', row[9]);
    body.replaceText('{{Discount}}', row[10]);
    body.replaceText('{{Initial Rate}}', row[11]);  
    body.replaceText('{{Reason for Discount}}', row[12]);
    body.replaceText('{{Discount Amount}}', row[13]);
    body.replaceText('{{Overall Rate}}', row[14]);
    body.replaceText('{{Include Estimate}}', row[15]);
    body.replaceText('{{Estimate of Overall Cost}}', row[16]);
    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(index   1, 1).setValue(url)
  })
}