Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Creating bills in QuickBooks Online from Google Spreadsheet

  • November 21, 2022
  • 6 replies
  • 262 views
D

Hi! I have a google spreadsheet that contains transaction details for incoming bills. I want to set up a process for importing these bills from sheet to QBO with multiple line support. Bills transaction journal looks like this (it is a sample of transactions that constitute one bill):
 


I have tried to set up direct integration between QBO and Google Spreadsheet, but it creates a separate bill for each line in a sheet while I need all line within on bill number (it is in Memo field now but I can add bill number field as separate key field). Could someone help me with this task?

I have read couple articles about line items, formatter, storage and delay, but frankly speaking it created a total mess about it.

I would appreciate any help. Thank you)

Best answer by Troy Tessalone

@DPob 

QBO Supports Line Items

 

Q: Does it mean that I need to build 2 data sets from existing plain spreadsheet in advance before Zapier gets into game or Zapier can do it from existing plain worksheet?

A: This will need to be created before the Zap.

 

Lookup

There needs to be a unique value on both data sets to associate the data sets.

For example, Bill ID would be on the Bill data set and Bill ID would be on the Line Items data set.

The Bill ID would be the lookup value to find the related Line Items.

 

 

“New or Updated Spreadsheet Row” trigger behavior

If you’re using the New or Updated Spreadsheet Row trigger and choose Any column to monitor, any change to a row will trigger your Zap.
If you select a specific column, then the Zap will only trigger when there are changes on that column.

miscEye icon Note

Zaps using New or Updated Spreadsheet Row will also trigger for any new rows in the spreadsheet, even if the specified column is blank.
To avoid triggering in those cases, add a Filter step to your Zap.

 

TIP: When in doubt, test it out!

View original
Did this topic help you find an answer to your question?
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

Troy Tessalone
Forum|alt.badge.img+14
  • Troy TessaloneZapier Expert
  • Zapier Expert
  • 31284 replies
  • November 21, 2022

Hi @DPob 

Good question.

Hopefully this advice will put you on the right path…

I’d recommend using Airtable instead of GSheets, because you can link records across Tables to create data relationships.

Create 2 data sets:

  • Bills
  • Line Items

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
D
  • DPob
  • Author
  • New
  • 3 replies
  • November 21, 2022

Thank you for your advise, Troy! It makes sense in general, but in this particular case this is just a part of much bigger workflow based on Google spreadsheets.. Maybe we will go to AirTable at some point in future, but now I need to to set it up coming from Google Spreadsheet. Actually we just need a workaround for QBO’s bills import feature that is not available for US accounts on some reason.
Is there any way to do it from Google spreadsheet?

Troy Tessalone
Forum|alt.badge.img+14
  • Troy TessaloneZapier Expert
  • Zapier Expert
  • 31284 replies
  • November 21, 2022

@DPob 

Concept is the same.

You need 2 data sets.

Trigger from the Bills data set, then find the related Line Items data set for each Bill.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
D
  • DPob
  • Author
  • New
  • 3 replies
  • November 21, 2022

Okaay..))) Please, confirm if I get it right…

1- Zapier is triggered each time a new row for a new bill is added to “Bills” data set (worksheet with list of bills with fields describing general bills’ attributes (date, vendor, etc) and NO LINE items, right?)

2 - Then action is executed - Lookup in another data set - list of line items for all bills I have. Transactions are searched by ID# of a new bill. It is going to give us an array of line items, right?

3 - We are using array from step 2 for filling line items in QBO bill.

Finish.

Does it mean that I need to build 2 data sets from existing plain spreadsheet in advance before Zapier gets into game or Zapier can do it from existing plain worksheet?

I am trying to set it up but I can’t get how to tell Zapier that he needs to search by ID of a bill that has appeared.

And also what will happen if I add couple bills to the sheet at once? Or I want to import history of bills for 2 years…

Sorry for so much questions.. Thank you for your help)
 

Troy Tessalone
Forum|alt.badge.img+14
  • Troy TessaloneZapier Expert
  • Zapier Expert
  • 31284 replies
  • Answer
  • November 21, 2022

@DPob 

QBO Supports Line Items

 

Q: Does it mean that I need to build 2 data sets from existing plain spreadsheet in advance before Zapier gets into game or Zapier can do it from existing plain worksheet?

A: This will need to be created before the Zap.

 

Lookup

There needs to be a unique value on both data sets to associate the data sets.

For example, Bill ID would be on the Bill data set and Bill ID would be on the Line Items data set.

The Bill ID would be the lookup value to find the related Line Items.

 

 

“New or Updated Spreadsheet Row” trigger behavior

If you’re using the New or Updated Spreadsheet Row trigger and choose Any column to monitor, any change to a row will trigger your Zap.
If you select a specific column, then the Zap will only trigger when there are changes on that column.

miscEye icon Note

Zaps using New or Updated Spreadsheet Row will also trigger for any new rows in the spreadsheet, even if the specified column is blank.
To avoid triggering in those cases, add a Filter step to your Zap.

 

TIP: When in doubt, test it out!

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
D
  • DPob
  • Author
  • New
  • 3 replies
  • November 22, 2022

Thank you, Troy! You have given me a good direction and now I understand how it should work))

Terms & Conditions