Skip to content

Instantly share code, notes, and snippets.

@geedew

geedew/gist:97c66b187db2ac8ecebaef535750a76b

Forked from mhawksey/gist:1170597
Created December 2, 2017 14:53
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save geedew/97c66b187db2ac8ecebaef535750a76b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save geedew/97c66b187db2ac8ecebaef535750a76b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mhawksey revised this gist Aug 25, 2011. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion gistfile1.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,7 @@
    function onOpen() {
    var menuEntries = [ {name: "Create Diary Doc from Sheet", functionName: "createDocFromSheet"}];
    SpreadsheetApp.getActiveSpreadsheet().addMenu("Fitness Diaries", menuEntries);
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    ss.addMenu("Fitness Diaries", menuEntries);
    }

    function createDocFromSheet(){
  2. mhawksey revised this gist Aug 25, 2011. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions gistfile1.js
    View file
    Original file line number Diff line number Diff line change
    @@ -42,6 +42,7 @@ function createDocFromSheet(){
    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
  3. mhawksey created this gist Aug 25, 2011.
    65 changes: 65 additions & 0 deletions gistfile1.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,65 @@
    function onOpen() {
    var menuEntries = [ {name: "Create Diary Doc from Sheet", functionName: "createDocFromSheet"}];
    SpreadsheetApp.getActiveSpreadsheet().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
    }
    }

    // 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