Total Amount | The Blog Pros

Cake Studio is a local bakery that accepts orders through Google Forms. When a customer places an order, the customer automatically receives a UPI QR Code to make the payment. This QR Code is generated dynamically based on the order amount and the customer can pay the bill using any UPI app.

In the above screenshot, the customer ordered a Butterscotch Cake through Google Form and they received a customized UPI QR Code that includes the exact bill amount. The amount is calculated automatically based on the selected items in the Google Form.

Google Forms and UPI Payments

This tutorial explains how you can send custom UPI QR Codes to customers automatically whenever they place an order via Google Forms. We will use Google Sheets to calculate the bill amount and generate the QR codes, and Document Studio to send the emails with the QR codes to the customers.

Let’s see how you set up this workflow in a few simple steps.

Prepare Google Form for Orders

Here is a sample Google Form that we have created for Cake Studio. As you can see, and this is important, we have mentioned the amount of each cake in the options itself.

Prepare Google Sheet

Open the Google Sheet that is linked to the Google Form. The Google Sheet will contain columns for the questions in the Google Form. We’ll now add extract columns that would help us generate the custom UPI QR codes.

You may find the Google Sheet with UPI formulae here

Add Columns to Google Sheet

1. Bill Amount - This column will store the price of the cake that the customer has ordered. We’ll write a formula using the REGEXREPLACE function to extract the price from the selected option.

=ARRAYFORMULA(IF(ROW(D:D)=1,"Bill Amount",
  IF(NOT(ISBLANK(D:D)),REGEXREPLACE(D:D,".+₹ ",""),)))

2. Total Amount - Our second column will store the total bill amount which adds GST on the price of the cake. We’ll use Arrayformula to apply the calculation down the entire column.

=ARRAYFORMULA(IF(ROW(E:E)=1,"Total Amount",
  IF(NOT(ISBLANK(E:E)), E:E * 1.18,)))

3. UPI QR Code - The final column will store the custom UPI QR code that includes the total bill amount. We will use the built-in UPI function to generate the QR code.

A customized UPI QR Code will be generated for each order. The QR code will include the total bill amount so that the customer can make the payment without having to enter the amount manually.

Embed UPI QR codes in Email

Now that we have the UPI QR codes in the Google Sheet, we will use Document Studio to send emails to customers with the QR codes embedded in the email body.

Launch Document Studio in your Google Sheets and create a new workflow. Add a Send Email task to the workflow. Create a message template that includes the Embed Image marker to embed the UPI QR code in the email.

{{Embed IMAGE, UPI QR Code}}

We have mentioned UPI QR Code as the second parameter in the above marker since it is the title of the column that contains the generated QR codes in our Google Sheet.

Save the Workflow and make sure to enable the Run on Form Submit option so that the emails are sent automatically whenever a new order is placed through the Google Form.

Test the UPI Payment Workflow

Fill this Google Form and you should see a new row added to this Google Sheet with the bill amount and the UPI QR code. You’ll also receive an email with the UPI QR code embedded in the email body.

You are working inside a Google Spreadsheet where a formula needs to copied down to the last row of the sheet. You also need the formula to be added automatically when a new row is added to the Google Sheet.

There are several ways to solve this problem.

Copy Formula Down in Google Sheets

The easiest approach to copy down formulas is to use the fill handle in Google Sheets. Write your formula in the first row of your spreadsheet, and then point your mouse to the lower right corner of the formula cell.

The pointer changes into a fill handle (black plus symbol) that you can drag to the last row of the sheet. The fill handle will not just copy down the formulas to all the adjacent cells but also copies the visual formatting.

If you need to copy the formulas across cells but sans any formatting, select the cell that contains the formatting and press Ctrl+C to copy it to the clipboard. Next, select the range where that formula needs to applied, right-click, choose Paste Special and Paste Formula only.

Apply Formula to the Entire Column in Google Sheets

If you have hundreds of rows in a Google Spreadsheet and you want to apply the same formula to all rows of a particular column, there’s a more efficient solution than copy-paste - Array Formulas.

Highlight the first cell in the column and type the formula as earlier. However, instead of specifying a single cell as a parameter, we’ll specify the entire column using the B2:B notation (start from cell B2 and go all the way down to the last row of column B).

Then press Ctrl+Shift+Enter, or Cmd+Shift+Enter on Mac, and Google Sheets will automatically surround your formula with ARRAYFORMULA function.

Thus, we could apply the formula to the entire column of the spreadsheet with only a single cell. Array Formulas are more efficient as they process a batch of rows in one go. They are also easier to maintain as you only need to modify a single cell to edit the formula.

One issue that you may have noticed with the above formulae is that it applies to every row in the column where you have only want to add formulas to rows that contain data and skip the blank rows.

This can be done by adding an IF contain to our ARRAYFORMULA so that it doesn’t apply the formula the any of the blank rows.

Google Spreadsheet offers two functions to help test whether a cell is empty or now.

ISBLANK(A1) - Returns TRUE if the referenced cell is empty.
LEN(A1) <> 0 - Returns TRUE if the referenced cell not empty, FALSE otherwise

Our modified Array Formulas would therefore read:

Using ISBLANK(Cell Reference):

There are several other ways to test if a cell is blank or not:

=ArrayFormula(IF(ISBLANK(B2:B), "", ROUND(B2:B*18%, 2)))
=ArrayFormula(IF(LEN(B2:B)<>0, ROUND(B2:B*18%, 2), ""))
=ArrayFormula(IF(B2:B="", "", ROUND(B2:B*18%, 2)))

Use Array Formulas inside Column Headers

In our previous examples, the text of the column titles (like Tax, Total Amount) was pre-populated and the formulas were only added to the first row of the dataset.

We can further improve our formula so that they can be applied to the column header itself. If the index of the current row is 1, calculated using the ROW() function, the formula outputs the column title else it performs the calculation using the formula.

=ArrayFormula(IF(ROW(B:B)=1,"Tax",IF(ISBLANK(B:B),"",ROUND(B:B*18%, 2))))

Auto Fill Formulas into Google Form Submissions

ARRAYFORMULA functions are particularly useful for Google Forms when the form responses are getting saved inside a Google Sheet. You cannot do live calculations inside Google Forms but they can be performed inside the spreadsheet that is collecting the responses.

You can create new columns inside the Google Spreadsheet and apply the ARRAYFORMULA to the first row of the added columns.

When a new form submission is received, a new row would be added to the Google Sheet and the formulas would be cloned and automatically applied to the new rows without you have to copy-paste stuff.

Also see: Convert Google Form Response to PDF Documents

How to Use VLOOKUP inside ARRAYFORMULA

You can combine ARRAYFORMULA with VLOOKUP to quickly perform a lookup across an entire column.

Say you have a “Fruits” sheet that lists the fruit names in column A and the corresponding prices in column B. The second sheet “Orders” has fruit names in column A, the quantity in column B and you are supposed to calculate the order amount in column C.

=ArrayFormula(
  IF(ROW(A:A)=1,
  "Total",
  IF(NOT(ISBLANK(A:A)), VLOOKUP(A:A, Fruits!A2:B6, 2, FALSE) * B:B, "")))

In simple English, if the row of the current cell is 1, output the column title in plain text. If the row is greater than 1 and the column A of the current row is not empty, perform a VLOOKUP to fetch the price of the item from the Fruits sheet. Then multiply that price with the quantity in cell B and output the value in cell C.

If your VLOOKUP range is in another Google Spreadsheet, use the IMPORTRANGE() function with the ID of the other Google Sheet.

Please note that you may have to use semicolons in the spreadsheet formulas instead of commas for some locales.

Tag: Total Amount

How to Request UPI Payments with Google Forms