Pushing data from the photon into google sheets

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?

I agree, it’s though to make it work. I did it years back and remember having trouble.
I have no tools to offer, I would re-run the instructions… :slightly_frowning_face:

Howdy,
I just did this again yesterday. I had no problems but I did have to log in as usual. Considering that I have about half a dozen google sheets with this code it may take a bit when doing it the first time. I remember when I did it for the very first time I had to futz with it. Meaning saving, deploying, hitting the test button over and over again until it just worked. One time I also had to switch the Webhook from Webform to JSON and then back to Webform. I would give it a few hours then try it again.

I know this sounds repetitive but you could try a new sheet and follow the instructions over again. Just to make sure there was not a missed step. New Sheet, New Script, New Webhook. Do it all over again. Once you get it you will be all set.

1 Like