How do you sent the interval time in google docs?
Thanks
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:
Hope it helps!
1 Like
Hey share how you done the chart. Thx it will be very helpful for me.
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!
Anyone know if its possible to have the google script act on the photon script to reset a counter?
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.
Have a look: https://developers.google.com/apps-script/guides/services/external#connecting_to_public_apis
2 Likes
@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.
1 Like
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.
Yup, that's the idea.
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;
}
}Yes, that looks OK.
Have you considered using an interrupt for your counter?
@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.
Nope, Iād rather use the interrupt to replace this
if(gateState != prevState)
{
if(gateState == HIGH)
{
count++;
digitalWrite(Led, HIGH);
}
prevState = gateState;
}
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! 
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.
@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?
If you have the interrupt just trigger on the RISING or FALLING edge, a lot of the ābounceā counting goes away immediately.
@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"'
}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.
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!