google-apps-scriptgoogle-drive-apigoogle-docs

How to save new create documents in one file?


I'm creating several doc.files according to the template, I take data from the sheet. Everything is saved in different files. Please help me to save the new files in one document.

function myFunction() {
  const docFile = DriveApp.getFileById("....."); //template file
  const tempFolder = DriveApp.getFolderById("...."); //folder for new files

  var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //get an active    book, get an asset.sheet
  var l = list.getLastRow(); // get the last line

  for (var i = 9; i <= l; i++) {

    const tempFile = docFile.makeCopy(tempFolder);//make a copy of the template
    const tempDocFile = DocumentApp.openById(tempFile.getId()); //open a copy of the template
    const body = tempDocFile.getBody(); //get the body (text) of the copy file

    //getting variables from the table::
    var a1 = list.getRange(i, 1).getValue(); // get data from drain number i and column 1 -
    var a2 = list.getRange(i, 2).getValue();
    var a3 = list.getRange(i, 3).getValue();

    //replacing the necessary data in the text of the template copies:
    body.replaceText("{......}", a1);
    body.replaceText("{.....}", a2);
    body.replaceText("{....}", a3);

    //naming the created file:
    const newFileName = '.....' + a2; // assigning a name
    tempFile.setName(newFileName);

    tempDocFile.saveAndClose(); //save and close
  }
}

I'm not a programmer. I found the script on the Internet and changed it for myself. I don't know exactly how to change it.


Solution

  • To achieve this, I modified your code according to the problem.

    The code now works as intended. It creates new document from a template using each row data from the spreadsheet. Then using forEach, it appends the contents of each generated document to the file called Merged file.

    Modified Code:

    function myFunction() {
      const docFile = DriveApp.getFileById("--ID--"); 
      const tempFolder = DriveApp.getFolderById("--ID--"); 
    
      const list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
    
      // gets all the data from range 9 to last row and remove rows where data is blank
      const data = list.getRange(9,1, list.getLastRow(),list.getLastColumn()).getValues()
      .filter(x => x.some(y => y !== ''));
    
      const newDoc = DocumentApp.create("Merge Files").getId();
      const newDocBody = DocumentApp.openById(newDoc).getActiveTab().asDocumentTab().getBody();
    
      data.forEach((x, i) => {
        const tempFile = docFile.makeCopy(tempFolder);
        const tempDocFile = DocumentApp.openById(tempFile.getId());
        const body = tempDocFile.getBody();
    
        // replace body text     
        body.replaceText("{......}", x[0]);
        body.replaceText("{.....}", x[1]);
        body.replaceText("{....}", x[2]);
        
        const newBody = tempDocFile.getBody().getText();
        if(i >= 1){
          newDocBody.getParagraphs()[newDocBody.getParagraphs().length - 1].appendPageBreak();
          newDocBody.appendParagraph("");
        }
        newDocBody.editAsText().appendText(newBody);
        DriveApp.getFileById(tempDocFile.getId()).setTrashed(true);
      })
    
      // moves the new Document file to the tempfolder
      const newFile = DriveApp.getFileById(newDoc).moveTo(tempFolder);
      // consoles the file url.
      console.log(newFile.getUrl())
    } 
    

    Reference: