Best answer

Create Sales Receipt in Quickbooks Online with Multiple Line Items

  • 24 December 2019
  • 6 replies
  • 559 views
L
Userlevel 1

Hi,


I'm trying to create a sales receipt in Quickbooks online using a Google Sheet that has a bunch of orders. Here's an example:

gsheets.pngWhat I need is to be able to create a sales receipt that will show all the items a customer purchased. For example, in the spreadsheet shown above, a sales receipt would have the 5 items for order 78654, then another sales receipt for the 1 item for order 79352, then another sales receipt for the 2 items for order 79938 and so forth.

Is there a way to do this?


icon

Best answer by PaulKortman 26 December 2019, 16:39

@Luis I wish that there was a way for Zapier to "parse" a spreadsheet and take action on each row, or alternatively copy a spreadsheet to another where Zapier is watching for new rows etc. However that's not currently fully possible.

There are a couple of workarounds. The first is using Zapier's Google Sheets integration that can fire on a new spreadsheet in a folder and do a search "Get Many Spreadsheet Rows (Advanced, With Line Item Support)" - but this only returns up to 20 rows, so if the sheet has more than 20 rows it will not get all of the rows.

The second workaround would be to use a Google Apps Script (not in Zapier, pretty advanced and is beyond the scope of this thread)

The third workaround would be to use a tool like mailparser which can process attachments to emails and feed them to Zapier as line items (meaning you could skip the intermediate google sheet.)


My recommendation based on what you've posted here is the third option, especially if you can automate the daily export to send to your mailparser mailbox (if it can only be emailed to your email address set up a zap to "forward" it to the mailparser inbox)


View original
This topic has been closed for comments

6 replies

PaulKortman
Rank
Userlevel 7
Badge +9

@Luis Welcome to the community, There are a couple ways to do what you are asking for, but the correct solution depends on how the data got to that sheet in the first place.

For example, if this is an export that needs to be processed each day/week etc or just once then it's going to be complicated.

If, however, the rows are written to this sheet as it happens you could do the following.

  1. Trigger off of a new row in this sheet
  2. Use Delay, Delay after Queue to ensure the next instance of this zap doesn't run during this instance.
  3. Use Storage by Zapier to get the value of a key "order-numbers"
  4. Use Formatter: Numbers: Spreadsheet like formula () to compare if the Order # (from step 1) is in the "order-numbers" (from step 3)
  5. Filter: If the output of step 4 is false (then continue as this will be the first row for that order, we only want to run this zap once for each order, not once for each line item)
  6. Use Storage by Zapier to push the value to the key "order-numbers" and use the order number from the trigger for the value to push.
  7. Delay - for 1 minute to ensure all the rows are written to the Google Sheet
  8. Use Google Sheets : Find Row(s) with line item support. and search the Order # column in the spreadsheet to find all of the rows with that order number.
  9. Quickbooks: Create a Sales receipt - use the output of the Trigger step for anything that is the same for each line and you don't want multiples of (like the date of the transaction, or the order number, or the address etc.) use the output of step 8 for the values in the product/line items section (like product name, quantity, price etc)

If it's an exported data that you want to process you could set up the above zap to run on a different sheet and copy/paste the rows onto that sheet after the zap is turned on so it will process each row and order.



Need Additional help? Feel free to reach out here: https://zapier.com/experts/connex-digital/
L
Userlevel 1

Hi @PaulKortman,

Thanks for your reply. Yes, this is Google Sheet that is generated once per day so I figured it wouldn't be easy.

I can definitely try what you mentioned at the end and copy/paste the rows onto a new sheet so they can be processed. Although I do have a question. Is there a way to do this with Zapier? Meaning is there a way to traverse every row from a Google Sheet and copy each one by one to a new sheet that can trigger the Zap to create the Sales Receipt?


PaulKortman
Rank
Userlevel 7
Badge +9

@Luis I wish that there was a way for Zapier to "parse" a spreadsheet and take action on each row, or alternatively copy a spreadsheet to another where Zapier is watching for new rows etc. However that's not currently fully possible.

There are a couple of workarounds. The first is using Zapier's Google Sheets integration that can fire on a new spreadsheet in a folder and do a search "Get Many Spreadsheet Rows (Advanced, With Line Item Support)" - but this only returns up to 20 rows, so if the sheet has more than 20 rows it will not get all of the rows.

The second workaround would be to use a Google Apps Script (not in Zapier, pretty advanced and is beyond the scope of this thread)

The third workaround would be to use a tool like mailparser which can process attachments to emails and feed them to Zapier as line items (meaning you could skip the intermediate google sheet.)


My recommendation based on what you've posted here is the third option, especially if you can automate the daily export to send to your mailparser mailbox (if it can only be emailed to your email address set up a zap to "forward" it to the mailparser inbox)


Need Additional help? Feel free to reach out here: https://zapier.com/experts/connex-digital/
L
Userlevel 1

@PaulKortman I see what you mean and I agree. I think option #3 would probably work best. I just watched a video on mailparser and it seems like it might do the trick. I'll give it a shot


PaulKortman
Rank
Userlevel 7
Badge +9

Good to hear @Luis feel free to comment here in the community if you need help with mailparser.io there are a lot of people with experience with it here.


Need Additional help? Feel free to reach out here: https://zapier.com/experts/connex-digital/
L
Userlevel 1

Thanks @PaulKortman


Getting started with Zapier?

Learn the basics so you can get started.