GAS to fill in a Document with Spreadsheet data

    0

    0

    Guntar Susatyo

    Google Apps Script to fill in a Document template with Spreadsheet data

    Shortcut: sheetFillDocWithSheetData

    // https://gist.github.com/mhawksey/1170597
    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
    Codiga Logo
    Codiga Hub
    • Rulesets
    • Playground
    • Snippets
    • Cookbooks
    soc-2 icon

    We are SOC-2 Compliance Certified

    G2 high performer medal

    Codiga – All rights reserved 2022.