-
-
Save mhawksey/1170597 to your computer and use it in GitHub Desktop.
| 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 |
somebody would have the final script, please :-)))) ???
Hi, This code is older and create errors, Can, I update this post?
Is there an updated version @fopkhan ?
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
I need someone that can code for me.
I tried this code but not working.
I need someone that can code for me.
I can help You !!
The section "// create a new document and add student as editor"
Should be changed as doclist is deprecated.
I did something like