How to use Spreadsheet Formulas in Pabbly Connect?

Status
Not open for further replies.
Pabbly Connect supports 300+ Spreadsheet functions.

Most of the functions supported by Excel or Google Sheets can be used directly inside Pabbly Connect.

Steps to use:

1. Choose App: Number Formatter
2. Choose Method: Spreadsheet Formulas
3. Use the formula you want to use and click on “Save & Send Test Request”
4. You will find the result in the result key-value pair.
5. You can then map the result in any other module that you need.

1619598693451.png



Supported Spreadsheet Formulas


DATE​

FunctionExample callExpected result
DATEDATE(2008, 7, 8)2008-07-08T00:00:00.000Z
DAYDAY('15-Apr-11')15
DAYSDAYS('3/15/11', '2/1/11')42
HOURHOUR('7/18/2011 7:45:00 AM')7
MINUTEMINUTE('2/1/2011 12:45:00 PM')45
ISOWEEKNUMISOWEEKNUM('3/9/2012')10
MONTHMONTH('15-Apr-11')4
NOWNOW()2021-05-28T05:33:52.062Z
SECONDSECOND('2/1/2011 4:48:18 PM')18
TIMETIME(16, 48, 10)0.7001157407407408
TIMEVALUETIMEVALUE('22-Aug-2011 6:35 AM')0.2743055555555556
TODAYTODAY()2021-05-28T05:41:13.217Z
WEEKDAYWEEKDAY('2/14/2008', 3)3
YEARYEAR('7/5/2008')2008
WEEKNUMWEEKNUM('3/9/2012', 2)11
YEARFRACYEARFRAC('1/1/2012', '7/30/2012', 3)0.5780821917808219

FINANCIAL​

FunctionExample callExpected result
ACCRINTACCRINT('01/01/2011', '02/01/2011', '07/01/2014', 0.1, 1000, 1, 0)350
CUMIPMTCUMIPMT(0.1/12, 30*12, 100000, 13, 24, 0)-9916.77251395708
CUMPRINCCUMPRINC(0.1/12, 30*12, 100000, 13, 24, 0)-614.0863271085149
DBDB(1000000, 100000, 6, 1, 6)159500
DDBDDB(1000000, 100000, 6, 1, 1.5)250000
DOLLARDEDOLLARDE(1.1, 16)1.625
DOLLARFRDOLLARFR(1.625, 16)1.1
EFFECTEFFECT(0.1, 4)0.10381289062499977
FVFV(0.1/12, 10, -100, -1000, 0)2124.874409194097
IPMTIPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)928.8235718400465
ISPMTISPMT(0.1/12, 6, 2*12, 100000)-625
NOMINALNOMINAL(0.1, 4)0.09645475633778045
NPERNPER(0.1/12, -100, -1000, 10000, 0)63.39385422740764
NPVNPV(0.1, -10000, 2000, 4000, 8000)1031.3503176012546
PDURATIONPDURATION(0.1, 1000, 2000)7.272540897341714
PMTPMT(0.1/12, 2*12, 100000, 1000000, 0)-42426.08563793503
PPMTPPMT(0.1/12, 6, 2*12, 100000, 1000000, 0)-43354.909209775076
PVPV(0.1/12, 2*12, 1000, 10000, 0)-29864.950264779152
RATERATE(2*12, -1000, -10000, 100000, 0, 0.1)0.06517891177181533

ENGINEERING​

FunctionExample callExpected result
BIN2DECBIN2DEC(101010)42
BIN2HEXBIN2HEX(101010)2a
BIN2OCTBIN2OCT(101010)52
BITANDBITAND(42, 24)8
BITLSHIFTBITLSHIFT(42, 24)704643072
BITORBITOR(42, 24)58
BITRSHIFTBITRSHIFT(42, 2)10
BITXORBITXOR(42, 24)50
COMPLEXCOMPLEX(3, 4)3+4i
CONVERTCONVERT(64, 'kibyte', 'bit')524288
DEC2BINDEC2BIN(42)101010
DEC2HEXDEC2HEX(42)2a
DEC2OCTDEC2OCT(42)52
DELTADELTA(42, 42)1
ERFERF(1)0.8427007929497149
ERFCERFC(1)0.1572992070502851
GESTEPGESTEP(42, 24)1
HEX2BINHEX2BIN('2a')101010
HEX2DECHEX2DEC('2a')42
HEX2OCTHEX2OCT('2a')52
IMABSIMABS('3+4i')5
IMAGINARYIMAGINARY('3+4i')4
IMARGUMENTIMARGUMENT('3+4i')0.9272952180016122
IMCONJUGATEIMCONJUGATE('3+4i')3-4i
IMCOSIMCOS('1+i')0.8337300251311491-0.9888977057628651i
IMCOSHIMCOSH('1+i')0.8337300251311491+0.9888977057628651i
IMCOTIMCOT('1+i')0.21762156185440265-0.8680141428959249i
IMCSCIMCSC('1+i')0.6215180171704283-0.3039310016284264i
IMCSCHIMCSCH('1+i')0.3039310016284264-0.6215180171704283i
IMDIVIMDIV('1+2i', '3+4i')0.44+0.08i
IMEXPIMEXP('1+i')1.4686939399158851+2.2873552871788423i
IMLNIMLN('1+i')0.3465735902799727+0.7853981633974483i
IMLOG10IMLOG10('1+i')0.1505149978319906+0.3410940884604603i
IMLOG2IMLOG2('1+i')0.5000000000000001+1.1330900354567985i
IMPOWERIMPOWER('1+i', 2)1.2246063538223775e-16+2.0000000000000004i
IMPRODUCTIMPRODUCT('1+2i', '3+4i', '5+6i')-85+20i
IMREALIMREAL('3+4i')3
IMSECIMSEC('1+i')0.4983370305551868+0.591083841721045i
IMSECHIMSECH('1+i')0.4983370305551868-0.591083841721045i
IMSINIMSIN('1+i')1.2984575814159773+0.6349639147847361i
IMSINHIMSINH('1+i')0.6349639147847361+1.2984575814159773i
IMSQRTIMSQRT('1+i')1.0986841134678098+0.45508986056222733i
IMSUBIMSUB('3+4i', '1+2i')2+2i
IMSUMIMSUM('1+2i', '3+4i', '5+6i')9+12i
IMTANIMTAN('1+i')0.2717525853195117+1.0839233273386946i
OCT2BINOCT2BIN('52')101010
OCT2DECOCT2DEC('52')42
OCT2HEXOCT2HEX('52')2a

LOGICAL​

FunctionExample callExpected result
ANDAND(true, false, true)false
falseFALSE()false
IFIF('John' = 'John', 'Hello!', 'Goodbye!')Hello!
IFSIFS(false, 'Hello!', true, 'Goodbye!')Goodbye!
NOTNOT(true)false
OROR(true, false, true)true
SWITCHSWITCH(7, 9, 'Nine', 7, 'Seven')Seven
XORXOR(true, false, true)false

MATH​

FunctionExample callExpected result
ABSABS(-4)4
ACOSACOS(-0.5)2.0943951023931957
ACOSHACOSH(10)2.993222846126381
ACOTACOT(2)0.46364760900080615
ACOTHACOTH(6)0.16823611831060645
ARABICARABIC('MCMXII')1912
ASINASIN(-0.5)-0.5235987755982988
ASINHASINH(-2.5)-1.6472311463710965
ATANATAN(1)0.7853981633974483
ATAN2ATAN2(-1, -1)-2.356194490192345
ATANHATANH(-0.1)-0.10033534773107562
BASEBASE(15, 2, 10)0000001111
CEILINGCEILING(-5.5, 2, -1)-6
CEILINGMATHCEILINGMATH(-5.5, 2, -1)-6
CEILINGPRECISECEILINGPRECISE(-4.1, -2)-4
COMBINCOMBIN(8, 2)28
COMBINACOMBINA(4, 3)20
COSCOS(1)0.5403023058681398
COSHCOSH(1)1.5430806348152437
COTCOT(30)-0.15611995216165922
COTHCOTH(2)1.0373147207275482
CSCCSC(15)1.5377805615408537
CSCHCSCH(1.5)0.46964244059522464
DECIMALDECIMAL('FF', 16)255
ERFERF(1)0.8427007929497149
ERFCERFC(1)0.1572992070502851
EVENEVEN(-1)-2
EXPEXP(1)2.718281828459045
FACTFACT(5)120
FACTDOUBLEFACTDOUBLE(7)105
FLOORFLOOR(-3.1)-4
GCDGCD(24, 36, 48)12
INTINT(-8.9)-9
ISEVENISEVEN(-2.5)true
ISODDISODD(-2.5)false
LCMLCM(24, 36, 48)144
LNLN(86)4.454347296253507
LOGLOG(8, 2)3
LOG10LOG10(100000)5
MODMOD(3, -2)-1
MROUNDMROUND(-10, -3)-9
MULTINOMIALMULTINOMIAL(2, 3, 4)1260
ODDODD(-1.5)-3
POWERPOWER(5, 2)25
PRODUCTPRODUCT(5, 15, 30)2250
QUOTIENTQUOTIENT(-10, 3)-3
RADIANSRADIANS(180)3.141592653589793
RANDRAND()[Random real number greater between 0 and 1]
RANDBETWEENRANDBETWEEN(-1, 1)[Random integer between bottom and top]
ROUNDROUND(626.3, -3)1000
ROUNDDOWNROUNDDOWN(-3.14159, 2)-3.14
ROUNDUPROUNDUP(-3.14159, 2)-3.15
SECSEC(45)1.9035944074044246
SECHSECH(45)5.725037161098787e-20
SIGNSIGN(-0.00001)-1
SINSIN(1)0.8414709848078965
SINHSINH(1)1.1752011936438014
SQRTSQRT(16)4
SQRTPISQRTPI(2)2.5066282746310002
SUMSUM(-5, 15, 32, 'Hello World!')42
SUMSQSUMSQ(3, 4)25
TANTAN(1)1.5574077246549023
TANHTANH(-2)-0.9640275800758168
TRUNCTRUNC(-8.9)-8

STATISTICAL​

FunctionExample callExpected result
BETADISTBETADIST(2, 8, 10, true, 1, 3)0.6854705810117458
BETAINVBETAINV(0.6854705810117458, 8, 10, 1, 3)1.9999999999999998
BINOMDISTBINOMDIST(6, 10, 0.5, false)0.205078125
COUNTACOUNTA([1, null, 3, 'a', '', 'c'])4
COUNTUNIQUECOUNTUNIQUE([1,1,2,2,3,3])3
DEVSQDEVSQ([2,4,8,16])115
EXPONDISTEXPONDIST(0.2, 10, true)0.8646647167633873
FDISTFDIST(15.2069, 6, 4, false)0.0012237917087831735
FINVFINV(0.01, 6, 4)0.10930991412457851
FISHERFISHER(0.75)0.9729550745276566
FISHERINVFISHERINV(0.9729550745276566)0.75
GAMMAGAMMA(2.5)1.3293403919101043
GAMMALNGAMMALN(10)12.801827480081961
GAUSSGAUSS(2)0.4772498680518208
HYPGEOMDISTHYPGEOMDIST(1, 4, 8, 20, false)0.3632610939112487
KURTKURT([3,4,5,2,3,4,5,6,4,7])-0.15179963720841627
LOGNORMDISTLOGNORMDIST(4, 3.5, 1.2, true)0.0390835557068005
LOGNORMINVLOGNORMINV(0.0390835557068005, 3.5, 1.2, true)4.000000000000001
MODESNGLMODESNGL([1,2,3,4,3,2,1,2,3])2
NORMDISTNORMDIST(42, 40, 1.5, true)0.9087887802741321
NORMINVNORMINV(0.9087887802741321, 40, 1.5)42
NORMSDISTNORMSDIST(1, true)0.8413447460685429
PERMUTPERMUT(100, 3)970200
PERMUTATIONAPERMUTATIONA(4, 3)64
PHIPHI(0.75)0.30113743215480443
POISSONDISTPOISSONDIST(2, 5, true)0.12465201948308113
SKEWSKEW([3,4,5,2,3,4,5,6,4,7])0.3595430714067974
SKEWPSKEWP([3,4,5,2,3,4,5,6,4,7])0.303193339354144
STANDARDIZESTANDARDIZE(42, 40, 1.5)1.3333333333333333
TDISTTDIST(60, 1, true)0.9946953263673741
TINVTINV(0.9946953263673741, 1)59.99999999996535
WEIBULLDISTWEIBULLDIST(105, 20, 100, true)0.9295813900692769

TEXT​

FunctionExample callExpected result
CHARCHAR(65)A
CLEANCLEAN('Monthly report')Monthly report
CODECODE('A')65
CONCATENATECONCATENATE('Andreas', ' ', 'Hauser')Andreas Hauser
EXACTEXACT('Word', 'word')false
FINDFIND('M', 'Miriam McGovern', 3)8
LEFTLEFT('Sale Price', 4)Sale
LENLEN('Phoenix, AZ')11
LOWERLOWER('E. E. Cummings')e. e. cummings
MIDMID('Fluid Flow', 7, 20)Flow
PROPERPROPER('this is a TITLE')This Is A Title
REGEXEXTRACTREGEXEXTRACT('Palo Alto', 'Alto')Alto
REGEXMATCHREGEXMATCH('Palo Alto', 'Alto')true
REGEXREPLACEREGEXREPLACE('Sutoiku', 'utoiku', 'TOIC')STOIC
REPLACEREPLACE('abcdefghijk', 6, 5, '*')abcde*k
REPTREPT('*-', 3)*-*-*-
RIGHTRIGHT('Sale Price', 5)Price
ROMANROMAN(499)CDXCIX
SEARCHSEARCH('margin', 'Profit Margin')8
SUBSTITUTESUBSTITUTE('Quarter 1, 2011', '1', '2', 3)Quarter 1, 2012
TT('Rainfall')Rainfall
TRIMTRIM(' First Quarter Earnings ')First Quarter Earnings
UNICHARUNICHAR(66)B
UNICODEUNICODE('B')66
UPPERUPPER('total')TOTAL


For getting more filtered results check this link: https://support.google.com/docs/table/25273?hl=en
 
Status
Not open for further replies.
Top