Date and time functions
Function  Return type  Description  Example  

TODAY  Date  Returns the current date 
Input: TODAY() Output: 20200722T08:00:25.406Z 

NOW  Date  Returns the current date and time. 
Input: Now() Output: 20200722T08:06:33.661Z 

STRTODATE  Date  This function converts String date to Date object similar to TODAY() function. 
Input: STRTODATE(“03122019”, “DDMMYYYY”) Output: 20191203T18:30:00.000Z 

DATE  Date  Returns the serial date value of the particular parameters 
Input: DATE(1969, 7, 20) Output: 19690720T18:30:00.000Z 

STRTOUNIX()  Unix time  This function converts string date to Unix time stamp. 
Input: STRTOUNIX(“03122019 17:30”,“DDMMYYYY HH:mm”) Output: 1575394200000 

TIMEOFFSET  Unix time  This function provides Time zone offset in milliseconds. 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(“1969720”) 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 360day year (twelve 30day 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”: “NonEssentials”, “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