Open Source Flipper Zero, Thread, Matter, and Zigbee on ESP32, Arduino go Zephyr, and more!

0:00:00 / -

Watch now on YouTube Watch now

Storing Data In Google

Made by gusgonnet / Home Automation

About the project

Store any data you want in Google Sheets

Project info

Difficulty: Easy

Platforms: Google, Particle

Estimated time: 1 hour

License: GNU General Public License, version 3 or later (GPL3+)

Items used in this project

Hardware components

Particle Photon

x 1

Software apps and online services

Google Sheets

Story

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:

sprintf(resultstr, "{"t":%s}", tempInChar);

and

sprintf(humiditystr, "{"t":%s}", humiInChar);

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:

char humiInChar[32]; float humid = (float)DHT.getHumidity(); int humid1 = (humid - (int)humid) * 100; sprintf(humiInChar,"%0d.%d", (int)humid, humid1); //google docs will get this variable - if you are storing the value in a google sheet sprintf(humiditystr, "{"t":%s}", humiInChar);

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!

Code

Google script

Enter this code in the Google Sheets script editor

function collectDatadht22() {
  var  sheet = SpreadsheetApp.getActiveSheet();

  var response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/__YOUR_PARTICLE_DEVICE_ID__/temp?access_token=__YOUR_PARTICLE_ACCESS_TOKEN__");
  var response2 = UrlFetchApp.fetch("https://api.spark.io/v1/devices/__YOUR_PARTICLE_DEVICE_ID__/humidity?access_token=__YOUR_PARTICLE_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

    var response2 = JSON.parse(response2.getContentText()); // parse the JSON the Core API created
    var result2 = unescape(response2.result); // you'll need to unescape before your parse as JSON

    try {
      var p = JSON.parse(result); // parse the JSON you created
      var p2 = JSON.parse(result2); // parse the JSON you created
      var d = new Date(); // time stamps are always good when taking readings
      sheet.appendRow([d, p.t, p2.t]); // append the date and data to the sheet
      sheet.getRange(2,4).setValue(d);
      sheet.getRange(2,5).setValue(p.t);
      sheet.getRange(2,6).setValue(p2.t);
      } catch(e)
    {
      Logger.log("Unable to do second parse");
    }
  } catch(e)
  {
    Logger.log("Unable to return JSON");
  }
  
}

Particle code

#include "PietteTech_DHT/PietteTech_DHT.h"

#define PUSHBULLET_NOTIF "pushbullet"

// system defines
#define DHTTYPE  DHT22              // Sensor type DHT11/21/22/AM2301/AM2302
#define DHTPIN   4         	    // Digital pin for communications
#define DHT_SAMPLE_INTERVAL   5000

//declaration
void dht_wrapper(); // must be declared before the lib initialization

// Lib instantiate
PietteTech_DHT DHT(DHTPIN, DHTTYPE, dht_wrapper);

// globals
unsigned int DHTnextSampleTime;	    // Next time we want to start sample
bool bDHTstarted;		    // flag to indicate we started acquisition
int n;                              // counter

char resultstr[64]; //String to store the sensor temp
char humiditystr[64]; //String to store the sensor humidity

char VERSION[64] = "0.11";
bool dryer_on = false;
String dryer_stat = "";
int humidity_samples_below_10 = 0;
int humidity_samples_below_5 = 0;

void setup()
{

 DHTnextSampleTime = 0;  // Start the first sample immediately
 Particle.variable("temp", resultstr, STRING);
 Particle.variable("humidity", humiditystr, STRING);
 Particle.variable("dryer_stat", dryer_stat);

 Particle.publish("DHT22 - firmware version", VERSION, 60, PRIVATE);

 bool success = Particle.function("resetDryer", resetDryer);
 if (not success) {
    Particle.publish("ERROR", "Failed to register function resetDryer", 60, PRIVATE);
 }
 
}


// This wrapper is in charge of calling
// must be defined like this for the lib work
void dht_wrapper() {
    DHT.isrCallback();
}

void loop()
{

  // Check if we need to start the next sample
  if (millis() > DHTnextSampleTime) {
      
	if (!bDHTstarted) {		// start the sample
	    DHT.acquire();
	    bDHTstarted = true;
	}

	if (!DHT.acquiring()) {		// has sample completed?

 float temp = (float)DHT.getCelsius();
 int temp1 = (temp - (int)temp) * 100;

 char tempInChar[32];
 sprintf(tempInChar,"%0d.%d", (int)temp, temp1);

 //google docs will get this variable - if you are storing the value in a google sheet
 sprintf(resultstr, "{\"t\":%s}", tempInChar);

 char humiInChar[32];
 float humid = (float)DHT.getHumidity();
 int humid1 = (humid - (int)humid) * 100;

 sprintf(humiInChar,"%0d.%d", (int)humid, humid1);

 //google docs will get this variable - if you are storing the value in a google sheet
 sprintf(humiditystr, "{\"t\":%s}", humiInChar);


  n++;  // increment counter
  bDHTstarted = false;  // reset the sample flag so we can take another
  DHTnextSampleTime = millis() + DHT_SAMPLE_INTERVAL;  // set the time for next sample

  //if humidity goes above 50% then we believe the dryer has just started a cycle
  if ( ( not dryer_on ) and ( humid > 50 ) and ( temp > 30 ) ) {
    dryer_on = true;
    humidity_samples_below_10 = 0;
    humidity_samples_below_5 = 0;
    Particle.publish(PUSHBULLET_NOTIF, "Your dryer started a cycle", 60, PRIVATE);
    
  }

  if ( dryer_on and ( humid < 10) and ( temp > 50 ) ) {
    humidity_samples_below_10 = humidity_samples_below_10 +1;
  }

  //if there are 5 samples below 10% then we are sure it's almost done
  if ( dryer_on and (humidity_samples_below_10 >= 5) ) {
        Particle.publish(PUSHBULLET_NOTIF, "Your clothes are dry", 60, PRIVATE);
        dryer_on = false;
  }

  if( dryer_on ) {
      dryer_stat = "dryer_on";
  } else {
        dryer_stat = "dryer_off";
  }

 }
 }
 
}

//call this function when the algorithm gets stuck (you aborted the dryer cycle, etc)
int resetDryer(String args)
{
 dryer_on = false;
 return 0;
}

Credits

gusgonnet

If you need professional help with a project, contact me at gusgonnet@gmail.com

Shop

Project Hub

Video

Blog

Shop home

3D printing

Single Board Computers

Development kits

Sensors

Internet of things

Robotics

Accessories

Product of the Week

Electromaker Educator

The Electromaker Show

The Electromaker Podcast

Storing Data In Google

About the project

Project info