Pushing Data to Google Docs

UPDATE 2020:

there is a much better approach to this in a Particle application note published here.

I recommend you base your projects on that app note, and NOT on this project.

--------------------------------------------------

ORIGINAL ARTICLE:

In this article I will explain how your hardware can push data into a Google spreadsheet.

Push Versus Poll

In the poll mechanism, as described in my previous article, the Google spreadsheet runs a script that sends a request to fetch data from our hardware at a regular interval.

You can use the poll mechanism when your hardware is online all the time, for instance to capture sensor data that changes slowly over time (example: the temperature of your pool).

In the push mechanism, described in the current article, your hardware sends a request with data to a Google server running a script that will, in turn, store that data received in a Google spreadsheet.

The push mechanism is ideal when your hardware might be sleeping from time to time (hence not reachable), to capture a specific event (example: your garage door is opening) or to store a log of what your hardware is doing.

Note: I used a Particle Photon in this project, but I think the mechanism can be helpful with other hardware in general, like Arduinos and Raspberry Pies.

Explanation

Like PopQuiz explained nicely in this post, here's what you will need to make:

• A Google Sheet with labels at the top of each column where your data will go.
• A Google Java script which controls the behavior of the Sheet. The tutorial links to a page which shows how to do this. The script will be deployed as a web app which gets hit by your webhook.
• Particle firmware which publishes JSON strings like this:{variable name : valueother variable: value2}
• A webhook which hits your Google Web App, is web form type, with query parameters like this:{GoogleSheetLabel1: {{variable name}}GoogleSheetLabel2: {{other variable}}}

Setup the Google Docs Side

Please follow the instructions on this site. In particular, follow ONLY these two sections:

• "The sheet"
• "The script"

Setup your Hardware to Push Data - General Case

Note: if you have a Particle hardware please skip this section.

You need to use an http library that allows your hardware to send the following http POST request:

POST / HTTP/1.1
Host: 127.0.0.1:8070
Connection: keep-alive
Content-Length: 71
Accept: */*
Origin: null
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.75 Safari/537.36
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
Accept-Encoding: gzip, deflate
Accept-Language: en,en-US;q=0.8,en-CA;q=0.6,es-419;q=0.4,es;q=0.2,fr-CA;q=0.2,fr;q=0.2
name=name123&email=email%40addr.com&phone=5144443322&message=message123

That is what the ajax call in the demo page of the tutorial I mentioned earlier is sending to the Google servers (don't worry, it's encrypted over HTTPS).

What I think matters is that the POST request contains this:

• a header with content-type application/x-www-form-urlencoded; charset=UTF-8
• a body containing the data to store in the Google spreadsheet in this particular format: name=name123&email=email%40addr.com&phone=5144443322&message=message123

Example:

POST / HTTP/1.1
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
name=name123&email=email%40addr.com&phone=5144443322&message=message123

NOTE: the %40 that you see in the email parameter is the @ sign url encoded, If I'm not mistaken.

Setup your Hardware to Push Data - Particle's Case

In the case you are using a Particle, you will need two things:

• configure a webhook
• code a publish command in your firmware to trigger that webhook with the wanted information

How this works:

STEP 1: the webhook

Create a particle webhook with the following information:

event name: googleDocs

full url: what you get from google docs (example: https://script.google.com/macros/s/1236278936489127634-2876348/exec)

method: POST

form (one way to look at it): key=name value={{my-name}}

form in JSON (another way to look at it):

{
  "name": "{{my-name}}"
}

headers: "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8"

include defaults: no

Enforce SSL: yes

Here's a screenshot of the webhook:

STEP 2: the firmware

Then in your firmware, add a line like this one:

String tempMessage = "Your garage door is opening";
Particle.publish("googleDocs", "{\"my-name\":\"" + tempMessage + "\"}", 60, PRIVATE);

Note: I'm using a dynamic custom field feature on webhooks that I learned in this discussion. You can read a bit more in this tutorial (search for mustache since the link seems not to work perfectly).

STEP 3: verify the console logs

Every time your hardware triggers the webhook you should see something like this in your Particle console logs:

Results

Here you can see how my hardware is filling up my Google spreadsheet:

Note: There are limits on how many times per day the Google API can be hit by your hardware. I'm pretty sure you would be able to pay Google for increasing your traffic quota, but I haven't looked into it.

Improvements

Ultimately, one would want to change the default name of headers for both the message body and the spreadsheet (name, email, phone and message) to something more meaningful. Once (and if) I get to this I will post the updates here.

In the meantime, you can start enjoying today the convenience of storing your data in Google Docs.

Troubleshooting

If things don't seem to work, you may check some Particle community posts here, specially this one from awardblvr.

Feel free to post your questions below this write-up as well.

Conclusion

Hope you see value in this tip. Feel free to respect it and/or check my other projects here.

Gustavo.

Need help?

If you require professional help with your projects, don't hesitate to write me a line about your needs at gusgonnet@gmail.com. Thank you!