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.
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: