Zapier multi line invoice from Google Sheet

Hi @Magpie2000k!

Could you share a screenshot of how the Formatter step is set up and what the output is when you test it? That will help us to see where the trouble might be. 

Thanks!

here is the formatter 

 and here is output

Here I can only select one product in Xero not the three that i need

Thanks for sharing that, @Magpie2000k!

Could you try adding commas after the EYFS and KSI fields to see if that does the trick?

Thanks and it seems to help but in the Xero step as seen below I can only select one product in the item code so I dont get the three lines in the invoice.

LIne one should be Chinese New Year EYFS Line 2 Chinese New Year KS1 and the last Chinese new Year KS2 they are all separate events and they will book varying quantities of each.

I really hope this makes sense

Hi @Magpie2000k!

Thanks for sharing that screenshot, I can see exactly what you mean :)

To add line items to the Item code field, you’ll need to a) use custom values and b) use another text to line item step. 

Let me break that down for you. When you have a dropdown field in Zapier, you can either select an item from the dropdown or use a custom value. You can learn more about custom values in this help guide: Add custom values to dropdown fields. A key point to take from that article is that when you use a custom value, you need the ID of an item and not the name of it. In your case, that means that you need the ID of the different products and not the name. You can usually find the ID of a product in Xero, but you can also see it when you use the dropdown field for Item code. Here’s an example 

You can see a list of customers, the name is in the larger black text and the ID is in the smaller text underneath. So, if I wanted to add Axel Rose as a customer using a custom value, I would add ‘528’ in the as a custom value. 

In this case, I would recommend adding the product ID into the Google Sheet eg add a column after item name that says item code. You can then take the information from Google Sheets and use the Text-to-line item Formatter step to create a set of line items for the Item ID, just like you did for the Quantity field. 

A couple of quick tips here -

• If you add columns to your sheet, you’ll need to re-test your trigger step and then check each step of the Zap that use information from Google Sheets as the columns will be different. 
• If you don’t want to manually find and add the ID of an item every time, you could add a VLOOKUP table in your sheet that would automatically populate the ID based on the name of the item. VLOOKUP is kind of an advanced Google Sheets formula but super handy once you get the hang of it. A quick google found this guide to using VLOOKUP in sheets, but you may want to search for a tutorial yourself if you’ve not used it before. 

I hope that’s all clear! Working with line items can be really tricky and you’re doing a great job!

Thank you

either I am not getting this or im going mad.

I think what you are saying is that if the quantities ordered were in a column with the header of the column having the ID (code) from Xero then it would work is that correct.

So in my column if I had EYFS column named as CNY EYFS (which is the product code (ID) then it would work.

Or are you saying add a column to the right of each quantity column saying the code like below

They I would need to write a formula in product column =if(E2>0,$E$1,"") assuming that column header was CNY EYFS

CNY EYFS would then match my ID(Code) in Xero 

Please confirm if this is correct then explain where I add the extra step in formatter text to line as surely I would need to join those two steps of product and quantity together. Sorry to be a pain I will get there I promise :) 

My concern would be if I copied down the formula for the columns to bring in the ID (code) then surely Zapier would see that spread sheet as having something in that row and as such skip all rows with the formula dragged down in even though the result would be “” ie blank

I have tested using a formula and the added record just overwrites the formula in the cells and as such leave those columns blank.

You’re not being a pain at all! Line items are really intimidating if you’ve not come across this type of data format before.

For your questions where to add the extra formatter steps and joining the quantity and product code together, I thought it would be easier to answer with a video:

So, the line items side of things looks good, it sounds like we need to figure out the best way to add the product code into the Google Sheet. You’re right that adding formula into the sheet wont work if a Zap is adding rows to the sheet. 

Are you using Google Forms for the information in the sheet or is it coming from somewhere else? If it’s Google Forms, a combination of hiding sections and getting a pre-filled link will allow us to get the product code information into the Google Sheet. Let us know if that’s the case and you need some help with it!

That is perfect in video format. 

The data is collected and zapped into a google sheet.

The collection is done in Typeform not sure typeform allows for hidden fields

But I can use google form if that makes it easier can you explain 

OK we are getting there.

I have followed the instructions and have been able to create a google form and have that populate a google sheets, using Forfacade to hide questions with prefilled out answers and as such have the data all firing into Xero with the zap including the product code and therefore the invoice is correct.

2 issues left to understand. First is that I cant seem to pass all of the information into the xero account.

I have to use contact name to be (company name) the school name for the invoice not the persons actual name aswell as the company. is this possible (im guessing you are going to say more formatter work lol)

Although I completed the step in zapier Xero step send to contact but it even says does not sctually send email just marks as sent.

1 So is it possible to send the invoice automatically

2 can I pass both contact name and Business name into Xero 

You have been an absolute star and a huge help.

Look forward to hearing from you 

One last thing I will need to consider so might aswell ask now.

Can i get zapier to automate an invoice paid back to a spreadsheet or onto a new spreadsheet where the invoice paid is in a specific account code only. So for example we use account code IR Schools for all of these invoices I am automating, so would want a spreadsheet to have new line entry when one of the schools pays so I know when to send the codes. (might automate the codes later but lets walk first.) Or more ideally if the zap could update the row on the google sheet that is used for the Zap into Xero.

Thank you so so much 

Jon x

Hi there, @Magpie2000k! Hope you don’t mind me popping in here to assist while Danvers is away. 🙂

1 So is it possible to send the invoice automatically

There’s a Send Sales Invoice By Email (Xero) action which would allow you to send the invoice you’re creating by email to the primary email address of the contact on the invoice. It would also send the invoice to any other people that are marks as “include in emails”.

2 can I pass both contact name and Business name into Xero 

Has the school name been used as the contact name in Xero? If so then I’d have thought you could use the school name in the Contact Name field on the Create Sales Invoice action. Otherwise, the contact’s actual name, as it appears in Xero, would need to be used instead of the school name.

It looks like there’s an Organization field so you could potentially use that to enter the business name - although you may need to enter it as a custom value. You can find out more about how to work with custom values here: Add custom values to dropdown menu fields in Zaps

Can i get zapier to automate an invoice paid back to a spreadsheet or onto a new spreadsheet where the invoice paid is in a specific account code only.

There’s a New Payment trigger for Xero so you could have another Zap that runs when you receive a payment. You can set it to only trigger for certain payment types, which might help to limit it to only school invoices. If not, you may need to consider adding a Filter by Zapier action to check that the account is IR. You can find out more about how to work with filters here: Add conditions to Zaps with filters.

Then use a Lookup Spreadsheet Row (Google Sheets) search action to find the row related to that payment. See our Search for existing data in Zaps guide for more information on how to use search actions. 

Then follow that up with an Update Spreadsheet Row action to update the row that was found. To ensure it selects the right row you’d need to select the Row ID number (use the custom value option) from the Lookup Spreadsheet Row action. 

Hope that helps to point you in the right direction. Let us know if you run into any issues on that!

Thank you Sam. and yes that all helps.

The previous question I asked was about the Typeform or google form integration.

On Google For I used fromfacade is that the best way to use a paid add on or is there an alternative I havent seen.

Kind regards

Jon