Best answer

Don't see line items section?

  • 24 December 2019
  • 3 replies
  • 686 views

Userlevel 1

I'm trying to get the creation of a new invoice in QuickBooks to trigger the updating of a Google Sheet, so that every row in the sheet's "Product" column that matches a product name in a line of the invoice gets that line's quantity added to the next column over, the "Count" column. For instance, if I make an invoice for 2 of Widget A and 3 of Widget B, the zap finds the Widget A and Widget B columns I have set up in the sheet, and then in the next columns over, adds 2 and 3 respectively to the "Count" cells.

I've managed to set this up fine when there's only 1 product on the invoice, but when I go to use Line Items in my GS action - Find Many Spreadsheet Rows (With line item support) - there's no section for line items! I made sure I selected the QB trigger New Invoice (With line item support), even though weirdly enough the "line item" part of that title doesn't show up in the outline.

For what it's worth, I've also tried using Line Itemizer to separate out my values but they still show up in my Lookup Value as one comma-separated value, which of course doesn't match any cell of the spreadsheet. Any leads would be appreciated! Still relatively new so could be missing an obvious logical step or something.



Screen Shot 2019-12-24 at 2.43.01 AM.pngScreen Shot 2019-12-24 at 2.44.53 AM.pngScreen Shot 2019-12-24 at 2.43.49 AM.pngScreen Shot 2019-12-24 at 2.43.13 AM.png




icon

Best answer by PaulKortman 24 December 2019, 15:43

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

3 replies

Userlevel 7
Badge +10

Welcome to the Zapier community @gavinschriver

I'm assuming that the Google Sheets "Find many spreadsheet rows" step is failing, not that an update row is failing. And here's why (from my understanding):

The "Find many spreadsheet rows with line item support" is not talking about "line item support" on the input (aka what you are searching for). Instead it means the output of that step will be multiple rows as line items.

So when you pass multiple items to it as an input it will look for an exact match row, so it's looking for "item 1, item 2" in a cell value. I understand your logic that it should instead search for "item 1" return that row and then search for item 2 and return that row. However it's not going to work that way.

I'd like to make a suggestion on how to do this instead, and in doing so I'm making an assumption. I assume that you are essentially keeping a running total of how many of which items sold, correct? So you actually need to add the number to column B "count", not just replace it, correct?

So here's how I would solve this, essentially with two Zaps and an intermediate Google sheet. The first zap triggers off of the new QB invoice and then writes the line items to an intermediate Google Sheet. The second zap triggers off of a new row in that intermediate sheet and finds the row in your Picksheet (test) and adds the quantity to the count number.


First Zap In more detail:

  1. Trigger: New Invoice in QB Online
  2. (Your utility formatter step - whatever it does)
  3. Google Sheets "Create Spreadsheet Row(s)" - creates a row in your intermediate sheet with all the information you need (is it just product name and qty?)


Second Zap in more detail:

  1. Trigger: New row in Google Sheet (the intermediate sheet)
  2. Google Sheets "Lookup Spreadsheet Row" search for the product name in your pick list using the value from the trigger step.
  3. Formatter Math - Add the number from quantity (trigger step) and the number in the Pick list, (step 2)
  4. Google Sheets "Update Spreadsheet Row" use the row number from step 2, and the total value from step 3 for the count.

That second zap will run for each line item in the QB invoice. 1, 2, 10, etc. So it'll update your picklist for each item ordered.


Additional Note:

I use this work around often, and in situations where the intermediate spreadsheet could grow quickly or become too big I use a third zap to tame the sheet. I actually have a Google apps script I have written to do this since Zapier's Google Sheets will only allow for one row deletion at a time. So here's the third zap:

  1. Trigger: Schedule by Zapier - Typically daily at 1am Eastern
  2. Zap Manager - turn off the second Zap above (messing with data in a sheet that is being watched by a zap for new rows will mess it up unless you turn it off first)
  3. Google Sheets Find row (check the box to search from the bottom) look in the quantity column for a 1 - (this may not get ALL the rows, but tomorrow it will clean up the few that remains, my biggest concern was to avoid having 50k rows in this sheet and breaking everything)
  4. Webhook - Send a GET to my Google Apps script to delete rows 2-(value from step 3)
  5. Zap Manager - turn on the second Zap above (now it will see the current rows in the intermediate sheet and trigger only on new rows moving forward)


Userlevel 1

Thank you so so much for explaining @PaulKortman ! That intermediate Google Sheet does exactly what I though the Line Item function was supposed to, the clarification is invaluable. Hopefully I'll wrap my head around a real use for Line Item support one of these days but your solution is perfect for splicing out the values from each of my invoice line columns and handling them from there, I've set it up and eveything seems to work well. Thanks again.🙌


Userlevel 7
Badge +10

Happy to help @gavinschriver ... It's a bummer that many integrations cannot take line items as inputs. But hey this intermediate sheets set up seems to work solidly for me too. Hopefully someday Zapier will integrate a better solution but in the meantime this works as a bandaid!