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


#192

There might be some other things wrong with this request.
I can’t see a Particle.variable() named result in your code, you may have a mistake in your device ID in that request and since you once use access_token=<yourAccessToken> and another time access_token=yourAccessToken, just for clarity, the angled brakets are not part of the request.


#193

Thanks so much for the help. I’m very new to all of this. My mistake was using the the angled brackets and not including result! My electron now publishes to my google spreadsheet! :grinning:

Now on to the next part. Maybe I should start a new thread? I want to Electron to log the quality and strength of its cellular signal (The purpose being I can send this module to clients, they can turn it on and leave it running for a couple days and I can evaluate cellular strength remotely). I think the code I need is something along the lines of:

CellularSignal signal = Cellular.RSSI();
Particle.publish("RSSI", String(signal.rssi));
Particle.publish("Qual", String(signal.qual));

But when I try putting that in my loop, although the code verifies, I get: "error": "Timed out." on Postman when I run the Get request. I tried formatting everything like the voltage, soc, and alert variables instead of events, but didn’t understand what I was doing…

#include "application.h"
#include "SparkFunMAX17043/SparkFunMAX17043.h" // Include the SparkFun MAX17043 library

char resultstr[64];
String voltage = "0"; // Variable to keep track of LiPo voltage
String soc = "4"; // Variable to keep track of LiPo state-of-charge (SOC)

void setup()
{
Serial.begin(9600); // Start serial, to output debug data

// Set up Spark variables (voltage, soc, and alert):
Particle.variable("voltage", voltage);
Particle.variable("soc", soc);
Particle.variable("result", resultstr, STRING);


delay(3000);
pinMode(D0, INPUT_PULLUP);
pinMode(D1, INPUT_PULLUP);

// Set up the MAX17043 LiPo fuel gauge:
Serial.print("Error: ");
Serial.println(lipo.begin()); // Initialize the MAX17043 LiPo fuel gauge

// Quick start restarts the MAX17043 in hopes of getting a more accurate
// guess for the SOC.
lipo.quickStart();

// We can set an interrupt to alert when the battery SoC gets too low.
// We can alert at anywhere between 1% - 32%:
lipo.setThreshold(20); // Set alert threshold to 20%.
pinMode(D6, OUTPUT);
digitalWrite(D6, LOW);
}

void loop()
{

CellularSignal signal = Cellular.RSSI();
Particle.publish("RSSI", String(signal.rssi));
Particle.publish("Qual", String(signal.qual));

// digitalWrite(D6, LOW)
lipo.quickStart();

// lipo.getVoltage() returns a voltage value (e.g. 3.93)
voltage = String(lipo.getVoltage());
// lipo.getSOC() returns the estimated state of charge (e.g. 79%)
soc = String(lipo.getSOC());
// format your data as JSON, don't forget to escape the double quotes
snprintf(resultstr, sizeof(resultstr), "{\"data1\":\"%s\",\"data2\":\"%s\",\"data3\":\"%s\"\"data4\":\"%s\"}", (const char*)voltage, (const char*)soc, signal.rssi, signal.qual);
Serial.println(resultstr);

delay(1000); // wait for a second
}

#194

You are exceeding the rate limit of max 1 publish per second.
If you put both values together into one publish you might have more luck.


#195

Hi guys, I’m back with a couple questions. I’ve formatted my particle so that it outputs the cellular signal, but I seem to be having a little trouble getting it to actually add to the spreadsheet. When I put my postman request in, I get these results:

{
"cmd": "VarReturn",
"name": "result",
"result": "{data1: -73 \n data2: 37}",
"coreInfo": {
"last_app": "",
"last_heard": "2016-07-14T13:49:55.945Z",
"connected": true,
"last_handshake_at": "2016-07-14T13:32:05.070Z",
"deviceID": "MY-DEVICE-ID",
"product_id": 10
 }
}

And when I run the script, I get these results:


But it doesn;t seem to be updated the spreadsheet at all. Any ideas? My two Codes are pasted below. Thanks so much for all the help:

#include "AssetTracker/AssetTracker.h"


//Device sleep time, minutes between publishes
int delayMinutes = 1;
 char resultstr[64];

//AssetTracker
AssetTracker t = AssetTracker();

FuelGauge fuel;
int rssi;

void setup() {

//Begin AssetTracker
t.begin();

//Enable the GPS module
//Takes 1.5s or so because of delays
t.gpsOn();

Particle.variable("result", resultstr);
}

void loop() {

//Need to capture the GPS output every loop
t.updateGPS();


    //Read and Publish lat/long
    Particle.publish("LatLong", t.readLatLon(), 60, PRIVATE);

    
    //Read and publish battery state
    //Formatted "Cell votlage, state of charge %"
    Particle.publish("Battery",String(fuel.getVCell()) + String(fuel.getSoC()), 60, PRIVATE);
    
    //Read and Publish cell strength
    //Formatted "RSSI (-113dBm to -51dBm), quality (0-49))"
    CellularSignal rssi = Cellular.RSSI();
    Particle.publish("RSSI", String(rssi), 60, PRIVATE);

    snprintf(resultstr, sizeof(resultstr), "{\"Rssi\":%d,\"Qual\":%d,\"data3\":}", rssi.rssi, rssi.qual);
    Serial.println(resultstr);

}

And here is my script code:

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

 var response =   UrlFetchApp.fetch("https://api.particle.io/v1/devices/MY-DEVICE-ID/result?access_token=MY-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");
  }
 }

#196

Any ideas?


#197

Hi @binaryfrost thanks for the great project share.

I got completely stuck with the Spark.variable(“result”, &resultstr, STRING);
and was wondering if you (or anyone else here) could have a look at my code to see what Im not seeing.

I don’t know if it’s customary that people directly paste code in a post so I wanted to check first.
Thanks


#198

Yes, it is customary (and necessary) to post the code you’re having trouble with. Also, please elaborate on what “got completely stuck” means. What’s the problem? Does the code not work, or do you not know how to write the code?


#199

Hi @Ric Thanks for your reply. Below my code, I’ve made as many notes as possible so you can see where the problem is (I hope). Everything work but I get stuck when enter: Spark.variable(“result”, &resultstr, STRING); Marked in the code between // PROBLEM STARTS HERE JDM and // PROBLEM ENDS HERE JDM.
It would be great if you have any idea how I can take this forward. Many thanks.

Ps: No idea why some of the code is posted in grew boxes and some not
I am fairly new to all this but having the time of my life :sunglasses:



/**
 * ReadSHT1xValues
 *
 * Read temperature and humidity values from an SHT1x-series (SHT10,
 * SHT11, SHT15) sensor.
 *
 * Copyright 2009 Jonathan Oxer <jon@oxer.com.au>
 * www.practicalarduino.com
 * 
 * Aditional code/changes made by Jaap de Maat (JDM)
 *
 * Ported to Spark Core by Anurag Chugh (https://github.com/lithiumhead) on 2014-10-15
 */
 
 
 
 // This #include statement was automatically added by the Spark IDE.
#include "SHT1x/SHT1x.h"
 
 
 
 
 // TRY THIS TRY THIS
 
 char resultstr[64]; // Part needed to Connect data to a google doc spreadsheet // JDM add

unsigned long wait = millis();              //Delay timer to create a non-delay() x-second sampling timer
const unsigned long waittime = 5000L;


 // TRY THIS TRY THIS
 

// Specify data and clock connections and instantiate SHT1x object
#define dataPin  D0
#define clockPin D1
// #define MOISTPIN_1 D3      // Pin where the soil moisture sensor is connected  // JDM add - Still in development


//Set two LED lights
int led1 = D7; // led 1 // JDM add
int led2 = D6; // led 2 // JDM add
// int sensorPin = D3; //soil moisture sensor is connected  // JDM add - Still in development


// No idea what this is JDM
SHT1x sht1x(dataPin, clockPin);





void setup()
{
 
    
       Serial.begin(9600); // Open serial connection to report values to host
       Serial.println("Starting up");
            
        pinMode(led1, OUTPUT);
        pinMode(led2, OUTPUT);
    
    
                         // Connect data to a google doc spreadsheet // JDM add
                     // Code by binaryfrost from: https://community.particle.io/t/example-logging-and-graphing-data-from-your-Particle-core-using-google/2929
      
                     //pinMode(A0, INPUT); // setup A0 as analog input // JDM add
                     //pinMode(A1, INPUT); // setup A1 as analog input // JDM add
                     // expose your char buffer to the Cloud API // JDM add
                     
                     
                     // PROBLEM STARTS HERE JDM
                     
              //        Spark.variable("result", &resultstr, STRING);  // JDM add
    
    
                       // PROBLEM END HERE JDM
    
    
    
}

void loop()
{
    float temp_c;
    float temp_f;
    float humidity;
    
    // Read values from the sensor
    temp_c = sht1x.readTemperatureC();
    humidity = sht1x.readHumidity();
    
    // Print the values to the serial port
    Serial.print("Temperature: ");
    Serial.print(temp_c, 2);
    Serial.print("C / ");

    Serial.print("Humidity: ");
    Serial.print(humidity);
    Serial.println("%");
    
    
         // Send a publish...
        Particle.publish("Temperature C", String(temp_c, 1), 60, PRIVATE);
        Particle.publish("Humidity %", String(humidity, 0), 60, PRIVATE);
    
    
// Simply blinking some LED light in each loop // added by JDM

            digitalWrite(led1, HIGH);   // turn the LED on (HIGH is the voltage level)
            delay(250);  
            digitalWrite(led2, HIGH);   // turn the LED on (HIGH is the voltage level)
            delay(250);              // wait for a second
            digitalWrite(led1, LOW);    // turn the LED off by making the voltage LOW
            delay(250);  
            digitalWrite(led2, LOW);    // turn the LED off by making the voltage LOW

// Delay for 10 sec before next reading // added by JDM
delay(10000); 
    
}

#200

I see several things that may be the problem, though you still haven’t told me what “getting stuck” means. What happens when you uncomment the code that you say is the problem?

I don’t know where you got this code, but you should definitely check out the docs to make sure you’re using the current api. Spark has been depreciated (but it still might work), so you should use Particle instead. Also, there’s no longer any need to use the “&”, or specify the type of the variable. The line should be,

Particle.variable("result", resultstr);

That line registers the variable with the cloud, but I don’t see anywhere in your code where you set the value of resultstr to anything.


#201

Hi @Ric Thanks for your quick reply!
The code is part of a library (found in the Particle Build library) for the Sparkfun sht1x.

As you suggested I’ve changed: Spark.variable(“result”, &resultstr, STRING); to Particle.variable(“result”, resultstr); and I don’t get any more error message when verifying, so the is a good sign.

What I met by “getting stuck” is basically that error message (only when it’s uncommented) sorry for not being more clear on that.

As mentioned it does look alright now so Im gonna go ahead a get further going with it. Thanks again for you help. Much appreciated. In case I “get stuck” again, I be more clear and might ask for you advise again if that ok.

All the best,
J


#202

@Ric I am very excited to let you know I allready got to the results I wanted!
And learned lots in doing so. Massive thanks to you for your help.

Next part of the project is going to be uploading the data to http://wunderground.com
All the best,
J


#203

Hi @peekay123 Would you be able to tell me how you got decimals into your reading?
Many thanks, j


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