ESP8266 Community Forum

- Mon May 16, 2016 7:44 am #47506 For those interested in easy IOT data collection, here is how you can generate googdle spreadsheets and graphics directly from your ESP8266.

1) Use a firmware allowing https (like fightanic's dev branch wiuth crypt & http)
2) Open a googlespreadsheet and note its id (appears in the uRL - something like 1YzIqQF21uOXs2HECXD_yP_30mKg1vK4Gsdxxxxxxxx

Put the value "2" in cell E1, "Ticker" in A1 and "Values" in B1

3) Create the following google script

var ss = SpreadsheetApp.openById(' --- your spreadsheet id ------------------');
var sheet = ss.getSheetByName('Sheet1');
var maxval = 65535;

function doPost(e) {
doGet(e);
}

function doGet(e) {
var val = e.parameter.value;

if (e.parameter.value !== undefined){
var idex = sheet.getRange('E1');
c = idex.getValue();
idex.setValue((c-1) % maxval+2);
var range1=sheet.getRange("A"+c);
var range2=sheet.getRange("B"+c);
var d = new Date();
var t = d.getTime();

range2.setValue(val);
range1.setValue(t);

return ContentService.createTextOutput(t+": "+val);
}
return("Missing arg");
}
4) Publish it (deploy as web app, Execute as Me, Anyone including anonymous has access)
The link shoud look like this https://script.google.com/macros/s/yyyy ... fdfdf/exec

5) Use the following lua file to update your excel with a new data (here, the voltage of the power)

conn=net.createConnection(net.TCP, 1)
url="/macros/s/yyyyyyyyyyyydfdfdfdf/exec?value="
args=adc.readvdd33()
conn:on("receive", function(cnn, payload)
print("Received")
cnn:close()
end)

conn:on("connection",function(cnn)
print("Connected")
cnn:send("GET "..url..args.." HTTP/1.1\r\nHost: script.google.com\r\n"
.."Connection: keep-alive\r\nkeep-alive: 1\r\nPragma: no-cache\r\n"
.."Cache-Control: no-cache\r\nContent-Type: application/x-www-form-urlencoded"
.."\r\nAccept: */*\r\n\r\n")
end)

conn:on("sent",function(cnn)
print("sent")
end)

conn:connect("443","script.google.com")

That's it !

Note that with googlespreadsheet you can generate a chart and publish it as an image. It will be updated automatically (there a setting for this to force recalculation every minute).

- Mon May 16, 2016 11:32 pm #47537 Cool! I could definitely use this, although the fact that it's publicly available is kind of a deal-breaker

- Tue May 17, 2016 1:07 am #47546 True so far it is "security on obscurity" (ie need to know the Excel file id).

But you can add some security in your google script (like de-enconding the data or checking a calculation based on a crypted checksum to block unwelcomed requested).

V