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


#142

How do you sent the interval time in google docs?
Thanks


#143

Hello all,
great project! I know this is an old post, but if someone is looking for a way to plot dynamic data via scripting in google sheets, I have uploaded my script here:

dynamicScatter-github

Hope it helps!


#144

Hey share how you done the chart. Thx it will be very helpful for me.


#147

Hey everybody & @binaryfrost ,

I’m just trying out different logging options and came across this thread. Cutting to the chase…

I have a small script that counts events. I have it working to upload to Google Sheets via the OPs tutorial. I would like the script to reset the variable count to 0 when the data is uploaded. Is there anyway to put something in my script or the google script to clear this counter so it’s not additive?

I guess I’m wondering if there is anyway the Photon loop can know that the data has been parsed or if there is a way the google script can act on the photon script?

My script:


int Gate = D4;
int Led = D7;
int gateState = 0;
int count = 0;
char resultstr[64];

void setup() {

  pinMode(Gate, INPUT);
  pinMode(Led, OUTPUT);
  Particle.variable("result", resultstr, STRING); 
  
}

void loop() {
    
  static byte prevState = 1;
  gateState = digitalRead(Gate);
  digitalWrite(Led, LOW);
  
  if(gateState != prevState) 
  {
    if(gateState == HIGH)
    {
    count ++;
    digitalWrite(Led, HIGH);
    }
    prevState = gateState; 
  } 
    sprintf(resultstr, "{\"count\" :%d}", count);
    delay(50);
}

My Google script:


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

  var response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/xxxxxxxxx/result?access_token=xxxxxxxx");

  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.count]); // 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");
  }
}

My returned data:

Thanks in advance!


#148

Anyone know if its possible to have the google script act on the photon script to reset a counter?


#149

You’d need to either POST a call that triggers a Particle.function() or publish an event to which you Particle.subscribe().

Either the function or the subscribe handler should resets the counter.

But either way depending on your count frequency you might loose some counts since you have no sync between your count read-out and the reset taking place.
To counteract this, you could for example first call the function, which should reset the counter but returns the last value of the counter.
If you are worried that the return value might not be interpreted properly, you could also make it two calls: The first sends a "frozesn count and a second only decrements the current count by the frozen count.


#150

Have a look: https://developers.google.com/apps-script/guides/services/external#connecting_to_public_apis


#151

@ScruffR, thanks man. So post a trigger in the Google script that calls a Particle.function() in the photon script that can act on the count variable?

Honestly, if I am publishing every hour, I am not too worried about missing a few events at this point but thanks for that advice.


#152

Thanks @Moors7! I’ve been looking at that page (a few times now) and I will continue to do so and see what I can come up with.


#153

Yup, that’s the idea.


#154

Hey @ScruffR, does this look about right for using the Particle.function() part of what I’m trying to do?

int Gate = D4;
int Led = D7;
int gateState = 0;
int count = 0;
int newCount(String command);
char resultstr[64];

void setup() {

  pinMode(Gate, INPUT);
  pinMode(Led, OUTPUT);
  Particle.variable("result", resultstr, STRING); 
  Particle.function("reset_counter", newCount);
}

void loop() {
    
  static byte prevState = 1;
  gateState = digitalRead(Gate);
  digitalWrite(Led, LOW);
  
  if(gateState != prevState) 
  {
    if(gateState == HIGH)
    {
    count ++;
    digitalWrite(Led, HIGH);
    }
    prevState = gateState; 
  } 
    sprintf(resultstr, "{\"count\" :%d}", count);
    delay(50);
}
int newCount(String command)
{
  if(command == "reset_counter")
  {
    count = 0;
  }
}

#155

Yes, that looks OK.
Have you considered using an interrupt for your counter?


#156

@ScruffR, thanks. Use an interrupt to basically recognize when the data parse takes place and then reset the timer? I did a fair amount of reading on the Particle Doc’s about it but was unsure of how to use it properly. It overwhelmed me.


#157

Nope, I’d rather use the interrupt to replace this

  if(gateState != prevState) 
  {
    if(gateState == HIGH)
    {
    count++;
    digitalWrite(Led, HIGH);
    }
    prevState = gateState; 
  }

#158

Ahhh. Hmm. No, haven’t considered that. That took me forever to figure out how to have it count just once when the gate became high and then not again until that bubble passed and the gate was reset. I’m open to suggestions! :grinning:

Sometimes the bubble was just staying in the gate and it would count multiple bubbles. Then once I got that fixed it was counting the leading and trailing edge of the bubbles so that was my solution.


#159

@Moors7, I’ve tried putting many on many things in my Google script to act on my Particle.function command and to no avail (and much head-scratching). Any guidance?


#160

If you have the interrupt just trigger on the RISING or FALLING edge, a lot of the “bounce” counting goes away immediately.


#161

@ScruffR I will start looking into that and report back!

@Moors7 This is what I have. Am I even remotely close?

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

  var response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/xxxxxxxxxx/result?access_token=xxxxxxxxxx");
  
  try {
    var response = JSON.parse(response.getContentText()); 
    
    var result = unescape(response.result); 

    try {
      var p = JSON.parse(result); 
      var d = new Date(); 
      sheet.appendRow([d, p.count]); 
    } catch(e)
    {
      Logger.log("Unable to do second parse");
    }
  } catch(e)
  {
    Logger.log("Unable to returned JSON");
  }
  var url = 'curl https://api.particle.io/v1/devices/xxxxxxxxxx/newCount \
     -d access_token=xxxxxxxxxx \
     -d "args=reset_counter"'

}

#162

First things first. Your function name, at 13 characters, is 1 character longer than the documented allowed 12 characters: https://docs.particle.io/reference/firmware/photon/#particle-function-
It might just work if you fix that first. If you first want to test if your functions work, you can try this page. Add a Blinking LED in the function, so you have some visual indication it’s being called during development, that often helps a lot.


#163

Very good point @Moors7. Thanks for that catch. I used your link and my function does in fact work with the new argument freshCount . That is promising but still going to have to work on the Google script a bit more.


int Gate = D4;
int Led = D7;
int gateState = 0;
int count = 0;
int newCount(String command);
char resultstr[64];

void setup() {

  pinMode(Gate, INPUT);
  pinMode(Led, OUTPUT);
  Particle.variable("result", resultstr, STRING); 
  Particle.function("freshCount", newCount);
}

void loop() {
    
  static byte prevState = 1;
  gateState = digitalRead(Gate);
  digitalWrite(Led, LOW);
  
  if(gateState != prevState) 
  {
    if(gateState == HIGH)
    {
    count ++;
    digitalWrite(Led, HIGH);
    }
    prevState = gateState; 
  } 
    sprintf(resultstr, "{\"count\" :%d}", count);
    delay(50);
}
int newCount(String command)
{
  if(command == "freshCount")
  {
    count = 0;
  }
}

and google script


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

  var response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/xxxxx/result?access_token=xxxxx");
  
  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.count]); // append the date, count to the sheet
      var url = 'https://api.particle.io/v1/devices/xxxxx/newCount/access_token=xxxxx/"args=freshCount"'

    } catch(e)
    {
      Logger.log("Unable to do second parse");
    }
  } catch(e)
  {
    Logger.log("Unable to returned JSON");
  }

}

Still not working and I have tried several different things. I’ll keep messing with it. If you see anything that I am missing on the Google script side, please let me know. Thanks!