Automatic Invoicing with Zapier and Quickbooks

OFN UK has used Zapier to automatically charge users at a percentage of the sales they make through the platform in a month.

If you’ve already set up a Zapier integration you can skip steps 1 and 2. If this is your first Zapier integration go from the start.

How to do it:

  1. You need a Zapier account. To get a paid account for free you need to put the Zapier logo somewhere on your website and then get in touch with them saying you’re a not-for-profit and would like to take advantage of their free basic plan for NFPs. You’ll need to put their logo on your website somewhere… as UK have done here 1.

  2. You’ll then need to ask a sysadmin to set up a specific user on your OFN DB such that Zapier can access the OFN DB to find out when new users order. In the UK we have set up a secure user that can only access the DB from a single IP (the Zapier IP) with a super secure password (like 60 random characters). This should take a sysadmin an hour tops.

  3. This Zap has 4 steps:
    1 - Schedule - I schedule this Zap to be triggered on the first of each month at midnight.
    2 - Postgres - Run the following postgres query:
    select sum(total) as total from spree_orders where state = 'complete' and completed_at >= NOW() - interval '1 month' and distributor_id = 12345
    3 - Python - Run the following python code:
    #we can only do math on numbers (int or float in python)
    original_total = float(input['total'])
    pc = float(input['percentage']) / 100
    #now let's calculate the value!
    fee = original_total * pc
    #...and return it!
    return {'fee': round(fee, 2)}
    4 - Quickbooks - Create an invoice in Quickbooks that charges a percentage of the sales to the user account. This can be set up to automatically charge via direct debit once you have created the user in Quickbooks and they have filled in their DD details.

  4. And wallah! At the start of each month a user will automatically be invoiced and debited for the fees of the month previous directly into your bank account with no coding and fuss. And you can change the percentage and the business model as much as you like. Once this is set up for one user (takes maybe 30mins the first time) then you can copy and paste the whole zap and just adjust the percentage and user to start charging a new user. It takes about 1 minute.

  5. I haven’t investigated using other accounting packages but you can see all the packages that Zapier integrates with here. Most of them will be able to do this simple task so don’t feel tied to Quickbooks. If you want this to be automatic though, check your accounting software enables invoices to be automatically paid upon create once a DD mandate is set up.


wow, this is such a great example of getting value out of it without having to build features!

Nice! This will come in handy for Katuma!

may I ask two things? what is exactly Quickbooks, a Saas that is integrated with Zapier where I need to create an account? where is that DB user specified? don’t tell me it’s hardcoded manually in the production server :see_no_evil:

ace! thanks @lin_d_hop! Have you had any attempt to do line items? I suppose getting into ofn orders and fees and stuff is likely to get complicated

@Kirsten Note this is for OFN instances to invoice their users. No line items required there.
The query and process is different for integrating users and their customers. While technically, in that very different other use case, this should be just about possible, I am not doing it and will wait for the API endpoint.


OFN has a Zapier DB users. Zapier stores the creds for the SAAS accounts… that’s kinda the whole point of it - interfacing between apps.

Quickbooks is a SAAS accounting package. You can see the range of accounting packages that you can integrate with using Zapier here.

1 Like

Cool! we will need to create that role as I’d like to try getting our users into Mailchimp so that we can start mailing campaigns. I think it’d benefit the entire OFN.

totally agree - I’ve been helping users set up mailchimp ‘manually’ - ie: downloading csvs of customer lists and uploading them to mailchimp periodically (mailchimp deals with duplicates…) and then creating mailchimp sign up lists in the shopfront description - but if there is a way to further simplify this - hurray!!! Mailchimp offers great marketing tools if we can take advantage of them (ie: sales of particular shop products, coupon programs…)

Nice @tschumilas! That’s good to hear. We might reach out to you as soon as we work on it :wink: