Hi!
I have a google form which prompts the user to fill out, among others,
Quantity1, Quantity2, Quantity3, Quantity4, Quantity5
Price1, Price2, Price3, Price4, Price5
My Google Doc then grabs this data and calculates and displays a grand total = Sum(n=1 to 5) [quantityn * Pricen]
The problem is that typically the user will fill out at least quantity1 and price1, with the option of leaving quantity2,3,4,5 and price2,3,4,5 blank. [I thought about using a pre-filled Google Form which defaults qty2,3,4,5 and price2,3,4,5 to 0, but upon clicking “submit another response”, the pre-filled values go back to blanks. so i would prefer to process the blanks in my automation instead of requiring the user to type 0]
Since the multiplication will fail with blank terms supplied,my zap consists of:
- get new google forms data - 1 step
- formatting price2,3,4,5 (if blank, defailt to 0) - 4 steps [to prevent multiplication from failing]
- formatting qty2,3,4,5 (if blank, defailt to 0) - 4 steps [to prevent multiplication from failing]
- calculating subtotal1,2,3,4,5 - 5 steps
- calculating grandtotal - 1 step
- formatting price1,2,3,4,5 in $x.00 - 4 - 5 steps
- formatting subtotal1,2,3,4,5 in $x.00 - 5 steps
- formatting grandtotal in $x.00 - 1 step
- generating google docs with all formatted data - 1 step
- drafting a gmail with pdf attachment - 1 step
That’s a whopping 28 steps! I was wondering if there’s any way to reduce this.
An additional problem is that if subtotal2,3,4,5 is zero, a $0.00 is printed in my google docs instead of outputting a blank value. I am not sure how to get the formatter to output nothing at all as opposed to a value 0.
I thought about using 5 lookup tables to further process subtotal2,3,4,5 to default to “0” but ran into two problems
a) I am running out of steps ti implement 4 lookup tables (i am at 28 steps)
b) the lookup table cannot output “blank”, it must output a string, i tried outputting a blank variable in my google forms but it fallsback to the default value instead of outputting the blank variable. the most I can do is to get it to output a “-” dash for example
I am still very new to this and definitely would appreciate any advice!
