Converting date to epoch adding a dot?

0
votes

I'm converting a DD-MM-YYYY 00:00:00 date to an epoch timestamp in gsheet but there is a dot added to the end of timestamp and I don't get why. It is then passing a wrong parameter to my API call. Any idea why?

In F142, I have a date : "09/04/2020 14:24:21" and my formula to convert (and call the API & use the answer) is :

=index(ImportJSON("https://api.coinpaprika.com/v1/coins/btc-bitcoin/ohlcv/historical?start="&(F142-date(1970,1,1))*86400&"&quote=usd"),2,6)

The URL construction give me : https://api.coinpaprika.com/v1/coins/btc-bitcoin/ohlcv/historical?start=1586442261.&quote=usd with this extra '.' at the end of the epoch date?

Where does it come from and how to remove it?

PS : ImportJson is from here : https://gist.github.com/paulgambill/cacd19da95a1421d3164 and work perfectly well

1
apigoogle-sheetsepoch

1 Answers

1
votes

Solution

It's a formatting error. When using the & operator the implicit casting operation is appending a "." to the number.

Try to force string formatting using the TEXT function.

This should do the trick:

="https://api.coinpaprika.com/v1/coins/btc-bitcoin/ohlcv/historical?start="&TEXT((D16-date(1970,1,1))*86400,"#")&"&quote=usd"

"#" stands for only digits.

Reference

TEXT