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


#204

For future reference, any time you get an error message, you should post that along with your code. It focuses the troubleshooting on the exact problem that you’re having…


#205

Hi,
Does this method still work (using JSON query) to pull data into google spreadsheets? I would really like to avoid using IFTTT, and this is the only example I’ve found that you don’t have to use that…

Whenever I try to follow this, I get the error message Unknown variable false - see exact below:
{“ok”:false,“error”:“Unknown Variable: false”}"

when trying:
https://api.particle.io/v1/devices/<--readacted-->/result?access_token=<--readacted-->

OR

https://api.particle.io/v1/devices/<--readacted-->/temperature?access_token=<--readacted-->

this works in a web browser, yet if I plug it into the code below and try to pull the data from google sheets, in the logging, I can see " timed out while waiting for response" mesage. I assume this is because its not the proper type of request ?

https://api.particle.io/v1/devices?access_token=<--readacted-->

I am able to get data via the above query, there are 3 variables in my code which are publishing ok, the below is the data from the above query

[{"id":"<--readacted-->","name":"farm-photon","last_app":null,"last_ip_address":"107.190.119.33","last_heard":"2018-05-18T09:27:10.167Z","product_id":6,"connected":true,"platform_id":6,"cellular":false,"notes":null,"status":"normal","current_build_target":"0.7.0","system_firmware_version":"0.7.0","default_build_target":"0.7.0"},
{"id":"<--readacted-->","name":"photon1","last_app":null,"last_ip_address":"107.190.119.33","last_heard":"2017-11-25T16:22:01.455Z","product_id":6,"connected":false,"platform_id":6,"cellular":false,"notes":null,"status":"normal","default_build_target":"0.7.0"}]

The code in the google spreadsheet which isn’t working is below:

function collectData() {
 var  sheet = SpreadsheetApp.getActiveSheet();

 var response =
UrlFetchApp.fetch("https://api.particle.io/v1/devices?access_token=<--readacted-->");

 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");
  }
 }

Edit: I realize I should have a URL that polls the device ID, and variable I want to retrieve, but I am not even able to do that, eg:

https://api.particle.io/v1/devices/<--readacted-->/temperature?access_token=<--readacted-->

The code on the Photon is:

// This #include statement was automatically added by the Particle IDE.
#include <Adafruit_DHT.h>

// This #include statement was automatically added by the Particle IDE.
#include "Adafruit_DHT/Adafruit_DHT.h"

// DHT parameters
#define DHTPIN 5
#define DHTTYPE DHT11

// Variables
int temperature;
int humidity;
int light;

// Pins
int light_sensor_pin = A0;

// DHT sensor
DHT dht(DHTPIN, DHTTYPE);

void setup() {
    
    // Start DHT sensor
    dht.begin();
}

void loop() {
    
    // Humidity measurement
    temperature = dht.getTempCelcius();
    
    // Humidity measurement
    humidity = dht.getHumidity();
    
    // Light level measurement
    float light_measurement = analogRead(light_sensor_pin);
    light = (int)(light_measurement/4096*100);
    
    // Publish data
    Particle.publish("temperature", String(temperature) + " °C");
    delay(2000);
    Particle.publish("humidity", String(humidity) + "%");
    delay(2000);
    Particle.publish("light", String(light) + "%");
    delay(2000);
    
}

Is anyone able to help?

thanks,
Jonathan


#206

You may have more luck with this thread, which is more up to date :wink:


Or this Hackster project


Pushing data from the photon into google sheets
#207

Thanks for the info, however that post’s method unfortunately uses ‘thingspeak’ to do it (I want to avoid this at all costs), and also provides one example is provided for IFTTT (again, I want to avoid this at all costs too).

I do not want to use those, I just wanted to simply query the device with a JSON call, and dump the data right in… why would they remove this simple functionality :frowning: :frowning: :frowning:

It is starting to look like this is the frustration with using “cloud” services, they just disappear at random, and you have to change the entire way you go about doing something.

I will just use CURL and dump it to a file on a unix box in a CSV format I guess :frowning:


#208

Why not just use Losant or Ubidots. I don’t either service is going anywhere anytime soon.


#209

Never heard of those, will check it out. Looks like another IOT platform - are they compatible with the ESP’s or Photons?

Jonathan


#210

As pointed out in the other thread, while it starts off using IFTTT reading through the whole thread does reveal alternative paths that work without IFTTT.


#211

Yes, they are.

Way better than Google Sheets in my opinion.


#213

Please?!?!?