Bobby Leonard
Published © GPL3+

Python/MicroPython Sensor Logger with Google Sheets

Wemos D1 mini takes sensor readings and sends a POST request containing data to Flask Server on RPi 3. Data is then written to Google Sheet.

IntermediateFull instructions provided2 hours10,687
Python/MicroPython Sensor Logger with Google Sheets

Things used in this project

Hardware components

Raspberry Pi 3 Model B
Raspberry Pi 3 Model B
×1
Wemos D1 Mini
Espressif Wemos D1 Mini
×1
DFRobot Capacitive Soil Moisture Sensor
×1

Software apps and online services

Google Sheets
Google Sheets
python3
MicroPython
MicroPython

Story

Read more

Schematics

circuit

Wiring

Code

main.py

MicroPython
The main file for micropython board.
Connects to wifi, sets time with NTP, then sends sensor readings to the RPi3 in a POST request.
When its done, goes to sleep to conserve battery power.
import machine
import urequests
import time

rtc = machine.RTC() # Clock for deepsleep
rtc.irq(trigger=rtc.ALARM0, wake=machine.DEEPSLEEP)

adc = machine.ADC(0) # Pin to Read sensor voltage

######################
# Sensor calibration #
######################

# values on right are inverse * 1000 values on left
# dry air = 759 (0%) = 1.31752305665349143610013175231
# water = 382 (100%) = 2.61780104712041884816753926702
# The Difference     = 1.30027799046692741206740751471
# 1 %                = 0.0130027799046692741206740751471

hours = str(time.localtime()[3])
mins = str(time.localtime()[4])
secs = str(time.localtime()[5])
if int(secs) < 10:
	secs = '0' + secs
if int(mins) < 10:
	mins = '0' + mins
timestr = hours + ':' + mins + ':' + secs

variable = (((1 / adc.read())* 1000) / 0.0130027799046692741206740751471) - 101
if variable > 100:
	variable = 100
if variable < 0:
	variable = 0

url = 'http://192.168.1.2:8000/solomon'
headers = {'content-type': 'application/json'}
data = '{"Value": "%s", "Time": "%s"}' % (variable, timestr)
resp = urequests.post(url, data=data, headers=headers) # Send the request
print(resp.json())

rtc.alarm(rtc.ALARM0, 25000) # Set alarm for 25 seconds
machine.deepsleep() # Go to sleep ...

WebService.py

Python
The web app which records the data and sends to Google sheets. Run on RPi3
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from flask import Flask, jsonify, abort, make_response, request, url_for

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']


def gAuth():
	global credentials, gc, worksheet
	credentials = ServiceAccountCredentials.from_json_keyfile_name('clientsecret.json', scope)
	gc = gspread.authorize(credentials) # Authorise with Google
	worksheet = gc.open("gsheet").sheet1 # Open the worksheet


gAuth()
	
worksheet.clear() # Delete Old Values

worksheet.append_row(['Time', 'Soil Moisture'], value_input_option='RAW') # Add column names

app = Flask(__name__)

readings = [
    {
        'DataPoint': 1,
        'Value': u'SoilMoistureReadings:',
        'Done': False
    }
]
	
@app.route('/solomon', methods=['POST'])
def create_reading():
    if not request.json or 'Done' in request.json == True:
        abort(400) 
    reading = {
        'DataPoint': readings[-1]['DataPoint'] + 1,
        'Value': request.json.get('Value', ""),
        'Time': request.json.get('Time', ""),
        'Done': False
    }
    readings.append(reading)
    variable = request.json.get('Value')
    if float(variable) > 100:
        variable = 100.00
    if float(variable) < 0:
        variable = 0.00
    
    worksheet.append_row([request.json.get('Time', ""), str(request.json.get('Value', "") + ' %')], value_input_option='RAW')

    return jsonify({'reading': reading}), 201
	

@app.errorhandler(404)
def not_found(error):
    return make_response(jsonify({'error': 'Not found'}), 404)

@app.errorhandler(400)
def not_found(error):
    return make_response(jsonify({'error': 'The request could not be understood by the server due to malformed syntax.'}), 400)

@app.errorhandler(500)
def NotAuth(error):
	gAuth()
    return make_response(jsonify({'error': 'Not Authorised. Reauthorising...'}), 500)	


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8000, debug=False)

boot.py

MicroPython
Network Config for micropython board. Change 'WIFI-Network' and 'Password' to your own values.
import esp
import network
import socket
import gc
esp.osdebug(None)
from ntptime import settime
gc.collect()

def do_connect():
        sta_if = network.WLAN(network.STA_IF)
        if not sta_if.isconnected():
                print('connecting to network...')
                sta_if.active(True)
                sta_if.connect('WIFI-Network', 'Password')
                while not sta_if.isconnected():
                        pass

do_connect() # Connect to WIFI
settime()    # Use NTP to set clock

Credits

Bobby Leonard

Bobby Leonard

6 projects • 36 followers
Making and breaking computers and electronics. Coding, Networking, Data Sec etc etc. Physics fan also.

Comments