Knowledge Base
  • Select Role
  • English
  • Login

Spreadsheet

The Spreadsheet form element allows users to perform various actions in an Excel-like environment, depending on their needs,  such as:

  • Database editing
  • Configuration controlling
  • Data merging
  • Workforce planning
  • Sales reporting
  • Financial analysis
The Spreadsheet is available only in Joget's Enterprise Editions.
The Spreadsheet form element in Joget uses the Handsontable library, specifically version 6.2.2 for Joget DX 8. The library offers a wealth of plugins and APIs to customize cell appearance, selection, dynamic data, validation, and much more.

Configure spreadsheet

The Configure Spreadsheet section provides the necessary settings to customize your spreadsheet element, including labels, IDs, columns, and formatting options.

Fill in the following fields:

  • Label: Optional spreadsheet label/title.
  • ID: Unique ID of the form element. Mandatory.
  • Columns:
    • Field ID: Field ID of the column.
    • Label: Column header title/label. You can include a tooltip that shows up when someone hovers over the column label.

      Example label:
      <span title='This tooltip will show when someone hovers over the column'>Column Label</span>
    • Format Type: Defines the type of input to store. The default format type is Text.
      • Text: Format the value as text or numbers.
      • Encrypted Text: Format the value as encrypted text or numbers.
      • Numeric: Accepts a numeric value. See Number Formatting.
      • Date: Accepts a date type value.
      • Time: Accepts a time type value.
      • Checkbox: Appears as a checkbox to check.
      • Dropdown: Appears as a dropdown menu to select options.
      • Autocomplete: Auto-completes any known phrases.
      • Password: Obscures any alphanumerical value into a password asterisk.
      • File: Accept file upload and render a file download link.
      • Image: Accept image file upload and render a thumbnail.
      • URL: Convert value to a URL.
      • Hidden: A hidden column is used to store formula values.
      • Custom: Use custom settings to configure the column. See Spreadsheet Custom Field Formats.
    • Format: Defines the format to show data on the spreadsheet based on the chosen format type.
      • Numeric: The format accepted by numbrojs library (e.g., 0.00 for 2 decimal places).
      • Date: The format accepted by moment.js library (Default value: DD/MM/YYYY).
      • Time: The format accepted by moment.js library (Default value: h:mm:ss).
      • Dropdown: The Form Def ID that contains the equivalent dropdown element.
        Dynamic Cascading Select Box: Supported when the target select box uses Options Binder with "Use AJAX for cascade options?" is checked.
      • Autocomplete: See handsontable for examples.
      • File: The Form Def ID that contains File Upload form element.
      • Image: The Form Def ID that contains Image Upload form element.
      • URL: Provide a URL syntax (e.g., http://www.joget.org?id={id}&name={name}).
      • Hidden: Key in the default value to be saved.
      • Custom: See handsontable for examples. Place the setting JSON in {{ and }} (e.g., {{"editor":"text"}}). Any string within {{ and }} will be treated as setting JSON and used to configure the column.
    • Regex Validation: Validates input value to match the defined Regular Expression pattern. For example, add ".+" (without the double quote) to make the column mandatory.
    • Formula: Defines a formula to perform a calculation. See Supported Formula Functions.
      For example:
      • Quantity * Unit_Price in the Formula column
      • subtotal - (subtotal * discountPercent / 100)
      • subtotal - discountAmount
      • subtotal - charges (Correct)
      • subtotal-charges (Wrong)

        Important

        • Include spacing between operators. 
        • The position and spacing of all elements of an equation must be exactly as they appear in printed form.
    • Width (%): Define the column width in percentage. Leave it blank, and the browser will automatically adjust the column widths.
    • Readonly: Defines if the column is editable or readonly.

UI 

The UI section includes sorting and grid management settings, allowing users to organize and manage spreadsheet data efficiently.

Sorting

Sorting settings allow users to organize spreadsheet data by column, making it easier to view and analyze information.

Configure the following fields:

  • Enable Header Sorting?: Determines if users can sort spreadsheet data by column in ascending or descending order.
  • Data Order Field ID: Field ID to store the ordering sequence of spreadsheet rows. Key in a unique field ID in the Data Order Field ID property. This field ID must not be an existing field in your form.
    If you need automatic sorting of another column value when loading the form grid/spreadsheet, use Load Grid Data with Custom Sorting and Filtering or Database SQL Query with the ... ORDER BY ASC SQL statement.

Grid

The Grid settings manage the appearance and behavior of the spreadsheet grid, allowing you to control features like row numbering, adding/removing rows, and fixed columns.

Configure the following fields:

  • Readonly: Defines if the entire spreadsheet is editable.
  • Disable Add Feature: Determines if a new row can be added.
  • Disable Delete Feature: Determines if a row can be removed.
  • Show Row Numbering?: Adds a new leftmost column to display the row numbers starting from 1.
  • Number of columns to fixed on left: Specifies the number of fixed (or frozen) columns on the left of the table. Default value: 0.
  • Number of spare rows: Number of spare rows to be added automatically after lines with values.
  • Max height: Maximum height of the spreadsheet to keep the bottom scrollbar within the browser view (e.g., "60vh").
  • Custom Settings (JSON): See to plugins and APIs from Handsontable for more customization options.
    For example.
    {
    selectionMode: 'single'
}
    See more examples at Spreadsheet Deep Customizations.

Data & validation

Data and validation options ensure that the input values meet the required standards and can be saved and retrieved accurately from the data store.

Validation

Validation settings ensure that the input values meet the required standards before being saved. This helps maintain data integrity and prevents errors.

Configure the following fields:

  • Validator: Attach a Validator plugin to validate the input value. Validation occurs whenever a form is submitted except when submitted as Save as Draft.
    Available Options:
    • BeanShell (Multirow)
    • Grid
  • Min Number of Row Validation (Integer): Defines the minimum number of visible rows required to save the form. Use Regular Expression or Grid Validator to prevent saving empty rows.
  • Max Number of Row Validation (Integer): Defines the maximum number of visible rows required to save the form. Use Regular Expression or Grid Validator to prevent saving empty rows.
  • Error Message: An error message is to be shown when the row requirements set above are not met.

Data store

The Data Store settings allow you to customize how data is retrieved and saved within the spreadsheet element. This ensures that your data is managed efficiently and accurately.

Configure the following fields:

  • Load Data From: Allows you to customize the method for data retrieval to populate the Advanced Grid in the form.
  • Save Data To: Allows you to customize the method of saving the Advanced Grid row records.
    This option is empty by default. An empty data store means the Advanced Grid records will be saved/loaded in JSON format in the parent form & database table. See the list of available Form Data Store.
    The recommended data store is the Multiple Form Row so that each record is saved into a child database table via a subform definition. The data store will update the foreign key parent id in each child record to point to the parent database table.

Supported formula functions

The Spreadsheet element implements a subset of Excel formula functions. For detailed usage, see Excel Formula Functions.

Function Description
ABS(number) Returns the absolute value of a number.
ACOS(number) Returns the arccosine of a number.
ACOSH(number) Returns the inverse hyperbolic cosine of a number.
ACOT(number) Returns the arccotangent of a number.
ACOTH(number) Returns the inverse hyperbolic cotangent of a number.
ADD(num1, num2) Adds two numbers.
AGGREGATE(function_num, options, ref1, ref2) Returns an aggregate in a list or database.
AND(logical1, [logical2], ...) Returns TRUE if all of its arguments are TRUE.
ARABIC(text) Converts a Roman numeral to an Arabic numeral.
ARGS2ARRAY(arg1, [arg1], ...) Converts arguments to an array.
ASIN(number) Returns the arcsine of a number.
ASINH(number) Returns the inverse hyperbolic sine of a number.
ATAN(number) Returns the arctangent of a number.
ATAN2(number_x, number_y) Returns the arctangent of the quotient of its arguments.
ATANH(number) Returns the inverse hyperbolic tangent of a number.
AVEDEV(number1, [number2], ...) Returns the average of the absolute deviations of data points from their mean.
AVERAGE(number1, [number2], ...) Returns the average of its arguments.
AVERAGEA(number1, [number2], ...) Returns the average of its arguments, including numbers, text, and logical values.
BASE(number, radix, min_length) Converts a number into a text representation with the given base.
BESSELI(x, n) Returns the modified Bessel function In(x).
BESSELJ(x, n) Returns the Bessel function Jn(x).
BESSELK(x, n) Returns the modified Bessel function Kn(x).
BESSELY(x, n) Returns the Bessel function Yn(x).
BETA.DIST(x, alpha, beta, cumulative, A, B) Returns the beta cumulative distribution function.
BETA.INV(probability, alpha, beta, A, B) Returns the inverse of the cumulative distribution function for a specified beta distribution.
BETADIST(x, alpha, beta, cumulative, A, B) Returns the beta cumulative distribution function.
BETAINV(probability, alpha, beta, A, B) Returns the inverse of the cumulative distribution function for a specified beta distribution.
BIN2DEC(number) Converts a binary number to decimal.
BIN2HEX(number, places) Converts a binary number to hexadecimal.
BIN2OCT(number, places) Converts a binary number to octal.
BINOM.DIST(successes, trials, probability, cumulative) Returns the individual term binomial distribution probability.
BINOM.DIST.RANGE(trials, probability, successes, successes2) Returns the probability of a trial result using a binomial distribution.
BINOM.INV(trials, probability, alpha) Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
BINOMDIST(successes, trials, probability, cumulative) Returns the individual term binomial distribution probability.
BITAND(number1, number2) Returns a bitwise AND of two numbers.
BITLSHIFT(number, shift) Shifts a number left by the specified number of bits.
BITOR(number1, number2) Returns a bitwise OR of two numbers.
BITRSHIFT(number, shift) Shifts a number right by the specified number of bits.
BITXOR(number1, number2) Returns a bitwise XOR of two numbers.
CEILING(number, significance, mode) Rounds a number up, to the nearest integer or to the nearest multiple of significance.
CEILINGMATH(number, significance, mode) Rounds a number up, to the nearest integer or to the nearest multiple of significance.
CEILINGPRECISE(number, significance, mode) Rounds a number up, to the nearest integer or to the nearest multiple of significance.
CHAR(number) Returns the character specified by the code number.
CHISQ.DIST(x, k, cumulative) Returns the chi-squared distribution.
CHISQ.DIST.RT(x, k) Returns the right-tailed probability of the chi-squared distribution.
CHISQ.INV(probability, k) Returns the inverse of the left-tailed probability of the chi-squared distribution.
CHISQ.INV.RT(p, k) Returns the inverse of the right-tailed probability of the chi-squared distribution.
CHOOSE(index_num, value1, [value2], ...) Chooses a value from a list of values.
CLEAN(text) Removes all non-printable characters from text.
CODE(text) Returns a numeric code for the first character in a text string.
COMBIN(number, number_chosen) Returns the number of combinations for a given number of items.
COMBINA(number, number_chosen) Returns the number of combinations with repetitions for a given number of items.
COMPLEX(real, imaginary, suffix) Converts real and imaginary coefficients into a complex number.
CONCATENATE(arg1, [arg1], ...) Joins several text items into one text item.
CONFIDENCE(alpha, standard_dev, size) Returns the confidence interval for a population mean.
CONFIDENCE.NORM(alpha, standard_dev, size) Returns the confidence interval for a population mean.
CONFIDENCE.T(alpha, standard_dev, size) Returns the confidence interval for a population mean using a Student's t distribution.
CONVERT(number, from_unit, to_unit) Converts a number from one measurement system to another.
CORREL(array1, array2) Returns the correlation coefficient between two data sets.
COS(number) Returns the cosine of a number.
COSH(number) Returns the hyperbolic cosine of a number.
COT(number) Returns the cotangent of a number.
COTH(number) Returns the hyperbolic cotangent of a number.
COUNT(arg1, [arg1], ...) Counts the number of cells that contain numbers.
COUNTA(arg1, [arg1], ...) Counts the number of cells that are not empty.
COUNTBLANK(arg1, [arg1], ...) Counts the number of empty cells in a range.
COUNTUNIQUE(arg1, [arg1], ...) Counts the number of unique values in a range.
COVARIANCE.P(array1, array2) Returns the population covariance, the average of the products of deviations for each data point pair in two data sets.
COVARIANCE.S(array1, array2) Returns the sample covariance, the average of the products of deviations for each data point pair in two data sets.
CSC(number) Returns the cosecant of an angle.
CSCH(number) Returns the hyperbolic cosecant of an angle.
CUMIPMT(rate, periods, value, start, end, type) Returns the cumulative interest paid on a loan between start period and end period.
CUMPRINC(rate, periods, value, start, end, type) Returns the cumulative principal paid on a loan between start period and end period.
DATE(year, month, day) Returns the serial number of a particular date.
DATEVALUE(date_text) Converts a date in the form of text to a serial number.
DAY(serial_number) Converts a serial number to a day of the month.
DAYS(end_date, start_date) Returns the number of days between two dates.
DAYS360(start_date, end_date, method) Calculates the number of days between two dates based on a 360-day year.
DB(cost, salvage, life, period, month) Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
DDB(cost, salvage, life, period, factor) Returns the depreciation of an asset for a specified period using the double-declining balance method.
DEC2BIN(number, places) Converts a decimal number to binary.
DEC2HEX(number, places) Converts a decimal number to hexadecimal.
DEC2OCT(number, places) Converts a decimal number to octal.
DECIMAL(number, radix) Converts a text representation of a number in a given base into a decimal number.
DEGREES(number) Converts radians to degrees.
DELTA(number1, number2) Tests whether two values are equal.
DEVSQ(number1, [number2], ...) Returns the sum of squares of deviations.
DIVIDE(dividend, divisor) Divides two numbers.
DOLLAR(number, decimals) Converts a number to text, using currency format.
DOLLARDE(dollar, fraction) Converts a dollar price expressed as a fraction into a dollar price expressed as a decimal number.
DOLLARFR(dollar, fraction) Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
E() Returns the base of the natural logarithm, e (~2.718).
EDATE(start_date, months) Returns the serial number of the date that is the indicated number of months before or after the start date.
EFFECT(rate, periods) Returns the effective annual interest rate.
EOMONTH(start_date, months) Returns the serial number of the last day of the month before or after a specified number of months.
EQ(value1, value2) Returns TRUE if the values are equal.
ERF(lower_bound, upper_bound) Returns the error function.
ERFC(x) Returns the complementary error function.
EVEN(number) Rounds a number up to the nearest even integer.
EXACT(text1, text2) Checks to see if two text values are identical.
EXPON.DIST(x, lambda, cumulative) Returns the exponential distribution.
EXPONDIST(x, lambda, cumulative) Returns the exponential distribution.
F.DIST(x, d1, d2, cumulative) Returns the F probability distribution.
F.DIST.RT(x, d1, d2) Returns the right-tailed probability of the F distribution.
F.INV(probability, d1, d2) Returns the inverse of the F probability distribution.
F.INV.RT(p, d1, d2) Returns the inverse of the right-tailed F distribution.
FACT(number) Returns the factorial of a number.
FACTDOUBLE(number) Returns the double factorial of a number.
FALSE() Returns the logical value FALSE.
FDIST(x, d1, d2, cumulative) Returns the F probability distribution.
FDISTRT(x, d1, d2) Returns the right-tailed probability of the F distribution.
FIND(find_text, within_text, position) Finds one text value within another (case-sensitive).
FINV(probability, d1, d2) Returns the inverse of the F probability distribution.
FINVRT(p, d1, d2) Returns the inverse of the right-tailed F distribution.
FISHER(x) Returns the Fisher transformation.
FISHERINV(y) Returns the inverse of the Fisher transformation.
FIXED(number, decimals, no_commas) Rounds a number to a specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
FLOOR(number, significance) Rounds a number down, toward zero.
FORECAST(x, data_y, data_x) Returns a value along a linear trend.
FREQUENCY(data, bins) Returns a frequency distribution as a vertical array.
FV(rate, periods, payment, value, type) Returns the future value of an investment.
FVSCHEDULE(principal, schedule) Returns the future value of an initial principal after applying a series of compound interest rates.
GAMMA(number) Returns the gamma function value.
GAMMA.DIST(value, alpha, beta, cumulative) Returns the gamma distribution.
GAMMA.INV(probability, alpha, beta) Returns the inverse of the gamma cumulative distribution.
GAMMADIST(value, alpha, beta, cumulative) Returns the gamma distribution.
GAMMAINV(probability, alpha, beta) Returns the inverse of the gamma cumulative distribution.
GAMMALN(number) Returns the natural logarithm of the gamma function, Γ(x).
GAMMALN.PRECISE(x) Returns the natural logarithm of the gamma function, Γ(x).
GAUSS(z) Returns 0.5 less than the standard normal cumulative distribution.
GCD(GCD) Returns the greatest common divisor.
GEOMEAN(number1, [number2], ...) Returns the geometric mean.
GESTEP(number, step) Tests whether a number is greater than a threshold value.
GROWTH(known_y, known_x, new_x, use_const) Returns values along an exponential trend.
GTE(num1, num2) Returns TRUE if the first number is greater than or equal to the second number.
HARMEAN(number1, [number2], ...) Returns the harmonic mean.
HEX2BIN(number, places) Converts a hexadecimal number to binary.
HEX2DEC(number) Converts a hexadecimal number to decimal.
HEX2OCT(number, places) Converts a hexadecimal number to octal.
HOUR(serial_number) Converts a serial number to an hour.
HTML2TEXT(value) Converts HTML to plain text.
HYPGEOM.DIST(x, n, M, N, cumulative) Returns the hypergeometric distribution.
HYPGEOMDIST(x, n, M, N, cumulative) Returns the hypergeometric distribution.
IF(test, then_value, otherwise_value) Specifies a logical test to perform.
IMABS(inumber) Returns the absolute value (modulus) of a complex number.
IMAGINARY(inumber) Returns the imaginary coefficient of a complex number.
IMARGUMENT(inumber) Returns the argument θ of a complex number.
IMCONJUGATE(inumber) Returns the complex conjugate of a complex number.
IMCOS(inumber) Returns the cosine of a complex number.
IMCOSH(inumber) Returns the hyperbolic cosine of a complex number.
IMCOT(inumber) Returns the cotangent of a complex number.
IMCSC(inumber) Returns the cosecant of a complex number.
IMCSCH(inumber) Returns the hyperbolic cosecant of a complex number.
IMDIV(inumber1, inumber2) Returns the quotient of two complex numbers.
IMEXP(inumber) Returns the exponential of a complex number.
IMLN(inumber) Returns the natural logarithm of a complex number.
IMLOG10(inumber) Returns the base-10 logarithm of a complex number.
IMLOG2(inumber) Returns the base-2 logarithm of a complex number.
IMPOWER(inumber, number) Returns a complex number raised to an integer power.
IMPRODUCT(inumber1, inumber2, ...) Returns the product of from 2 to 255 complex numbers.
IMREAL(inumber) Returns the real coefficient of a complex number.
IMSEC(inumber) Returns the secant of a complex number.
IMSECH(inumber) Returns the hyperbolic secant of a complex number.
IMSIN(inumber) Returns the sine of a complex number.
IMSINH(inumber) Returns the hyperbolic sine of a complex number.
IMSQRT(inumber) Returns the square root of a complex number.
IMSUB(inumber1, inumber2) Returns the difference between two complex numbers.
IMSUM(inumber1, inumber2, ...) Returns the sum of complex numbers.
IMTAN(inumber) Returns the tangent of a complex number.
INT(number) Rounds a number down to the nearest integer.
INTERCEPT(known_y, known_x) Returns the intercept of the linear regression line.
INTERVAL(second) Converts seconds to a time interval.
IPMT(rate, period, periods, present, future, type) Returns the interest payment for an investment for a given period.
IRR(values, guess) Returns the internal rate of return for a series of cash flows.
ISBINARY(number) Returns TRUE if the value is binary.
ISBLANK(value) Returns TRUE if the value is blank.
ISEVEN(number) Returns TRUE if the number is even.
ISLOGICAL(value) Returns TRUE if the value is a logical value.
ISNONTEXT(value) Returns TRUE if the value is not text.
ISNUMBER(value) Returns TRUE if the value is a number.
ISODD(number) Returns TRUE if the number is odd.
ISOWEEKNUM(date) Returns the ISO week number of the year for a given date.
ISPMT(rate, period, periods, value) Returns the interest paid during a specific period of an investment.
ISTEXT(value) Returns TRUE if the value is text.
JOIN(array, separator) Joins elements of an array with a specified separator.
KURT(number1, [number2], ...) Returns the kurtosis of a data set.
LCM(number1, [number2], ...) Returns the least common multiple.
LEFT(text, number) Returns the leftmost characters from a text value.
LEN(text) Returns the number of characters in a text string.
LINEST(data_y, data_x) Returns the parameters of a linear trend.
LN(number) Returns the natural logarithm of a number.
LOG(number, base) Returns the logarithm of a number to a specified base.
LOG10(number) Returns the base-10 logarithm of a number.
LOGEST(data_y, data_x) Returns the parameters of an exponential trend.
LOGNORM.DIST(x, mean, sd, cumulative) Returns the cumulative lognormal distribution.
LOGNORM.INV(probability, mean, sd) Returns the inverse of the lognormal cumulative distribution.
LOGNORMDIST(x, mean, sd, cumulative) Returns the cumulative lognormal distribution.
LOGNORMINV(probability, mean, sd) Returns the inverse of the lognormal cumulative distribution.
LOWER(text) Converts text to lowercase.
LT(num1, num2) Returns TRUE if the first number is less than the second number.
LTE(num1, num2) Returns TRUE if the first number is less than or equal to the second number.
MATCH(lookupValue, lookupArray, matchType) Looks up values in a reference or array.
MAX(number1, [number2], ...) Returns the maximum value in a list of arguments.
MAXA(number1, [number2], ...) Returns the maximum value in a list of arguments, including numbers, text, and logical values.
MEDIAN(number1, [number2], ...) Returns the median of the given numbers.
MID(text, start, number) Returns a specific number of characters from a text string starting at the position you specify.
MIN(number1, [number2], ...) Returns the minimum value in a list of arguments.
MINA(number1, [number2], ...) Returns the minimum value in a list of arguments, including numbers, text, and logical values.
MINUS(num1, num2) Subtracts one number from another.
MINUTE(serial_number) Converts a serial number to a minute.
MIRR(values, finance_rate, reinvest_rate) Returns the modified internal rate of return for a series of periodic cash flows.
MOD(dividend, divisor) Returns the remainder from division.
MODE.MULT(number1, [number2], ...) Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data.
MODE.SNGL(number1, [number2], ...) Returns the most common value in a data set.
MONTH(serial_number) Converts a serial number to a month.
MROUND(number, multiple) Returns a number rounded to the desired multiple.
MULTINOMIAL(number1, [number2], ...) Returns the multinomial of a set of numbers.
MULTIPLY(factor1, factor2) Multiplies two numbers.
NE(value1, value2) Returns TRUE if the values are not equal.
NEGBINOM.DIST(k, r, p, cumulative) Returns the negative binomial distribution.
NEGBINOMDIST(k, r, p, cumulative) Returns the negative binomial distribution.
NETWORKDAYS(start_date, end_date, holidays) Returns the number of whole workdays between two dates.
NOMINAL(rate, periods) Returns the annual nominal interest rate.
NORM.DIST(x, mean, sd, cumulative) Returns the normal cumulative distribution.
NORM.INV(probability, mean, sd) Returns the inverse of the normal cumulative distribution.
NORM.S.DIST(z, cumulative) Returns the standard normal cumulative distribution.
NORM.S.INV(probability) Returns the inverse of the standard normal cumulative distribution.
NORMDIST(x, mean, sd, cumulative) Returns the normal cumulative distribution.
NORMINV(probability, mean, sd) Returns the inverse of the normal cumulative distribution.
NORMSDIST(x, mean, sd, cumulative) Returns the standard normal cumulative distribution.
NORMSINV(probability) Returns the inverse of the standard normal cumulative distribution.
NOT(logical) Reverses the logic of its argument.
NOW() Returns the serial number of the current date and time.
NPER(rate, payment, present, future, type) Returns the number of periods for an investment.
NPV(arg1, [arg2], ...) Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.
NUMBERS(arg1, [arg2], ...) Converts arguments to numbers.
NUMERAL(number, format) Formats a number according to a specified format.
OCT2BIN(number, places) Converts an octal number to binary.
OCT2DEC(number) Converts an octal number to decimal.
OCT2HEX(number, places) Converts an octal number to hexadecimal.
ODD(number) Rounds a number up to the nearest odd integer.
OR(logical1, [logical2], ...) Returns TRUE if any argument is TRUE.
PDURATION(rate, present, future) Returns the number of periods required for an investment to reach a specified value.
PEARSON(data_x, data_y) Returns the Pearson product moment correlation coefficient.
PERMUT(number, number_chosen) Returns the number of permutations for a given number of objects.
PERMUTATIONA(number, number_chosen) Returns the number of permutations with repetitions for a given number of objects.
PHI(x) Returns the value of the density function for a standard normal distribution.
PI() Returns the value of π.
PMT(rate, periods, present, future, type) Returns the periodic payment for an annuity.
POISSON.DIST(x, mean, cumulative) Returns the Poisson distribution.
POISSONDIST(x, mean, cumulative) Returns the Poisson distribution.
POW(base, exponent) Returns the result of a number raised to a power.
POWER(number, power) Returns the result of a number raised to a power.
PPMT(rate, period, periods, present, future, type) Returns the payment on the principal for an investment for a given period.
PRODUCT(number1, [number2], ... ) Multiplies its arguments.
PROPER(text) Capitalizes the first letter in each word of a text value.
PV(rate, periods, payment, future, type) Returns the present value of an investment.
QUOTIENT(numerator, denominator) Returns the integer portion of a division.
RADIANS(number) Converts degrees to radians.
RAND() Returns a random number between 0 and 1.
RANDBETWEEN(bottom, top) Returns a random number between the numbers you specify.
RATE(periods, payment, present, future, type, guess) Returns the interest rate per period of an annuity.
REFERENCE(context, reference) Returns a reference as text to a single cell in a worksheet.
REGEXEXTRACT(text, regular_expression) Extracts matching substrings according to a regular expression.
REGEXMATCH(text, regular_expression, full) Checks if text matches a regular expression.
REGEXREPLACE(text, regular_expression, replacement) Replaces matching substrings according to a regular expression.
REPLACE(text, position, length, new_text) Replaces characters within text.
REPT(text, number) Repeats text a given number of times.
RIGHT(text, number) Returns the rightmost characters from a text value.
ROMAN(number) Converts an Arabic numeral to Roman, as text.
ROUND(number, digits) Rounds a number to a specified number of digits.
ROUNDDOWN(number, digits) Rounds a number down, toward zero.
ROUNDUP(number, digits) Rounds a number up, away from zero.
RRI(periods, present, future) Returns an equivalent interest rate for the growth of an investment.
RSQ(data_x, data_y) Returns the square of the Pearson product moment correlation coefficient.
SEARCH(find_text, within_text, position) Returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive).
SEC(number) Returns the secant of an angle.
SECH(number) Returns the hyperbolic secant of an angle.
SECOND(serial_number) Converts a serial number to a second.
SERIESSUM(x, n, m, coefficients) Returns the sum of a power series based on the formula.
SIGN(number) Returns the sign of a number.
SIN(number) Returns the sine of an angle.
SINH(number) Returns the hyperbolic sine of a number.
SKEW(number1, [number2], ...) Returns the skewness of a distribution.
SKEW.P(number1, [number2], ...) Returns the population skewness of a distribution.
SKEWP(number1, [number2], ...) Returns the population skewness of a distribution.
SLN(cost, salvage, life) Returns the straight-line depreciation of an asset for one period.
SLOPE(data_y, data_x) Returns the slope of the linear regression line.
SPLIT(text, separator) Divides text around a specified character or string, and puts each fragment into a separate cell in the row.
SQRT(number) Returns a positive square root.
SQRTPI(number) Returns the square root of (number * π).
STANDARDIZE(x, mean, sd) Returns a normalized value.
STDEV.P(number1, [number2], ...) Calculates standard deviation based on the entire population given as arguments (ignores logical values and text).
STDEV.S(number1, [number2], ...) Estimates standard deviation based on a sample (ignores logical values and text in the sample).
STDEVA(number1, [number2], ...) Calculates standard deviation based on the entire population, including logical values and text.
STDEVP(number1, [number2], ...) Calculates standard deviation based on the entire population, ignoring logical values and text.
STDEVPA(number1, [number2], ...) Calculates standard deviation based on the entire population, including logical values and text.
STDEVS(number1, [number2], ...) Estimates standard deviation based on a sample, ignoring logical values and text.
STEYX(data_y, data_x) Returns the standard error of the predicted y-value for each x in the regression.
SUBSTITUTE(text, old_text, new_text, occurrence) Substitutes new text for old text in a text string.
SUBTOTAL(function_code, ref1) Returns a subtotal in a list or database.
SUM(number1, [number2], ...) Adds its arguments.
SUMPRODUCT(array1, [array2], [array3], ...) Returns the sum of the products of corresponding array components.
SUMSQ(number1, [number2], ...) Returns the sum of the squares of the arguments.
SUMX2MY2(array_x, array_y) Returns the sum of the difference of squares of corresponding values in two arrays.
SUMX2PY2(array_x, array_y) Returns the sum of the sum of squares of corresponding values in two arrays.
SUMXMY2(array_x, array_y) Returns the sum of squares of differences of corresponding values in two arrays.
SWITCH(expression, value1, result1, [value2, result2], [value3, result3], ..., [default]) Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
SYD(cost, salvage, life, period) Returns the sum-of-years' digits depreciation of an asset for a specified period.
T(value) Converts its arguments to text.
T.DIST(x, df, cumulative) Returns the Student's t-distribution.
T.DIST.2T(x, df) Returns the Student's t-distribution.
T.DIST.RT(x, df) Returns the right-tailed Student's t-distribution.
T.INV(probability, df) Returns the inverse of the Student's t-distribution.
T.INV.2T(probability, df) Returns the inverse of the two-tailed Student's t-distribution.
TAN(number) Returns the tangent of a number.
TANH(number) Returns the hyperbolic tangent of a number.
TBILLEQ(settlement, maturity, discount) Returns the bond-equivalent yield for a Treasury bill.
TBILLPRICE(settlement, maturity, discount) Returns the price per $100 face value for a Treasury bill.
TBILLYIELD(settlement, maturity, price) Returns the yield for a Treasury bill.
TDIST(x, df, cumulative) Returns the Student's t-distribution.
TDIST2T(x, df) Returns the Student's t-distribution.
TDISTRT(x, df) Returns the right-tailed Student's t-distribution.
TEXT(value, format) Formats a number and converts it to text.
TIME(hour, minute, second) Returns the serial number of a particular time.
TIMEVALUE(time_text) Converts a time in the form of text to a serial number.
TINV(probability, df) Returns the inverse of the Student's t-distribution.
TINV2T(probability, df) Returns the inverse of the two-tailed Student's t-distribution.
TODAY() Returns the serial number of today's date.
TRANSPOSE(matrix) Returns the transpose of an array.
TREND(data_y, data_x, new_data_x) Returns values along a linear trend.
TRIM(text) Removes all spaces from text except for single spaces between words.
TRUE() Returns the logical value TRUE.
TRUNC(number, digits) Truncates a number to an integer by removing the fractional part of the number.
UNICHAR(number) Returns the Unicode character that is referenced by the given numeric value.
UNICODE(text) Returns the number (code point) corresponding to the first character of the text.
UNIQUE(arg1, [arg2], ...) Returns the unique values in a list or range.
UPPER(text) Converts text to uppercase.
VALUE(text) Converts a text argument to a number.
VAR.P(number1, [number2], ...) Calculates variance based on the entire population given as arguments.
VAR.S(number1, [number2], ...) Estimates variance based on a sample.
VARA(number1, [number2], ...) Calculates variance based on the entire population, including logical values and text.
VARP(number1, [number2], ...) Calculates variance based on the entire population, ignoring logical values and text.
VARPA(number1, [number2], ...) Calculates variance based on the entire population, including logical values and text.
VARS(number1, [number2], ...) Estimates variance based on a sample, ignoring logical values and text.
WEEKDAY(serial_number, return_type) Converts a serial number to a day of the week.
WEEKNUM(serial_number, return_type) Converts a serial number to a number representing where the week falls numerically with a year.
WEIBULL.DIST(x, alpha, beta, cumulative) Returns the Weibull distribution.
WEIBULLDIST(x, alpha, beta, cumulative) Returns the Weibull distribution.
WORKDAY(start_date, days, holidays) Returns the date's serial number before or after a specified number of workdays.
XNPV(rate, values, dates) Returns the net present value for a schedule of cash flows that is not necessarily periodic.
XOR(logical1, [logical2], ...) Returns a logical exclusive OR of all arguments.
YEAR(serial_number) Converts a serial number to a year.
YEARFRAC(start_date, end_date, basis) Returns the year fraction representing the number of days between start_date and end_date.

Special function

FORMDATA(formDefId, primaryKey, fieldName)

in Spreadsheet Formula column, where:

  • formDefId: The form id of the source form containing the setup data to 'pull' from.
  • primaryKey: The dependent pulldown field id in your spreadsheet design, that provides the WHERE condition to search the data to populate.
  • fieldName: The field ID of the source form to populate in this spreadsheet column based on the above primaryKey value.

Use this function to pull and populate a spreadsheet cell based on available data in other forms.

For example, to make a spreadsheet pull and display the population value after the user picks the city name (pulldown menu), use FORMDATA("city_formId",select_city,"population") where select_city is the first field id in your spreadsheet.

Related documentation

This section links additional resources and guides to help you further customize and enhance your spreadsheet elements.

Download sample app 

Download the demo app for Spreadsheet:
Created by Julieth Last modified by Aadrian on Dec 13, 2024
Tags:
Previous Signature NextSubform