I wanted an easy way to set temperature profiles that my fermentation fridge would follow. This would enable me to:
- Start a fermentation on a lower/higher temperature and gradually bring it to a normal fermentation temperature.
- Slowly raise the temperature near the end of fermentation to boost attenuation
Google spreadsheets turned out to be a nice way to do this:
- They can be queried like a database
- They can be accessed from anywhere
- Working with cells is easier than working with a txt file
- They work well with the Google Visualization API
You can view a sample of the spreadsheet I use here.
Querying the spreadsheet
The content of specific cells can be downloaded as a CSV file directly with the Google Visualization API Query Language.
The query can even be added to the URL, so the router can directly download the file with wget. The following PHP code downloads the temperature profile from the spreadsheet:
<?php $output1 = shell_exec("wget -nv -O /mnt/<strong>uberfridge</strong>/settings/tempprofile.csv \"http://spreadsheets.google.com/tq?key=0AgHYRKRROsRFdHQ2d0c4RkROQUtOV3lZbHFfRVV1N1E&tq=select D,E&tqx=out:csv\" 2>&1"); $output2 = shell_exec("cp /mnt/<strong>uberfridge</strong>/settings/tempprofile.csv /mnt/<strong>uberfridge</strong>/settings/currentprofile.csv 2>&1"); echo "Profile copied, wget output:" . $output1 . $output2; ?> |
The query requests column D and E from the spreadsheet in CSV format. The file is downloaded to a temporary file first, so that the script cannot access the file while it is still being downloaded. That caused the script to crash.
The CSV file looks like this:
"Date & Time","Setting (°C)" 14/11/2011 16:00:00,18 14/11/2011 18:24:00,18 14/11/2011 18:24:00,19 15/11/2011 01:36:00,19 15/11/2011 01:36:00,18.5 15/11/2011 08:48:00,18.5 15/11/2011 08:48:00,19 15/11/2011 16:00:00,19 15/11/2011 20:48:00,20 16/11/2011 01:36:00,20 |
Reading the CSV file in Python
The Python script iterates over the rows of the CSV file, skipping the header. I compare the date with the current date and stop when I have found the two settings it should be in between. I interpolate the temperature in between these settings and return it. Two special cases are when all dates are in the future or all dates are in the past. I use the first or last setting in those cases.
When the script is in profile mode, it calls this function repeatedly and when the result is different than the current setting, it sends the new temperature to the Arduino.
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 | import time import csv def getNewTemp(): temperatureReader = csv.reader(open('/mnt/<strong>uberfridge</strong>/settings/currentprofile.csv','rb'), delimiter=',',quoting=csv.QUOTE_ALL) temperatureReader.next() #discard the first row, which is the table header prevTemp = -1; nextTemp = -1; prevDate = -1; nextDate = -1; interpolatedTemp = -1; now = time.mktime(time.localtime()) # get current time in seconds since epoch for row in temperatureReader: datestring = row[0] if(datestring != "null"): temperature = float(row[1]) prevTemp = nextTemp nextTemp = temperature prevDate = nextDate nextDate = time.mktime(time.strptime(datestring, "%d/%m/%Y %H:%M:%S")); timeDiff = now - nextDate; if(timeDiff < 0): if(prevDate == -1): interpolatedTemp = nextTemp #first setpoint is in the future break; else: interpolatedTemp = (now - prevDate)/(nextDate-prevDate)*(nextTemp-prevTemp)+prevTemp break; if(interpolatedTemp == -1): #all setpoints in the past interpolatedTemp = nextTemp return int(interpolatedTemp*10+.5) #retun temp in tenths of degrees |
Displaying the profile in the web interface
The web interface also queries the spreadsheet to display a table and a graph of the temperature profile. I use the query response format for the chart and table. The datatable can be extracted from the response easily. For the table, I compare the date with a date in 2070 to remove empty cells from the query response.
function drawProfileChartAndTable() { drawProfileChart(); drawProfileTable(); } function drawProfileChart() { var query = new google.visualization.Query('https://docs.google.com/spreadsheet/tq?range=D:E&amp;key=0AgHYRKRROsRFdHQ2d0c4RkROQUtOV3lZbHFfRVV1N1E&amp;gid=0'); query.send(handleProfileChartQueryResponse); } function handleProfileChartQueryResponse(response) { if (response.isError()) { alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage()); return; } var profileData = response.getDataTable(); profileChart = new google.visualization.AnnotatedTimeLine(document.getElementById('profileChartDiv')); profileChart.draw(profileData, { 'displayAnnotations': true, 'scaleType': 'maximized', 'displayZoomButtons': false, 'allValuesSuffix': "\u00B0 C", 'numberFormats': "##.0", 'displayAnnotationsFilter' : true}); } function drawProfileTable() { var query = new google.visualization.Query('https://docs.google.com/spreadsheet/tq?range=C:E&amp;where=D&lt;date "2070-01-01"&amp;key=0AgHYRKRROsRFdHQ2d0c4RkROQUtOV3lZbHFfRVV1N1E&amp;gid=0'); query.send(handleProfileTableQueryResponse); } function handleProfileTableQueryResponse(response) { if (response.isError()) { alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage()); return; } var profileData = response.getDataTable(); var profileTable = new google.visualization.Table(document.getElementById('profileTableDiv')); profileTable.draw(profileData,null); } |
