HTTPS POST to Google Sheets for Low (no) Power Spark applications

Hi Forum,
I think I get it that the Spark Core does not have the resources to handle https - but after Binary Frost’s excellent Google Sheets / Spark data logger I wanted to have a look at a low power interface to Google Sheets that does not require the Spark Core to be “always powered”. I saw this great bit of simple coding that allows direct data transmission to a Google sheet, posted on Stack Overflow by a guy called Mogsdad and wanted to get it working with the Spark Cloud.
So my questions are:
Is it likely the Spark Photon will be able to send data directly to a Google sheet (or another https:/ site) using a POST method?
Can a proxy be used with the Spark Core (maybe PHP on a webserver) to sit in the middle and redirect the data to the (secure) Google sheet?
Is there a much easier way to do this that I am missing (Webhooks)?
Any help greatly appreciated.

Hi @DRCO

The work that @binaryfrost did a while back was amazing but the interface Google Sheets is fragile and every time Google changes some little thing it breaks until someone figures it out and fixes it again.

If I were starting this today, I would use Spark web hooks which enable you to use the Spark cloud to do HTTPS actions on other hosts. Here’s the doc:

http://docs.spark.io/webhooks/

It is the proxy you want that is already built-in to the Spark ecosystem.

2 Likes

Thanks @bko - I was just looking for this.
Corrected link: http://docs.particle.io/core/webhooks/

1 Like

So, from http://docs.particle.io/core/webhooks/ it says:

# how to create a webhook with json data using the CLI
$ spark webhook create slack.json

Adjusting for the new “particle” naming, when I enter the follwoing, I get:

C:\>particle webhook create slack.json
Please specify an event name

Any ideas on an “event name”; what that means, or what I should be doing differently?

Hi @h2ogood

What is in your slack.json file? There is an example in the doc.

The event name should match one you are publishing or subscribing to in your core firmware.

Sorry - I need Webhooks for Dummies.
I thought I was following the example. :flushed:

Well, I made something happen with:

C:\>particle webhook create temp_a http://www.test.com

…as I now have a hook defined.
So… the “webhook” will watch for data in the file temp_a, then post to test.com?
Thanks for your patience/kindness @bko .

I think you are missing the part that connects to your core/photon. The webhook does not exist other than to service a Particle device via publish and/or subscribe.

So I think your command above is waiting for an event called “temp_a” to be published and then I don’t know if defaults to GET or POST at that URL.

1 Like

Gotcha - thanks! Getting closer. I’m going through the weather example, and have in my code:

// Lets listen for the hook response
Spark.subscribe("hook-response/get_weather", gotWeatherData, MY_DEVICES);

…but I’m not sure what I should put for MY_DEVICES ?
I tried #define it as a variable and included my deviceID, but it didn’t work.
When I subscribe to my own hooks - I see the weather info, but also a “coreid”:“undefined”

Not quite sure what isn’t coming together. My code currently includes:

   #define MYDEVICE "55ff_rest_of_device_ID_3" 

   Spark.subscribe("hook-response/get_weather", gotWeatherData, MYDEVICE);

@h2ogood, MY_DEVICES is a fixed flag and simply indicates you want to subscribe to your devices’ published events and not the full public “pipe”. So the webhook if fired by a Spark.publish() in your code and then responds via the Spark.subscribe() also in your code. :smile:

2 Likes

Ha! Thanks @peekay123 ! Wow, I was over engineering that one. :unamused:
Fixed that back.

I rebuilt the hook and see the following response:

C:\>particle webhook GET get_weather http://w1.weather.gov/xml/current_obs/KMSP.xml

Sending webhook request  { uri: 'https://api.particle.io/v1/webhooks',
  method: 'POST',
  json: true,
  form:
   { event: 'get_weather',
     url: 'http://w1.weather.gov/xml/current_obs/KMSP.xml',
     deviceid: undefined,
     access_token: '69cefg_removed_some_token_info_2c5',
     requestType: 'GET' } }
Successfully created webhook!

When I subscribe, I can see the data coming through.

I’ve entered the code exactly as described via http://docs.particle.io/core/webhooks/#your-first-webhook-creating-the-webhook (cut and pasted it just to make sure)

yet - my serial output only produces:

Requesting Weather!
Requesting Weather!
Requesting Weather!

I fee like something is missing in the code to call the gotWeatherData function. Maybe the example assumes some general knowledge that I don’t have. :confused:

Just deleted the webhook and re-created it… it is now working properly. :smile:
Off to adjust some code to pipe this back to my original intent… whatever that was??

2 Likes

Did you get this working with google sheets ?. I am wanting to achieve the same so any examples would be great.

Thanks,Dean

Hi @deancs

I used IFTTT to post data to Google sheets using Particle.publish(). See the details here:

This is very simple to setup and works great!

Adding to @bko 's response. On another thread that I can no longer find, someone said that the disadvantage of IFTTT was that you can only upload a single variable to google. You can add more than one by formatting the Particle published variable as follows:

sprintf(Batt,"%0.2f ||| %d ", V, sig); // in this example Batt is a string to store the values ready for publication, V is a float variable and sig an integer, separate them with the tripple |
Particle.publish("status", Batt, 60, PRIVATE);

The IFTTT recipe entry for GoogleDrive Formatted row has:

Created At|||Event Contents

and the two variables published by the Particle appear in separate columns. No tricky parsing or scripting involved.

  February  1, 2016 at 05:38PM  4.8    3.4
  February  1, 2016 at 05:39PM  4.86  3.43
2 Likes