Code.gs

/**
 * Modified https://github.com/inviqa/SysAdmin/tree/master/goddamn
 * By Lars Englund <[email protected]>
 */


/**
 * GoDDaMn is inspired by  the Google Script project named 'Generate Google Docs' by Mikko Ohtamaa (http://opensourcehacker.com)
 * http://opensourcehacker.com/2013/01/21/script-for-generating-google-documents-from-google-spreadsheet-data-source/
 *
 * Generate Google Docs based on a template document and data incoming from a Google Spreadsheet
 *
 * License: MIT
 *
 * Copyright 2013 Mikko Ohtamaa, http://opensourcehacker.com
 *
 * Modified and Adapted for the Inviqa Group http://inviqa.com
 * Authored by Marco Massari Calderone <[email protected]>
 *
 * - modified script dubbed GoDDaMn or gddm.gs (Google Docs Data Marge)
 * - modified script published 
 * - Added String replacement instead of Paragraph replacement
 * - Modified the replacement string TAG format to :key:
 * - the substituted Strings are set in Bold to be 'highlighted' in the output file
 * - changed 'customer' to 'employee' because this is the use we make of it in Inviqa -
 * - if no employee_ID is embedded in the script, then the script sources it from the currently selected line of the spreadsheet
 * - The modified script is meant to be run as an embedded script in a spreadsheet and triggered at any Form submission,
 *   in such case the employee_ID is the I of the newly inserted line, and this is a default behaviour of Google Script
 * 
 * IF the script is run manually, the function to run is 'generateEmployeeDatasheet'
 */

/*

Convert to PDF: pdfDocument = DocsList.getFileById(targetId).getAs(“application/pdf”);
Send by e-mail: MailApp.sendEmail(destination, subject, message, {htmlBody: messageHTML, attachments: pdfDocument});

*/

// Row number from where to fill in the data (starts as 1 = first row)
// leave this empty to let script pick the ID from the currently selected row of the spreadsheet
var FIRST_ROW = 2;

// Google Doc id from the document template
// (Get ids from the URL)
var TARGET_TEMPLATE = "1FX1jmyr_m9NGMRdkmC7-Vh4QUpPcIjDzCoZIZ2F2p24";

// In which spreadsheet we have all the employee data
var SOURCE_SPREADSHEET = "1ZKBYbl-7MX_A_-nPfzajwDqKgGlxQ9SLHnTG6tCfdiE";

// In which Google Drive we toss the target documents
var TARGET_FOLDER = "1uvwwrycCqDl2sDXWTcU6VwN5yAnJtens";

/**
 * Return spreadsheet row content as JS array.
 *
 * Note: We assume the row ends when we encounter
 * the first empty cell. This might not be 
 * sometimes the desired behavior.
 *
 * Rows start at 1, not zero based!!!  
 *
 */
function getCurrentRow() {
  var currentRow = SpreadsheetApp.getActiveSheet().getActiveSelection().getRowIndex();
  return currentRow;
}


function getRowAsArray(sheet, row) {
  var dataRange = sheet.getRange(row, 1, 1, 10);
  var data = dataRange.getValues();
  var columns = [];
  Logger.log("got data: " + data);
  
  for (i in data) {
    var row = data[i];

    Logger.log("Got row", row);

    for(var l=0; l<10; l++) {
        var col = row[l];
        // First empty column interrupts
        if(!col) {
            break;
        }

        columns.push(col);
    }
  }

  return columns;
}

/**
 * Duplicates a Google Apps doc
 *
 * @return a new document with a given name from the orignal
 */
function createDuplicateDocument(sourceId, name) {
    var source = DriveApp.getFileById(sourceId);
    var targetFolder = DriveApp.getFolderById(TARGET_FOLDER);
    var newFile = source.makeCopy(name, targetFolder);

    /** newFile.addToFolder(targetFolder);*/
    return DocumentApp.openById(newFile.getId());
}
/**
 * Search a paragraph in the document and replaces it with the generated text 
 */
function replaceParagraph(doc, keyword, newText) {
  var ps = doc.getParagraphs();
  for(var i=0; i<ps.length; i++) {
    var p = ps[i];
    var text = p.getText();

    if(text.indexOf(keyword) >= 0) {
      p.setText(newText);
      p.setBold(false);
      
    }
  } 
}

/**
 * Search a String in the document and replaces it with the generated newString, and sets it Bold
 */
function replaceString(doc, String, newString) {

  var ps = doc.getParagraphs();
  for(var i=0; i<ps.length; i++) {
    var p = ps[i];
    var text = p.getText();
    //var text = p.editAsText();

    if(text.indexOf(String) >= 0) {
      //look if the String is present in the current paragraph
      

      //p.editAsText().setFontFamily(b, c, DocumentApp.FontFamily.COMIC_SANS_MS);
      p.editAsText().replaceText(String, newString);
      
      
      // we calculte the length of the string to modify, making sure that is trated like a string and not another ind of object.
      var newStringLength = newString.toString().length;
      
      /*// if a string has been replaced with a NON empty space, it sets the new string to Bold, 
      if (newStringLength > 0) {
        // re-populate the text variable with the updated content of the paragraph
        text = p.getText();
        p.editAsText().setBold(text.indexOf(newString), text.indexOf(newString) + newStringLength - 1, true);
      }*/
    }
  } 
}



/**
 * Script entry point
 */
function generateDocuments() {

  var data = SpreadsheetApp.openById(SOURCE_SPREADSHEET);

  if(!FIRST_ROW) {
    FIRST_ROW = getCurrentRow();
    //if the current line is the Column Headers line then ask the user to specify the ID, very rare case.
    if (FIRST_ROW == 1) {
      var first_row = Browser.inputBox("Enter row number of first row of data in the source spreadsheet", Browser.Buttons.OK_CANCEL);
    }
  }

  // Fetch variable names
  // they are column names in the spreadsheet
  var sheet = data.getSheets()[0];
  var columns = getRowAsArray(sheet, 1);

  //var Avals = sheet.getRange("A1:A").getValues();
  //var last_row_nr = Avals.filter(String).length;
  //Logger.log("last_row_nr: " + last_row_nr);
  
  Logger.log("Processing columns:" + columns);
  Logger.log("Column 0:" + columns[0]);

  var curr_row = FIRST_ROW;
  while (1) {
    //var start_row = 2;
    var sheet = data.getSheets()[0];
    var sheetData = getRowAsArray(sheet, curr_row);  
    Logger.log("Processing data:" + sheetData);
    
    if (!sheetData[0] || sheetData[0] == "") {
      Logger.log("Row " + curr_row + " was empty, stopping script");
      break;
    }
    var target = createDuplicateDocument(TARGET_TEMPLATE, sheetData[0] + " document");

    Logger.log("Created new document:" + target.getId());
    
    for(var i=0; i<columns.length; i++) {
      // TAG forma is :key:
      var key = ":" + columns[i] + ":"; 
      var text = sheetData[i] || ""; // No Javascript undefined
      replaceString(target, key, text);
      
      //var newString = key +" " + text;
      //var newParagraph = key + " " + text;
      //replaceParagraph(target, key, newParagraph);
    }
    curr_row ++;
  }
  Logger.log("All rows processed, " + FIRST_ROW + " to " + curr_row);
}