public
Last active

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

  • Download Gist
gistfile1.js
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.