CreateDocFromFragments

CreateDocFromFragments.gs

/**
This script generates a google doc from multiple 'parent/child' records.
It is meant to be triggered from an AppSheet.com app vis a vis a Google Sheet, e.g.:

1. AppSheet adds record/event to Google Sheet >> 
2. Trigger listening to Google Sheet >> 
3. This script invoked
4. your shiny google doc is generated from a parent/child appsheet app

*/

/**
STEP 1: PROVIDE A FILE NAME
this is the final outputed document name, it will appear on the root of My Drive
*/
outputDocumentName = "MERGE-EXAMPLE";



/**
STEP 2: PROVIDE THIS SCRIPT WITH THE LOCATION OF THE CHILD DATA
the child Google Sheet that is to be queried
A (child) SHEET ID for document fragments, referenced by the active sheet being called here (the parent)
we assume this sheet has only one tab, with four columns: ChildID, header, details, image
*/ 
gsheetID = "1Fcmbbsc8GDmahDiWcIvo78u8DXKP7mGNvt3yRGNMrDA";



/** 
STEP 3: PROVIDE THIS SCRIPT WITH TWO LOCATIONS FOR TWO SETS OF IMAGES
where are the header (parent) image and fragment (child) image folders located?
*/
headerImagesFolder = DriveApp.getFolderById("1q5mvfnCXWQH_V1Vsqx68rgDDEW1E30nO");
fragmentsImagesFolder = DriveApp.getFolderById("16528PaARV_T_3wnhjyl1rs6p8Zhck_s6");



/**
STEP 4: PROVIDE A GOOGLE DRIVE ID FOR A TEMPLATE FILE TO GET STARTED
we have provided a world readable one with a header and a footer
this is a hacky workaround to get things like pagination
wondering why we didn't implement headers and footers much more than we did?
see here: https://issuetracker.google.com/issues/36755897
we were able to "copy from a template" however. Still, it's pretty tedious work..
here is the publicly shared google doc link:
https://docs.google.com/document/d/15essUDitFh8Hr5jHN8Hurs-2uUSIEktD6uOlrSg4vwk

*/
templateForHeadersAndFooters = "15essUDitFh8Hr5jHN8Hurs-2uUSIEktD6uOlrSg4vwk";



/**
STEP 5: PROVIDE COLUMN NUMBERS FOR TITLE, DETAILS, IMAGE, and PARENT/CHILD KEYS
this is to abtract this script so that you don't have to go digging down below in the muck
column numbers start at zero (0) from the left of the spreadsheet
*/
yourParentKeyCol = 0
yourParentTitleCol = 1
yourParentDetailCol = 2
yourParentImagePathCol = 3

yourChildKeyCol = 0
yourChildParentKeyCol = 1
yourChildTitleCol = 2
yourChildDetailCol = 3
yourChildImagePathCol = 4



/**
TECHNICALLY, YOU COULD STOP HERE AND ATTEMPT TO RUN THE SCRIPT :)
*/


// using the above ID, let's get ranges up to 500 rows for the fragments (children)
docfragmentsSheet = SpreadsheetApp.openById(gsheetID).getSheets()[0];
fragments = docfragmentsSheet.getRange('A2:H500');



// begin formatting styles
var parentParagraphStyle = {};
parentParagraphStyle[DocumentApp.Attribute.FONT_SIZE] = 11;
parentParagraphStyle[DocumentApp.Attribute.FONT_FAMILY] = 'Open Sans';

var fragmentParagraphStyle = {};
fragmentParagraphStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.LEFT;
fragmentParagraphStyle[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
fragmentParagraphStyle[DocumentApp.Attribute.FONT_SIZE] = 10;
fragmentParagraphStyle[DocumentApp.Attribute.BOLD] = false;
fragmentParagraphStyle[DocumentApp.Attribute.INDENT_FIRST_LINE] = 50;
fragmentParagraphStyle[DocumentApp.Attribute.INDENT_START] = 50;
fragmentParagraphStyle[DocumentApp.Attribute.INDENT_END] = 50;

var fragmentHeaderStyle = {};
fragmentHeaderStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;

var fragmentImageStyle = {};
fragmentImageStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER;
fragmentImageStyle[DocumentApp.Attribute.INDENT_FIRST_LINE] = 50;
fragmentImageStyle[DocumentApp.Attribute.INDENT_START] = 50;
fragmentImageStyle[DocumentApp.Attribute.INDENT_END] = 50;
fragmentImageStyle[DocumentApp.Attribute.MARGIN_LEFT] = 50;

var pageHeaderStyle = {}
pageHeaderStyle[DocumentApp.Attribute.MARGIN_TOP] = 0.0;

// end formatting styles


// begin main function "onChange" 
// this is the function you would add to your App Script Trigger
function onChange(e) {

    // the google doc we are going to create, at the top level of your drive
    newDoc = DocumentApp.create(outputDocumentName);
    // we use a world readable template to copy the header and footer, this part is pretty janky.
    finalDoc = addHeaderFooterFromTemplate(newDoc);
    // in fact does it even work? We are skeptical

  
    // the actively called sheet (the parent), we assume it only has one tab, with four columns extracted: ID, header, details, image
    var mergeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var range = mergeSpreadsheet.getRange('A2:H500');
    range.activate();
    var rows = range.getDisplayValues();
    
    var docrows = []
    for (var i = 0; i < rows.length ; i++){  
      // if the first column is not empty let's grab that row
      if(rows[i][0].length > 0)            
      {
        docrows.push(rows[i]);
      }
    }

    for (var parentrow = 0; parentrow < docrows.length; ++parentrow ) {        
      
        body = finalDoc.getActiveSection();
      
        // Append a section header paragraph.
        var header = body.appendParagraph(docrows[parentrow][yourParentTitleCol]);
        header.setHeading(DocumentApp.ParagraphHeading.HEADING2);
      
        // Get the image specified by column D ("Image")
        var rowImage = headerImagesFolder.getFilesByName(docrows[parentrow][yourParentImagePathCol].split("/")[1]).next();
        var getimageFileID = getFileByName(rowImage);
        var insertable = DriveApp.getFileById(getimageFileID.id).getBlob();      
        var finalImage = body.appendImage(insertable);
        finalImage.setHeight(finalImage.getHeight()/2.0);
        finalImage.setWidth(finalImage.getWidth()/2.0);
      
        // Append a section body paragraph from the parent/header info      
        var thisParagraph = body.appendParagraph("\r\r");
        thisParagraph.appendText(docrows[parentrow][yourParentDetailCol]);
        thisParagraph.appendText("\r\r");
        thisParagraph.setAttributes(parentParagraphStyle);
      
        // now let's run through the possible children of each parent row
        fragments.activate();
        fragmentRows = fragments.getDisplayValues();
      
        // this is a brute force loop - super wasteful - comparing the parent ID to the child ID
        // because we didn't have time to learn the sheets query subsystem
        for (var row = 0; row < fragmentRows.length; ++row) {

            if (docrows[parentrow][yourParentKeyCol] == fragmentRows[row][yourChildParentKeyCol]) {
              
              var fragmentTitle = body.appendParagraph(fragmentRows[row][yourChildTitleCol]);
              fragmentTitle.setHeading(DocumentApp.ParagraphHeading.HEADING4);
              fragmentTitle.setAttributes(fragmentHeaderStyle);
              
              var fragmentBody = body.appendParagraph(fragmentRows[row][yourChildDetailCol]);
              fragmentBody.appendText("\r\r");
              fragmentBody.setAttributes(fragmentParagraphStyle);
              
              // this next line says, "get image column from appsheet data, parse away the "_Document_Files" folder
              // so that we have just a filename to search with, then call getFilesByName with this filename,
              // this returns a drive ID so that we can insert the image as a blob into our final Google Doc
              var rowImage = fragmentsImagesFolder.getFilesByName(fragmentRows[row][yourChildImagePathCol].split("/")[1]).next();
              var getimageFileID = getFileByName(rowImage);
              var insertable = DriveApp.getFileById(getimageFileID.id).getBlob();
              // interestingly we need to append the image into the paragraph fragment, not the overall body
              // else it does not get indented correctly.
              
              var imageParagraph = body.appendParagraph("");
              var finalImage = imageParagraph.appendInlineImage(insertable);
              imageParagraph.setAttributes(fragmentImageStyle);
              
              // we also decided to cut the size of any child image to 50% of original
              finalImage.setHeight(finalImage.getHeight()/2.0);
              finalImage.setWidth(finalImage.getWidth()/2.0);
            }
        }
        // final spaces, spread things out a bit
        finalSpaces = body.appendParagraph("\r\r\r");     
    }   
}




// helper function to copy headers and footers from a template

function addHeaderFooterFromTemplate(document){

  var body = document.getBody();
 
  var headerSection = document.addHeader();
  var footerSection = document.addFooter();
 
  var headerFooterTemplateDoc = DocumentApp.openById(templateForHeadersAndFooters);
  var headerTemplate = headerFooterTemplateDoc.getHeader();
  var footerTemplate = headerFooterTemplateDoc.getFooter();
 
  var headerParagraphs = headerTemplate.getParagraphs();
  var footerParagraphs = footerTemplate.getParagraphs();
     
  var bodyParagraphsAttributes = headerFooterTemplateDoc.getBody().getAttributes();
  var bodyParagraphs = headerFooterTemplateDoc.getBody().getParagraphs();
  
  body.setMarginTop(headerFooterTemplateDoc.getBody().getMarginTop());
  body.setMarginBottom(headerFooterTemplateDoc.getBody().getMarginBottom());
  body.setMarginLeft(headerFooterTemplateDoc.getBody().getMarginLeft());
  body.setMarginRight(headerFooterTemplateDoc.getBody().getMarginRight());
  
  body.setAttributes(bodyParagraphsAttributes);
  
  
  for (i = 0; i < headerParagraphs.length; ++i) {
  headerSection.appendParagraph(headerParagraphs[i].copy());
  headerSection.setAttributes(pageHeaderStyle);
  }
  for (i = 0; i < footerParagraphs.length; ++i) {
  footerSection.appendParagraph(footerParagraphs[i].copy());
  footerSection.setAttributes(footerParagraphs[i].getAttributes());    
  }
  for (i = 0; i < bodyParagraphs.length; ++i) {
  body.appendParagraph(bodyParagraphs[i].copy());  
  }
  
  return document;
}




// helper function called above, you should not have to change this
// finding ID's by filename can result in more than one file found, 
// this function tries to deal with that
function getFileByName(fileName, fileInFolder){
 
  var filecount = 0;
  var dupFileArray = [];
  var folderID = "";
  
  var files = DriveApp.getFilesByName(fileName);
  
  while(files.hasNext()){
    var file = files.next();
    dupFileArray.push(file.getId());
    
    filecount++;
  };
  
  if(filecount > 1){
    if(typeof fileInFolder === 'undefined'){
        folderID = {"id":false,"error":"More than one file with name: "+fileName+". \nTry adding the file's folder name as a reference in Argument 2 of this function."}
    
    }else{
     //iterate through list of files with the same name
     for(fl = 0; fl < dupFileArray.length; fl++){
       var activeFile = DriveApp.getFileById(dupFileArray[fl]);
       var folders = activeFile.getParents();
       var folder = ""
       var foldercount = 0;
       
       //Get the folder name for each file
       while(folders.hasNext()){
         folder = folders.next().getName(); 
         foldercount++;
       };
       
       if(folder === fileInFolder && foldercount > 1){
         folderID = {"id":false,"error":"There is more than one parent folder: "+fileInFolder+" for file "+fileName}
       };
       
       if(folder === fileInFolder){
           folderID = {"id":dupFileArray[fl],"error":false};
           
       }else{
         folderID = {"id":false,"error":"There are multiple files named: "+fileName+". \nBut none of them are in folder, "+fileInFolder}
       };
     };
   };
   
  }else if(filecount === 0){
      folderID = {"id":false,"error":"No file in your drive exists with name: "+fileName};
      
  }else{ //IF there is only 1 file with fileName
    folderID = {"id":dupFileArray[0],"error":false};
    };
 
  return folderID;
};