Go Down

Topic: Send multiple values from multiple Arduinos to the same Google spreadsheet (Read 1 time) previous topic - next topic

berra

I wanted to send bursts of sensorvalues to Google spreadsheet without delays in execution of sketch. I was unsuccessful with runAsynchronously().

I came up with the following script that works quite well, but since I am new to all technologies involved (Python, Linux, Arduino), I am curious if someone has a better approach.


Code: [Select]

import gspread
import sys
import time

sys.path.insert(0, '/usr/lib/python2.7/bridge/')
import json                                             
from bridgeclient import BridgeClient as bridgeclient

#google account specifics                                                     
myAccount='karatemaskin@gmail.com'                                             
myPassword='karatemaskin1'
myDocument="karatemaskin"
mySheet="data"
myTimeCell='d1' #cell must contain =NOW()
myRowCountCell='b1' #cell must contain COUNT(A:A)

#This script moves data from Arduino bridge to google spreadsheet asynchronyosly.
#The scenario is that multiple Arduinos ship bursts of data to the same spreadsheet, sometimes at the almost same time
#It is best used when sensor data comes in cycled bursts, faster than can be consumed by google, but you don't want delayes in the Arduino excecution.
#Sketch must build series of bridge variables: val+counter. Important that every new cycle is initiated by a 1 in beginning of string (defined as incrementTag below).
#This script keeps count of val + counter and stays logged in to Google which increases performance
#Another key thing is to send one string only to Google spreadsheet, separated by ":"
#Using the SPLIT function will instantly put multiple values into spreadheet.

#this script requires you to run
#okpg update
#okpg python-openssl
#okpg python-expat

#it also requiers gpsread files (google Github to get insructions)
#and the formula =NOW() in the myTimeCell cell, the =COUNTA(A:A) formula to be in the myRowCountCell above


#the key exceptions handled are:
# 1 restart of Arduino
# 2 restart of Linuino
# 3 involuntary log out from Google
# 4 changing number of sensor value per cycle



#********************** script**********


#When script is started (first time), log into google
gc = gspread.login(myAccount, myPassword)

# Set object of a workbook and sheet to use later
wks = gc.open(myDocument).worksheet(mySheet)

# in this application, cell d1 contains the now() function. Since multiple Arduinos write to the same spreadsheet
# it is critical to have one definition of time. The value is stored in timeStamp
timeStamp=wks.acell(myTimeCell).value

#Flag that this is first run of script
firstRun=1

#oldString keeps track of previous value in Bridge variable val0. This is necessary to catch some exceptions
#When script is initiated, this must be reset to ''
oldString=''

#the que of variables are 'val' + counter. The counter is defined as valCounter and now starts at 0
valCounter=0

#initiation of bridgeclient
value = bridgeclient()

#This while statement clears que of val+counter values. This is necessary since the number of sensor reads per burst cycle can vary.
while firstRun==1:
try:
#compose val0
valString='val' + str(valCounter)                             

#get val0 from bridge
resultString=value.get(valString)

#if val0 is not yet defined in bridge, keep trying
if resultString==None:
firstRun=0
valCounter=0

#if val0 is defined/sent from sketch, clear val0 and start asking for val1
else:
value.put(valString,'')
valCounter=valCounter+1
#if no val+counter values exist in bridge, keep trying
except:
firstRun=0
valCounter=0

#Now, val+counter values have started to ship from sketch. This is a perpetual loop looking for val+ counter until there is a new val0.
#When val0 is changed, valCounter starts again from 0 (a new burst of sensor values is expected to come)
while True:
try:
#get value from bridge
valString='val' + str(valCounter)                             
resultString=value.get(valString)
if resultString==None:
resultString=''

#if there is a val + counter in bridge that has more than 2 characters
if len(resultString)>2:

#read values must be reset to keep count
if valCounter!=0:
value.put(valString,'')

#get mac ID. This is specific for application since multiple Arduinos send data to the same Spreadsheet, It is then necessary to identify the specific Arduino.
from uuid import getnode as get_mac
mac = get_mac()

#test if new increment by getting first character of resultstring. if 1, then new incrememnt/cycle of sensor values
if valCounter==0:
try:
timeStamp=wks.acell(myTimeCell).value
#if error, Google logged us out. try logging in again
except:
#log in
gc = gspread.login(myAccount, myPassword)
wks = gc.open(myDocument).worksheet(mySheet)
timeStamp=wks.acell(myTimeCell).value

#store resultstring for val0 to know when new burst of sensor values will come
oldString=resultString

#create next val+counter increment (this will keep incrementing as long as the sketch keeps incrementing val + counter)
valCounter=valCounter+1

#get string from sketch minus the first character which only indicates if new increment/burst of sensor values
#the increment tag must not go to spreadsheet
resultString=resultString[1:]

try:
#get the count of rows with values +3 since we have 2 header rows before values in table start (in this specific application)
val = int(wks.acell(myRowCountCell).value) + 3

except:
#if force logged out from Google, try again
gc = gspread.login(myAccount, myPassword)
wks = gc.open(myDocument).worksheet(mySheet)
val = int(wks.acell(myRowCountCell).value) + 3


#compose the string to put into the spreadsheet. Since it is separated by :, the SPLIT function will automatically distribut across columns
mac=str(mac) + ':'
timeStamp=str(timeStamp) + ':'
timeStampDay=timeStamp[:10] + ':'
timeStampHour=timeStamp[12:13] + ':'
resultString=timeStampHour + resultString
resultString=timeStampDay + resultString
resultString=timeStamp + resultString
resultString=mac + resultString
resultString='=split("' + resultString + '";' + '":")'

#put the resultstring into the one cell, row per val and column 1 (first column is 0)
wks.update_cell(val,1 , resultString)
#clear reulstString
resultString=''
else:
#no action from sketch, keep looking for changes in val0 by comparing oldstring and resultstring
resultString2=value.get('val0')

#if change, start cycle from val0 and store the new resultstring as oldstring
if resultString2!=oldString:
valCounter=0
oldString=resultString2

except:
# no val + counter variable on bridgeclient, then test if new cycle (val0) has started
try:
resultString2=value.get('val0')
if resultString2!=oldString:
valCounter=0
oldString=resultString2
except:
print 'no new val0'
#no val0 on bridgeclient
time.sleep(0.1)




Go Up