Limiting observations recorded in a spreadsheet

bjehnbergbjehnberg Member Posts: 23

Hallo world.

I have learned how to record sensor values från my Weatherhub sensors in a Google spreadsheet, but the number of recorded observations (rows entered in the spreadsheet) depends on the manufacturer's data refresh interval, as explained in this note
In this case the refresh interval can vary from less than a minute at least the 10 minutes, and I want to put a limit on how many of these observations really should be recorded in my spreadsheet. I can, of course, use the limit parameter in the Google actuator, but the shortest interval therein is 1 hour, and I would like to have one observation recorded every 15 minute (or one of every 5th observation or something like that). I have tried both the Counter and Delay functions, but I can't figure out if the really deliver what I want (or if it is even possible to create a behavour as described above).


  • ChristianChristian Administrator, Moderator Posts: 1,866 admin

    Hi @bjehnberg, interesting, but there is no ad hoc solution I can think of at the moment. Let me keep that in mind...

  • bjehnbergbjehnberg Member Posts: 23

    Hi Christian, never mind. A data capture once every hour will be good enough. What I am not exactly sure about is the process flow. One might surmise it works like this: Conrad Connect is mainly a cloud-to-cloud system and I suppose that new data values from (in this case) the weather sensors are push fed from the Weatherhub/MobileAlert Observer cloud into the Conrad Connect cloud and then appear in the sensor widgets I have specified. If all conditions are fullfilled (=TRUE) and the actuator limit is set to "Always" then the actuator (Google in my case) is executed immediately. But what if the limit is set to "Once per hour". When is the actuator executed in that case?. Can it be any time between HH:00 and HH:59?

  • ChristianChristian Administrator, Moderator Posts: 1,866 admin

    Hi @bjehnberg, yes, it can be any minute, but most likely inbetween '15 and '45.

  • bjehnbergbjehnberg Member Posts: 23

    OK, so that's the way it is. Despite that I have found some strange behavour with the "Limit to once per hour" actuator setting. Have a look at this series of weather observations from one of my observation sites for the 14th of August (click on the "Table" tab in order to get the full history for the observations this day).
    As you can see it's a hole in the series, no values have been reported between 2 o'clock and 3 o'clock AM this morning, i.e. the Google actuator has not been triggered/executed during this interval. The values captured from the weather sensors are stored in a Google spreadsheet as in intermediate processing step, and the same values and timestamps are found within the actual spreadsheet. So the problem must clearly be located within the Conradconnect project. Why is that? The upper and lower bounds are set to 100/-50 (temperature sensor) and 100/0 (humidity) and the sensor widgets are combined with a general connector with an "in range" condition, so there cannot be any logical problems with the process control.

  • ChristianChristian Administrator, Moderator Posts: 1,866 admin
    edited August 14

    Hi @bjehnberg, thanks. Which particular observation makes you believe, that the problem is on our side? Just missing data within the respective spreadsheet? Or did you crosscheck (e.g. comparing Weatherhub app data with Google Sheets)? I am asking because we had a simliar behavior before and I remember that this needed to be solved on the manufacturer's side.

  • bjehnbergbjehnberg Member Posts: 23

    Hi Christian, that's absolutely a valid question. I have looked att the observation values extracted from the Weatherhub Observer cloud and there are in fact a number of observations recorded between 2 o'clock anf 3 o'clock, but unfortunaly that does not prove very much. The Weatherhub architecture works like this: A weather value is measured by a sensor, transmitted with a radio signal to a nearby internet connected gateway and from the latter unit forwarded to the Observer cloud. If the radio transmission is disturbed or if the connection with the gateway is completely lost, I think (but I'm not absolute sure about this) the sensor has to capability to store at least a limited number of values. When the connection with the gateway is restored the values from the sensor's memory are tranrferrered to the gateway and likewise to the Observer cloud. So in this case it might as well have been a local distortion at the observer site which prevented the weather values from entering the Conradconnect cloud, and the actuator might not have been triggered for that reason. If this is correct the is no way to further investigate this event, but a possible solution for the future is to set up an almost identical project, with the only difference that this project has the actuator limit set to "Always" instead. If such an event as described above occurs once more it would be fairly easy to decide whether an observation (or a set of observations) was inserted into the Conradconnect cloud or not. If not, the problem is clearly on the manufacturer's side.
    Best regards/Björn

  • bjehnbergbjehnberg Member Posts: 23

    Hi again, Christian.
    I can't really say on who's side the problem is, but this is how I set up my project in order to monitor the phenomenon with lost actuator calls.

    Weather data (temperature an humidity) are fed into the project from two widgets in order to record the readings in two (in this case, normally just one sheet would be needed) Google spreadsheets.
    The upper actuator is limited to "once per hour", but the lower has the limit "always". As I understand it, this must be the most accurate way to set up a monitor track, due to the fact the two actuators share the same data feed.

    The result from the upper actuator looks like this:

    Here we can easily see that there is a gap between 11:03:41 and 13:09:26. That's a period of more than 120 min. As I understand the limit "once per hour" the actuator should have been triggered at least once more during this interval.

    If we take a look at what is recorded by the lower actuator, we find a number of readings in between the times 11:03:41 and 13:09:26. It's very hard to understand the control flow in ConradConnect and what is really triggering the actuators, but in this case we have a number of data values in the data flow, and none of them have triggered the (upper) actuator the way I wanted it to be. This kind of glitch doesn't happen very often, not even during every 24 hour period.

  • bjehnbergbjehnberg Member Posts: 23

    Another peculiar thing that can be observed in the spreadsheet from the "once per hour" actuator, is that the point of execution is constantly pushed forward in time, and sometimes pushed "over the edge", for example 09:51:14 and then afterwards 11:03:41. This raises a question about the interpretation of the limit "once per hour". Is it one execution between '00 and '59 or is it one execution within a (~) 60 min. period, counted from the time of the last execution?

  • ChristianChristian Administrator, Moderator Posts: 1,866 admin

    Hi @bjehnberg, interesting test approach. According to your findings both conclusions seem to be valid. Going to address this accordingly.

  • ChristianChristian Administrator, Moderator Posts: 1,866 admin

    Just one more question: Are there any obvious gaps within the 'ALWAYS' version's data?

  • bjehnbergbjehnberg Member Posts: 23
    edited August 20

    Hi again, Christian, and thank you for your response.
    If I take a look at the cutout from the spreadsheet containing the data from the "always" actuator (see second exemple above) between 2018-08-18 10:44:33 an 14:05:39 I cannot se any obvious gaps in the data flow. It's in fact even hard to see any pattern at all. The temp./humidity sensor is said to be delivering a new value within "max. 7 minutes, the transmission will be immediately if the changes of the temperature are more than 0.6°C (0.8°F) or of the humidity are more than 3% compared to the last transmission" (from the manufacturer's data sheet). But from the sensor it's a long way before the corresponding values are fed into the ConradConnect cloud! As I said before, I don't even understand the control flow from there to here, or if the data feed is a pull feed or a push feed. Another funny thing is this. As a redundance I have set up a mirror project but with only an "always" actuator (and a different spreadsheet but with the same widget, pointing at the same sensor), but the amazing fact is that timestamps in the two spreadsheets used by the two "always" actuators are not always the same. But as they are located in two different projects these actuators may not share the same data feed, although the are pointing at the same sensor.

Sign In or Register to comment.