About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

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

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

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.

Question

Zapier multi line invoice from Google Sheet

M
Userlevel 1
Badge

Hi I have previously had help on this but I am still struggling.

I have all the data for the invoice in a single row in a google sheet.

I have used utilities in formatter to add line item

However the problem is that I need to add three potiential lines to an invoice

In the google sheet under a column EYFS will be a number in the new row

again under KS1 will be a number and under KS2 will be a number

I need to invoice them for x * EYFS as line item X* KS1 as line item and X*KS2 as line item.
The products are all set up in Xero and I seem to be able to follow your instructions to add first line item but not 2nd and third

As I have said all data is in one row and I can pass one of the three items accross no problem but I cant seem to work out how I pass all three

 

Any help would be great

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

11 replies

Danvers
Userlevel 7
Badge +12

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!

M
Userlevel 1
Badge

here is the formatter 

 and here is output

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

 

 

christina.d
Userlevel 7
Badge +9

Thanks for sharing that, @Magpie2000k!

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

 

M
Userlevel 1
Badge

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

 

 

Danvers
Userlevel 7
Badge +12

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!

M
Userlevel 1
Badge

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.

Danvers
Userlevel 7
Badge +12

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!

 

M
Userlevel 1
Badge

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 

 

M
Userlevel 1
Badge

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

 

SamB
Rank
Userlevel 7
Badge +11

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!

M
Userlevel 1
Badge

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

 

Terms & Conditions