Skip to content

Instantly share code, notes, and snippets.

@geedew
Forked from mhawksey/gist:1170597
Created December 2, 2017 14:53
Show Gist options
  • Save geedew/97c66b187db2ac8ecebaef535750a76b to your computer and use it in GitHub Desktop.
Save geedew/97c66b187db2ac8ecebaef535750a76b to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment