Hi - We maintain a google sheet with all the data required to produce customer invoices. We do not need triggers etc. i.e. we can trigger this manually once a month. we generate about 20 invoices each month. Each customer has multiple line items in the sheet that then goes and becomes a line item in the invoice. one invoice, multiple line items. Some line items can be negative due to credit or discount. the columns are Month of invoice, Item Name, item description, quantity, rate, total. Google Sheets and Quickbooks online. I have to believe that this is something that has been solved relatively simply somewhere but I havent found a step by step solution for this that just works. Any pointers would be appreciated.
Question
Create Quickbooks online invoices from Google Sheets
+11- Community Manager
Hi and welcome to the Community,
What’s the reasoning behind the monthly trigger for this? Asking as I wonder if it might work better to create a “Status” column in the Spreadsheet to help indicate whether the Zap should trigger for the customer order. Then have a Zap with the following trigger and actions:
- Trigger: New or Updated Spreadsheet Row (Google Sheets) - runs when a new row is added or the value in the “Status” column is updated.
- Action: Only Continue If... (Filter by Zapier) - set to only allow the Zap to continue if the “Status” column contains the value “Ready to invoice”.
- Action: Lookup Spreadsheet Rows (Google Sheets) - searches the spreadsheet for all rows that contain the same unique ID (the order number perhaps?).
- Action: Find Products (QuickBooks Online) - searches for the IDs of the products/services that need to be selected in the invoice line items. IDs are needed to allow the Create Invoice action to be able to dynamically select the relevant product/services - see: Add custom values to dropdown menu fields in Zaps to learn more about using IDs to select values.
- Action: Create Invoice (QuickBooks Online) - uses the line item data and Product/Service IDs supplied by the previous actions to generate the invoice.
With that approach the Zap would trigger when one of the line item rows is marked as ”Ready to invoice”, find all the relevant line items and generate an invoice for the customer in QuickBooks.
Do you think that approach could work for your needs here, or would it have generate all 20 invoices once a month on a specific day?
If it needs to be all 20 invoices once a month, you’d likely need to use Schedule by Zapier for the trigger, followed by a Get Many Spreadsheet Rows (Google Sheets) action to get all the rows, use a Code by Zapier action to group them all together, and Looping by Zapier to run through each group of order line items and create each of the invoices with a Create Invoice (QuickBooks Online) action. That said, as with the previous approach, you’d likely need to also use Lookup Spreadsheet Rows (Google Sheets) and Find Products (QuickBooks Online) actions within the loop.
Hope that helps to point you in the right direction. If you run into any issues with either of those approaches just let me know! 🙂
+14- Zapier Orchestrator & Solution Partner
Hi
You’ll need to make sure your GSheet is configured correctly to work with Zaps.
- Row 1 with headers
- No gap rows/columns
- No grouped rows/columns
Help links for using GSheets in Zaps: https://zapier.com/apps/google-sheets/integrations#help
To be able to automate this, there are certain helper fields you probably need to include, such as a line item group identifier (e.g. Order #), Status, etc.
Resources for handling line items in Zaps:
If you are trying to run on a Schedule, here’s info about the Schedule Zap app: https://zapier.com/apps/schedule/integrations#help
If you are looking to hire help, I’m a Zapier Partner: https://zapier.com/partnerdirectory/automation-ace
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.