GAS to fill in a Document with Spreadsheet data



    Guntar Susatyo

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

    Shortcut: sheetFillDocWithSheetData

    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);
      // move file to right folder
      var file = DocsList.getFileById(newDoc.getId());
      var folder = DocsList.getFolder(FOLDER_NAME);
      // 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
        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]);
        // 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
    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") {
      } else if (type == "TABLE") {
      } // 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.