fill_weight_adjust_doc.gs

/* =========== Setup Menu ======================= */
/**
 * Create a Menu when the script loads. Adds a new csvconfig sheet if
 * one doesn't exist.
 */
function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('DBW Menu')
    .addItem('Weight Adjustment Report', 'createReport')
    .addToUi();
}

function createReport() {
  // createDocFromSheet(0,  20,  1);
  // createDocFromSheet(20, 40,  2);
  createDocFromSheet(40, 60,  3);
  createDocFromSheet(60, 80,  4);
  createDocFromSheet(80, 100, 5);
}

function createDocFromSheet(vStart, vEnd, vPart){
  var templateDocID = "xxxxxxxxxxxxxxxxxxxxxxxxx"; // get template file id - Weighbridge Adjustment
  var WEIGHT_DATA = "Sheet5"; // name of sheet with grower data
  var START_ROW   = 2; // The row on which the data in the spreadsheet starts
  var START_COL   = 1; // The column on which the data in the spreadsheet starts
  var GROWER_DATA = "Grower Payments"; // name of sheet with grower data - adjustments
  var START_ROW2  = 4; // The row on which the data in the spreadsheet starts
  var START_COL2  = 1; // The column on which the data in the spreadsheet starts
  var DOC_PREFIX  = "Weighbridge Adjustment - "; // prefix for name of document to be loaded with grower adjustment data

  // get the data for the report
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  var sheet = ss.getSheetByName(WEIGHT_DATA);
  var data  = sheet.getRange(START_ROW, START_COL, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
  var sheet = ss.getSheetByName(GROWER_DATA);
  var data2 = sheet.getRange(START_ROW2, START_COL2, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();

  // Get the body and components of the template document
  var bodyCopy = DocumentApp.openById(templateDocID).getBody();

  var paras = bodyCopy.getParagraphs();
  var para
  for (var i = 0; i < paras.length; i++) {
    para = paras[i];
    if (i==0) {var h1Para  = para.copy();}
    if (i==1) {var h2Para  = para.copy();}
    if (i==3) {var h3Para  = para.copy();}
    Logger.log(para.getText());
  }

  var tables = bodyCopy.getTables();
  var table, numRows, row, numCells, cell;
  for (var i = 0; i < tables.length; i++) {
    table = tables[i];
    numRows = table.getNumRows();
    for (var j = 0; j < numRows; j++) {
      row = table.getRow(j);
      numCells = row.getNumCells();
      if (j==0) {var h1Row  = row.copy();}
      if (j==1) {var h2Row  = row.copy();}
      if (j==2) {var newRow = row.copy();}
      for (var k = 0; k < numCells; k++) {
        cell = row.getCell(k);
        Logger.log(cell.getText());
      }
    }
  }

  // create new document
  var adviceNbr = "-" + vPart + "-" + Utilities.formatDate(new Date(), tz, "yyyy/MM/dd"); // get date
  var doc = DocumentApp.create(DOC_PREFIX+adviceNbr);
  var body = doc.getBody();
  var newBody = bodyCopy.copy();
  body.setMarginLeft(43);
  body.setMarginRight(43);
  body.setMarginBottom(43);

  // for each grower fill in the template with the data 
  for (var i in data2){
    if ( i >= vStart && i < vEnd ) {
    // Put in a page break between each grower, but only after the first one
    if( i > vStart) {
      body.appendPageBreak();
    }
    // append template to new document
    body.appendParagraph(h1Para.copy());
    body.replaceText("<Code>", data2[i][0]);
    body.appendParagraph(h2Para.copy());
    body.replaceText("<Name>", data2[i][2]);
    body.appendParagraph("");
    body.appendParagraph(h3Para.copy());
    body.replaceText("<adjustment>", Utilities.formatString( '$%.2f',data2[i][1]) );
    body.appendParagraph("");

    addTableInDocument2(body, data, data2[i][0], h1Row, h2Row, newRow, h1Para);

    // remove first blank line / paragraph
    if( i == vStart) {
      body.getChild(0).removeFromParent();
    }
  }
  }
  doc.saveAndClose();
  ss.toast("Grower Adjustments Report " + vPart + " Completed");
}


// http://www.googleappsscript.org/home/create-table-in-google-document-using-apps-script

function addTableInDocument2(docBody, dataTable, grower, h1Row, h2Row, newRow, h1Para) {
  //define header cell style
  var headerStyle = {};
  headerStyle[DocumentApp.Attribute.BACKGROUND_COLOR] = '#d9d9d9';
  headerStyle[DocumentApp.Attribute.BOLD] = true;
  headerStyle[DocumentApp.Attribute.FONT_SIZE] = 10;
  
  //Style for the cells other than header row
  var cellStyle = {};
  cellStyle[DocumentApp.Attribute.BOLD] = false;
  cellStyle[DocumentApp.Attribute.FOREGROUND_COLOR] = '#000000';
  cellStyle[DocumentApp.Attribute.FONT_SIZE] = 10;

  // paragraph style
  var paraStyle = {};
  paraStyle[DocumentApp.Attribute.SPACING_AFTER] = 0;
  paraStyle[DocumentApp.Attribute.LINE_SPACING] = 1;
  
  // Centre the table
  var tstyle = {};
  tstyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] =
     DocumentApp.HorizontalAlignment.CENTER;
  
  var pgnum = 1;
  var j = -1;
  var numRows = 25;
  // Load schedule
  for (var i in dataTable){
    if(dataTable[i][0] == grower) {
      j++;
      // set page breaks and new page details
      if(j==0 || (j==numRows) || (j>numRows && j/numRows==parseInt(j/numRows))){
        if( (j==numRows) || (j>numRows && j/numRows==parseInt(j/numRows))){
          docBody.appendPageBreak()
          docBody.appendParagraph(h1Para.copy());
          docBody.replaceText("<Code>", (grower + " Cont."));
          pgnum++;
          j = 27;
          numRows = 27;
        }
        //Add a table in document
        var table = docBody.appendTable();
        table.setBorderColor("#cccccc");
        // Put header row
        var rowCopy = h1Row.copy();
        table.appendTableRow(rowCopy);
        rowCopy = h2Row.copy();
        table.appendTableRow(rowCopy);
      }
      // load data
      var dRow = dataTable[i];
      rowCopy = newRow.copy();
      table.appendTableRow(rowCopy);
      docBody.replaceText("<dck>",    dRow[1] );
      docBody.replaceText("<OGross>", Utilities.formatString('%6.2f', dRow[3] ) );
      docBody.replaceText("<OTare>",  Utilities.formatString('%6.2f', dRow[4] ) );
      docBody.replaceText("<ONett>",  Utilities.formatString('%6.2f', dRow[5] ) );
      docBody.replaceText("<AGross>", Utilities.formatString('%6.2f', dRow[13]) );
      docBody.replaceText("<ATare>",  Utilities.formatString('%6.2f', dRow[15]) );
      docBody.replaceText("<ANett>",  Utilities.formatString('%6.2f', dRow[17]) );
      docBody.replaceText("<DGross>", Utilities.formatString('%6.2f', dRow[12]) );
      docBody.replaceText("<DTare>",  Utilities.formatString('%6.2f', dRow[14]) );
      docBody.replaceText("<DNett>",  Utilities.formatString('%6.2f', dRow[16]) );
    }
  }
  // add page break for duplexing if required
  if( (pgnum % 2) ){
    pgnum++;
    docBody.appendPageBreak();
  } 
}

String.prototype.splitCSV = function(sep) {
  for (var foo = this.split(sep = sep || ","), x = foo.length - 1, tl; x >= 0; x--) {
    if (foo[x].replace(/"\s+$/, '"').charAt(foo[x].length - 1) == '"') {
      if ((tl = foo[x].replace(/^\s+"/, '"')).length > 1 && tl.charAt(0) == '"') {
        foo[x] = foo[x].replace(/^\s*"|"\s*$/g, '').replace(/""/g, '"');
      } else if (x) {
        foo.splice(x - 1, 2, [foo[x - 1], foo[x]].join(sep));
      } else foo = foo.shift().split(sep).concat(foo);
    } else foo[x].replace(/""/g, '"');
  } return foo;
};

function ExcelDateToJSDate(serial) {
   var utc_days  = Math.floor(serial - 25569);
   var utc_value = utc_days * 86400;                                        
   var date_info = new Date(utc_value * 1000);

   var fractional_day = serial - Math.floor(serial) + 0.0000001;

   var total_seconds = Math.floor(86400 * fractional_day);

   var seconds = total_seconds % 60;

   total_seconds -= seconds;

   var hours = Math.floor(total_seconds / (60 * 60));
   var minutes = Math.floor(total_seconds / 60) % 60;

   return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
}

function getDocProperties () {
  var templateDocID = "1EBhOal4ziPRQ3f-XSsKPyZrK61maLDkFHi8geLGix6U"; // get template file id - Weighbridge Adjustment
  var body = DocumentApp.openById(templateDocID).getBody();
  var tables = body.getParagraphs();
  
  var tables = body.getTables();

  var table, numRows, row, numCells, cell;
  for (var i = 0; i < tables.length; i++) {
    table = tables[i];
    numRows = table.getNumRows();
    for (var j = 0; j < numRows; j++) {
      row = table.getRow(j);
      numCells = row.getNumCells();
      if (j==0) {var h1Row  = row.copy();}
      if (j==1) {var h2Row  = row.copy();}
      if (j==2) {var newRow = row.copy();}
      for (var k = 0; k < numCells; k++) {
        cell = row.getCell(k);
        Logger.log(cell.getText());
      }
    }
    var tbl = table;
  }
  var rowCopy = newRow.copy();
  tbl.appendTableRow(rowCopy);
  rowCopy = newRow.copy();
  tbl.appendTableRow(rowCopy);
  rowCopy = newRow.copy();
  tbl.appendTableRow(rowCopy);
  // if(q==5||(q>6&&q/6==parseInt(q/6))){doc.appendPageBreak()}
}



function restripe() {
  var tables = DocumentApp.getActiveDocument()
    .getBody()
    .getTables();

  var colors = ['#ffffff', '#f0f0f0'];

  var table, numRows, row, numCells, cell, bgCol;
  for (var i = 0; i < tables.length; i++) {
    table = tables[i];
    numRows = table.getNumRows();
    for (var j = 0; j < numRows; j++) {
      row = table.getRow(j);
      bgCol = colors[j % colors.length];
      numCells = row.getNumCells();
      for (var k = 0; k < numCells; k++) {
        cell = row.getCell(k);
        cell.setBackgroundColor(bgCol);
      }
    }
  }
}