Create invoices2

Docs-5

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

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

  //Get company & dates and format data
  var company = invoiceData[0][1],
    orderDate = invoiceData[0][2];
  var invoiceNo = ('0000' + invoiceNumb).slice(-4);

  //Format dates & calculate due date
  var todaysDate = new Date();
  var orderDate = Utilities.formatDate(orderDate,
    timeZone,
    'd MMMMM yyyy');
  var todaysDate2 = Utilities.formatDate(todaysDate,
    timeZone,
    'dd/MM/yyyy');
  var dueDateMSecs = new Date(todaysDate.getTime()
    + (30 * 86400000));
  var dueDate = Utilities.formatDate(dueDateMSecs,
    timeZone,
    'dd/MM/yyyy');

  //Get product info and format
  var productInfo = invoiceData.map(function (rw) {
    return [rw[3],
    "$" + rw[4].toFixed(2)
      .replace(/\d(?=(\d{3})+\.)/g, '$&,'),
    rw[5].toFixed(0)
      .replace(/-{0,1}(\d)(?=(\d\d\d)+$)/g, '$1,'),
    "$" + rw[6].toFixed(2)
      .replace(/\d(?=(\d{3})+\.)/g, '$&,')];
  });

  //Create invoice document & move to folder
  var newInvoice = DocumentApp.create("Invoice #" + invoiceNo);
  var newInvID = newInvoice.getId();
  var newInvoiceDoc = DriveApp.getFileById(newInvID);
  fInvoices.addFile(newInvoiceDoc);
  DriveApp.removeFile(newInvoiceDoc);

  //Open doc and set up body
  var newInvoiceDoc = DocumentApp.openById(newInvID);
  var body = newInvoiceDoc.getBody();
  body.setMarginTop(150);
  body.setMarginLeft(70);
  body.setMarginRight(70);

  //Get styles
  var style = getStyles();

  addLogo(newInvoiceDoc);
  addInvoiceDetails(body, style, company, invoiceNo, todaysDate2);
  addListOfProducts(body, orderDate, style, productInfo);
  var table = addTable(body, sh, style, productInfo);
  addGrandTotal(invoiceData, table, productInfo, style);
  addLastTextAndFooter(body, style, dueDate, newInvoiceDoc);

  //Add document link to sheet
  var indexNo = data.map(function (row) {
    return row[0];
  })
    .indexOf(invoiceNumb);
  sh.getRange(indexNo + 3, 8)
    .setFormula('=HYPERLINK("' + newInvoiceDoc.getUrl()
      + '","LINK")');
}         

//SCRIPT 2
function getStyles() {
  var style0 = {};
  style0[DocumentApp.Attribute.FONT_FAMILY] = 'Helvetica Neue';

  var style1 = {};
  style1[DocumentApp.Attribute.FONT_SIZE] = 30;
  style1[DocumentApp.Attribute.BOLD] = true;
  style1[DocumentApp.Attribute.LINE_SPACING] = 1.5;

  var style2 = {};
  style2[DocumentApp.Attribute.FONT_SIZE] = 12;
  style2[DocumentApp.Attribute.BOLD] = true;
  style2[DocumentApp.Attribute.LINE_SPACING] = 2;

  var style3 = {};
  style3[DocumentApp.Attribute.FONT_SIZE] = 12;
  style3[DocumentApp.Attribute.BOLD] = false;
  style3[DocumentApp.Attribute.LINE_SPACING] = 2;

  var style4 = {};
  style4[DocumentApp.Attribute.FONT_SIZE] = 12;
  style4[DocumentApp.Attribute.BOLD] = false;

  var style5 = {};
  style5[DocumentApp.Attribute.FONT_SIZE] = 11;
  style5[DocumentApp.Attribute.FONT_FAMILY] = 'Comfortaa';
  style5[DocumentApp.Attribute.FOREGROUND_COLOR] = '#FFFFFF';

  var style6 = {};
  style6[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] =
    DocumentApp.HorizontalAlignment.LEFT;

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

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

  return [style0, style1, style2, style3, style4,
    style5, style6, style7, style8];
}

//SCRIPT 3
function addLogo(newInvoiceDoc) {
  //Get the logo and create the header
  const logo = DriveApp.getFileById('FILE ID').getBlob();
  newInvoiceDoc.addHeader();
  var header = newInvoiceDoc.getHeader();

  //Add the logo and position
  var hparas = header.getParagraphs();
  hparas[0].addPositionedImage(logo)
    .setTopOffset(-30)
    .setLeftOffset(-70);
}
//const logo = DriveApp.getFileById("FILE ID").getBlob();

//SCRIPT 4
function addInvoiceDetails(body, style, company,
  invoiceNo, todaysDate2) {
  //Add Invoice details
  body.setAttributes(style[0]);
  body.appendParagraph("INVOICE")
    .setAttributes(style[1]);

  var text1 = body.appendParagraph("COMPANY: ")
    .setAttributes(style[2]);
  text1.appendText(company)
    .setAttributes(style[3]);

  var text2 = body.appendParagraph("INVOICE NUMBER: ")
    .setAttributes(style[2]);
  text2.appendText("#" + invoiceNo)
    .setAttributes(style[3]);

  var text3 = body.appendParagraph("DATE: ")
    .setAttributes(style[2]);
  text3.appendText(todaysDate2)
    .setAttributes(style[3]);

  //Add horizontal rule and empty row
  body.appendHorizontalRule();
  body.appendParagraph("");
}

//SCRIPT 5
function addListOfProducts(body, orderDate, style, productInfo) {
  //Product descriptions
  var products = ["Product A", "Product B", "Product C"];
  var prodDescs = ["Red Widget with matching case.",
    "Solar-powered blue widget",
    "100% recycled green widget"];

  //Add list & descs
  body.appendParagraph("The following products were ordered on "
    + orderDate).setAttributes(style[3]);

  productInfo.forEach(function (r) {
    var desc = prodDescs[products.indexOf(r[0])];
    body.appendListItem(r[0] + " - " + desc);
  });
  body.appendParagraph("");
}

//SCRIPT 6
function addTable(body, sh, style, productInfo) {
  //Add table and header row
  var table = body.appendTable();
  var headerRow = table.appendTableRow();
  var tableHeaders = sh.getRange(2, 4, 1, 4).getValues();

  //Add header data and format
  tableHeaders[0].forEach(function (hr) {
    var headerCell = headerRow.appendTableCell()
      .setBackgroundColor('#ea9999')
      .setAttributes(style[2])
      .setText(hr);
    headerCell.getChild(0).setAttributes(style[7]);
  });

  //Add table rows and cells
  productInfo.forEach(function (tr, r) {
    var tableRow = table.appendTableRow();
    //Alternate row colouring
    if (r % 2 === 0) {
      var color = '#efefef';
    } else {
      var color = '#ffffff';
    }

    //Add product info and style
    productInfo[r].forEach(function (tc, c) {
      var tableCell = tableRow.appendTableCell()
        .setAttributes(style[4])
        .setBackgroundColor(color)
        .setText(tc);

      //The first cell we align to the left
      //And the others to the right
      if (c === 0) {
        tableCell.getChild(0).setAttributes(style[6]);
      } else {
        tableCell.getChild(0).setAttributes(style[8]);
      }
    });
  });
  return table;
}

//SCRIPT 7
function addGrandTotal(invoiceData, table, productInfo, style) {
  //Calculate grand total
  var grandTotal = invoiceData.map(function (r) {
    return r[6];
  })
    .reduce(function (runningTotal, subTotal) {
      return runningTotal + subTotal;
    });

  //Add grand total cells
  var gtRow = table.appendTableRow();

  productInfo[0].forEach(function (tc, c) {
    var tableCell = gtRow.appendTableCell();
    tableCell.setBackgroundColor('#d9d9d9')
      .setAttributes(style[2]);

    if (c === 2) {
      tableCell.setText('Grand total: ');
      tableCell.getChild(0).setAttributes(style[8]);
    } else if (c === 3) {
      tableCell.setText("$" + grandTotal
        .toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,'));
      tableCell.getChild(0).setAttributes(style[8]);
    }
  });
}

//SCRIPT 8
function addLastTextAndFooter(body, style,
  dueDate, newInvoiceDoc) {
  //Add due date
  var text4 = body.appendParagraph("Payment is due on ")
    .setAttributes(style[3]);
  text4.appendText(dueDate).setAttributes(style[2]);
  text4.appendText(".").setAttributes(style[3]);

  //Add thank you & name
  body.appendParagraph("Thank you for your custom.")
    .setAttributes(style[3]);
  body.appendParagraph("Baz Roberts")
    .setAttributes(style[3]);
  body.appendHorizontalRule();

  //Add footer
  newInvoiceDoc.addFooter();
  var footer = newInvoiceDoc.getFooter();
  var tableF = footer.insertTable(0);
  var tableRowF = tableF.appendTableRow();
  var footerText =
    ["E: baz@bazroberts.com",
      "T: 123456789",
      "W: www.bazroberts.com"];

  footerText.forEach(function (fText, f) {
    var fCell = tableRowF.appendTableCell()
      .setBackgroundColor('#cc0000')
      .setAttributes(style[5])
      .setWidth(150)
      .setText(fText);
    fCell.getChild(0).setAttributes(style[7]);
  });
}

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