Skip to main content
  • Home
  • Get Help
  • How Do I
  • Unique rules WPforms to Spreadsheet plus bij adding a new row should automatically continue the formulas.
Best answer

Unique rules WPforms to Spreadsheet plus bij adding a new row should automatically continue the formulas.

Hello everyone,

Thanks in advance for reading and thinking along!

Explanation situation:
When ordering through my Website via WPForms, I have set up a Zap that it is added to my spreadsheet. The spreadsheet includes formulas to arrive at enough data to create an invoice.

The invoice should be sent via mail with two attachments. The invoice itself and a dimensional drawing of the product ordered.

I have two problems that I am running into.

Problem 1:
Orders through my website via WPForms are passed a unique offer number. When a customer refreshes the page after an order then the order comes through twice. I would like to set up my Zap so that it only adds unique lines. Can you set that a particular column should not appear twice?

Problem 2:
The data coming through from WPforms needs to be expanded to make a good invoice. I do this using different formulas in 8 different columns. I have looked at Arrayformula but this does not work well enough for my situation. The formulas are all right in the second row. When a new row is added by the Zap, I would like the formulas to be applied as well. I have tried to write a macro for this but unfortunately it does not work. 

Do you have any tips on how I can best do this?

* I am still working on creating automatic invoices via docs and spreadsheet, and I am looking for a suitable program to automatically send the mail plus two attachments. If there are tips for this I would like to hear them!

Currently I do it all manually, sending lists Wordt and excel. I send them in bulk with Thunderbird using the Mailmerge plugin. Works very well, but I would like to automate it.
 

Best regard,

Thomas

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

10 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @Waggie 

Good question.

 

For #1

You can use this Zap action: GSheets - Find/Create Row

 

For #2

You can add the formula in the Zap GSheet step.

 

Alternatively, you can use Airtable.

Airtable has Formula fields: https://support.airtable.com/docs/formula-field-reference

Formula fields will be applied to any new record.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
W
Userlevel 2
Badge +1

Thank you for your response. 

#1

Is this a new zap in an existing zap? That you go from sheet 2 to a sheet 3 with only unique numbers?

#2

Where and how can you enter these formulas? Are these formulas the same as Gsheet uses?

Is there another easy way to save your generated Google Docs invoices to pdf without creating an extra account somewhere? I have seen options but here you all have to pay for it which I find rather strange for just saving to pdf.

Troy Tessalone
Userlevel 7
Badge +14

@Waggie

Is this a new zap in an existing zap? That you go from sheet 2 to a sheet 3 with only unique numbers?

No, this would be for the Zap that sends data from the form to the GSheet to make sure only unique records are added.

 

Where and how can you enter these formulas? Are these formulas the same as Gsheet uses?

Related topics: https://community.zapier.com/search?q=google%20sheet%20formula
You can use any formulas available in Google Sheets in your Zap, if the variables in the formula can be mapped from other Zap steps.

This solution won't work if not all variables in the formula are available in the Zap, as it wouldn't be possible to reference specific cells dynamically. For example, let's say you have a sheet like the one in the image below. The Zap will populate the Number and Other Number columns, but column C will be manually updated later.

Example of Google Sheets worksheet with manual data entry

In that case, you can't reference C2 in the Zap, as this would mean that every time the Zap runs, it would make a reference to C2, instead of using the appropriate row number. In this case, you should create a new worksheet on the same spreadsheet and use the =IMPORTRANGE formula. This way you'll have a sheet connected to the Zap and another sheet that imports data from the original sheet, and allows you to use formulas, sort and filter the data, among other things, without having errors on the Zap.

 

Is there another easy way to save your generated Google Docs invoices to pdf without creating an extra account somewhere? I have seen options but here you all have to pay for it which I find rather strange for just saving to pdf.

Check out this help article about getting the PDF version of a GDoc.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
W
Userlevel 2
Badge +1

Thanks for your response!

All the data is available in the zap to make a correct formula.

If we keep your example, there are two amounts in A and B.

For example A 100 and B 200

Both amounts I want to do *2.5.

So =(A2*2,4) 
        =(B2*2,4)

This is a simple one to send my quotes.

Now how do I fill this correctly because you don't know in which row the line is added.

Troy Tessalone
Userlevel 7
Badge +14

@Waggie 

Take a look at these related Zapier Community Topics: https://community.zapier.com/search?q=google%20sheet%20formula%20dynamic

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
W
Userlevel 2
Badge +1

Hi Troy,

Thanks for your response, I have gone through everything in the community and have come a long way. I would like to use Arrayformula, because it seems the best to me.

Basically all the data is there to put the formulas in the Zap.

I also managed to do this with Arrayformula. When I run a test, the first row is successfully added with all the formulas.

However, each time a new row is added under the row that already exists, which is how I would like it to be.

An example formulas I use:

ARRAYFORMULA(IF(V2:V5000>100;0;7))

As you can see, I have set the range to V2:V5000 so that I can continue for a while. However when a new entry comes to my WPForms and thus a new line in my GSheets, it is added in line 5001 and without the formulas.

I don't understand why the Zap doesn't just put it in line 3.

I would like to hope you can help me further.

Thanks in advance for your input.

Troy Tessalone
Userlevel 7
Badge +14

@Waggie 

You most likely wouldn’t set array formulas via the GSheet Zap step since that would be added for each row.

Array formulas are best set in the header column of the GSheet.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
W
Userlevel 2
Badge +1

Hi Troy,

I just tried this, but when I use:

=ARRAYFORMULA(IF(V2:V5000>100;0;7))

Then a new entry is placed via the zap on line 5001.

I have now placed this formula in the header.

Troy Tessalone
Userlevel 7
Badge +14

@Waggie 

Honestly, trying to provide advanced GSheet formulas without seeing the GSheet is tough.

 

Perhaps consider hiring a Zapier Expert for help: https://zapier.com/experts

 

Alternatively, you can use Airtable.

Airtable has Formula fields: https://support.airtable.com/docs/formula-field-reference

Formula fields will be applied to any new record.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
W
Userlevel 2
Badge +1

I will fiddle a bit more myself, have now done the work around first with an extra step in the Zap.

anyways thanks for your responses.

Terms & Conditions