Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Google Apps Script to fill in a Document template with Spreadsheet data
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);
// move file to right folder
var file = DocsList.getFileById(newDoc.getId());
var folder = DocsList.getFolder(FOLDER_NAME);
// 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
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]);
// 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
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") {
} else if (type == "TABLE") {
} // 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

aoudin commented Nov 17, 2013

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)
soyjuan commented Jan 2, 2016

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

fopkhan commented Jan 28, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment