Create a gist now

Instantly share code, notes, and snippets.

Embed
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);
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

This comment has been minimized.

Show comment
Hide comment
@chengguan

chengguan Oct 2, 2012

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.

chengguan commented Oct 2, 2012

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

This comment has been minimized.

Show comment
Hide comment
@RudoCris

RudoCris May 11, 2013

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 commented May 11, 2013

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

This comment has been minimized.

Show comment
Hide comment
@RudoCris

RudoCris May 11, 2013

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

RudoCris commented May 11, 2013

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

This comment has been minimized.

Show comment
Hide comment
@aoudin

aoudin 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

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

@Jacobvdb

This comment has been minimized.

Show comment
Hide comment
@Jacobvdb

Jacobvdb Mar 11, 2015

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)

Jacobvdb commented Mar 11, 2015

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)
@soyjuan

This comment has been minimized.

Show comment
Hide comment
@soyjuan

soyjuan Jan 2, 2016

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

soyjuan commented Jan 2, 2016

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

@fopkhan

This comment has been minimized.

Show comment
Hide comment
@fopkhan

fopkhan Jan 28, 2017

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

fopkhan commented Jan 28, 2017

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

@quicoto

This comment has been minimized.

Show comment
Hide comment
@quicoto

quicoto May 23, 2017

Is there an updated version @fopkhan ?

quicoto commented May 23, 2017

Is there an updated version @fopkhan ?

@epileftro85

This comment has been minimized.

Show comment
Hide comment
@epileftro85

epileftro85 Jun 6, 2017

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

epileftro85 commented Jun 6, 2017

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

@tokky1

This comment has been minimized.

Show comment
Hide comment
@tokky1

tokky1 Jun 23, 2017

I need someone that can code for me.

tokky1 commented Jun 23, 2017

I need someone that can code for me.

@GraniteConsultingReviews

This comment has been minimized.

Show comment
Hide comment
@GraniteConsultingReviews

GraniteConsultingReviews Sep 4, 2017

I tried this code but not working.

I tried this code but not working.

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