Creating bills in QuickBooks Online from Google Spreadsheet
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)
Page 1 / 1
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
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?
@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.
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)
@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.
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!
Thank you, Troy! You have given me a good direction and now I understand how it should work))