Pushing data from the photon into google sheets

Sorry I saw your question after I posted. I would start with Gus’s post and link before trying to do multiple entries into one string like awardblvr is doing. I think his post is tailored more towards getting multiple data entries sent via one webhook. I am still in kindergarten when it comes to webhooks, JSON, C++, ETC. I am very close to getting multiple entries into a JSON file but not there yet. Like I said start with one, something simple and then go from there.

Keep asking for help here. The people on this board are extremely helpful, especially to newbies like you and me. I didn’t even know what C++ was or webhooks two weeks ago. Now I am logging data from a 4-20 ma sensor and logging into google. Granted it took me a few bottles of wine and a lot of curse words but with the help here I got it… kind of. lol.

You got this man.

2 Likes

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.

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

2 Likes

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

1 Like

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.

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

2 Likes

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

1 Like

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

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.

2 Likes

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

1 Like

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

1 Like

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.

1 Like

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

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.

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:

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.

great thanks @nrobinson2000 Im going to look into this!

1 Like

@gusgonnet

After entering the script and saving and selecting publish/Deploy as web app - when I press Deploy I get this popup. Selecting Review Permissions takes me to another popup where I need to login (again) and it tells me Google hasn’t verified the app yet. I didn’t see these steps mentioned in any of the tutorials - is this new?

[Update] - I got past this in Advanced link.

Hey, since Google and any infrastructure provider will add features and change things, new steps or pop-ups or permissions will come and go all the time.

Happy to hear you got past that!

I was getting 401 errors and now I am getting 301 in the Webhook responses.

Haven’t got it logging data into my google sheet yet! Incredibly fiddly - I’m sure when it is working I will forget all about this. Are there any other tools to debug the script you are aware of?