Storing Data In Google

In this article we will learn how to store data in a Google spreadsheet.

Update: in this article I discuss how Google servers can poll your hardware for >please read this article instead.

Why? well, you could have different reasons, but one could be that you want to know how samples of anything are changing in time in order to investigate what could be going on around you.

For instance, last summer I wanted to know how the temperature of my pool would behave during the night. So I stayed all night long one night and wrote in paper the temperature at every 15 minutes? No way! Instead, I put google sheets to work for me and this is what I got:

Welcome to Google Sheets

Don't know what Google Sheets or a Google spreadsheet is? Think of it as an online Excel editor. Even more, it's Google's response to Microsoft Excel, as Google Docs is to Microsoft Word.

Don't have a Google account? Get one here.

Now, go to this link and create your first blank sheet by hitting on the big plus sign:

Clink on "Untitled spreadsheet" and enter the name you want for this spreadsheet. Hitting enter will save your change.

Hit Menu -> Tools -> Script editor... This will open the script editor in a new tab on your browser.

In the script editor, copy-paste the code you find in the software section below called Google script. Replace __YOUR_PARTICLE_DEVICE_ID__ with your photon or core id and __YOUR_PARTICLE_ACCESS_TOKEN__ with your particle Access Token. 

Remember not to share this token with anybody!

Now you are ready to test the script. Hit Menu -> Run -> select the function.

The first time you run it, it will ask you to save the script and to accept this script connecting to your spreadsheet (a popup will come up with an "Authorization required" message).

Hit Continue and then Allow (in the next popup).

UPDATE: Rico Hageman created a much better version of the script that goes in the script editor. You can find it here. Feel free to use his version instead.

Thanks Rico!

Where is the data?

Now we need to identify the source of the >this project. The project deals with monitoring the humidity and the temperature of an old dryer.

Go ahead, flash the firmware provided in the software section below to your particle and make sure your hardware is connected exactly like in this project:

Do you have your own data source? Ok, so I will explain a little bit how to modify your current firmware.

Exposing the data so Google can access it

Our script in Google Sheets will do REST calls to the Particle cloud. So one way of exposing our hardware >cloud variables.

In my project, I defined two cloud variables like this:

Beware! There are limits on the number of variables and the length of their name:

Currently, up to 10 cloud variables may be defined and each variable name is limited to a maximum of 12 characters.

This set me back a couple of hours in the past when I tried to name my cloud variable "fancy_variable_name_for_a_hackster_project". That of course did not work (since is longer than 12 characters) and there were no apparent reasons for the failure (the code compiled just fine).

Around lines 74 and 83 in the firmware we provide values for these cloud variables, formatted in a special way:

and

If your data is in different formats, as Murphy states they would be, you would have to make the proper type conversions and have them in string at the end. For instance, these is the code that converts the float reading of humidity in the string format Google can read after:

Now that the data is exposed, let's see if our google spreadsheet gets populated with it.

Run the Google script

Let's go back to our script. If you have closed the browser already, no problem, just open it again from the menu in our spreadsheet: Menu -> Tools -> Script editor... 

Now hit Menu -> Run -> collectDatadht22. There will be a little yellowish banner on top saying "Running function collectDatadht22... Cancel  Dismiss".

Head back to the spreadsheet and you will see some values being populated. The more you hit Run the more values you get.

You may have noticed that some values seem misplaced:

Well, not quite. I like to see the latest reading on top of the page, so I can get something like this:

Otherwise you would have to scan down to the last cell populated, and when this has been running for a while the list can get very long.

Scheduling the script

Now this wouldn't be fully automated if we had to be there clicking in Run to get new data values, right?

For this to happen, go back to the script editor and hit Menu -> Resources -> Current project's triggers.

There will be a little popup coming up with a like stating "No triggers set up. Click here to add one now." So click there and add a time-driven event to your liking, it could be every hour, day, minute, every 5 minutes, etc:

Now hit Save and you are done. The values are going to be captured at the schedule you specified. You can now sit down and wait for the columns to populate!

Conclusion

your data now will be gathered in a Google spreadsheet and you can do whatever you want with it, make a chart, get max and min across time, etc.

I hope this was as helpful to you as it was to me,

Gustavo.

Psst: please find my other projects here.

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!