Altium
esp32 iot projects

Send data from Google Spreadsheet or Google Sheets to ESP8266 or ESP32

Google Spreadsheet to ESP8266 or ESP32, Soil Moisture monitoring

Google Spreadsheet to ESP8266:

Google spreadsheet to ESP8266

Send data from Google Spreadsheet or Google Sheets to ESP8266 or ESP32– Ever since I made a video on Google Spreadsheet and ESP8266, many boys and girls have been requesting me to make Google Spreadsheet and ESP8266 part-2 video; to explain how do we send data or sensors threshold values from Google Spreadsheet to ESP8266 or ESP32?

Well, let me tell you that in Part-1, I explained how you can send data from ESP8266 to Google Spreadsheet. So, if you want to monitor sensors, or send any other data from ESP8266 or ESP32 to Google Spreadsheet, then I highly recommend that you guys must read my article on the ESP8266 and Google Spreadsheet.

Sending data to Google Spreadsheet from ESP8266 or ESP32 is quite easy and you will get many videos and articles on it. However, sending data from Google Spreadsheet to ESP8266 or SEP32 is a bit tricky. And there is not a single video available on it, so I decided to make a detailed video on it and I also decided to write this article.


So, in this article, you will learn how to make a Soil Moisture monitoring system using Google Spreadsheet or Google Sheets, ESP8266 WiFi module or ESP32 WiFi + Bluetooth module, a Soil Moisture Sensor, if you don’t have this particular soil moisture sensor it’s ok you can use any other type of the soil moisture sensor, DHT11 Temperature & Humidity Sensor, you will also need a 5V buzzer, and an Oled display module.

Google spreadsheet to ESP8266

I have connected everything as per the circuit diagram which I will explain in a minute. But first I am going to explain how this project actually works and then I will start practical demonstration.

On the monitoring side, I have two Google Spreadsheets.

Google spreadsheet to ESP8266

On this Google sheet I am using 5 columns for monitoring Date, Time, Temperature, Humidity, and Soil Moisture. On this Google Spreadsheet, I am receiving data from the ESP8266.

Google spreadsheet to ESP8266

While the other Google Spreadsheet, I am using for setting the threshold values of Soil Moisture and Temperature.  Let’s say I want the buzzer to turn ON when the Soil moisture drops below 60%, then I will simply write 60 in A2 Cell, and in the same way; If I want the Led to turn ON when the temperature exceeds above 45 Degrees Celsius then I will simply write 45 in B2 Cell. To further confirm that the Google Spreadsheet actually sends these threshold values to the ESP8266, I will be displaying these values on the Oled display module. So, I will use Buzzer as an indicator when the soil moisture drops below the threshold value, and I will use the ESP8266 onboard Led as an indicator when the temperature increases above the set value or threshold value. Now, let’s go ahead and kick off our practical demonstration.



Altium Designer + Altium 365 + Octopart:

Arduino LoRa Free SMS

Altium 365 lets you hold the fastest design reviews ever. Share your designs from anywhere and with anyone with a single click. it’s easy, leave a comment tagging your teammate and they’ll instantly receive an email with a link to the design. Anyone you invite can open the design using a web browser. Using the browser interface, you’re able to comment, markup, cross probe, inspect, and more. Comments are attached directly to the project, making them viewable within Altium designer as well as through the browser interface. Design, share, and manufacture, all in the same space with nothing extra to install or configure. Connect to the platform directly from Altium Designer without changing how you already design electronics. Altium 365 requires no additional licenses and comes included with your subscription plan.

Get real-time component insights as you design with Octopart built into Altium 365. Octopart is the fastest search engine for electronic parts and gives you the most up-to-date part data like specs, datasheets, cad models, and how much the part costs at different amounts etc. Right in the design environment so you can focus on your designs. Start with Altium Designer and Activate Altium 365. Search for electronic parts on Octopart.


Practical Demonstration:

I have powered up all the electronics. Right now ESP8266 and Laptop are connected with the WiFi. After every few seconds I am receiving the Temperature, Humidity, and Soil Moisture values from the ESP8266. This time duration can be changed in the programming.

Google spreadsheet to ESP8266

Right now the Buzzer is OFF as the soil moisture value (47) is above the threshold value (35). Let’s say I want to maintain the Soil moisture value above 60%, so what I will do, I will simply increase the threshold value.

Google spreadsheet to ESP8266

So, any value that you want to send from Google Spreadsheet or Google sheet; simply write these values in the A2 and B2 Cells, as these cells are defined the programming. Once the values are entered, the Google Spreadsheet will automatically send these values to the ESP8266.

It takes a few seconds before the value is updated on the Oled display module.

Google spreadsheet to ESP8266

You can see the threshold value on the Oled display module is updated and also the buzzer has turned ON.

You can also see, the onboard Led is off as the temperature is below the threshold value of 45 degrees Celsius.


Now, the threshold values are set. So, now I can go ahead and add some water.

Google spreadsheet to ESP8266

You can see the updated value of the soil moisture and the buzzer has also turned OFF. On the Google Spreadsheet I increased the fonts size, so that you can easily see the values.

Google spreadsheet to ESP8266

Now, I am going to apply some heat.

Google spreadsheet to ESP8266

DHT11 Sensor is quite slow, it takes time to give updated readings. So, I kept applying the heat. The Nodemcu ESP8266 WiFi module onboard Led is off as the measured temperature is less than the threshold value which is 45 degrees Celsius.

Google spreadsheet to ESP8266

You can see the temperature values has increased and also the Onboard Led has turned ON. I am sure by now, you might have got an idea of how does this system work. So, without any further delay let’s get started!!!


Amazon Links:

12v Adaptor:

Nodemcu ESP8266 WiFi Module:

ESP32 WiFi + Bluetooth Module

DHT11 Temperature and Humidity Module:

Soil Moisture Sensor

SSD1306 Oled display module

LM7805 Voltage Regulator:

470uf capacitor:

330-ohm resistor:

DC Female Power Jack:

Female Headers:

5V buzzer

Other Tools and Components:

Top Arduino Sensors:

Super Starter kit for Beginners

Digital Oscilloscopes

Variable Supply

Digital Multimeter

Soldering iron kits

PCB small portable drill machines

*Please Note: These are affiliate links. I may make a commission if you buy the components through these links. I would appreciate your support in this way!



Soil moisture Sensor:

This soil moisture sensor provides data in both digital and analog form. The two probes on the sensor act as variable resistors. Use it in a home automated watering system or just use it to find out when your plant needs water.

Google spreadsheet to ESP8266

The soil moisture sensor consists of two probes which are used to measure the volumetric content of water. The two probes allow the current to pass through the soil and then it gets the resistance value to measure the moisture value.

When there is more water, the soil will conduct more electricity which means that there will be less resistance. Therefore, the moisture level will be higher. Dry soil conducts electricity poorly, so when there will be less water, then the soil will conduct less electricity which means that there will be more resistance. Therefore, the moisture level will be lower.

Wiring Connection

VCC: 3.3V-5V
GND: GND
DO: Digital output interface (0 and 1)
AO: Analog output interface

Features:

  1. Dual output mode, analog output more accurate
  2. A fixed bolt hole for easy installation
  3. With power indicator (red) and digital switching output indicator (green)
  4. Having LM393 comparator chip, stable.

DHT11 Sensor:

The DHT11 is a basic, ultra low-cost digital temperature and humidity sensor. It uses a capacitive humidity sensor and a thermistor to measure the surrounding air, and spits out a digital signal on the data pin. It is simple to use, but requires careful timing to grab data.

Google spreadsheet to ESP8266

Description:

  • Low cost
  • 3 to 5V power and I/O
  • 5mA max current use during conversion (while requesting data)
  • Good for 20-80% humidity readings with 5% accuracy
  • Good for 0-50°C temperature readings ±2°C accuracy
  • No more than 1 Hz sampling rate (once every second)
  • Body size 15.5mm x 12mm x 5.5mm


ESP8266 (NodeMCU):

ESP8266 is a highly integrated chip designed for the needs of a new connected world. It offers a complete and self-contained Wi-Fi networking solution, allowing it to either host the application or to offload all Wi-Fi networking functions from another application processor.
ESP8266 has powerful on-board processing and storage capabilities that allow it to be integrated with the sensors and other application specific devices through its GPIOs with minimal development up-front and minimal loading during runtime. Its high degree of on-chip integration allows for minimal external circuitry, and the entire solution, including front-end module, is designed to occupy minimal PCB area.

Google spreadsheet to ESP8266

Specifications:

  • Serial/UART baud rate: 115200 bps
  • Input power: 3.3V
  • I/O voltage tolerance: 3.6V Max
  • Flash Memory Size: 1MB (8Mbit)
  • WiFi security modes: WPA, WPA2

Read my getting started article on ESP8266 Nodemcu.

Circuit Diagram:

The following circuit diagram shows, how to interface soil moisture sensor, dht11 sensor, 5V buzzer, and SSD1306 Oled display module with the Nodemcu ESP8266 WiFi module.

Google spreadsheet to ESP8266

I am using a 5V regulated power supply to power up all the electronics. The SCK or SCL and SDA pins of the SSD1306 Oled display module are connected with the D1 and D2 pins on the Nodemcu ESP8266 WiFi module. While the VDD and GND pins of the Oled display module are connected with the 3.3V and GND pins.

D4 pin on the ESP8266 is used to control the 5V buzzer. I use 2N22222 NPN transistor and a 10K ohm resistor to control this buzzer. The transistor and resistor make the driver circuit.

Data pin of the DHT11 Temperature and Humidity sensor is connected with the D3 pin on the ESP8266 module. While the voltage and GND pins of the DHT11 sensor are connected with the 3.3V and GND pins.

The Analog output pin of the Soil Moisture sensor is connected with the A0 Pin and VCC and GND pins are connected with the 5V and GND. So, that’s all about the circuit diagram and now let’s work on the Google Spreadsheet.


Google Spreadsheet with ESP8266:

First of all we will create the Google Spreadsheet for which we will click on the Google apps and in which we will click on the Sheets.

Google spreadsheet to ESP8266

After that we will click on the blank in order to create a new spreadsheet.

Google spreadsheet to ESP8266

After that give name to Google sheet which is temperature and soil moisture monitoring.

Google spreadsheet to ESP8266

Then we will click on the extensions and click on the Apps Script.

Google spreadsheet to ESP8266

 

After opening the Apps Script, delete the function and copy the following code.

Google spreadsheet to ESP8266

Code:

After that copy the script id from the Google sheet

Google spreadsheet to ESP8266

Paste this id in the code and click on the deploy button

Google spreadsheet to ESP8266

Then click on the Deploy and in the drop down list click on the New deployment.

Google spreadsheet to ESP8266

Then click on the enable deployment types.

Google spreadsheet to ESP8266



Now select Web app

Google spreadsheet to ESP8266

Now select anyone and click on the Deploy button.

Google spreadsheet to ESP8266

Now select the Gmail account in which you have created the Google sheet.

Google spreadsheet to ESP8266

Now click on the Go to Untitled project (unsafe).

Google spreadsheet to ESP8266

Now click on the allow button to give access to the account

Google spreadsheet to ESP8266

Then copy the deployment id which will be used in the code.

Google spreadsheet to ESP8266

Now the Google sheet code is completed.


Google sheet to ESP8266:

Now, to send data from Google Spreadsheet to ESP8266 add another Google Sheet in the same way as we did previously. Give a name to the Spreadsheet, set the column names as Soilmoisture and Temperature. Values stored in A2 and B2 cells are going to be send to ESP8266. So, in the programming we only access these two cells A2 and B2.

Google spreadsheet to ESP8266

Click on the extensions and select Apps Scripts. All the other steps are exactly the same, the only difference, this time we will use a different script code as given below. If you face any issues, watch the video tutorial given at the end of this article.

Now, copy the deployment ID, paste it next to the GScriptedId in the ESP8266 programming. This is used for receiving data from Google Script address. Now the Google sheet is ready for sending the data to the ESP8266.

Note: if you face any issues, watch video tutorial given at the end of this article.

Now the process of the Google spreadsheet is completed and we will open the Arduino IDE. If this is your first time using the Nodemcu ESP8266 WiFi module then you will need to install the Nodemcu board. I have a very detailed article on how to install the Nodemcu board.

Libraries Needed:

Before, you start the programming, first of all, make sure you download all the necessary libraries.

Download: Adafruit_GFX.h

Download: Adafruit_SSD1306.h

The Adafruit_GFX.h and Adafruit_SSD1306.h libraries I have been using for quite a long time and I have already explained how to download and use these libraries.

You will also need to install the DHT11 library for this click on the Sketch Menu then go to the Include Library and then click on the Manage Libraries. Type dht in the search box and then install the dht sensor library.

For this project, you will also need these three files DebugMacros.h, HTTPRedirect.cpp, and HTTPRedirect.h. These files are need to be placed with the main programming file in the same folder.

Download: All the files

Google spreadsheet to ESP8266


Google Spreadsheet and ESP8266 Programming:

Watch Video Tutorial:

Engr Fahad

My name is Shahzada Fahad and I am an Electrical Engineer. I have been doing Job in UAE as a site engineer in an Electrical Construction Company. Currently, I am running my own YouTube channel "Electronic Clinic", and managing this Website. My Hobbies are * Watching Movies * Music * Martial Arts * Photography * Travelling * Make Sketches and so on...

Leave a Reply

Back to top button
%d bloggers like this: