LOOKUP

LOOKUP is used to retrieve data from any sheet. 

Definition

 LOOKUP([Sheetname.columnname],filter,sorting,order)

 LOOKUP([Sheetname.columnname AS "<My Column Name>"],filter,sorting,order)

Where, 

Sheetname is the sheet's name from where you want to fetch the data; columnname is the column whose data you want to fetch.

filter is used to filter results in the sheet. Filter has three components, i.e. Column to filter, filter operator & filter criteria. It has the following format - Sheetname.columnname1="Studio"

sorting is used for sorting the result set on the basis of the column specified in either ascending ("ASC") or descending ("DESC") order;

order is the order in which you want to sort the data fetched from the sheet. It can have value either ASC or DESC which needs to be put within double quotes "".

AS This is used to provide custom/user friendly name to refer to this column. This is also useful to bind data from Multiple LOOKUP and assign to a Control.

Example 1

LOOKUP([BaseSheet.name.first_name],BaseSheet.email="olag.gentil@house.gov")

Example 2

LOOKUP([BaseSheet.company AS "ORG", BaseSheet.department AS "DEP"])


For More Info - Refer here

-----------------------------------------------------------------------------------------------------------------------------

DLOOKUP

DLOOKUP is much more advanced than LOOKUP which is used to retrieve data from any sheet. You can use All supported formula's in DLOOKUP's filter which is not possible in LOOKUP function which supports only AND and OR function. For Multiple conditions in Filter you can use DAND and DOR functions which are same as AND and OR function which are supported by LOOKUP

Definition

 DLOOKUP([Sheetname.columnname],filter,sorting,order)

 DLOOKUP([Sheetname.columnname AS "<My Column Name>"],filter,sorting,order)

Example 1

DLOOKUP([BaseSheet.name.first_name],BaseSheet.email="olag.gentil@house.gov")

Example 2

DLOOKUP([BaseSheet.company AS "ORG", BaseSheet.department AS "DEP"])

Example 3

DLOOKUP([BaseSheet.company AS "ORG", BaseSheet.department AS "DEP"],DAND(BaseSheet.name = "OLAD", BaseSheet.emal $cn ".gov"))

-----------------------------------------------------------------------------------------------------------------------------

DATESTR

This is a custom function that converts date (unix format) to readable date format, say, 29/Apr/2019.

Definition

 DATESTR(date,format)

Example 1

DATESTR(TODAY(),"MM/DD/YYYY")

Output

12/12/2019

Example 2

DATESTR(datepicker,"DD YYYY")

For More Info - Refer here

-----------------------------------------------------------------------------------------------------------------------------

STRTODATE

This function converts String date to Date object similar to TODAY() function. 

Definition

 STRTODATE(strDate,format)

Where,
strDate - Date in string format
format - Given String date's format

Example 1

STRTODATE("03-12-2019 17:30","DD-MM-YYYY HH:mm")

Output

2019-12-02T18:30:00.000Z

For More Info on Format - Refer here

-----------------------------------------------------------------------------------------------------------------------------

STRTOUNIX

This function converts string date to Unix time stamp.

Definition

 STRTOUNIX(strDate,format)

Where,
strDate - Date in string format
format - Given String date's format

Example 1

STRTODATE("03-12-2019 17:30","DD-MM-YYYY HH:mm")

Output

1575394200000

Example 2

STRTODATE("03-12-2019","DD-MM-YYYY")

Output

1575331200000

For More Info on Format - Refer here

-----------------------------------------------------------------------------------------------------------------------------

DATEDIF

Calculates the number of days, months, or years between two dates.

Definition

 DATEDIF(start_date,end_date,unit)

Example 

DATEDIF( datepicker , datepicker1 , "D")

For More Info - Refer here

-----------------------------------------------------------------------------------------------------------------------------

TIMESTR

This is a custom function that converts date (unix format) to readable time format, say, 09:58 PM.

Definition

 TIMESTR(date,format)

Example 

TIMESTR(TODAY(),"HH:mm ss a")

Output

09:06 22 am

-----------------------------------------------------------------------------------------------------------------------------

STRING

It is used to convert NUMBER TO TEXT. This function takes a Number OR Array of Numbers as Input and gives a String OR Array of Strings as Output. Basically this is usefull when you have a NUMBER and you want to apply some function on it which are applicable to TEXT type but not applicable to NUMBER type. For ex. - CONCATENATE() function is available for TEXT type but not available for NUMBER.

Definition

 STRING(number)
 STRING([number1, number2, number 3, ....])

Example 1 

STRING(1)

Output

"1"

Example 2

STRING([1,2])

Output

["1","2"]

-----------------------------------------------------------------------------------------------------------------------------

NUMBER

It is used to convert TEXT TO NUMBER. This function takes a Text OR Array of Texts as Input and gives a Number OR Array of Numbers as Output. Basically this is usefull when you have a TEXT and you want to apply some function on it which are applicable to NUMBER type but not applicable to TEXT type. For ex. - SUM() function is available for NUMBER type but not available for TEXT.

Definition

 NUMBER(text)
 NUMBER([text1, text2, text 3, ....])

Example 1 

NUMBER("1")

Output

1

Example 2

NUMBER(["1","2"])

Output

[1,2]

-----------------------------------------------------------------------------------------------------------------------------

SELECT

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

Definition

SELECT(val1,val2,....,valn, index)
SELECT([val1,val2,....,valn], index)

Example 1

SELECT("A","B","C", 2)

Output

B

Example 2

SELECT(["A","B","C"], 2)

Output

B

-----------------------------------------------------------------------------------------------------------------------------

SELECTANY

This function gives one random value between the input entered.This function accepts a integer, string, array and values seperated by comma.

Definition

SELECTANY(val1,val2,....,valn)
SELECTANY([val1,val2,....,valn])

Example 1

SELECTANY("A","B","C")

Output

B

Example 2

SELECTANY(["A","B","C"])

Output

A

-----------------------------------------------------------------------------------------------------------------------------

INDEX

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.

Definition

 INDEX([text1, text2, text 3, ....], textToFind)

Example 1 

INDEX(["A","B","C","D"],"B")

Output

2

-----------------------------------------------------------------------------------------------------------------------------

INDEXVALUE

It is used to get Item in an Array for a given Index. Index starts from 1.

Definition

 INDEXVALUE([text1, text2, text 3, ....], indexNumber)

Example 1 

INDEXVALUE(["A","B","C","D","E"],2)

Output

B

-----------------------------------------------------------------------------------------------------------------------------

UNIQUEOBJECTS

It is used to get Unique values based on given Column Index. In case of Duplicate Column values for the given index, the first value will be returned and rest all duplicates will be filtered out. ColumnIndexNumber starts from 1.

Definition

 UNIQUEOBJECTS([column1, column2, column3, ....], ColumnIndexNumber)

Example 1 

UNIQUEOBJECTS(LOOKUP([ProfileViews.unique_id,ProfileViews.Name]),2)

Output

[{"unique_id":1,"Name":"Fenil"},{"unique_id":2,"Name":"Ramanuj"},{"unique_id":3,"Name":"Divyesh D"},{"unique_id":4,"Name":"Rishabh"},{"unique_id":5,"Name":"Rahul G"},{"unique_id":6,"Name":"Brijesh"},{"unique_id":7,"Name":"Ronak"},{"unique_id":8,"Name":"Luv"}]

-----------------------------------------------------------------------------------------------------------------------------

MERGE

It is used to Merge Multiple Arrays into single array. It can also be use to merge individuals items into single array.

Definition

 MERGE([item1, item2, item3, ....], [item4, item5, item6, ....])
 MERGE(item1, item2, item3, ...)
 MERGE([item1, item2, item3, ....], item4, item5, ...)

Example 1 

MERGE( [2,3, 5],[2,4,5])

Output

[2,3,5,2,4,5]

Example 2

MERGE(2,3,5)

Output

[2,3,5]

Example 3

MERGE([2,3,5],7,8,9)

Output

[2,3,5,7,8,9]

-----------------------------------------------------------------------------------------------------------------------------

FLATTEN

It is used to Flatten Array of Multiple Arrays into single array. 

Definition

 FLATTEN( [ array1, array2, .... ] )

Example 1 

FLATTEN([[2,3,5],[2,4,5]])

Output

[2,3,5,2,4,5]

-----------------------------------------------------------------------------------------------------------------------------

UNIQUEIDGENERATOR

It is used to generate a UNIQUE ID. You can also optionally pass USERID as a parameter to this function, in both cases, a unique id will be generated.
You can also give an Array as an Input and the output will be an Array of Unique IDs.
UserID can be Number or Alphabets.

Definition

 UNIQUEIDGENERATOR()
 UNIQUEIDGENERATOR(userID)
 UNIQUEIDGENERATOR( [userID1, userID2, .... ] )

Example 1 

UNIQUEIDGENERATOR()

Output

507-829-228-1ds9u81ea

Example 2

UNIQUEIDGENERATOR("ABC")

Output

ABC-903-506-1ds9ua9o0

Example 3

UNIQUEIDGENERATOR(["AB", "CD"])

Output

["AB-549-438-1ds9uc0e7","CD-948-641-1ds9uc0e7"]

-----------------------------------------------------------------------------------------------------------------------------

RANGE

Given two numbers this function will return the integers between these two values entered

Definition

 RANGE(number1,number2)

Example

RANGE(5,10)

Output

[5,6,7,8,9,10]

-----------------------------------------------------------------------------------------------------------------------------

UNIXTIME

EXCEL TIME TO UNIX TIME CONVERTER. This function will convert Given Excel time and give Unix time as a result.
It also accepts optional Default value in case of incorrect ExcelTime is given as Input. This Function can also Accept Array of Excel time and give an array of Unix time as output. You can also choose the Input and Output Format in optional parameter.

** Unix time is calculated from 1 Jan, 1970. Excel time is calculated from 1, Jan, 1900.  

Definition

 UNIXTIME(ExcelTime)
 UNIXTIME(ExcelTime,defaultInputTime)
 UNIXTIME(ExcelTime,defaultInputTime,InputFormat, OutputFormat)

 UNIXTIME( [ ExcelTime1, ExcelTime2, ...] )
 UNIXTIME( [ ExcelTime1, ExcelTime2, ...] , defaultInputTime)
 UNIXTIME( [ ExcelTime1, ExcelTime2,...],defaultInputTime, InputFormat, OutputFormat)

Where,

defaultInputTime must be greater than or equal to 25569 and will be used in case of invalid Exceltime. defaultInputTime format will be same as InputFormat

Input Format & OutputFormat can be any one of the below -
"m"  => milliseconds
"s" => seconds
"d" => days
If no Input/Output format is given it will consider be default Input/Output format.
Default Input => "d" (days)
Default Output => "m" (milliseconds)*

Example 1 

UNIXTIME(25571)

Output

172800000

Example 2

UNIXTIME(255, 25569)

Output

0

Example 3

UNIXTIME(25571, 25569, "d","d")

Output

2

Example 4

UNIXTIME([25571,25575, 255], 25569, "d","d")

Output

[2,6,0]

-----------------------------------------------------------------------------------------------------------------------------

EXCELTIME

UNIX TIME TO EXCEL TIME CONVERTER. This will convert Given Unix time in milliseconds and give Excel time in days as a result.
It also accepts optional Default value in case of incorrect UnixTime is given as Input. This Function can also Accept Array of Unix time and give an array of Excel time as output. You can also choose the Input and Output Format in optional parameter.

** Unix time is calculated from 1 Jan, 1970. Excel time is calculated from 1, Jan, 1900.  

Definition

 EXCELTIME(UnixTime)
 EXCELTIME(UnixTime,defaultInputTime)
 EXCELTIME(UnixTime,defaultInputTime,InputFormat, OutputFormat)

 EXCELTIME( [ UnixTime1, UnixTime2, ...] )
 EXCELTIME( [ UnixTime1, UnixTime2, ...] , defaultInputTime)
 EXCELTIME( [ UnixTime1, UnixTime2,...],defaultInputTime, InputFormat, OutputFormat)

Where,

defaultInputTime will be used in case of invalid Unixtime. defaultInputTime format will be same as InputFormat

Input Format & OutputFormat can be any one of the below -
"m"  => milliseconds
"s" => seconds
"d" => days
If no Input/Output format is given it will consider be default Input/Output format.
Default Input => "m" (milliseconds)*
Default Output => "d" (days) 

Example 1 

EXCELTIME(172800000)

Output

25571

Example 2

EXCELTIME(-1,1)

Output

25569.000000011572

Example 3

EXCELTIME(2, 0, "d","d")

Output

25571

Example 4

EXCELTIME([2,6,0], 0, "d","d")

Output

[25571,25575,25569]

-----------------------------------------------------------------------------------------------------------------------------

DISTANCE

This function is useful 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). It also accepts optional defaultDistance parameter, which is returned in case there is any error is occurred for given source & destination Location.

Definition

 DISTANCE(destinationLocation,sourceLocation)
 DISTANCE(destinationLocation, sourceLocation, defaultDistance)
 DISTANCE([destinationLocation1,destinationLocation2,...],sourceLocation)
 DISTANCE([destinationLocation1,...],sourceLocation, defaultDistance)

Example 1 

DISTANCE( "45.7458,74.9864" , "40.7486,-73.9864")

Output

9915192.888361111

Example 2

DISTANCE( ["45.7458,74.9864","45.64,74.88"], "40.7486,-73.9864")

Output

[9915192.888361111,9922787.743762657]

Example 3

DISTANCE( ["45.7458","45.64,74.88"], "40.7486,-73.9864",100)

Output

[100,9922787.743762657]

-----------------------------------------------------------------------------------------------------------------------------

ISPLACENEARBY

It is used to check whether distance between source and destination location is within the given distance. It also accepts multiple destinations to check whether given source in within given distance or not. This function has optional inputUnit which basically denotes the distance format. If no inputUnit is specified, it will by default consider the given distance is in meters.

Definition

 ISPLACENEARBY(destinationLocation,sourceLocation, distance)
 ISPLACENEARBY(destinationLocation,sourceLocation, distance, inputUnit)
 ISPLACENEARBY([destinationLocation1, ...],sourceLocation, distance)
 ISPLACENEARBY([destinationLocation1, ...],sourceLocation, distance, inputUnit)

Where inputUnit can be any one of the below -
"m"  => meters
"km" => Kilimeters
"miles" => miles

Example 1 

ISPLACENEARBY("70.5,80.5","10.5,20.5",10000000)

Output

true

Example 2

ISPLACENEARBY("70.5,80.5","10.5,20.5",10000,"km")

Output

true

Example 3

ISPLACENEARBY(["70.5,80.5","10.5,25.5","11.5,21.5"],"10.5,20.5",1000, "km")

Output

[false,true,true]

-----------------------------------------------------------------------------------------------------------------------------

PLACESNEARBY

This function returns those destinations location pairs that are within the distance from the source location. This function has optional inputUnit which basically denotes the distance format. If no inputUnit is specified, it will by default consider the given distance is in meters.

Definition

 PLACESNEARBY(destinationLocation,sourceLocation, distance)
 PLACESNEARBY(destinationLocation,sourceLocation, distance, inputUnit)
 PLACESNEARBY([destinationLocation1, ...],sourceLocation, distance)
 PLACESNEARBY([destinationLocation1, ...],sourceLocation, distance, inputUnit)

Where inputUnit can be any one of the below -
"m"  => meters
"km" => Kilimeters
"miles" => miles

Example 1 

PLACESNEARBY("70.5,80.5","10.5,20.5",10000000)

Output

["70.5,80.5"]

Example 2

PLACESNEARBY("70.5,80.5","10.5,20.5",10000,"km")

Output

["70.5,80.5"]

Example 3

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"]

-----------------------------------------------------------------------------------------------------------------------------

LEFTJOIN

Perform Left Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the left join in SQL. This function has the first 4 required parameters, which are - 

  1. LOOKUP/DLOOKUP for sheet 1, 
  2. LOOKUP/DLOOKUP for sheet 2
  3. Key Name for comparison from sheet 1
  4. Key Name for comparison from sheet 2. 

It also has 3 optional parameters after above first 4, which are: 

  1. MODE: This is a number type and it applies only to the key1 & key2 used for comparison
  2. ORDERBY: This is a string type that accepts the column name according to which ordering/sorting should be done
  3. ORDERBYTYPE: This is a string that accepts "ASC"/"DESC", means ORDERBY can be done in ascending/descending order.

Definition

LEFTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2")
LEFTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE)
LEFTJOIN( LOOKUP(sheet1),LOOKUP(sheet2),"key1","key2", MODE,ORDERBY)
LEFTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE, ORDERBY, ORDERBYTYPE)

Where Mode can be 1 of the below 4 values,
1 => Include key1 of sheet1 from the comparison and do not include key2
2 => Include key2 of sheet2 from the comparison and do not include key1
3 => Include both key1 & key2 of sheet1 and sheet2 from the comparison
4 => Do not include oth key1 & key2. This is useful if you do not want the internal keys which you used in comparison in you response/output

PS - If no MODE is present, by default 3 is selected

Example 1 

LEFTJOIN(LOOKUP( [employee.emp_name,employee.dep_id AS "DEP1"]) ,LOOKUP( [department.dep_id AS "DEP2",department.dep_name]) , "DEP1" , "DEP2")

Output

[{"emp_name":"Alice","DEP1":12,"DEP2":12,"dep_name":"Sales"},{"emp_name":"Dan","DEP1":14,"DEP2":14,"dep_name":"Engineering"}]

Example 2

LEFTJOIN(LOOKUP( [employee.emp_name,employee.dep_id AS "DEP1"]) ,LOOKUP( [department.dep_id AS "DEP2",department.dep_name]) , "DEP1" , "DEP2"),1,"DEP1","DESC")

Output

[{"emp_name":"Dan","DEP1":14,"dep_name":"Engineering"},
{"emp_name":"Alice","DEP1":12,"dep_name":"Sales"}]

-----------------------------------------------------------------------------------------------------------------------------

RIGHTJOIN

Perform Right Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the right join in SQL.  

Definition

 RIGHTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2")
 RIGHTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE)
 RIGHTJOIN( LOOKUP(sheet1),LOOKUP(sheet2),"key1","key2", MODE,ORDERBY)
 RIGHTJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE, ORDERBY, ORDERBYTYPE)

PS - All parameters of this function is same as LEFTJOIN function described above. Please refer LEFTJOIN Defintion & Examples section for more details.

-----------------------------------------------------------------------------------------------------------------------------

FULLJOIN

Perform Full Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the full join in SQL.  

Definition

 FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2")
 FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE)
 FULLJOIN( LOOKUP(sheet1),LOOKUP(sheet2),"key1","key2", MODE,ORDERBY)
 FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE, ORDERBY, ORDERBYTYPE)

PS - All parameters of this function is same as LEFTJOIN function described above. Please refer LEFTJOIN Defintion & Examples section for more details.

-----------------------------------------------------------------------------------------------------------------------------

INNERJOIN

Perform Inner Join on given two tables (Sheets) with LOOKUP/DLOOKUP formula. This works similar to the inner join in SQL.  

Definition

 INNERJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2")
 INNERJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE)
 INNERJOIN( LOOKUP(sheet1),LOOKUP(sheet2),"key1","key2", MODE,ORDERBY)
 FULLJOIN( LOOKUP(sheet1), LOOKUP(sheet2), "key1", "key2", MODE, ORDERBY, ORDERBYTYPE)

PS - All parameters of this function is same as LEFTJOIN function described above. Please refer LEFTJOIN Defintion & Examples section for more details.

-----------------------------------------------------------------------------------------------------------------------------

SETTOKV

Using this function data can be set to KV storage (key Value storage) which is a persistent data store. 

Definition

 SETTOKV(key,value)
 SETTOKV(key,value, isGlobal, isWritable) 

Where -
key - Specific string with which you can store or access data
value - represents your data
isGlobal - Either 0 OR 1 , Set to 1 if you want this key to be accessible by other MicroApps
isWritable - Either 0 OR 1 , Set to 1 if you want this key to be editable by other MicroApps

Note - This function doesn't work as expected in Preview but will work on Client Apps after it is published

Example 1

SETTOKV("UserOpen","Value to Save")

Output

""

Example 2

SETTOKV("SharedKey","Value to Save", 1, 1)

Output

""

-----------------------------------------------------------------------------------------------------------------------------

GETFROMKV

Using this function data can be get from KV storage (key Value storage) which is a persistent data store. This will get data from KV storage using the key which was used during SETTOKV function. This function doesn't work in Preview but will work on Client Apps after it is published

Definition

 GETFROMKV(key)
 GETFROMKV(key, isGlobal) 

Where -
key - Specific string with which you have stored the data using SETTOKV
isGlobal - Either 0 OR 1 , Set to 1 if you want the key's value which was SETTOKV using isGlobal as 1

Note - This function doesn't work as expected in Preview but will work on Client Apps after it is published

Example 1

GETFROMKV("UserOpen")

Output

"Value to Save"

Example 2

GETFROMKV("SharedKey",1)

Output

"Value to Save"

-----------------------------------------------------------------------------------------------------------------------------

DELETEFROMKV

Using this function you can delete data stored in KV storage  (key Value storage) with a specific key which was used to store data using SETTOKV function. 

Definition

 DELETEFROMKV(key)
 DELETEFROMKV(key, isGlobal) 

Where -
key - Specific string with which you have stored the data using SETTOKV
isGlobal - Either 0 OR 1 , Set to 1 if you want the key's value which was SETTOKV using isGlobal as 1

Note - This function doesn't work as expected in Preview but will work on Client Apps after it is published

Example 1

DELETEFROMKV("UserOpen")

Output

""

Example 2

DELETEFROMKV("SharedKey",1)

Output

""

-----------------------------------------------------------------------------------------------------------------------------

CLEARKV

Using this function you can clear all data from KV storage  (key Value storage). 

Definition

 CLEARKV()

Note - This function doesn't work as expected in Preview but will work on Client Apps after it is published

Example

CLEARKV()

Output

""

-----------------------------------------------------------------------------------------------------------------------------

Did this answer your question?