Like many other people, one of the more fun things to get your :spark: Core to do is interact with the real-world with sensors. The beauty of the Core, perhaps over the arduino, is the ability to access this sensor-read data from anywhere :smile: without the need for additional Shields / punching holes in NAT-routers etc etc. A few people have asked how they can log data, and draw graphs from it - so this weekend I spent some time finding out how to do this with the spreadsheets available in Google Drive. What you’ll need: Working :spark: Core - with sensor(s) - that you expose using a Spark.variable (basic example below). A Google account, so that you can access Google Drive A bit of Google Apps Script (aka Javascript) magic (again, basic example below). I used JSON to format the data from my :spark: Core, so that it was already in a format that’s easily parse-able by the Google Apps Script. char resultstr[64]; void setup() { pinMode(A0, INPUT); // setup A0 as analog input pinMode(A1, INPUT); // setup A1 as analog input // expose your char buffer to the Cloud API Spark.variable("result", &resultstr, STRING); } void loop() { int data1 = analogRead(A0); // read some data int data2 = analogRead(A1); // some some other data // format your data as JSON, don't forget to escape the double quotes sprintf(resultstr, "{\"data1\":%d,\"data2\":%d}", data1, data2); delay(1000); // wait for a second } Test this works with Postman (or your fav RESTFUL client app). You should see a request like this… https://api.spark.io/v1/devices/YOUR-DEVICE-ID/result?access_token=YOUR-ACCESS-TOKEN …return something like: { "cmd": "VarReturn", "name": "result", "result": "{\"data1\":23,\"data2\":26,}", "coreInfo": { "last_app": "", "last_heard": "2014-02-17T20:37:53.300Z", "connected": true, "deviceID": "YOUR-DEVICE-ID-HERE" } } In Google Drive: Create -> Spreadsheet Tools -> Script Editor Add the code below, amended accordingly Resources -> Current Project’s Triggers Filling: collectData - Time Driven - Minutes Timer - Every 15 minutes Google Apps Code for Script Editor: function collectData() { 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 } catch(e) { Logger.log("Unable to do second parse"); } } catch(e) { Logger.log("Unable to returned JSON"); } } This then connects to your :spark: Core every 15 minutes, parses the data returned, and appends it to the spreadsheet auto-magically. You don’t even have to keep the spreadsheet open :smiley: TIP: Don’t make the frequency toooo frequent, otherwise the spreadsheet fills up pretty fast!! As with other Google Drive spreadsheets, it’s then dead easy to go Insert -> Chart, to create a graph that automatically updates as your data comes it. The code can probably be improved somehow, but I hope that’s of some use to someone :slight_smile:

Send 3D.js data from the Spark Core? Or to Google Drive Spreadsheets?

I think you mean http://d3js.org/ ? D3 is a great javascript library for making interactive data visualizations. If you wanted to work with that, it’s just a matter of storing your data somewhere, and building a small page or widget with D3 that does something with that data. If I get a chance I’ll whip up a D3 demo if someone doesn’t beat me to it first. :smile: Thanks, David

@Dave I’d love to see some different data visualization options.

D3 is amazing, but I have very minimal experience with it. RWB. If you can put something together, I’ll try incorporating it in my Spark Tools web app.

lol Thanks @Dave I need to be more careful with my typing! Yes [quote=“Dave, post:57, topic:2929”] I think you mean http://d3js.org/ ? [/quote] That is what I mean. Looks great. I like the “it’s just a matter of…” comment. In an similar vein climbing mount Everest is just a matter of putting one foot in front of the other a few times! :slight_smile: A Demo / tutorial would be very welcome. Thanks for your positive response.

I’m trying to make that “just a matter of storing your data somewhere” comment true. I have a web application that makes it that simple. Thread here. [image] Atomiot (Formerly Spark Tools) - Monitor Spark Projects with zero code.. and... The Spark team has made integrating with hardware so easy with the cloud api integration. All I was missing was a quick way to generically work with any Spark project I made without constantly playing with curl or writing new code. I also needed an... Reading time: 6 mins 🕑 Likes: 33 ❤

Hi All, Just to share my implementation for a pulse based energy meter based on the code of the topic starter. This was my attempt to get a graph of my home energy consumption based on the light-pulse that my energy meter will let out every watt-hour consumed. By measuring the time between each pulse, you can calculate the current energy consumption. Below is the code I put into the sparc: // Define the pins we're going to call pinMode on int sensor = D0; char resultstr[64]; unsigned long pulsearray[100]; unsigned int pulsearrayindex = 0; unsigned int storearrayindex = 0; void blink(); int getLastData(String args); void setup() { pinMode(sensor, INPUT_PULLUP); attachInterrupt(sensor, blink, FALLING); Spark.variable("result", &resultstr, STRING); Spark.function("getLastData", getLastData); Spark.function("getData", getData); } void loop() { } int getLastData(String args) { if(storearrayindex != pulsearrayindex) { sprintf(resultstr, "{\"time\":%d,\"idx\":%d}", pulsearray[storearrayindex], storearrayindex); storearrayindex++; if(storearrayindex > 99) { storearrayindex = 0; } return pulsearrayindex; } else { return -1; } } // This is a circular buffer of 100 items to store when a pulse happened void blink() { pulsearray[pulsearrayindex] = millis(); pulsearrayindex++; if(pulsearrayindex > 99) { pulsearrayindex = 0; } } And this is what I put into the google drive script to fill an excel file with data: function collectData() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); while(1) { var returnval = -1; var payload = { "access_token" : "YOUR_ACCESS_TOKEN", "args" : "" }; var options = { "method" : "POST", "payload" : payload }; var url = "https://api.spark.io/v1/devices/YOUR_DEVICE_ID/getLastData"; var response1 = UrlFetchApp.fetch(url, options); try { var response2 = JSON.parse(response1.getContentText()); // parse the JSON the Core API created returnval = unescape(response2.return_value); // you'll need to unescape before your parse as JSON } catch(e) { Logger.log("Unable to returned JSON1"); } if(returnval == -1) { break; } var response3 = UrlFetchApp.fetch("https://api.spark.io/v1/devices/YOURDEVICE_ID7/result?access_token=YOUR_ACCESS_TOKEN"); try { var response4 = JSON.parse(response3.getContentText()); // parse the JSON the Core API created var result = unescape(response4.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.time, p.idx]); // append the date, data1, data2 to the sheet } catch(e) { Logger.log("Unable to do second parse2"); } } catch(e) { Logger.log("Unable to returned JSON2"); } } } Now, if you run this script directly, it works like a charm, retrieving all entries in the buffer, and adding them to the excel. If you add a column with the following formula in the first cell of column D: =ArrayFormula(if(iserror((B2:B - B1:B) = 0),iferror(1/0),if((B2:B - B1:B) = 0,iferror(1/0),3600/((B2:B - B1:B)/1000)))) you can get the instantaneous power, and plot a nice graph, as long as you do not miss any pulses! (check idx for this, it should increment on each entry). The only problem I have, is that when you let this script run on a timer event, weird stuff starts to happen, that seems to be related to the UrlFetchApp.fetch failing more then 50% of the time for no apparent reason. I actually read up on numerous topics in the Google drive forum related to this problem, and only the response from Google that they would look into it. I guess you can improve the script by having it gracefully detect a failed fetch, and retrying, but I’m afraid it will start bombarding the spark cloud service unnecessary, and you would also need to update the sparc code to gracefully handle the re transmission, as currently it will cause it to skip an entry. In any case, this made the script unusefull for my application, so I’m abandoning this attempt at storing my energy consumption in Google drive. Instead I will try to build a local storage trough a tcp service or something. But I thought I’d post here my progress so far, for anyone who would like to have another go at it. If you’re able to fix it, I’d be all ears :wink: Regards, Robin

Hello, Is there a way to pull the data into the spreadsheet more frequently than 1 minute? I have an accelerometer which I would like to graph. Thanks!

1st of all this is great. using the exact code provided. I am testing this out set the time interval to 1 minute(for testing), but i see time between points range from 4 seconds to over 3 minutes…what effects the times between data point being recorded? I worry if I these are all “missed” collection attempts then choosing a 15minute interval would recorded intervals that vary between 15minutes and 45 minutes. recorded time intervals -0:03:23 -0:01:21 -0:02:02 -0:01:58 -0:01:04 -0:03:56 -0:01:13 -0:03:50 -0:01:04 -0:01:02 -0:00:04 -0:01:48 -0:01:05 -0:02:06 -0:00:01 -0:03:55 -0:02:04 -0:00:45

I update temp data every min and the actual imported time stamps to the google drive spreadsheet will vary by about 1 min - 3 mins between time stamps sometimes. Here is the time stamp on for my data importing: 2/27/2014 15:04:01 2/27/2014 15:05:53 2/27/2014 15:09:02 2/27/2014 15:10:48 2/27/2014 15:12:51 2/27/2014 15:14:59 2/27/2014 15:15:01 2/27/2014 15:17:55 2/27/2014 15:19:57 2/27/2014 15:20:53 2/27/2014 15:25:01 2/27/2014 15:26:47 2/27/2014 15:27:57 2/27/2014 15:28:01 2/27/2014 15:29:59 2/27/2014 15:30:01 2/27/2014 15:33:56 2/27/2014 15:34:55 2/27/2014 15:37:00 2/27/2014 15:37:01 2/27/2014 15:38:59 2/27/2014 15:39:01 2/27/2014 15:41:00 2/27/2014 15:43:00 2/27/2014 15:44:19 2/27/2014 15:46:47 2/27/2014 15:47:55 2/27/2014 15:49:00 2/27/2014 15:49:01 2/27/2014 15:51:55 2/27/2014 15:52:49 2/27/2014 15:53:53 2/27/2014 15:56:51 2/27/2014 15:59:49 2/27/2014 16:01:50 2/27/2014 16:02:49 2/27/2014 16:03:55 2/27/2014 16:04:51 2/27/2014 16:08:38 2/27/2014 16:09:55 2/27/2014 16:10:49 2/27/2014 16:11:49 2/27/2014 16:12:50 2/27/2014 16:15:51 2/27/2014 16:18:55 2/27/2014 16:21:53 2/27/2014 16:23:00 2/27/2014 16:23:52 2/27/2014 16:27:54 2/27/2014 16:28:55 2/27/2014 16:30:01 2/27/2014 16:30:03 2/27/2014 16:31:49 2/27/2014 16:33:00 2/27/2014 16:33:01 2/27/2014 16:34:52 2/27/2014 16:35:53 2/27/2014 16:38:49 2/27/2014 16:40:56 2/27/2014 16:43:02 2/27/2014 16:44:38 2/27/2014 16:46:49

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

Social Project Share

kareem613 March 6, 2014, 1:48pm 61

I’m trying to make that “just a matter of storing your data somewhere” comment true. I have a web application that makes it that simple.
Thread here.

Topic		Replies	Views
Pushing data from the photon into google sheets Firmware	92	19957	April 11, 2020
Webhook builder to google sheets Firmware	11	4162	July 5, 2020
Atomiot (Formerly Spark Tools) - Monitor Spark Projects with zero code.. and other stuff General	61	8038	February 6, 2019
Control, monitor, and log a Spark Core with ThingSpeak Troubleshooting	15	8762	April 16, 2016
Using Spark.publish() with Simple JSON Data Tutorials	119	36712	November 11, 2017

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

Related topics