Best answer

Update QBO Invoice from editing cells in Smartsheet

  • 10 September 2022
  • 4 replies
  • 22 views

Userlevel 1
Badge

Hi there Zapier Community,

I am trying to update quantities on my QBO invoice by updating the sales order in my Smartsheets system.

Trigger: Smartsheet Update Row 

Action 1: I chose to use Zapier Formatter: Event “Utilities” → Transform “Line Itemizer” to identify the smartsheet data I want to make into a line item.

I am unsure if I am programming the Line-item Properties properly - screenshot below

 

I am also unsure how to properly program the output action. I think I need to use the Utilites output data widgets instead of the smartsheets data widgets? 

 

Thank you!

icon

Best answer by christina.d 23 September 2022, 05:32

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.

4 replies

Userlevel 7
Badge +9

Hey there, @DBS Analytics! I went ahead and merged your second post question into the original topic.

I hope you don’t mind, I did some digging into the open ticket with my friend in support and wanted to share some of what was reviewed there so far. It sounds like the original flow with Smartsheets wasn’t possible. 

 

For the workflow to work as you want it to, you need a way to grab all of the rows associated with the updated row's invoice number. For example, if the Zap triggers when you update one row, you'd need an action to find all the other rows that contain the invoice number associated with the updated row. 
 
This is important because the "QuickBooks | Update invoice" action needs all of the line items (updated or not) that you want to show up on the updated invoice. As you've experienced, mapping only the updated line item deletes all others.
 
I hope this is making sense! 
 
In short, the workflow isn't possible with Smartsheets. But we could potentially create a workaround if you're willing to go that route. 

 
A workaround would entail adding something like Google Sheets to your workflow since it has an action that lets you find multiple rows based on certain search criteria. This isn't an official recommendation of Google Sheets over other apps, just a suggestion that might enable your workflow. 

 

The recommend setup from there looked like:

[...] instead of using Smartsheet, the info should be in a Google Sheet. Then the Zap would be set up as below:

  • 'New or Updated Spreadsheet Row' in Google Sheets trigger
  • 'Find Many Spreadsheet Rows (With Line Item Support)' in Google Sheets. This can find a maximum of 10 rows. In this step, select the invoice number column as the lookup column and map the invoice number from the trigger as the lookup value. This would then return all the rows (maximum of 10) on the spreadsheet that has that invoice number
  • 'Find Product(s)' in QuickBooks Online to search for the products from the 'Find Many Spreadsheet Rows (With Line Item Support)' step. This would return all the product IDs
  • 'Update Invoice' in QuickBooks Online to update the invoice. Here, in the 'Product/Service' field, map the product ID from the 'Find Product(s)' step

 

Which brings us to your second question:

I have created a zap using googlesheet “Update Row” as my trigger, then used formatter Line itemizer (becasue googlesheet doesn’t appear to have line item support) but I not doing it correctly and keep getting error:

 

The tip from support looked like this:

It looks like you may have gotten stuck on the Line Itemizer step, is that right?
 
If so, it looks like you were just slightly off with the structure you wanted with the line items. you can move "line" over to the Group Name. This will be the parent field. You can then add each property as an individual row of this Line Item properties table:
 
45c8231bf78a694d2bb0a8409c580424.png

 

I hope having it laid out in this way helps!

It’s also worth noting it could be also be worth consulting with a Zapier Expert at this time! We’ve got great minds in Community and it’s possible someone will come through with some magic✨. If you’re on a time crunch and/or would prefer not to fuss though, I’d highly recommend looking into hiring an Expert. 🙂

Userlevel 1
Badge

Hello Zap-sters,

I am trying to update QBO invoice from a googlesheet. 

Currently, I have a Sales Order googlesheet where every line item from my QBO invoice auto populates the googlesheet. I wouls like to be able to change quantities and price and have it update the QBO invoice. 

I have created a zap using googlesheet “Update Row” as my trigger, then used formatter Line itemizer (becasue googlesheet doesn’t appear to have line item support) but I not doing it correctly and keep getting error, screenshots below. Any help is most appreciated. Thank you!

line itemizer action

  Last action step

 

the error

 

Userlevel 1
Badge

Hi there, thanks for following up I was just about to re-post.

I built a zap using the “update row” for Smartsheet as the Trigger. Then, I used the lilne itemizer to translate the line data in Smartsheet into a line item that QB would recognize (the smartsheet update row does not have “line suport”). 

The final Action was the “Update QBO invoice” with the data from the line itemizer. 

I got is to mostly work but am stuggling with a few things. 

Issue 1: When I update the quantity or price in Smartsheets, it updates only the first line item in QBO invoice, deletes any other QBO invoice line items. I tried to use a zap loop but I haven’t been able to make that work. Ideally, I would like to be able to update all the rows in the QBO invoice from the corresponding row in smartsheets. 

Issue 2: The updated invoice shows as “Deposited” in QBO. I am not selecting any payment updates that I am aware of.

screen shot of my line itemizer data (all data is staged): 

 

screenshot of the QBO action programming: 

 

where am i going wrong here? Thanks! 

Userlevel 7
Badge +9

Hey there, @DBS Analytics! Great question! 

I wanted to touch base and see if you were able to test this out live yet? Sometimes that’s the best way to see if you have things setup how you’d like! 🙂