Create Invoices

Docs-4

function makeInvoice() {
  //Get invoice data
  const ss = SpreadsheetApp.getActiveSpreadsheet(),
    sh = ss.getSheetByName('INVOICES'),
    invoiceTemp = DriveApp.getFileById('DOCUMENT ID'),
    fInvoices = DriveApp.getFolderById('FOLDER ID'),
    timeZone = Session.getScriptTimeZone(),
    data = sh.getDataRange().getValues();
  var invoiceNo = data[0][6];

  //Filter data by invoice selected
  data.splice(0, 2);
  var invoiceData = data.filter(function (row) {
    return row[0] === invoiceNo;
  });

  //Get company & date and format data
  var company = invoiceData[0][1],
    date = invoiceData[0][2];

  var invoiceNo = ('0000' + invoiceNo).slice(-4);
  var date = Utilities.formatDate(date, timeZone,
    'd MMMMM yyyy');

  //Get invoice info
  var invoiceRows = invoiceData.map(function (rw) {
    return [rw[3],
    rw[5],
    "$" + rw[4].toFixed(2),
    "$" + rw[6].toFixed(2)];
  });

  //Create invoice document and get body
  var newInvoice = invoiceTemp.makeCopy("Invoice #" + invoiceNo,
    fInvoices);
  var newInvoiceDoc = DocumentApp.openById(newInvoice.getId());
  var body = newInvoiceDoc.getBody();

  //Set up styles
  var s1 = {};
  s1[DocumentApp.Attribute.FONT_FAMILY] = 'Helvetica Neue';
  s1[DocumentApp.Attribute.FONT_SIZE] = 14;
  s1[DocumentApp.Attribute.FOREGROUND_COLOR] = '#FFFFFF';

  var s2 = {};
  s2[DocumentApp.Attribute.PADDING_TOP] = 15;

  var s3 = {};
  s3[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] =
    DocumentApp.HorizontalAlignment.RIGHT;

  //Add company name, date & invoice no
  var tables = body.getTables();
  var table0 = tables[0];
  var companyCell = table0.getRow(0).getCell(1);
  var dateCell = table0.getRow(1).getCell(1);
  var invoiceNoCell = table0.getRow(2).getCell(1);

  companyCell.setText(company).setAttributes(s1)
    .setAttributes(s2);
  dateCell.setText(date).setAttributes(s1);
  invoiceNoCell.setText("Invoice No: " + invoiceNo)
    .setAttributes(s1);

  //Add invoice items
  var table1 = tables[1];
  invoiceRows.forEach(function (invRow) {
    table1.getRow(1).getCell(0)
      .appendParagraph(invRow[0]);
    table1.getRow(1).getCell(1)
      .appendParagraph(invRow[1])
      .setAttributes(s3);
    table1.getRow(1).getCell(2)
      .appendParagraph(invRow[2])
      .setAttributes(s3);
    table1.getRow(1).getCell(3)
      .appendParagraph(invRow[3])
      .setAttributes(s3);
  });

  //Calculate and add grand total
  var grandTotal = invoiceData.map(function (r) {
    return r[6];
  })
    .reduce(function (runningTotal, subTotal) {
      return runningTotal + subTotal;
    });

  table1.getRow(2).getCell(3)
    .setText("$" + grandTotal.toFixed(2))
    .setAttributes(s3);
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("INVOICE")
    .addItem("Make invoice", 'makeInvoice')
    .addToUi();
}