Example: Logging and graphing data from your Spark Core using Google


#21

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


#22

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.


#23

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. :slight_smile:

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.


#24

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.


#25

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.


#26

Yea that would be perfect. That could be useful in so many different scenarios.


#27

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.


#28

@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.


#29

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.


#30

Any idea or help to get a Google drive access token?


#31

@CNBurger, I don’t understand what you’re trying to do here.

Why do you need a Google access token?


#32

@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.


#33

Really wanted to share my graph. Thanks for sharing this!


#34

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?


#35

Highcharts has amazing demos and documentation.
They have a demo with love updating and source available.


#36

can you share your code from highcharts?


#37

@RWB we are already waiting @kareem613 's web app :smiley:


#38

It’s just JavaScript. View source here

http://sccb.azurewebsites.net/Graph?48ff6b065067555041551287-DHT22-humidity=on&48ff6b065067555041551287-DHT22-temperature=on&48ff71065067555049142387-DHT11-humidity=on&48ff71065067555049142387-DHT11-temperature=on


#39

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! :slight_smile:


#40

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.