Temp/Humidity Data Analysis Using Ubidots and Google-Sheets

In this tutorial, we will measure different temperature and humidity data using temperature and humidity sensor. You will also learn how to send this data to Ubidots. So that you can analyze it from anywhere for different application. Also by sending this data to google sheets, predictive analysis can be achieved.

Step 1: Hardware and Software Required

Hardware Required:

Software Required:

Arduino IDE
Ubidots

Library Used:

PubSubClient Library
Wire.h

Step 2: Uploading the Code to ESP32 Using Arduino IDE:

Before uploading the code you can view the working of this sensor at a given link.
Download and include the PubSubClient Library and Wire.h Library.

#include <WiFi.h>
#include <PubSubClient.h>
#include <Wire.h>
#include <HardwareSerial.h>

You must assign your unique Ubidots TOKEN, MQTTCLIENTNAME, SSID (WiFi Name) and Password of the available network.

#define WIFI SSID "XYZ" // Put your WifiSSID here

#define PASSWORD "XYZ" // Put your wifi password here
#define TOKEN "XYZ" // Put your Ubidots' TOKEN

#define MQTT_CLIENT_NAME "XYZ" // MQTT client Name

Define variable and device name on which the data will send to Ubidots.

#define VARIABLE_LABEL "Temperature" // Assing the variable label

#define VARIABLE_LABEL2 "Battery"
#define VARIABLE_LABEL3 "Humidity" #define DEVICE_LABEL "esp32" // Assig the device label

Space to store values to send:

char payload[100];

char topic[150];

char topic2[150];

char topic3[150];// Space to store values to send

char str_Temp[10];

char str_sensorbat[10];
char str_humidity[10];

Code to publish data to Ubidots:

sprintf(topic, "%s", ""); // Cleans the topic content  

sprintf(topic, "%s%s", "/v1.6/devices/", DEVICE_LABEL);
sprintf(payload, "%s", ""); // Cleans the payload content  
sprintf(payload, "{\"%s\":", VARIABLE_LABEL); // Adds the variable label  
sprintf(payload, "%s {\"value\": %s", payload, str_Temp); // Adds the value 
sprintf(payload, "%s } }", payload); // Closes the dictionary brackets  
client.publish(topic, payload);

Compile and upload the temp_humidity.ino code.
To verify the connectivity of the device and the data sent, open the serial monitor.If no response is seen, try unplugging your ESP32 and then plugging it again. Make sure the baud rate of the Serial monitor is set to the same one specified in your code 115200.

temp_humidity.ino
Download

Step 3: Serial Monitor Output.

Step 4: Making the Ubidot Work:

Create the account on Ubidots.
Go to my profile and note down the token key which is a unique key for every account and paste it to your ESP32 code before uploading.
Add a new device to your Ubidots dashboard name esp32.
Click on devices and select devices in Ubidots.
Now you should see the published data in your Ubidots account, inside the device called "ESP32".
Inside the device create a new variable name sensor in which your temperature reading will be shown.
Now you are able to view the Temperature and other sensors data which was previously viewed in the serial monitor. This happened because the value of different sensor reading is passed as a string and store in a variable and publish to a variable inside device esp32.

Step 5: Export Your Ubidots Data to Google Sheets

In this we can extract the data stored in the Ubidots cloud for further analysis. The possibilities are enormous; for instance, you could create an automatic report generator and send it to your customers every week.

Another application would be device provisioning; if you have thousands of devices to deploy, and their information is in a Google Sheet, you could create a script to read the sheet and create a Ubidots data source for every line on the file.

Steps to do this:

Create a Google Sheet and add two sheets to it with these names:

Variables
Values

From your Google Sheet, click on "Tools" then "Script Editor...", then "Blank Project":
Open the Script Editor:
Add the code below (in the code section) to the script Script.
Also add the Token Id, device Id taken from your Ubidots account to the following code.
Done! now open your Google Sheet again and you'll see a new menu to trigger the functions.