[neilh20]

@cameronlight,    I had a suggestion to try replit.com – enabled an account and it allows 10apps to be built free.

 

I tried “Create a curl script with Oauth2 access to post 3 values to google sheets”

I was logged into my google account, for latter testing. When asked to allow replit permissions pretty have to allow it all it seems, particularly read/write spreadsheets.

This was the script it created. I ended up being able to add 3 values to a spreadsheet in my account.

 

Shell

#!/bin/bash SPREADSHEET_ID="${1:-YOUR_SPREADSHEET_ID}" RANGE="${2:-Sheet1!A1:C1}" VALUE1="${3:-Hello}" VALUE2="${4:-World}" VALUE3="${5:-123}" get_access_token() { local hostname="$REPLIT_CONNECTORS_HOSTNAME" local x_replit_token="" if [ -n "$REPL_IDENTITY" ]; then x_replit_token="repl $REPL_IDENTITY" elif [ -n "$WEB_REPL_RENEWAL" ]; then x_replit_token="depl $WEB_REPL_RENEWAL" else echo "Error: No authentication token found" >&2 exit 1 fi local response=$(curl -s "https://${hostname}/api/v2/connection?include_secrets=true&connector_names=google-sheet" \ -H "Accept: application/json" \ -H "X_REPLIT_TOKEN: ${x_replit_token}") local access_token=$(echo "$response" | grep -o '"access_token":"[^"]*"' | head -1 | cut -d'"' -f4) if [ -z "$access_token" ]; then echo "Error: Could not retrieve access token. Make sure Google Sheets is connected." >&2 echo "Response: $response" >&2 exit 1 fi echo "$access_token" } post_values_to_sheet() { local spreadsheet_id="$1" local range="$2" local val1="$3" local val2="$4" local val3="$5" local access_token="$6" local json_body=$(cat < [range] [value1] [value2] [value3]" echo "" echo "Examples:" echo " ./post_to_sheets.sh 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms" echo " ./post_to_sheets.sh 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms Sheet1!A1:C1" echo " ./post_to_sheets.sh 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms Sheet1!A1:C1 Apple Banana Cherry" echo "" echo "To find your Spreadsheet ID:" echo " Open your Google Sheet and look at the URL:" echo " https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit" echo " The SPREADSHEET_ID is the long string between /d/ and /edit" echo "" exit 0 fi echo "Retrieving OAuth2 access token..." ACCESS_TOKEN=$(get_access_token) if [ $? -ne 0 ]; then echo "Failed to get access token" exit 1 fi echo "Access token retrieved successfully." echo "" post_values_to_sheet "$SPREADSHEET_ID" "$RANGE" "$VALUE1" "$VALUE2" "$VALUE3" "$ACCESS_TOKEN"

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

#!/bin/bash   SPREADSHEET_ID="${1:-YOUR_SPREADSHEET_ID}" RANGE="${2:-Sheet1!A1:C1}" VALUE1="${3:-Hello}" VALUE2="${4:-World}" VALUE3="${5:-123}"   get_access_token() {     local hostname="$REPLIT_CONNECTORS_HOSTNAME"     local x_replit_token=""          if [ -n "$REPL_IDENTITY" ]; then         x_replit_token="repl $REPL_IDENTITY"     elif [ -n "$WEB_REPL_RENEWAL" ]; then         x_replit_token="depl $WEB_REPL_RENEWAL"     else         echo "Error: No authentication token found" >&2         exit 1     fi          local response=$(curl -s "https://${hostname}/api/v2/connection?include_secrets=true&connector_names=google-sheet" \         -H "Accept: application/json" \         -H "X_REPLIT_TOKEN: ${x_replit_token}")          local access_token=$(echo "$response" | grep -o '"access_token":"[^"]*"' | head -1 | cut -d'"' -f4)          if [ -z "$access_token" ]; then         echo "Error: Could not retrieve access token. Make sure Google Sheets is connected." >&2         echo "Response: $response" >&2         exit 1     fi          echo "$access_token" }   post_values_to_sheet() {     local spreadsheet_id="$1"     local range="$2"     local val1="$3"     local val2="$4"     local val3="$5"     local access_token="$6"          local json_body=$(cat < [range] [value1] [value2] [value3]"     echo ""     echo "Examples:"     echo "  ./post_to_sheets.sh 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"     echo "  ./post_to_sheets.sh 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms Sheet1!A1:C1"     echo "  ./post_to_sheets.sh 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms Sheet1!A1:C1 Apple Banana Cherry"     echo ""     echo "To find your Spreadsheet ID:"     echo "  Open your Google Sheet and look at the URL:"     echo "  https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit"     echo "  The SPREADSHEET_ID is the long string between /d/ and /edit"     echo ""     exit 0 fi   echo "Retrieving OAuth2 access token..." ACCESS_TOKEN=$(get_access_token)   if [ $? -ne 0 ]; then     echo "Failed to get access token"     exit 1 fi   echo "Access token retrieved successfully." echo ""   post_values_to_sheet "$SPREADSHEET_ID" "$RANGE" "$VALUE1" "$VALUE2" "$VALUE3" "$ACCESS_TOKEN"

This is a script worked in the replit sandbox.

./post_to_sheets.sh <spreadsheet_id> [range] [value1] [value2] [value3]

or per the example

./post_to_sheets.sh 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms Sheet1!A1:C1 Apple Banana Cherry

There maybe some REPLIT secret sauce, as its in a bash file, it does identify program flow.

The next step would be to see if it can be made to work on local PC. !!

 

 

 

[neilh20]

@cameronlight, very interesting needing to be clear about security of readings. Hard to do but a requirement for some property owners.

I was interested in doing something with sheets, however it was to learn about Oauth2 security as I remember.  Obviously security access for google is core to the simplicity of their interfaces/APIs – but how does it work. Still evolving for me.

What I would do with this, is attempt to prototype it with  CURL (or any other CLI) which can provide a log of all its internet activity. When there is a reliable script, running on the local PC that can POST  to google sheets, with a traceable log, that takes into account security renewables and timeouts, then you have the algorithm that is needed.

Then translate that algorithm to C++ (publisher subsystem) running on the Mayfly.

I tried asking these questions to get some insights

gemini.google.com: Create a curl script with Oauth2 access to post values to google sheets
Answer Analysis : identifies an Oauth2 Access Token and a Refresh Token. Access tokens expire (typically in 1 hour). To automate this process without human intervention, you must first get a Refresh Token and then use it to request a new access token.
https://gemini.google.com/app/2c95df171b1a80b1    (it gave me this reference, may only work if logged in. Didn’t work for me when moving to another browser that I wasn’t logged in on)

[neilh20]

Hi @cameronlight, gosh fascinating to do some lateral thinking.

You would need to publish your whole program – preferably on a traceable git, like github.com.

The challenge in software is abstracting the complicated stuff (service layers) to make it look simple by encapsulating it into a simple interface.

Its always fascinating when someone knows enough to be able to use some new “libs” to do some magic.

From knowing modular sensors architecture – though I haven’t touched for a couple of years after I ran out of program space on the Mayfly – what you are looking to do is create a new publisher for the “POST to a private Google Script webhook.” That is it needs to be the service layer for POSTing to your chosen destination.

As you are identifying, there are a lot of infrastructure layers to keep the Mayfly on a low power even keel.    As is often said, you learn more by your failures.

Of course when it all works its nice  to describe it for others to discuss/try. 🙂

[neilh20]

Well as you can see this thread is almost 3 years old. I haven’t tried anything more than what was mentioned above. Your analysis is solid however its dealing with any current pulses and battery voltage measurement circuit.

[neilh20]

I’ve had some response from  staging.monitormywatershed.org:80

but then it seems to become unreliable.

https://github.com/WikiWatershed/monitor-my-watershed/issues/752

[neilh20]

Nuts that it sounds like the switch might be oxidizing. The switch solves a power vampire problem by switching off the CP210x – so I would expect the processor to carry on working.  You might check what the humidity levels are in the unit, ensure that its largely isolated from fogs and possibly put in some dessicant to dry it out. At one point I mad some electronics (not the Mayfly) in a temperate forest  – and some fungus or something grew across the reset line causing the processor to permanently reset.

For purely monitoring the processor, I use the FTDI link to do that. This means it can be monitored in the field for its current state, and doesn’t reset when connecting to the USB .

Diagrams here, showing only the Tx line connection https://github.com/neilh10/ModularSensors/wiki/Test-Equipment-FTDI-cable

For a full connector with Rx and Tx it can also download programs to the processor.

[neilh20]

Hi Eric. this is a voltage sensor, so it fits a pretty basic type of sensor, measure the voltage and then translate it to depth.

For a sensor, layers of calibration give value. Calibration of the sensor to defined units (V to mm), and calibration of the water height to the measured height in the flume.

The accuracy sounds pretty reasonable, for a visual check can probably can only get to +/ 1mm visually under ideal circumstance.  There are some good ideas of attaching the sensor into the water https://www.envirodiy.org/knowledge-base/ Sect 7.3.4 – however each time is going to require mapping to a physical measurement tape line. So how to secuse the milone tape in a a protective pipe and also relate to its calibration. Might be an idea to have a see through protective plastic pipe, to map milone tape measurements to external physical reading

For the milone tape output, the auxiliary ADC. ADS1115 gives the most accuracy with an input over 0 to 3.3V. You need to decide if running it at switched 3.3V or boosted 5.0V.   I would think 3.3V

I did see this discussion, that is using a 0-10V , but would need to scale it to 0-3.3V

https://docs.google.com/presentation/d/1LqyQEa3EJx3xvbQfo6V8wqlVyj3OxlF3AZsk2E7ZLn0/edit#slide=id.g149c4d18368_0_1

https://envirodiy.github.io/ModularSensors/group__sensor__ads1x15.html

I should point out that main ModularSensors is a “scientific software” package – best effort architecture. Nobody is sharing metrics as to how well it works, which is usual practice for engineering types. Engineering is about high reliability, software and hardware.  There are some good software examples, but no standard software reference builds. If there is good wireless cell signal,  then readings on the server (assuming MonitorMyWatershed ) gives a good visual that the site is operational If the wireless or server becomes unavailable, readings are dropped – however its accessible on the loggers uSd card.  If the available power in the battery gets low and not managed the processor starts resetting and needs a site visit and new battery. So I recommend one of your sensor be the battery and also the  SampleNumber – a linear number increments each sample and reset to 0 when the processor boots. https://envirodiy.github.io/ModularSensors/group__sensor__processor.html

I’ve done my own fork of ModularSensors to make it as reliable as a “Walking Boot Net”, that is walking up to the logger and copying the uSD to get ALL the time series readings. Features here  https://github.com/neilh10/ModularSensors/wiki/1a-Feature-notes

Description of my systems that have been operating in the field for some years https://www.envirodiy.org/n-ca-mayflys-through-the-winter-storms/ 

I did do a crude Electrical Conductivity sensor – measuring the waters EC through a simple sensor, and no wireless – I don’t think this is going to be that helpful but this is where it is – https://github.com/neilh10/ModularSensors/tree/release1/examples/tu_ec01

Goodluck

 

 

 

[neilh20]

Good to hear. and definitely very interested in use with a flume.  What range of water depth measurement are you looking for and depth accuracy of measurement.

Typically, because  ADCs are not good around 0.0V and rail – the Sensor range from 0.0 to full range, the Voltage range is from 10%Rail to 90%Rail (eg 0.3V to 3.0V for 3.3VDC rail) and typically wants to be ratiometric. That is the ADC voltage VREF is the same as that used by the sensor.  The other way of adjusting that is that the measuring device has a greater range than the actual range of measurement. That is 0.0  is less than the flume entrance.

Resistance  varies with temperature (as everything in the universe varies with temperature)- so they haven’t listed what the temperature dependency is.   It might not be much in the water, and may need a reasonable shield for that out the water.

How are you looking to power it, Are you looking to report it wirelessly?   Standard ModularSensors is best effort in both cases.

 

[neilh20]

Hi Eric – I’ve purchased and looked at using the eTape.

I was just wondering what made you think they are “calibrated” – that is the “0.0” markings relates to a defined voltage.  Usually this is shown for a calibrated sensor.  Similarly for a Full Scale relates to a known Voltage.?

looking at the graphs,  they describe “0-5VDC Linear Resistance to Voltage Module PN-05V00199 REV 2” but no graphs of 0.0 to V

https://img1.wsimg.com/blobby/go/ab87a52f-5721-46e1-a348-7722f5ddcf97/downloads/0-5%20VDC%20Module.pdf?ver=1737319366040

Be interested if you find that they have it calibrated .

[neilh20]

Hello Elliot, gosh too bad for the experience. Its a core issue of how to ensure there is enough power, and then also recover from low power events.  However to be able to get some help its best to post the details of the two systems.  Need to phrase the problem with enough detail that some one else might be able to comment.  Battery size and source, Solar panel wattage and source, Mayfly version and software. I would also suggest the graph of when it was operating well and then when it was drooping. Also of course the  aspect of the solar panel. The 5V Solar Panels don’t do very well in shade.

[Author]

Posts