Getting my fridge to follow a temperature profile from a Google spreadsheet

Posted on Jan 2, 2012 in JavaScript, PHP, Programming, Python, UberFridge | 0 comments

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;amp;key=0AgHYRKRROsRFdHQ2d0c4RkROQUtOV3lZbHFfRVV1N1E&amp;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;amp;where=D&amp;lt;date "2070-01-01"&amp;amp;key=0AgHYRKRROsRFdHQ2d0c4RkROQUtOV3lZbHFfRVV1N1E&amp;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);
}

Leave a Reply

Your email address will not be published. Required fields are marked *