Log data to Google Spreadsheet

Due to the resource limit of ESP8266, establishment of HTTPS connection while serving other functions will crash the system. Google spreadsheet doesn't allow HTTP access, so an indirect method must be used. This method might be invalid if google decide to drop HTTP support from script.google.com.

The file to support Google Sheet logging is in extra folder.

A simple script as the proxy to push data to Google Sheet is needed. Here is how to do it.

1. Create a script to access Google Sheet.

a. you must have a google account. obviously.

b. got to “script.google.com”, create a new project, and copy the content of code.gs in extra folder.

c. release it as a Web Application, and set it to “Run as ME” and "anyone can access.” Note the script ID in the URL

2. Create a Spread Sheet for logging.

a. Create a google spreadsheet in any name you like, but note the spreadsheet ID. input the lables at cell 'A1', and 3rd rows. leave 2nd row empty. like this. You might need to manually change the format of Time column to display Date & Time. The default format seems to show Date only.

	A	B	C	D	E
1	Last Update:
2
3	Time	BeerTemp	BeerSet	FridgeTemp	FridgeSet
4

b. rename or keep the sheet label. note it

3. Setup proxy script. you must have access to a server that you can run your CGI script and the CGI script can connect to other hosts.

Do it yourself or ask someone for help. The URL that we will use is this script.

4. Settings In the log setting pages, default to http://brewpiless.local/log

• URL: http:// {your server} / {your path} /logdata.php <= your script in step 3

• Method: POST

• format: script=[script_ID]&ss=[spreadsheet_ID]&st=[sheet_label]&pc=thisistest&bt=%b&bs=%B&ft=%f&fs=%F

  • [script_ID] is the id from step 1c
  • [spreadsheet_ID] is from step 2a
  • [sheet_label] is the value in step 2b
  • thisistest is the passcode for google script app.

• Log time period. <= set the value you like, in seconds.

Then, BrewPiLess will post temperature data to the URL, and the script(logdata.php) at that URL will get the data and do HTTPS request to google script which write the data to the specified spreadsheet.