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