last_updated to usable format

[ddarbs]

This might be more of a user thing, but could be something to add in the readme.

For UTC =O2/60/60/24 + DATE(1970,1,1)

to change it to UTC-6 =O2/60/60/24 + DATE(1970,1,1) - 6/24

to change it to UTC+6 =O2/60/60/24 + DATE(1970,1,1) - 30/24

Remember to change the format of the cells after setting the formula.

---

Sheets formula from https://webapps.stackexchange.com/a/112651

[Project-42]

What I do is to use it as a timer check, so I know how old the prices are

=CONCATENATE("Prices Updated ",round((NOW()-(Rates!H2/86400+date(1970,1,1)))2460), " Minutes ago")

That is what I included in the guide.

I could add your option as well, so people have more options But not sure if we should change the script code, leave it as "raw" data and we can do wherever we want in the portfolio sheet 😋

[Project-42]

BTW, please have a look to the guide in the wiki, I really will appreciate some feedback there

[corpetty]

Why do you have H2 in your formula? That's market cap. Also, are you only using the top coin as your reference for when stats were updated?

I can't seem to find in your guide where you detail this information.

[Project-42]

Hi @corpetty

We have added some reference for that in the wiki https://github.com/saitei/crypto-sheets/wiki/Easy-guide-(I-hope)-to-create-a-Portfolio-Tracker

I need to update it anyway since the calculation is based in GMT, so I will need to add some clarification how to get it done in different timezones, or just change your spreadsheet preferences to GMT+0

[LesterCovax]

@iKrazy @solifugo So what do you think? Hardcode that formula in to convert to UTC (non-unix format) or just keep the raw data in that column and take solifugo's approach to add an additional column with the conversion?

[ddarbs]

@saitei I have been using an additional column with the conversion. I am not sure if people want the UNIX timestamp in general though.

[Project-42]

Well, in eithercase, we will need to either convert the spreadshet into UTC or make sure the Date conversion works with "local time" (I guess the spreadsheet has something like that)

I cant find an easy conversion formula that automatically check your spreadsheet setting, and not sure how that will work for daylight saving..

So, either we convert the spreadsheet to UTC from the Script, we add that into the readme

Regarding that, I think will be better to go @iKrazy path and make the script to show you the time directly to UTC (we can make the ticket as "Last Updated UTC") so people can use it directly if the want. I can modify the formula from the Wiki

[ddarbs]

I think I've solved it @saitei @solifugo

    var timeNow = new Date();
    ssRates.getRange('O'+(c).toString()).setValue((((new Date(timeNow.getTime()) / 1000 ) - (myCoinsObj[myCoins[i]]['last_updated'])) / 60).toFixed(2) + ' Minutes Ago');

[Project-42]

The problem I see with this is that is not updated over time. Having this code in the sheet (cant find a way to set it up in the script) you will see how the time increase until the data collection is trigger again:

=CONCATENATE("Prices Updated ",round((NOW()-(Rates!O2/86400+date(1970,1,1)))*24*60), " Minutes ago")

[ddarbs]

Yeah true, but I thought the whole point was we were trying to get last update from unix to a readable format in the same cell/keep it in the script. I will try setting it to date time instead of minutes ago.

[Project-42]

Yes, I think will be easier to just set it up to "date" and do "last update" - "now" simpler

[ddarbs]

@solifugo I think this will work; it's in my latest commit.

var ratesDateFormat = ssRates.getRange("O2:O");
ratesDateFormat.setNumberFormat("mmm dd h:mm A/P\".M.\"");  

combined with

ssRates.getRange('O'+(c).toString()).setValue(new Date((myCoinsObj[myCoins[i]]['last_updated'])*1000));

[LesterCovax]

Works for me. Good to close?