just add a column with a formula like this.
I should have explicitly stated that the spreadsheet as is, it won’t let me add a column at all. I suspect because the fields and data are still tied to the station’s data. And I did make a copy of it into my account and I have been able to pull the data down successfully.
I have it locked to the public, so it doesn’t get messed up. Go up to “File → Make a copy” and that will copy the document to your personal drive. You can then edit that one. For the date, just type out the literal string in mm/dd/yyy hh:mm format in the cell and Sheets should recognize it as a date.
I have copied it to my account, and I have successfully imported my data. It is within that copy that it will not allow me to add a column so I can convert the timestamp. If I manually type in the date in the timestamp field, it removes all of the data and I get #Ref! where the formula is.
So it appears the data as-is is locked. So I’m hoping there is a workaround.
What if you type the date/time in manually and the go Format->Date/Time in that cell?
It does the same thing as before, removes all data and column headers.
Regardless, I wouldn’t know what date to type in because it’s still in code (e.g. 1736817000). So I need to convert the Unix timestamp. But I can’t because I am unable to add a column to add the formula to do that.
You don’t need to convert the date, I’m already doing the conversion for you in the formula in A7.
With that said… I just went to the spreadsheet to see what you might be doing that’s causing the problem and it does work for me now either. I found 2 things
- The “bucket” parameter is undocumented and unsupported and it looks like WeatherFlow has since disabled it entirely. It causes an internal server error when used now.
- Taking the bucket parameter off results in a successful call to the API, but the CSV that the spreadsheet uses to populate is coming back empty. I’m able to replicate this outside of the spreadsheet also, so I know it’s not a problem with the spreadsheet. I presume there is an issue on WeatherFlow’s side. I’ll reach out to their support for an answer.
Stay tuned.
This still works today
This is really useful - THANKS!! Your sheet works great, and seems much less prone to errors than when I editing the URL. Now I just need to figure out how to adjust the time (hr), which I presume is GMT.
I revisited this thread today and reread your last comment.I could really use the granular data the Tempest collects.
You don’t need to convert the date, I’m already doing the conversion for you in the formula in A7.
I still don’t know what this means, because I see no where the date is converted into a readable format. All that is there is the 10 digit time stamp.
I don’t understand what I am missing if everyone else is using this.But if there’s no way to convert the time stamp to dd/mm/yyyy and time, I don’t know how people are actually using this in a meaningful way.
just add a formula like this (the 7200 is for the time zone, so change that to make it match yours)
Hey! That worked! Is there a way to extract the time as well?
Edit: Never mind, I figured it out. Just needed to format the cells to date and time. Derp. Thanks all.
of course it works. I send you that answer on january 14th earlier in this thread. But glad you’re happy and found out the time part yourself. The original number is the amount of seconds since 1 jan 1970 (the unix standard)
I am very sorry, I don’t know how I missed that.