Google Apps Script to fill in a Document template with Spreadsheet data

gistfile1.js

function onOpen() {
  var menuEntries = [ {name: "Create Diary Doc from Sheet", functionName: "createDocFromSheet"}];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("Fitness Diaries", menuEntries);
}

function createDocFromSheet(){
  var templateid = "1O4afl8SZmMxMFpAiN16VZIddJDaFdeRBbFyBtJvepwM"; // get template file id
  var FOLDER_NAME = "Fitness Diaries"; // folder name of where to put completed diaries
  // get the data from an individual user
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
  
  var username = sheet.getName(); // get their email (from sheet name)
  
  // create a new document and add student as editor
  var newDoc = DocumentApp.create("Fitness Diary - "+username);
  newDoc.addEditor(username);
  // move file to right folder
  var file = DocsList.getFileById(newDoc.getId());
  var folder = DocsList.getFolder(FOLDER_NAME);
  file.addToFolder(folder);
  
  // for each week's entry fill in the template with submitted data 
  for (var i in data){
    var row = data[i];
    
    // next bit taken from csmithiowa's code http://www.google.com/support/forum/p/apps-script/thread?tid=70aae4c0beabeac7&hl=en
    var docid = DocsList.getFileById(templateid).makeCopy().getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getActiveSection();
    body.replaceText("%WEEKNO%", row[2]);
    body.replaceText("%TIMESTAMP%", Utilities.formatDate(row[1], "GMT", "HH:mm dd/MM/yyyy"));
    body.replaceText("%SLEEPQUAL%", row[4]);
    body.replaceText("%ENERGYLVL%", row[5]);
    body.replaceText("%MOTIVLVL%", row[6]);
    body.replaceText("%OUTLINE%", row[7]);
    body.replaceText("%PROGRESS%", row[8]);
    doc.saveAndClose();
    // end of csmithiowa's
    
    appendToDoc(doc, newDoc); // add the filled in template to the students file
    DocsList.getFileById(docid).setTrashed(true); // delete temporay template file
  }
  ss.toast("Diary has been complied");
}

// Taken from Johninio's code http://www.google.com/support/forum/p/apps-script/thread?tid=032262c2831acb66&hl=en
function appendToDoc(src, dst) {
  // iterate accross the elements in the source adding to the destination
  for (var i = 0; i < src.getNumChildren(); i++) {
    appendElementToDoc(dst, src.getChild(i));
  }
}

function appendElementToDoc(doc, object) {
  var type = object.getType(); // need to handle different types para, table etc differently
  var element = object.removeFromParent(); // need to remove or can't append
  Logger.log("Element type is "+type);
  if (type == "PARAGRAPH") {
    doc.appendParagraph(element);
  } else if (type == "TABLE") {
    doc.appendTable(element);
  } // else if ... I think you get the gist of it
}
// end of Johninio's

As line 59 is removing elements from doc, I think you will get error when trying to getChild by the original index in line 53. You might end up getting only the odd number children and got an error half way.

I think it would be better to write the object.copy() in line 59,
because when object is the last element in the src, it can not be removed

I think it would be better to write the var element = object.copy() in line 59
because when object will be the last element in the src, it can not be removed

I try you code but I have a problem with this line :
appendToDoc(doc, newDoc);

the good element to pass is the Body of the docs :
var body = doc.getActiveSection();
var newBody = newDoc.getActiveSection();
appendToDoc(body, newBody);

and then it runs for me, thx a lot

The section "// create a new document and add student as editor"
Should be changed as doclist is deprecated.

I did something like

 var newDoc =DocumentApp.create("Fitness Diary - "+username)
 var newDocId = newDoc.getId()
 var file = DriveApp.getFileById(newDocId)
 folder.addFile(file)
 DriveApp.removeFile(file)

somebody would have the final script, please :-)))) ???

Hi, This code is older and create errors, Can, I update this post?

Is there an updated version @fopkhan ?

SORRY the markdown, here is an updated script
function createDocFromSheet(){
var templateid = "TEMPLATE_ID"; // get template file id

// get the data from an individual user
var ss = SpreadsheetApp.openById("FILE_ID");
var sheet = ss.setActiveSheet(ss.getSheets()[1]);
var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();

var username = sheet.getName(); // get their email (from sheet name)

// for each week's entry fill in the template with submitted data
for (var i in data){
var col = data[i];
var fileName = col[1] + col[2];

var newDoc = DocumentApp.create(fileName);
var file = DriveApp.getFileById(newDoc.getId());
var folder = DriveApp.getFolderById('FOLDER_ID');
folder.addFile(file);

// next bit taken from csmithiowa's code http://www.google.com/support/forum/p/apps-script/thread?tid=70aae4c0beabeac7&hl=en
var docid = DriveApp.getFileById(templateid).makeCopy().getId();
var doc = DocumentApp.openById(docid);
var body = doc.getActiveSection();
body.replaceText("%PARAM1%", col[2]);
body.replaceText("%PARAM2%", col[1]);
body.replaceText("%PARAM3%", col[0]);
body.replaceText("%PARAM4%", col[8]);

doc.saveAndClose();
// end of csmithiowa's

appendToDoc(doc, newDoc); // add the filled in template to the students file
DriveApp.getFileById(docid).setTrashed(true); // delete temporay template file

}
ss.toast("Diary has been complied");
}

// Taken from Johninio's code http://www.google.com/support/forum/p/apps-script/thread?tid=032262c2831acb66&hl=en
function appendToDoc(src, dst) {
// iterate accross the elements in the source adding to the destination
for (var i = 0; i < src.getNumChildren(); i++) {
appendElementToDoc(dst, src.getChild(i));
}
}

function appendElementToDoc(doc, object) {
var type = object.getType(); // need to handle different types para, table etc differently
var element = object.copy();
Logger.log("Element type is "+type);
if (type == "PARAGRAPH") {
doc.appendParagraph(element);
} else if (type == "TABLE") {
doc.appendTable(element);
} // else if ... I think you get the gist of it
}
// end of Johninio's

I need someone that can code for me.

I tried this code but not working.

I need someone that can code for me.

I can help You !!