Great stuff.
I managed to get data into Google_sheet and get the graph out of it (thank you for the exampe)
For the graph part, is it possible to auto update data range? Let me explain better:
to create a graph I have to select the range of data. Since data automatically increase due to script/trigger, if I want to have an updated chart I have to reselect the full data range.
Is there a way to tell Gsheet to always get the full set of data?
thank you
I had to increase the size of the spreadsheet to go down 1300 boxes by go to the bottom of the spread sheet where it says add 20 boxes, I typed in add 1000 boxes. Then I highlighted the top ABC rows that had data in them, and then I chose to make the graph and it kept auto updating the graph within google drives spreadsheet page.
Iād add that another easy thing to do from Google Apps Scripts is to send emails.
This could be used in the cases like when the temp rises above a certain level, or rises too fast too quickly, or drops below a certain point; alerts when itās unexpectedly freezing so you can check pipes, or emails to discover that your HVAC system may not be on (or not working at all).
For example, after the sheet.appendRow() line in the collectData() script above, add
var triggerTemp = 90; // test is Fahrenheit, adjust for Celsius
if (p.data1 > triggerTemp) {
MailApp.sendEmail('youremail@address.com', '[ SPARK ALERT ] It's getting hot in here!', 'The current temp is : ' + p.data1);
}
You might want to add in logic to see if youāve already sent the email so you only send it once, or to check if it falls back below your trigger temp. (Google does have a method call you can use to see how many more emails you can send that day, if youāre sending a lot).
You might have to run the script once in the script editor to authorize the script to send emails, but that depends on various other settings, IIRC.
Thereās no end to the things you can do here, including interfacing with other services easily, like Twitter. 
Once Spark has the ability to call https URLs, you can also turn these scripts into āweb appsā in Google Apps, which provide a URL that you can simply make a GET request to and do all manner of things. For instance, instead of Google polling to see if the temp is over a limit, the Spark could simply call a URL when it senses itās too hot and send the alert email, not waiting for your polling interval.
Thatās Awesome. I hope someone keeps exploring these features and post examples showing others how to do it. It looks like Google App is providing the easiest to implement online logging, graphing, and feedback platform that I have come across so far.
I would love to see the logic added to your email script to only send emails once per event instead of unlimited times.
The graphing is working great for me.
Just an ideaā¦ what if your app kept track that it sent the email alertSent = true; and if it wants to send another alert if checks if(!alertSent){ //send alert } That should fix the multiple sends if you just want it sent once. Now the idea, is it possible to reply to the email sent from the Google app with a response: "ok" that would allow you to reset alertSent = false; ? That way you wonāt get flooded with emails if you are not paying attention, but can easily re-enable it remotely when you want to start paying attention again.
Yea that would be perfect. That could be useful in so many different scenarios.
The thing that Iām not sure about is if a time-based trigger maintains state; if it does, then a technique like what @BDub suggested would work, but if not there would need to be another way.
The first two I could think of would be:
Add a column to the spreadsheet data that is a simple binary 0 or 1. Before adding a new row, check the value of this third column in the current row (which should be the last, previously added, row). If the incoming value triggers the alert, and the previous rowās third column is 0, send the alert. Then add the new row, with the third column being set to 1 (or leave 0 if not a trigger value). This will keep the email to being sent only with the initial trigger. Resetting this could be either via the incoming value falling below a threshold, or via user access.
Alternatively, you could use the Spark as more than a ādumb sensorā. That is, put the logic in the Spark to check if the value is a trigger value and add an appropriate querystring value to the GET call to Google. Then you could certainly maintain state on the Spark. The downside to this is that to adjust the trigger values would either require additional coding to handle an incoming URL or digital input (think: thermostat-like display with up/down buttons), or changing a variable and recompiling.
The MailApp API I used above is only for sending emails. There is a separate GmailApp object API that lets you manipulate your Gmail account. So potentially you could look at your inbox for a new message with a specific subject line, and reset the alert trigger.
@RWB That looks awesome! Iāve got a dumb question for you. How did you get it to show the time on your graph. Where are you pulling time from? Iāve got a DHT22 that is pulling temp and humidity and it works great, but my horizontal access is empty.
This thread has come a long way. I originally saw this when it was only the first couple of posts.
I built a solution to accomplish this same goal. Funny enough, Iām logging the same stuff. Temp/Humidity.
Maybe youāll find my graph interesting here.
For charting, I really like highcharts, but googleās guages look fun. Iāll have to check out what else theyāve got.
Any idea or help to get a Google drive access token?
@CNBurger, I donāt understand what youāre trying to do here.
Why do you need a Google access token?
@RWB So I found out I was using the wrong chart. I looks so much better. Sadly when I try and publish the chart, dreamweaver keeps giving me a syntax error. Itās a direct copy and paste, Iām not a java guy, so Iām at a bit of a loss.
<script type="text/javascript" src="//ajax.googleapis.com/ajax/static/modules/gviz/1.0/chart.js"> {"dataSourceUrl":"//docs.google.com/spreadsheet/tq?key=0AiKT6tJF6MRSdEpkVDlzdVQ5TFZjSzRuZXUwNTJaMGc&transpose=0&headers=1&range=A1%3AC201&gid=0&pub=1","options":{"displayAnnotations":true,"titleTextStyle":{"fontSize":16},"vAxes":[{"useFormatFromData":true,"title":"Left vertical axis title","minValue":null,"viewWindow":{"max":null,"min":null},"maxValue":null},{"useFormatFromData":true,"minValue":null,"viewWindow":{"max":null,"min":null},"maxValue":null}],"booleanRole":"certainty","title":"Chart title","height":371,"animation":{"duration":500},"legend":"right","width":600,"wmode":"opaque","hAxis":{"useFormatFromData":true,"title":"Horizontal axis title","minValue":null,"viewWindow":{"max":null,"min":null},"maxValue":null},"focusTarget":"series","tooltip":{"trigger":"none"}},"state":{},"view":{},"isDefaultVisualization":false,"chartType":"AnnotatedTimeLine","chartName":"Chart 2"} </script>
Anyone have ideas? It works fine if I publish as an image, but the interactive chart wonāt work.
Really wanted to share my graph. Thanks for sharing this!
Yea It looks like the data in the chart will only refresh ever time you load the page. There has to be a way to automate the refreshing of the chart though.
You have to set the chart to publicly available also for others to see it or it will not show up.
Can you show us how you got the graphs working with HighCharts?
Highcharts has amazing demos and documentation.
They have a demo with love updating and source available.
can you share your code from highcharts?
@RWB we are already waiting @kareem613 's web app 
Itās just JavaScript. View source here
Ok. Soā¦
Google App Scripts by themselves do not maintain state; each time they runāmanually or triggeredāall variables get reset, even if created outside of the function that is called in the trigger.
However, each script has its own database ā as a ScriptDB object. This allows a lot of values to be stored and queried on subsequent runs of a script.
Iāve mashed up some code I wrote to change the state of a value in a database with @binaryfrostās initial spreadsheet code. I havenāt tested this with a time-based trigger, just running it manually.
function collectData() {
// initialize some variables
var hasSentAlertEmail = false;
var record;
var temperatureTriggerValue = 90; // what temp the alert email will be sent, in Fahrenheit
var temperatureTriggerReset = 80; // what temp the alert will be reset (temp falls below this)
// grab the ScriptDB for this script
var db = ScriptDb.getMyDb();
// see if there is an existing record
var numRecs = db.count({});
// if no existing record, initialize the db
if (numRecs == 0) {
Logger.log("No data found");
Logger.log("Creating db data");
var baseRecord = {hasSentEmail: 0};
db.save(baseRecord);
}
// get the current value from the database
record = db.query({hasSentEmail: db.anyValue()});
var currentRec = record.next();
// set a flag as to whether or not we've previously sent an email
if (currentRec.hasSentEmail == 1) {
hasSentAlertEmail = true;
} else {
hasSentAlertEmail = false;
}
// normal logging of values to spreadsheet
var sheet = SpreadsheetApp.getActiveSheet();
var response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/YOUR-DEVICE-ID/result?access_token=YOUR-ACCESS-TOKEN");
try {
var response = JSON.parse(response.getContentText()); // parse the JSON the Core API created
var result = unescape(response.result); // you'll need to unescape before your parse as JSON
try {
var p = JSON.parse(result); // parse the JSON you created
var d = new Date(); // time stamps are always good when taking readings
sheet.appendRow([d, p.data1, p.data2]); // append the date, data1, data2 to the sheet
// check if we need to send alert email
if (p.data1 > temperatureTriggerValue && !hasSentEmail) {
// send email
MailApp.sendEmail("myemail@addy.com", "[ ALERT ] Spark value trigger!!", "The value is greater than the trigger amount. Oh noes!");
// set the flag in the ScriptDB
currentRec.hasSentEmail = 1;
db.save(currentRec);
}
// check if we've fallen below reset value and have previously sent an email
if (p.data1 < temperatureTriggerReset && hasSentEmail) {
currentRec.hasSentEmail = 0;
db.save(currentRec);
}
} catch(e)
{
Logger.log("Unable to do second parse");
}
} catch(e)
{
Logger.log("Unable to returned JSON");
}
}
This should initialize a database with a key-value pair if one doesnāt exist, send an email once when needed, then reset the value if the temp falls below a point.
Obviously you could have different ways of resetting the value, or putting each time thereās an alert into a different spreadsheet, or what have you. You could also have another value stored in the DB that is incremented each time thereās a value above the trigger value, and if you have X number of high values, send another email.
Hope this helps!
is there any specific Highcharts guide you followed to ge that chart working? Iām no expert so instructions is the only way I get this stuff working.