Important Functions

Date and time functions

Function Return type Description Example
TODAY Date Returns the current date Input: TODAY()
Output: 2020-07-22T08:00:25.406Z
NOW Date Returns the current date and time. Input: Now()
Output: 2020-07-22T08:06:33.661Z
STRTODATE Date This function converts String date to Date object similar to TODAY() function. Input: STRTODATE(“03-12-2019”, “DD-MM-YYYY”)
Output: 2019-12-03T18:30:00.000Z
DATE Date Returns the serial date value of the particular parameters Input: DATE(1969, 7, 20)
Output: 1969-07-20T18:30:00.000Z
STRTOUNIX() Unix time This function converts string date to Unix time stamp. Input: STRTOUNIX(“03-12-2019 17:30”,“DD-MM-YYYY HH:mm”)
Output: 1575394200000
TIMEOFFSET Unix time This function provides Time zone offset in milli-seconds. This can either be positive or negative based on the device’s timezone Input: TIMEOFFSET()
Output: 19800000 or -19800000
based on time zone"
UNIXTIME Unix time Excel time to Unix time converter. This function will convert given Excel time to provide Unix time as a result. Input: UNIXTIME(25571)
Output: 172800000
DATEVALUE Excel Date This function converts a date that is stored as text to a serial number. Input: DATEVALUE(“1969-7-20”)
Output: 25403.99386574074
EOMONTH Excel Date Returns the serial number of the last day of the month before or after a specified number of months Input: EOMONTH(“5/20/1969”, 1)
Output: 25383.99386574074
EDATE Excel Date Returns the serial number of the date that is the indicated number of months before or after the start date Input: EDATE(“7/20/1969”, 1)
Output: 25434.99386574074
EXCELTIME Excel Date Unix time to Excel converter. This will convert the given Unix time in milliseconds and give Excel time in days as a result. Input: EXCELTIME(172800000)
Output: 25571
DATEDIF Number Calculates the number of days, months, or years between two dates. Input: DATEDIF(TODAY(), “09/09/2020”)
Output: 48
TIMESTR String This is a custom function that converts date (unix format) to readable time format, say, 09:58 PM. Input: TIMESTR(TODAY(),“hh:mm ss A”)
Output: 06:54 20 AM
DATESTR String This is a custom function that converts date (unix format) to readable date format, say, 29/Apr/2019. Input: DATESTR(TODAY(),“MM/DD/YYYY”)
Output: 07/22/2020
DAY Number Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31 Input: DAY(DATE(“2019”,“05”,“24”))
Output: 24
DAYS Number Returns the number of days between two dates. Input: DAYS(“2019/05/24”,“2019/05/22”)
Output: 2
DAYS360 Number This function returns the number of days between two dates based on a 360-day year (twelve 30-day months). Input: DAYS360(“03/22/2019”,“05/24/2019”,0)
Output: 60
HOUR Number Returns the hour as a number from 0 to 23 Input: HOUR(TODAY())
Output: 11 (assuming that time is 11:30 AM at that given moment)
MINUTE Number Converts a serial number to a minute Input: MINUTE(TODAY())
Output: 30 (for example if time is 11:30 AM at that given moment)
OR
Input: MINUTE(535543)
Output: 38
MONTH Number Converts a serial number to a month Input: MONTH(“7/24/2020”)
Output: 7
OR
Input: MONTH(78866)
Output: 12
NETWORKDAYS Number Returns the number of whole workdays between two dates Input: NETWORKDAYS(“7/16/2020”,“7/30/2020”)
Output: 11
TIMEVALUE Number Converts a time in the form of text to a serial number Input: TIMEVALUE(TODAY())
Output: 0.538912037037037
WEEKDAY Number Converts a serial number to a day of the week Input: WEEKDAY(“7/2/2020”, 2)
Output: 4
WEEKNUM Number Converts a serial number to a number representing where the week falls numerically with a year Input: WEEKNUM(“8/20/2020”, 1)
Output: 34
YEAR Number Converts a serial number to a year Input: YEAR(2455)
Output: 1906
YEARFRAC Number Returns the year fraction representing the number of whole days between start_date and end_date Input: YEARFRAC(“7/16/1969”, “7/16/2019”, 1)
Output: 50

Geolocation functions

Function Return type Description Example
DISTANCE Number Is used to find the distance(in Metre) between two given points i.e, destinationLocation and sourceLocation with their ( Latitude, Longitude Values) in a CSV format i,e, (lat,lng). Input: DISTANCE( “45.7458,74.9864” , “40.7486,-73.9864”
Output: 9915192.888361111
ISPLACENEARBY Boolean It is used to check whether distance between source and destination location is within the given distance. Input: ISPLACENEARBY(“45.7458,74.9864” , “40.7486,-73.9864”,1000000)
Output: No
PLACESNEARBY List This function returns those destinations location pairs that are within the distance from the source location. Input: PLACESNEARBY([“70.5,80.5”,“10.5,25.5”, “11.5,21.5”], “10.5,20.5”,1000, “km”)
Output: [“10.5,25.5”,“11.5,21.5”]

List type functions

Function Return type Description Example
SELECT Any Based on Given Index, return a value from provided list of values as array as second parameter or from list of parameters. Index values starts from 1 Input: SELECT([“A”,“B”,“C”], [“AA”,“AB”,“AC”], [“XA”,“XB”,“XC”], 3)
Output: [“XA”,“XB”,“XC”]
SELECTANY Any This function gives one random value between the input entered. Input: SELECTANY([“A”,“B”,“C”])
Output: For the above options, the result can be anything randomly whenever it is executed.
INDEX Number It is used to get Index of an Item in an Array. Useful to search an item in an Array and get its index. Index starts from 1. Input: INDEX([“A”,“B”,“C”,“D”],“B”)
Output: 2
INDEXVALUE Any It is used to get Item in an Array for a given Index. Index starts from 1. Input: INDEXVALUE([“A”,“B”,“C”,“D”],4)
Output: D
UNIQUEOBJECTS List It is used to get Unique values based on given Column Index. For duplicate values for the given index, the first value is returned and rest all duplicates are filtered out. ColumnIndexNumber starts from 1. Input: UNIQUEOBJECTS( LOOKUP([Prod_doctest.Prod_type, Prod_doctest.Quantity, Prod_doctest.Price]) , 2).
Output: The Quantity in the sheet has 2 rows with same quantity as 5, out put shows only unique values from quantity.
[{“Prod_type”: “”, “Quantity”: “”, “Price”: “” }, {“Prod_type”: “Apparels”, “Quantity”: 4, “Price”: 500 }, {“Prod_type”: “Non-Essentials”, “Quantity”: 5, “Price”: 1350},{“Prod_type”: “Essentials”, “Quantity”: 9,“Price”: 670}]
MERGE List It is used to Merge Multiple Arrays into single array. Input: MERGE([2,3,5],7,8,9)
Output: [2,3,5,7,8,9]
FLATTEN List It is used to Flatten Array of Multiple Arrays into single array. Commonly used for Multiselect column from sheets Input: FLATTEN(LOOKUP([AllColumnTypes. Multiselect]))
Output: Array of all values from the multiselect column.
RANGE List Given two numbers this function will return the integers between these two values entered Input: RANGE(5,10)
Output: 5,6,7,8,9,10

String functions

Function Return type Description Example
STRING String Converts Number to Text. Can be a Number OR Array of Numbers as Input and gives a String OR Array of Strings as Output. Input: STRING([1,2])
Output: [“1”,“2”]
CONCATENATE String Is used to join two or more strings or values from controls. Input: CONCATENATE(“DRONA”, “HQ”, " Welcomes", " You!!")
Output: DRONAHQ Welcomes You!!
LOWER String Converts given text to Lower case Input: LOWER(“CONTACT LIST”)
Output: contact list
UPPER String Converts given text to upper case Input: UPPER(“contact list”)
Output: CONTACT LIST
DATESTR String This is a custom function that converts date (unix format) to readable date format, say, 29/Apr/2019. Input: DATESTR( datepicker1 , “MM/DD/YYYY”) where date selected in datepicker1 control is 29th April 2019,
Output: 04/29/2019

Numeric functions

Function Return type Description Example
NUMBER Number Is used to convert Text to Number. It takes a Text OR Array of Texts as Input and gives a Number OR Array of Numbers as Output. Input: NUMBER([“1”,“2”])
Output: [1,2] which is numeric.
SUM Number Provides the addition of input values / arguments. It can be the sum of columns or rows in a range or the values that are input from controls. Input: SUM(1,2,5)
Output: 8
Input: If you want from sheet column then SUM(LOOKUP([Prod_doctest.Quantity])).
Output: Total of the Quanity column of sheet
AVERAGE Number Provides the average of the input values / arguments Input: AVERAGE(1,4,5,6,3)
Output: 3.8
Input: If from a sheet AVERAGE(LOOKUP([Prod_doctest.Quantity]))
Output: Will return the average of the Quanity column.
MAX Number Returns the maximum value from among the list of values/arguments Input: Max(1,4,5,6,3)
Output: 6.
Input: If from sheet then MAX(LOOKUP([Prod_doctest.Quantity]))
Output: Will return the max value from the respective column
MIN Number Returns the minimum value from among the list of values / arguments Input: MIN(1,4,5,6,3)
Output: 6.
Input: From a sheet it can be done as MIN(LOOKUP([Prod_doctest.Quantity]))
Output: Returns the minimum value from the respective column.
ROUND Number Rounds a number to a specified number of digits Input: ROUND(12.456829,3)
Output: 12.457
ROUNDUP Number Rounds a number up, away from zero Input: ROUNDUP(12.456829,5)
Output: 12.45683
ROUNDDOWN Number Rounds a number down, toward zero Input: ROUNDDOWN(12.456829,5)
Output: 12.45682

Boolean functions

Function Return type Description Example
TRUE Boolean Returns the logical value TRUE Input: If comparing value in cities control then : IF(cities = “NY” , TRUE() , FALSE())
Output: TRUE only when the cities control has ‘NY’ as input
FALSE Boolean Returns the logical value FALSE Input: If comparing value in cities control then : IF(cities = “NY” , TRUE() , FALSE())
Output: FALSE when the cities control has anything other than ‘NY’ as input

Logical functions

Function Description Example
IF Specifies a logical test to perform and returns True or False Input: IF( cities = “NY” , TRUE , FALSE)
Output: TRUE only when Cities control has value as NY
AND Returns TRUE if all of its arguments are TRUE Input: AND(1=1,8>7) (All true)
Output: TRUE as both conditions satisfied
OR Returns TRUE if either of its arguments are TRUE Input: OR(1=2,8>7) (One of the condition is true)
Output: TRUE as one of the condition is satisfied
XOR Returns a logical exclusive OR of all arguments Input: XOR(1=2, 3>5) (All false)
Output: FALSE

Sheet functions

Function Description
LOOKUP Is used to fetch data from the sheet which can also be based on conditions
DLOOKUP DLOOKUP is used to retrieve data from any sheet similar to LOOKUP.However, you can use several advanced functions like DAND, DOR which are not possible in LOOKUP.
LEFTJOIN Perform Left Join on given two Sheets with LOOKUP/DLOOKUP formula similar to that in SQL which returns all rows from the left sheet, even if there are no matches in the right sheet.
RIGHTJOIN Perform Right Join on given two Sheets with LOOKUP/DLOOKUP formula. This works similar to the right join in SQL which returns all rows from the right sheet, even if there are no matches in the left sheet.
FULLJOIN Perform Full Join on given two Sheets with LOOKUP/DLOOKUP formula. This works similar to the full join in SQL which contain all records from both the sheets and fill in NULLs for missing matches on either side.
INNERJOIN Perform Inner Join on given two Sheets with LOOKUP/DLOOKUP formula. This works similar to the inner join in SQL which returns rows when there is a match in both tables.

Key Value storage functions

Function Description Example
SETTOKV Using this function data can be set to KV storage (key Value storage) which is a persistent data store. Input: SETTOKV(“SharedKey”,“Value to Save”, 1, 1)
GETFROMKV Using this function data can be fetched from KV storage which is a persistent data store. This will get data from KV storage using the key that was used during SETTOKV function. Input: GETFROMKV(“UserOpen”)
Output:“Value to Save”
DELETEFROMKV Using this function you can delete data stored in KV storage with a specific key which was used to store data using SETTOKV function. DELETEFROMKV(“UserOpen”)
CLEARKV Using this function you can clear all data from KV storage. CLEARKV()
List of functions