Including Sky data into Google Spreadsheet

Tempest Owners
1

I recently added my new Sky unit and it works fine.
At present my Google spreadsheet has only Air data.
How do I adjust so that Sky data are included also?

2

There currently isn’t an option to do so but this is on the long term road map.

3

You mean via IFTTT?

If you make a custom Applet you can get sky data. You can choose metric units too.

Eg
{{Timestamp}}|||{{TimestampEpoch}}|||{{AirTemp}}|||{{Humidity}}|||{{Pressure}}|||{{LightningStrikeCount}}|||{{HeatIndex}}|||{{DewPoint}}|||{{WetBulbTemperature}}|||{{DeltaT}}|||{{AirDensity}}||| {{Rain}}||| {{WindAvg}}|||{{WindGust}}|||{{WindLull}}|||{{WindDir}}|||{{Uv}}|||{{Brightness}}

4

Yes, I do mean IFTTT.
How does one make a “custom Applet”?

5

My Applets,
New Applet, click “ + this”, search for Weatherflow, select “new observation”
Set units and click trigger
Click “+ that” search for Google Sheets
“Add row to spreadsheet”
Add ingredient as required.
Click Create action, Finish

Also see https://ifttt.com/blog/2017/05/how-to-build-an-applet

2of6%20(Medium)5of6a%20(Medium)5of6%20(Medium)

4 Likes
6

Thank you very much Peter
That works perfectly!

7

The applet writes data to a Google spreadsheet called Observation log (xx) and when that file gets full (2000 lines) it creates a new log file.
I’d like to put a header line in each new sheet that labels the columns, and that line should be fixed so that when you scroll down, it remains at the top.
Is this possible?

8

Hi Peter

Thanks for answering my question.

Pleased to meet another Kiwi who is using WeatherFlow Air & Sky.

I have my Sky mounted alongside a traditional weather station (Fine Offset).

My plan is to check out how well the Sky works.

So far today, Sky measured 3.58 mm vs 4.8 mm of rain and the wind (a southerly) doesn’t seem to compare at all well in either magnitude or direction.

I won’t be ditching my Fine Offset anytime soon.

Cheers

9

Hi spreadsheet fans,

Has anyone made progress with using Google Sheets to automatically store history over 2000 lines?

Has anyone used Google sheets to automatically analyse the history?

Has anyone used Google sheets to present graphs with several types of data within the same graph (eg barometer and wind speed and direction).

I might try some experimenting to do so and wondered if those who have tried can save me some time with what you discovered.
cheers Ian :slight_smile:

10

I was waiting for you :sunglasses:

1 Like
11

The newly revised IFTTT applet that includes SKY data is published here: Log Tempest data to Google Sheets - IFTTT

If you are using the original applet…ditch it and use the new one. :wink:

12

It is just my first rough experiment learning about google sheets to present multi data graphs straight onto the 2000 row sheets to see how well it works. It is not ideal, I am just experimenting the capabilities and issues that crop u.
I am using the 2000 row sheets because my first experiment with a large sheet with 30,000 rows gets bogged down.
I havnt worked with google sheets before so my main difficulty was when trying to create a graph it is really important to format the top label row as text, the date time as date time, and the numbers as a number, I chose number format 0.0 because it was simpler to do, and I had to do it every time after copying the values from the formulas to the graph sheet even though they were already formatted correctly. The graph was easily upset otherwise.
This method involved creating formulas onto a sheet which is then copied onto any of the automatically generated sheets.
Open the target sheet and rename the copied sheet by removing ‘copy of’
Then the graph sheet is also copied onto the same ‘sheet/ which is now a workbook’.
On that targeted workbook the values are copied from the formula sheet beginning at row 2 onto the graph sheet at row 1.
Then the formats of the numbers are again reformatted to numbers otherwise the graph is confused.
Then the graph looked like this again with the ‘new’ graphed values.

58%20PM
58%20PM1844×1224 401 KB

Here are the formulas used as shown:
=DATEVALUE(MID(Sheet1!A1,FIND(“,”,Sheet1!A1)-2,2)&“-”&LEFT(Sheet1!A1,(FIND(" “,Sheet1!A1)-1))&”-“&MID(Sheet1!A1,FIND(”,“,Sheet1!A1)+2,4))+TIMEVALUE(MID(Sheet1!A1,FIND(”:“,Sheet1!A1)-2,5)&” "&RIGHT(Sheet1!A1,2))

36%20PM
36%20PM1712×464 71.7 KB

=Formulas!B1&iferror(" “&right(Sheet1!B2,LEN(Sheet1!B2)-(FIND(” “,Sheet1!B2)-0)),”")

43%20PM
43%20PM1242×248 32.5 KB

=LEFT(Sheet1!B1,(FIND(" ",Sheet1!B1)-1))
54%20PM

Summary: I dont like it yet and I dont think this is very useful yet. Its purpose was a possible starter for configurable specific multi value graphs.

cheers Ian :slight_smile:

2 Likes
13

Hi @spacesnow or anyone else trying google sheets?

Tonights little project for me was to do away with my lengthy sheet formulas and create this javascript function which does what I wanted to extract all the values from the google sheets: (Using an image to prevent manipulation by the forum software)

28%20PM
28%20PM758×142 13.8 KB

I have plans for its future use to copy just the values to another sheet.
Is anyone else fiddling with google sheets?
cheers Ian :slight_smile:

14

Hi, is it possible to just record to a google sheet hourly- not every minute as is currently the case?

15

I do not remember seeing an option in the Ifttt app to do that. But if you were able to write code to manipulate the google sheets it is possible to read the sheet every hour, summerise the values however you decide, copy your results to your summary sheet and delete the one minute values.
That would also prevent the 2000 row limit from being a problem.
There was one person in this forum who does a daily save and delete process.
I stopped using ifttt because it sometimes had an error that required manually resetting.
Cheers Ian :slight_smile: