Articles Hierarchy

Articles Home » RPI » RPI feeds google spreadsheets with ARDUINO data

RPI feeds google spreadsheets with ARDUINO data

prior info

after the manual feed of a url with data to a google spreadsheet works
i now setup a new Raspberry PI system, because long time i did not play
and i hope there is some better system software now.

from raspberrypi.org i download the RASPBIAN DEBIAN WHEEZY release 20.6.2014 (788MB (ZIP) under one hour )
here you see how to write the SD card from windows.
i just hope that "old way" is little bit faster as the NOOBS way.


while the download runs i have time to look for some news about RPI:
Raspberry Pi B+ model ( 1.382 THB )
datasheet B / datasheet B+
first idea: now have 4 USB ( instead of 2 ) and header 40 pin ( instead of 26 )
now use a uSD card
the power circuit got an overhaul ( 1.9W down to now 1.2W )

and the new Raspberry Pi Compute Module Kit ( 8.437 THB ) shop



i format a old 8GB SD card and burn the ( unzip 3GB ) wheezy / an other hour.
( and edit the config.txt file with my tuning and codec info )

ok some more details to the hardware setup:
- RPI in housing
- camera installed ( but not used in this project )
- ethernet cable to router
- ( usb radio adapter to logitech mouse + keyboard if connected HDMI TV only )
- powered (5V 2.5A) belkin 4 usb HUB with 8GB usb stick ( and wireless usb adapter if no ethernet cable )
( that is powering the RPI backward via the usb cable )

SD in, power on, RPI show as 192.168.1.37 ( in IP scanner)
putty ssh to pi@192.168.1.37 [yes] raspberry [enter]
lucky, don't need to move downstairs to connect to TV, as SSH is enabled i can start headless.

here a short remark to the working style:
i highlight the RPI commands BOLD
when you run a putty window to RPI
and you select that commands from browser window with mouse and copy ( or CTRL C )
and move mouse to PUTTY window and press mouse right button
that command is in putty and you only have to press enter
that is very fast, i hope it works for you too!


sudo raspi-config
system SD resize, asia bangkok, camera enable..reboot

sudo rpi-update
sudo reboot

sudo apt-get update

sudo apt-get upgrade
[Y]

sudo apt-get install mc
[Y]

sudo apt-get install tightvncserver
[Y]
vncserver :1
( password piremote, piremote, n )

use this code
sudo nano /etc/init.d/vncserver
sudo chmod 755 /etc/init.d/vncserver
sudo update-rc.d vncserver defaults
sudo reboot


to make a static IP, use this code
sudo nano /etc/network/interfaces
what i will need for router ( and dyndns / not free anymore / config ) if i use RPI as webserver.
sudo reboot
now you will find the RPI on the new IP on the ethernet lan

sudo apt-get install arduino
[Y]
sudo apt-get install arduino-mk avrdude-doc equivs libjna-java-doc icedtea-plugin libnss-mdns fonts-ipafont-gothic fonts-ipafont-mincho ttf-wqy-microhei ttf-wqy-zenhei ttf-indic-fonts
see 2 errors / connection failed, so do it again
sudo apt-get update
sudo apt-get install arduino-mk avrdude-doc equivs libjna-java-doc icedtea-plugin libnss-mdns fonts-ipafont-gothic fonts-ipafont-mincho ttf-wqy-microhei ttf-wqy-zenhei ttf-indic-fonts

again error.

anyhow, after about 4 hour i have a new setup RPI , fully updated, headless operation and ready for arduino work:


for a first test to connect to google: from here
sudo curl -k "https://script.google.com/macros/s/A...L/exec?TIMESTAMP=$(date +%F@%T)&TEMP_PV=2.0&TEMP_SP=3.0&OUTPUT=50"
works, but the @ in the time format string makes problems in the graph,
and different from the browser tests i not get the "DATA LINE APPENDED" , only a "DOCUMENT HAS MOVED TEMPORARILY" info back ( to PUTTY )??.

?TIMESTAMP=$(date +%D+%T) seems to be right syntax for my sheet ( and cell date setting! )


now the linux work starts:
as there are prior projects about the connection arduino - RPI
i want start with some existing python code

example arduino data to sd card file and also show in web (server)

example arduino data to graph ( for poor man scope )

and in the camera tests is a short part about making a service operable from desktop
esp. for arduino reconnection... it might be better as just reboot the RPI also.

but first need to install a serial library for PYTHON:
sudo apt-get install python-serial // already exist in this RPI version

this code goes here
sudo nano /home/pi/arduinoproject/arduinostream.py

pls. find
mygooglekey = "A..L"
and fill in the keycode for your own google online macro spreadsheet

pls. find
fstore = 30.0
and select your own google sample time ( in sec.)


now in RPI desktop ( VNC) pls start IDLE
file open home/pi/arduinoproject/arduinostream.py [open]
RUN run module [F5]
if no arduino connected it goes into serial exception error.


next step: make the python autostart
-- make it executable
chmod +x /home/pi/arduinoproject/arduinostream.py
-- auto start it at boot with this code
sudo nano /etc/init.d/arduinostream
sudo chmod 755 /etc/init.d/arduinostream
sudo update-rc.d arduinostream defaults
optional remove auto start
sudo update-rc.d -f arduinostream
for start
sudo /etc/init.d/arduinostream start
for stop ( f.e. to run arduino IDE )
sudo /etc/init.d/arduinostream stop
check with top for python, if it still shows

now to make that service operable from desktop need additionally
with this code
sudo nano /home/pi/Desktop/arduinostream.desktop
sudo chmod +x /home/pi/Desktop/arduinostream.desktop
and with this code
sudo nano /home/pi/arduinoproject/arduinostream.sh
sudo chmod +x /home/pi/arduinoproject/arduinostream.sh


now in RPI desktop ( VNC) pls find arduinostream service button
with double click get a LXterminal ( to read info )
and a zenity option window to choose / operate the service.
( zenity is a nice RPI build in tool )


for work i need a arduino connected and now finally can get into the code:
from the historic project there was a
- CSV file ( SD card or USB stick) open / write one line ( after catch from USB - arduino ) / close file
( that will be optional / disabled, because now we want store the data to online google spreadsheet )
- current record file ( one line ) in the RPI RAM-DISK for use by webserver...
( that will be optional / enabled because data will be needed for OPERATION of PID controller )
pls find: run/shm/arduinodata.csv
the recordfile contains one line ( last line from arduino)
first the by RPI generated timestring as
,YYYY,MM,DD,HH,MM,SS, _ optional debug text... from arduino or datastring from arduino
the datastring should be send as ,PV,SP,OUTPUT,,
,2014,07,21,17,20,07,,84.73,80.0,90.0,,

the default will be the google connection for the data collection and graphical representation.
i tested with a arduino leonardo sending all 5 sec a ,PV,SP,OUT,, with a simulated PV by a sinus
and in arduinostream python catch program i get all lines from arduino,
but send to google only all 30sec.


how to send google data URL by python?
import urllib
content = urllib.urlopen("%s%s/exec?TIMESTAMP=%s&TEMP_PV=%s&TEMP_SP=%s&OUTPUT=%s" %(googlepage, mygooglekey, wtime, TPV, TSP, TOUT)).read()
print content


operate python arduino data stream service / default is auto start at boot RPI

load leonardo from IDE

created ONE RECORD file in RAM DISK

run python from python 2.7 interface

google macro spreadsheet with graph


how to make your own macro sheet?
pls. copy example from here and follow that manual

then add a chart for the long range of the sheet ( 8xx lines)
the macro service adding lines has no problems with that limit, it can add more
the graph has, there later better use the button [ ADD 1000 lines ] and ( small arrow down) in advanced edit of the graph change the cell range manually.

pls find all code again as ZIP

next step