Skip to content

@mhawksey /gist:1170597
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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);
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
@chengguan

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.

@RudoCris

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

@RudoCris

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

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

@Jacobvdb

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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.