Invoice creation in Google Docs: How to create line-items via Zapier / Integromat?

I built an invoice generation system a while back using mostly free tools (it uses a few scripts, so a Pro account is required for that) that has worked well enough for my purposes. All I have to do is create new line items (done using a script that creates and links records across multiple tables) and record incoming payments (I’ve been meaning to make a script for this process as well, but haven’t done so yet). The creation and emailing of invoices and overdue notices is all scripted/automated.

My system uses an Airtable automation that triggers at a certain time each week on any new invoice that has passed a certain balance threshold. That automation calls a webhook-triggered Integromat scenario (the only active scenario I use at the moment, so I can get by with Integromat’s free account) to take HTML data from my invoice record (collected from a LOT of formula fields across multiple tables), send it to CloudConvert (free account) which converts it to a PDF file, and then put the resulting PDF file back into my Airtable invoice record. Other automations take care of emailing the PDF to the client once it’s in place.

The most cumbersome part of this process was making all of the formula fields to build the HTML for the invoice. I’ve considered switching to Google Docs and using their template system (also accessible via Integromat) and ran some really brief tests to confirm that the idea would work, but I haven’t taken the plunge to make the switch yet.

Sounds good. Can you share more about how those scripts work exactly w.r.t. line items? Are you using Integromat’s line items feature?

No. As I said above, I’m building everything—including line items—as HTML in Airtable. Each line item in my base is a record in a [Line Items] table. As part of that record, a formula field builds an HTML string for a table row that contains all of the relevant line item info. In my case, that formula is:

"<tr><td>" & Name & "</td><td>$" & Rate & {Type Abbr.} & "</td><td>" & Quantity & "</td><td style=\\"text-align:right\\">" & {Total Display} & "</td></tr>"

Each line item links to an invoice from the [Invoices] table. The invoice record rolls up those HTML formula fields for the table rows and inserts them into the master formula, which contains HTML for the rest of the document, including the main table definition for the line items table. The string from that master formula is what’s passed via webhook to Integromat, which in turn passes it to CloudConvert to turn it into a PDF file.

I’ve done something similar, except that my script calls CloudConvert directly instead of going through Integromat. I’ve also done a script (and a Marketplace app) that uses PDFshift.

I think @openside’s On2Air has an integration that uses Google Docs with line items.

I’ve considered going directly to CloudConvert, but I haven’t yet taken the time to figure out how that works. It’s a multi-step process if I read their docs correctly (and as of now I’ve only briefly skimmed them): create a job containing several tasks (import file, convert file, export file), wait for the job to finish, then retrieve the file. Is that correct? Have you run into any issues with the full job not completing within Airtable’s script action time limit?

Yes, that’s the process. All of the documents that I’ve done have completed well within the time limit. They’ve all been fairly lightweight documents—a few pages of text and a handful of images.

No. As I said above, I’m building everything—including line items—as HTML in Airtable. Each line item in my base is a record in a [Line Items] table. As part of that record, a formula field builds an HTML string for a table row that contains all of the relevant line item info. In my case, that formula is:

"<tr><td>" & Name & "</td><td>$" & Rate & {Type Abbr.} & "</td><td>" & Quantity & "</td><td style=\\"text-align:right\\">" & {Total Display} & "</td></tr>"

Each line item links to an invoice from the [Invoices] table. The invoice record rolls up those HTML formula fields for the table rows and inserts them into the master formula, which contains HTML for the rest of the document, including the main table definition for the line items table. The string from that master formula is what’s passed via webhook to Integromat, which in turn passes it to CloudConvert to turn it into a PDF file.

Thanks a lot, I’ll try this!

No. As I said above, I’m building everything—including line items—as HTML in Airtable. Each line item in my base is a record in a [Line Items] table. As part of that record, a formula field builds an HTML string for a table row that contains all of the relevant line item info. In my case, that formula is:

"<tr><td>" & Name & "</td><td>$" & Rate & {Type Abbr.} & "</td><td>" & Quantity & "</td><td style=\\"text-align:right\\">" & {Total Display} & "</td></tr>"

Each line item links to an invoice from the [Invoices] table. The invoice record rolls up those HTML formula fields for the table rows and inserts them into the master formula, which contains HTML for the rest of the document, including the main table definition for the line items table. The string from that master formula is what’s passed via webhook to Integromat, which in turn passes it to CloudConvert to turn it into a PDF file.

I also have another slight variation. My line item records have a formula field that creates JSON. Then my rollup formula converts the JSON to HTML. This makes it easier to make changes to the HTML at the parent record level instead of switching back and forth between tables when generating HTML. It puts a bit more processing in Airtable’s side, but it makes the child record a bit cleaner.

This method was particularly useful for the following situations:

• a base for managing co-op classes, students, teachers, and families. Student information needed to be in reports for both classes and families. Classes information needed to be rolled up for both students and teacher reports. Teaching information needed to be rolled up for both families and classes. (Each class had 2-4 teachers assigned, each teacher taught or assisted in 1-3 classes, and each teacher was a parent of kids in classes.) Having each record generate its own JSON to be used elsewhere, and it’s own HTML kept things straight.

• In another base the report needed line item information needed to be in multiple sections of the report, with slightly different (but overlapping) fields based on different conditions. Rolling up JSON info let me have a single formula field in the child record, rather than a new formula field in the child record for every single section.

I found having child records as JSON so useful that I added a formula for creating JSON data to the Ready Made Formulas app.