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

[Rupert_Hoffschm]

Hi there, I hope someone can help me :slightly_smiling_face: I have been using page designer to create invoices, but want to automate the process a bit more. Page designer has a built-in line-items functionality and I’m trying to build the same, but the document gets created in Google Docs. I’ve tried a lot using Zapier, but wanted to reach out to you guys for any suggestions.

I know of Documint, Formstack etc., but find these options wayyyy too expensive.

Thanks & Cheers!

[Justin_Barrett]

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.

[Rupert_Hoffschm]

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?

[Justin_Barrett]

Rupert_Hoffschmidt1:

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.

[kuovonne]

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.

[Justin_Barrett]

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?

[kuovonne]

Justin_Barrett:

It’s a multi-step process . . . Is that correct?

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.

[Justin_Barrett]

Good to know. Now I just need to do it…

[Rupert_Hoffschm]

Thanks a lot, I’ll try this!

[Rupert_Hoffschm]

Using a roll up field for the individual HTML rows is genius!