You may have noticed while testing your Frontly app that any tasks, functions or formulas that you may have set up for your Google Sheets are not being applied to your new row data submissions from Frontly.
Due to the technical relationship between Frontly and Google Sheets, any formulas you may have preset into your Sheets rows by "clicking and dragging" down the column will be erased and overwritten. Here are two solutions that will enable you to input any of your Sheet formulas and automations into your Frontly account instead.
Read on to learn more!
Using Hidden Form Fields and Hidden Values
The key to enabling these Sheet functions into your data are through the 'Hidden Form Field' type and their 'Hidden Values'.
Hidden Form Fields are a type of field type at Frontly that are typically assigned to data columns that are not user filled. On a Form block, they are typically kept from your user's view as they typically hold a constant static value or a dynamic one, depending on your configuration. At Frontly, these values are stored in the Hidden Value field.
This is where you will enter your Sheet formula, formatted exactly how you typically would with one difference. When inputting your functions that reference a specific cell, you will be required to inject the dynamic variable {{row_count}}.
Injecting the dynamic variable {{row_count}} will allow the Frontly system to identify the row the new data resides in when processing the data. Additionally, this dynamic variable will replicate the 'click and drag' function in your Google Sheets that will apply its formula to all subsequent rows.
Click here to learn more about Hidden Form Fields and their Hidden Values.
Example Formula
Imagine you have three columns, A, B and C: "Dogs", "Cats" and "Total Pets".
Column A and Column B contain actual values, and Column C should be a sum of these values. "Dogs" and "Cats" will remain regular input fields submitted by the user, but "Total Pets" will be a Hidden Form Field.
For the Hidden Value of this Hidden Form Field, you would normally use a simple formula like this:
=SUM(A1+B1)
Again, this works in Google because you can click and drag to auto-populate the rows with the correct row numbers, but when you're injecting a value from a Form in Frontly, you'll need to use the dynamic variables {{row_count}} to replace the row count.
The resulting formula would look like this:
=SUM(A{{row_count}}+B{{row_count}})
When the row is created, Frontly will replace these variables with the new row number and the formula will appear correctly as =SUM(A1+B1) and will generate a value when all conditions are met.
Using Array Formulas
Warning: The set-up of an array formula in your Sheets can be a little more complex as the functions inside one may consist of functions that are considered to be
We highly encourage you to read up on array formulas to increase your understanding of this function. Alternatively, working with an AI model like ChatGPT to find the appropriate function for you is also recommended!
Another method exists where you can maintain the formulas inside your spreadsheet without needing to move them into Frontly via an Array Formula.
Array formulas allow you to perform calculations across a range of cells or arrays, and differs from regular formulas in that they can operate on multiple values simultaneously, producing multiple results.
Therefore, rather than having your formulas exist into each individual cell, you instead will create an array formula on the first row where your field headers are also stored, using the syntax below (and note the bracket types):
={"Label"; ARRAYFORMULA(array_formula)}Example Formula
Using the same example from the previous section, see below how the array formula would be typed out in your spreadsheets and breakdown its components.
Here's breakdown of the array formula's syntax, be sure to encase the entire function with curly "{ }" brackets:
"Total Pets"; - This is a text string enclosed in double quotes. It represents the label you want to appear in the first cell of column C.
ARRAYFORMULA(A2:A + B2:B)
This is where the real calculation happens. Let's break it down further:
ARRAYFORMULA: This is a function in Google Sheets that allows you to apply a function or operation to an array or range of cells.
A2:A + B2:B: This part calculates the sum of corresponding cells in columns A and B, row by row, within the specified range.
A2:A: This specifies the range of cells in column A starting from cell A2.
B2:B: This specifies the range of cells in column B starting from cell B2.
2. The resulting "Label" output set by your array formula in the beginning of its syntax.
3. The calculated outputs based on the function set inside the ARRAYFORMULA and the inputted values in columns A and B.
Additional Links
Click here to take your knowledge further with the Generation of Unique IDs