Pushing data from the photon into google sheets


#42

One more thing before I peace out for the night. I read all the questions and comments on the Hackster page that Gus posted to. I added this post to hopefully help noobs like me. Its basically the same thing as I noted above but thought it could not hurt to post it here as well.

I wanted to share my experience with this. It has been thrilling and incredibly frustrating. In my experience, if you are getting “undefined” in your columns switch your webhook from JSON to Web Form, or from Web Form to JSON and enter the same parameters. I have two identical apps with identical webhooks. Only the names are different. One will post to sheets with JSON, the other only Web Form. Also if you are having trouble with the google script app not testing correctly try pasting the code again and running through the setup process. This has happened to me every time and I find if I just past the code over again it fires right up. Good luck and keep at it. Cheers.


#43

A few dozen tries and a lot less hair, but I’m now pushing data to Google Sheets.

I’m tired, but happy. I’ll explain tomorrow but in short it was a script editor thing, how you run and deploy/update scripts is important.

Going to sleep like a rock tonight :slight_smile:

Thanks everyone for your help


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

congratulations! happy to hear you got it working!!!


#45

I knew you would get it. The feeling you get when you get it to work is pure joy…even if you are exhausted and ready for bed. I can’t wait to read what steps you took to get it to work. Very curious over here…

Great job J.


#46

It’s been working all day now with multiple data entries.

My issue was two fold, and the first clue was actually in the error message returned. If you look further up in this thread you’ll see in the error message that there was an invalid argument stemming from “line 35” in the script. This is the part of the script where it gets the spreadsheet name to append, this is different from the sheet name in the spreadsheet. If you don’t save the script and select setup and run (this runs the setup function at the very end of the script) before you deploy the script, this sheet name is not saved and the script doesn’t know where to put the data. This is very nicely explained here;

https://steven.codes/blog/google-sheets-db-tutorial/

Secondly save your altered/updated scripts as new versions and deploy the correct version. :roll_eyes:

Sending multiple values was just a matter of naming an extra column and sending a second value like this

Particle.publish("Sunlight_Data_JS", "{\"my-name\":\"" + tempMessage + "\",\"my-count\":\"" + Ocelot.sampleCount +"\"}", 60, PRIVATE);

The trick is to keep track of all the quotes. Granted, I just brute forced it to make it work but am reading the webhook portion of the docs to change this to what I actually need for my application, I don’t need the dynamic custom fields. I should just be able to compile the JSON string with one of the printf variants and send it off.

I tried the webhook to Google Sheets a couple of years ago but I was much greener then and found the whole thing way too daunting. It’s amazing when you look back and discover what you’ve learned with the help of the people on this forum and the excellent documentation and eco-system that Particle has created. I’m a fan and I’m sure there are many more.

I am eagerly awaiting a wonderful box full of the new “meshy” things once they are ready to ship :smile:

Thanks,
Joerg


Need Suggestion for external Cell and GPS antennas locations
#47

Thank you Joerg,
I appreciate you taking the time to write what worked for you. I got caught up in some other projects to I hope to try your tips today. I would get those same errors like line 35 and line 44… I think it was 44 lol. But it eventually worked.

Look forward to hearing what you think of the mesh. I am also a big fan of all things particle. Even though I am still a complete newbie at all of this. It is amazing that I have not fried anything to this point. My setup has a 24v dc power supply that I am converting to 12V. I made a post about it but still have some work to do.

Cheers,
Tom


#48

Hi,

I am trying to get data into google sheets, without using IFTTT or “thinkspeak” webhooks.

Is there anyway to make this legacy method work still?

using this code in google sheets

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

thanks,
Jonathan


#49

Hi, I wouldn’t know.
All I know in the subject I dumped in this write up below. I’m using it every day, it does not use IFTTT or thingspeak so it may seem to match your needs:

People in this thread that followed it to the letter got it working, lost some hair, got frustrated and then got proud of themselves, all that but maybe in a different order :slight_smile:
I tried to maintain it over the years and last time I updated it with comments or suggestions on this thread was like 2 weeks ago.
Good luck!
Gustavo.


#50

Thanks Gustavo for all your hard work maintaining that!

I must have missed something when the other member pointed me there. I’ll take a break and re-read that post you linked to!

thanks,
Jonathan


#51

Obviousy, since I also linked the Hackster project :wink:


#52

By the way, the original script works fine for me without the update. No change in 2 years. It’s notable that I don’t use any auth in the webhook as @awardblvr does. It’s an anonymous post but shows as an update by me.
Also, judging by the posts above ( can’t see the webhook by @awardblvr ) but he seems to be using JSON, and not the web form type webhook. That has definitely caused my data to no-show. So, in short, if you must use JSON, you need the update. If you don’t care, then use the web form type and follow the tutorial.


#53

Hi @nrobinson2000 Thanks for sharing. Wondering if there is a way to have two events (reading Temperture and Humidity from Particle) into one single spreadsheet using IFTTT. This one only publishes one event into the google spreadsheet. Thanks


#54

For two events I’d suggest either using two applets (one for each event), or in your case, combining the temperature and humidity readings into a single event.


#55

Super sonic response!! Thanks.
Got two applets running and two spreadsheet works fine just not as neat :slight_smile:
If I combine the two reading into one event (looking into how to do so) could I still split them into different columns? Thanks again for your help! :+1:


#56

An easy way would be use a char array variable to store the payload of the event, format the readings into the payload variable, and then publish the event with the payload.

Example:

// temperature and humidity are assumed to defined as doubles
// Ideally you would have them set to the readings, not dummy values

double temp = 123.456;
double humid = 456.789;

char payload[100];
snprintf(payload, sizeof(payload), "%1.3f:%1.3f", temp, humid);
Particle.publish("to_google_sheets", payload);

The event payload would show up like 123.456:456.789 in the webhook.

To actually get the values to go into two different columns would require some changes to the webhook structure and the applet.


#57

great thanks @nrobinson2000 Im going to look into this!