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
Page 1 / 1
Hi @Waggie
Good question.
For #1
You can use this Zap action: GSheets - Find/Create Row
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.
@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?
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.
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.
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.
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.
@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.
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.
@Waggie
Honestly, trying to provide advanced GSheet formulas without seeing the GSheet is tough.
