Factură dintr-un rând

Cum arată foaia

Instalare, cam 5 minute

1. Fă o copie a foii și redenumește-o Invoices, sau folosește foaia activă.
2. Păstrează antetul: Client, Email, Invoice, Description, Quantity, Unit price.
3. În Apps Script, lipește codul, salvează, și autorizează Docs, Drive și Gmail.
4. Opțional: pune id-ul unui Google Doc în Config!B2. Doc-ul poate folosi {{Client}} și {{Total}}.
5. Selectează un rând și rulează Invoices > Send PDF for the selected row. Durează câteva minute la prima autorizare.

/**
 * Invoice row -> PDF -> email.
 * Mirrors the total rounding in src/lib/freeTemplates/invoice.ts.
 * Leave Config!B2 blank to build a simple Docs invoice.
 * Or paste a Google Doc id there. The doc can use {{Client}}, {{Email}},
 * {{Invoice}}, {{Description}}, {{Quantity}}, {{Unit price}} and {{Total}}.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Invoices')
    .addItem('Send PDF for the selected row', 'sendInvoiceForActiveRow')
    .addToUi();
}

function roundMoney_(value) {
  if (!isFinite(value)) return 0;
  var negative = value < 0;
  var parts = Math.abs(value).toFixed(8).split('.');
  var digits = (parts[1] + '000').slice(0, 3);
  var cents = 100 * Number(parts[0]) + Number(digits.slice(0, 2));
  if (Number(digits[2]) >= 5) cents += 1;
  var rounded = cents / 100;
  return negative ? -rounded : rounded;
}

function headerMap_(sheet) {
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var map = {};
  for (var i = 0; i < headers.length; i++) map[String(headers[i]).trim()] = i;
  return { headers: headers, map: map };
}

function cell_(values, map, name) {
  var index = map[name];
  if (index === undefined) throw new Error('Missing column: ' + name);
  return values[index];
}

function templateId_() {
  var ss = SpreadsheetApp.getActive();
  var config = ss.getSheetByName('Config');
  if (!config) {
    config = ss.insertSheet('Config');
    config.getRange('A1:B2').setValues([['Key', 'Value'], ['DOC_TEMPLATE_ID', '']]);
  }
  return String(config.getRange('B2').getValue() || '').trim();
}

function escapeRe_(text) {
  return String(text).replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
}

function replaceToken_(body, key, value) {
  var safe = String(value).replace(/\$/g, '$$');
  body.replaceText('\\{\\{' + escapeRe_(key) + '\\}\\}', safe);
}

function fillCopy_(templateId, data) {
  var copy = DriveApp.getFileById(templateId).makeCopy('Invoice ' + data.invoice);
  var doc = DocumentApp.openById(copy.getId());
  var body = doc.getBody();
  Object.keys(data).forEach(function (key) {
    replaceToken_(body, key, data[key]);
  });
  doc.saveAndClose();
  return copy.getId();
}

function buildPlainDoc_(data) {
  var doc = DocumentApp.create('Invoice ' + data.invoice);
  var body = doc.getBody();
  body.appendParagraph('Invoice ' + data.invoice).setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph('Client: ' + data.client);
  body.appendParagraph('Email: ' + data.email);
  body.appendParagraph(data.description);
  body.appendParagraph('Quantity: ' + data.quantity);
  body.appendParagraph('Unit price: ' + data.unitPrice);
  body.appendParagraph('Total: ' + data.total);
  doc.saveAndClose();
  return doc.getId();
}

function sendInvoiceForActiveRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveCell().getRow();
  if (row < 2) throw new Error('Select a data row, not the header.');
  var looked = headerMap_(sheet);
  var values = sheet.getRange(row, 1, 1, looked.headers.length).getValues()[0];
  var quantity = Number(cell_(values, looked.map, 'Quantity'));
  var unitPrice = Number(cell_(values, looked.map, 'Unit price'));
  if (!isFinite(quantity) || !isFinite(unitPrice) || quantity < 0 || unitPrice < 0) {
    throw new Error('Quantity and Unit price must be zero or more.');
  }
  var data = {
    Client: String(cell_(values, looked.map, 'Client')).trim(),
    Email: String(cell_(values, looked.map, 'Email')).trim(),
    Invoice: String(cell_(values, looked.map, 'Invoice')).trim(),
    Description: String(cell_(values, looked.map, 'Description') || ''),
    Quantity: String(quantity),
    'Unit price': String(unitPrice),
    Total: String(roundMoney_(quantity * unitPrice)),
    client: '',
    email: '',
    invoice: '',
    description: '',
    quantity: '',
    unitPrice: '',
    total: ''
  };
  data.client = data.Client;
  data.email = data.Email;
  data.invoice = data.Invoice;
  data.description = data.Description;
  data.quantity = data.Quantity;
  data.unitPrice = data['Unit price'];
  data.total = data.Total;
  if (!data.Client) throw new Error('Client is empty.');
  if (data.Email.indexOf('@') < 1 || data.Email.indexOf('.') < 0) throw new Error('Email is not usable.');
  if (!data.Invoice) throw new Error('Invoice is empty.');
  var templateId = templateId_();
  var docId = templateId ? fillCopy_(templateId, data) : buildPlainDoc_(data);
  var pdf = DriveApp.getFileById(docId).getAs('application/pdf');
  pdf.setName('Invoice-' + data.Invoice + '.pdf');
  MailApp.sendEmail({
    to: data.Email,
    subject: 'Invoice ' + data.Invoice,
    body: 'Hello ' + data.Client + ',\n\nYour invoice ' + data.Invoice + ' is attached. Total: ' + data.Total + '.\n',
    attachments: [pdf]
  });
  DriveApp.getFileById(docId).setTrashed(true);
}