Airtable Invoice Automation

Introduction

Integrating Airtable with CustomJS provides a robust solution for automating your invoicing processes within a complete accounting system. This guide will walk you through setting up your Airtable base, linking it to CustomJS, and automating PDF invoice generation.

Setting Up Your Airtable Base

To begin, create an Airtable base tailored for accounting purposes. Here's how you can structure your base to manage invoices effectively. You can also take a look at this example in airtable in a public example: Airtable Example

Tables: Clients, Invoices, and Invoice-Items

Fields:

• -  Clients: Id, Company Name, Default Hourly Rate, Street, Street No., Postal Code, City, Tax
• -  Invoices: Id, Client (linked to Clients table), Item (linked to Invoice-Item table), Date Issued, Status
• -  Item: Id, Description, Hours, Custom Hourly Rate

Simply add a new column with the field "Button" to your table. You can then enter the URL of the CustomJS function in the URL formula field provided. You also have the option of transferring dynamic values with GET parameters to CustomJS, as shown in the following examples.

CONCATENATE("https://e.customjs.io/edBrebXR?recordId=",RECORD_ID())

CONCATENATE("https://e.customjs.io/OQnaUeeT?invoiceDate=",Invoicedate,"&invoiceNumber=",ID,"&clientName=",{Client Name},"&clientAddress1=",ClientAddressField1,"&clientAddress2=",ClientAddressField2,"&clientTax=",ClientTax,"&descriptions=",ARRAYJOIN({Description (from Invoice-Items)},','),"&prices=",ARRAYJOIN({Prices (from Invoice-Items)},','))

Code Example

These parameters can now be defined as dynamic parameters in CustomJS and then used in the code via the "input" variable. You can simply use this example as a template when creating a new function. The HTML template variable and the dynamic input fields are then automatically created for you.

const { HTML2PDF } = require("./utils");
const nunjucks = require("nunjucks");

console.log(generateInvoiceItems(input.prices, input.descriptions));

const content = nunjucks.renderString(variables["HTML Template"], {
  currency: "€",
  items: generateInvoiceItems(input.prices, input.descriptions),
  taxRate: 19,
  invoiceNumber: input.invoiceNumber,
  createdDate: showDate(input.invoiceDate),
  dueDate: calcDueDate(input.invoiceDate),
  companyLogo: "https://beta.customjs.space/customJS-logo.png",
  sender: {
    name: "Technology Circle GmbH",
    address1: "Karolinenstraße 24 Hause",
    address2: "20357 Hamburg",
  },
  receiver: {
    name: input.clientName,
    address1: input.clientAddress1,
    address2: input.clientAddress2,
    tax: input.clientTax,
  },
  footerText: "2024© TechnologyCircle",
});

return await HTML2PDF(content);

/////////

function generateInvoiceItems(prices, descriptions){
  descriptions = descriptions.split(',');
  
  return prices.split(',').map((p,i) => {
    return {
      price:Number(p), 
      description: descriptions[i]
    };
  });
}

function showDate(date) {
  const nowDate = new Date(date);
  
  return `${nowDate.getFullYear()}-${
    padWithLeadingZero(nowDate.getMonth() + 1)
  }-${padWithLeadingZero(nowDate.getDate())}`;
}

function calcDueDate(date) {
  const invoicingDate = new Date(date);
  const futureDate = new Date(
    invoicingDate.getTime() + 14 * 24 * 60 * 60 * 1000
  );
  
  return `${futureDate.getFullYear()}-${
    padWithLeadingZero(futureDate.getMonth() + 1)
  }-${padWithLeadingZero(futureDate.getDate())}`;
}

function padWithLeadingZero(number) {
    return number.toString().padStart(2, '0');
}