Best answer

Creating Xero multi line invoice from Google sheet

  • 28 September 2022
  • 4 replies
  • 216 views

Userlevel 1
Badge

Hi I have tried and tried reading and following guides but cant get there,

I have a google sheet in each row is an invoice request with 

Name | email | Product 1 (quantity) | Product 2 (quantity) | Product 3 (quantity)| unique id | telephone number

I Have made it pull single line items into Xero ie pulls product 1 and match in Xero with product and as such I get a single line invoice that is correct but I cannot seem to do this for all the products requested ie multi line invoice.

The google sheet does not have prices I need to link this as I have done on single line to the products in Xero

 

Any help would be great

icon

Best answer by Danvers 6 October 2022, 12:58

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 +12

Hi @Magpie2000k!

It sounds like you need to use the search action Find Many Spreadsheet Rows (With Line Item Support). This will pull any rows that match the a given search (it looks like you could use the unique id of the order). The output of that action is formatted as line items, so you can put that straight into Xero. One thing to note if you’ve not worked with line items in Zapier before is that it will look like a text list separated with commas, but if you use the Find Many Spreadsheet rows action, they will be in line items. 

 

Would that work for you? If not could you give us some more details? What’s the trigger for your Zap for example? Thanks!

Userlevel 1
Badge

So currently the trigger is when a row is added to the google sheet.

So I have a typeform that is completed and that populates the persons order into the google sheet.

Your suggestion sounds like it should work but could you give me anymore directions as I am still confused.

just for clarity the ordered items all appear in same row in google sheet. the new row added is my trigger and I can make it populate one product into Xero.

In simple terms the typeform says how many of product one do you want then how many of product 2 and then product 3.

 

Userlevel 7
Badge +12

Hi @Magpie2000k 

Reading this again, I think I misunderstood how the sheet was set up the first time, sorry about that. 

As all of the order information is in the same row in Google Sheets then you don’t need the Find Many Rows action, the information from the New row trigger is all you need. 

What you do need to do is to convert the information from the sheet into line items. You can do that using Formatter by Zapier. The Formatter can do a whole host of things, one of which is to convert text into line items. 

You need to choose the Event ‘Utilities’ and the transform ‘Text to line item’

When you set up the step, what you put in the input box will be transformed into line items. So you would add each of the product fields into the Input box, separated by a comma:

You will need to do this for any fields that you need for any line item field in Xero (eg product name). It sounds like the Product name isn’t in the sheet (other than in as the column header) but that’s no problem, you can type the name of each product into the input box (separating each one with a comma) and the step will convert that into line items for you. 

 

Does that makes sense? Let me know if that helps or if you need some more detail :) 

Userlevel 1
Badge

Thankyou that makes much more sense for me