Invoice-Generator

function insertTables() {
  
  var doc = DocumentApp.openById("YOUR_DOCUMENT_ID");
  var body = doc.getBody();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Invoice_Data");
  
  var nrows = sheet.getRange("H2").getValue();
  
  var dataAll = sheet.getRange(2, 1, nrows, 5).getDisplayValues();

  var headerRow = ["Art", "Date", "Details", "Time h", "Cost €"];
  
  var data = [];
  
  var subTotalX = sheet.getRange("H6").getDisplayValue();
  var subTotalY = sheet.getRange("H7").getDisplayValue(); 
  var subTotalZ = sheet.getRange("H8").getDisplayValue();
  var taxPercent = sheet.getRange("H10").getDisplayValue();
  var taxValue = sheet.getRange("H11").getDisplayValue();
  var total = sheet.getRange("H13").getDisplayValue();
  

  var finalTotalRow = [["", "", "", "", ""], 
                       ["SubTotal", "net", "", "", subTotalZ],
                       ["Tax Deduction", taxPercent, "", "", taxValue], 
                       ["Total", "", "", "", total]];
  
   if(nrows <= 15) {
      
      var dataZ = data.concat([headerRow]);
      dataZ = dataZ.concat(dataAll);
      dataZ = dataZ.concat(finalTotalRow);
      
      var table1 = body.insertTable(2, dataZ);
      formatTable(table1);

    }
    
    if(nrows > 15 && nrows <= 40) {
            
      var dataY = data.concat([headerRow]);
      dataY = dataY.concat(dataAll.slice(0, 14));
      dataY = dataY.concat([["Sub Total", "", "", "", subTotalY]]);
      
      //var dataZ = data.concat([["Invoice:{{invoiceno}}","","","","{{date}}"]]);
      var dataZ = data.concat([headerRow]);
      dataZ = dataZ.concat([["Transfer", "", "", "", subTotalY]]);
      dataZ = dataZ.concat(dataAll.slice(15, nrows - 1));
      dataZ = dataZ.concat(finalTotalRow);
            
      var table1 = body.insertTable(2, dataY);
      formatTable(table1, "first");
      
      body.insertPageBreak(3);
           
      body.insertParagraph(4, "Invoice:{{invoiceno}}\t\t\t\t\t\t\t\t\t   {{date}}");
//      var tableA = body.insertTable(4, [["bling","bling"]]);
//      formatTableA(tableA);
      
      var table2 = body.insertTable(5, dataZ);
      formatTable(table2, "next");
      
    }
    
    if(nrows > 40 && nrows <= 65) {
      
      var dataX = data.concat([headerRow]);
      dataX = dataX.concat(dataAll.slice(0, 14));
      dataX = dataX.concat([["Sub Total", "", "", "", subTotalX]]);
      
      var dataY = data.concat([["Invoice:{{invoiceno}}","","","","{{date}}"]]);
      dataY = dataY.concat([headerRow]);
      dataY = dataY.concat([["Transfer", "", "", "", subTotalX]]);
      dataY = dataY.concat(dataAll.slice(15, 39));
      dataY = dataY.concat([["Total", "", "", "", subTotalY]]);
      
      var dataZ = data.concat([["Invoice:{{invoiceno}}","","","","{{date}}"]]);
      dataZ = dataZ.concat([headerRow]);
      dataZ = dataZ.concat([["Transfer", "", "", "", subTotalY]]);
      dataZ = dataZ.concat(dataAll.slice(40, nrows - 1));
      dataZ = dataZ.concat(finalTotalRow);
            
      var table1 = body.insertTable(2, dataX);
      formatTable(table1, "first");
      
      body.insertPageBreak(3);
      
      body.insertParagraph(4, "Invoice:{{invoiceno}}\t\t\t\t\t\t\t\t\t   {{date}}");
      
      var table2 = body.insertTable(5, dataY);
      formatTable(table2, "next");
      
      body.insertPageBreak(6);
            
      var table3 = body.insertTable(7, dataZ);
      formatTable(table3, "next");
      
  }
  
}


function formatTable(table, position){
    
    _setColumnWidth(table);
    table.setAttributes(_formatAttrs("all"));
  
    for(var i = 0; i < table.getNumRows(); i++) {
      var row = table.getRow(i);

        for(var j = 0; j < row.getNumCells(); j++) {
          var cell = row.getCell(j);
          
          _setCellPaddingZero(cell);
          
          if(position == "first") {
            if(i == 0 || i == table.getNumRows() - 1) {
              cell.setBackgroundColor('#D9D9D9');
              cell.setAttributes(_formatAttrs("borderRow"));
            }
          }
          
          if(position == "next") {
            if(i == 1) {
              cell.setAttributes(_formatAttrs("borderRow"));
            }
            if(i == 0 || i == table.getNumRows() - 1) {
              cell.setBackgroundColor('#D9D9D9');
              cell.setAttributes(_formatAttrs("borderRow"));
            }
          }
                  
          if(j == 3 || j == 4) {
            cell.getChild(0).setAttributes(_formatAttrs("numbersCol"));
          }
        }
    }
}

function _formatAttrs(type) {
 
  var cellFormatting = {};
  
  if(type == "all") {
    cellFormatting[DocumentApp.Attribute.BORDER_WIDTH] = 0;
    cellFormatting[DocumentApp.Attribute.FONT_SIZE] = 9;
    cellFormatting[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.LEFT;
  }
  if(type == "borderRow") {
    cellFormatting[DocumentApp.Attribute.BOLD] = true; 
    cellFormatting[DocumentApp.Attribute.FOREGROUND_COLOR] = '#000000';
  }
  if(type == "numbersCol") {
    cellFormatting[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.RIGHT;
  }
  
  return cellFormatting;
}

function _setColumnWidth(table) {
  table.setColumnWidth(0, 80);
  table.setColumnWidth(1, 40);
  table.setColumnWidth(2, 250);
  table.setColumnWidth(3, 30);
  table.setColumnWidth(4, 50);
}

function _setCellPaddingZero(cell) {
  cell.setPaddingTop(2);
  cell.setPaddingBottom(2);
  cell.setPaddingLeft(2);
  cell.setPaddingRight(2);
}

//function formatTableA(table) {
//  
//  table.setColumnWidth(0,225);
//  table.setColumnWidth(1,225);
//  
//  table.setAttributes(_formatAttrs("all"))
//  
//  var cell1 = table.getRow(0).getCell(0);
//  var cell2 = table.getRow(0).getCell(1);
//  
//  _setCellPaddingZero(cell1);
//  _setCellPaddingZero(cell2);
//  
//}