Controlling a light bulb through Google Sheets
ฝัง
- เผยแพร่เมื่อ 9 ก.ย. 2020
- IoT devices can be controlled in multiple ways, from custom written dashboards to Alexa and Google Home depending on the requirements. A very simple way that I haven't seen used so far is controlling your smart home setup through Google Sheets.
A custom spreadsheet can be created that can have dedicated cells for controlling devices. In my example, I control just a single light bulb by setting the cell value to one of several predefined values and the device reads that value periodically and turns on or off the relay that controls the light bulb.
The project can also be expanded to multiple devices that can also read the desired temperature, humidity, or anything else that you will like to control in a system like this.
To start a system like this you can copy my spreadsheet from the link below:
docs.google.com/spreadsheets/...
The full source code for the Arduino device and the Google Sheet can be found at:
github.com/bkolicoski/arduino...
Check out my other video on how to add custom functions to Google Sheets:
• Adding custom function...
The written article for the project can be found at Instructables:
www.instructables.com/Control...
This device is inspired and powered by the work of Sujay Phadke: github.com/electronicsguy
Tools and materials used in the video:
NodeMCU - s.click.aliexpress.com/e/_dXc...
5V 1 Channel relay module - s.click.aliexpress.com/e/_d7l...
Dupont jumper wires - s.click.aliexpress.com/e/_dYy...
Mini breadboards - s.click.aliexpress.com/e/_dX3...
Light bulb - s.click.aliexpress.com/e/_d8r...
Quick wire terminals - s.click.aliexpress.com/e/_dSr...
Support my work on Patreon so I can continue producing free content and tutorials!
/ taste_the_code
My recording gear:
GT10 Pro 5G Android Phone - s.click.aliexpress.com/e/_oDn...
Dragon Touch 4K Action Camera - s.click.aliexpress.com/e/_mrc...
USB Condenser Microphone - s.click.aliexpress.com/e/_mq5...
Tripod - s.click.aliexpress.com/e/_ooa...
Phone tripod mount - s.click.aliexpress.com/e/_s9WaiJ
Controlling a light bulb through Google Sheets
#smarthome #googleSheets #arduino #iot #nodemcu #tastethecode
Main Site and blog: www.tastethecode.com
Facebook: / tastethecode
Instagram: / taste_the_code
Twitter: / taste_the_code
Get exclusive Taste The Code t-shirts and merch at
teespring.com/taste-the-code
Click on the link below to get two months of free premium access to Skillshare and start learning today!
skl.sh/37LnRua
Get $50 off your order on hosting for all of your website needs and learning projects on Dreamhost!
www.dreamhost.com/r.cgi?24057... - แนวปฏิบัติและการใช้ชีวิต
![[Full Episode] MasterChef Junior Thailand มาสเตอร์เชฟ จูเนียร์ ประเทศไทย Season 3 Episode 2](http://i.ytimg.com/vi/YpQnHoQvZt4/mqdefault.jpg)
Fantastic! You rescued the IoT class that I am teaching.
That's awesome! Thanks for sharing 😊😊😊 🤗🤗🤗🤗
great vid. I've been researching this idea for a while but am not very familiar with google app scripts(java scripts). I plan on using this with Appsheets to control a chicken coop door. Ill probably have the delay something like 5 mins so I don't hit the Google server too much. Thanks for the great content.
Thank you for the nice words! Good luck with your project!
Awesome project! Thanks for sharing!!
Thanks! You are welcomed!👍👍👍
thanks for your sharing sir. I have question. do you have any ideas, how to use your method for controlling more than 1 output. your sharing very much appreciated
Yes, you can add reading of multiple cells and vary the output from them. The cell address is in the URL so you need to duplicate that part.
that was cool man! Did you do a video once about using multiple buttons on one arduino pin?
Thanks! And yes I did. Here it is: th-cam.com/video/ZsSJdGdLF-A/w-d-xo.html Any project in mind?
@@TasteTheCode just watched it. So the 5v gets divided by the number of resistors in the circuit and hence the thresholds get narrower? That's cool. I'm going to have about 30 buttons and switches on a music player I'm designing.
That's a bit to much for this approach. This works best for up to 5 buttons. For more look into using shift registers.
@@TasteTheCode shift registers, okay thanks I'll look into that. 👍🏻
Sir, I am working on RFID in which First I store the data in google sheet and then read it back and conditionally outputs the result.
👍
Amazing
Thank you
Is there a way to make the bulb turn on with command for 5 second and then shut off automatically?
Yes, you can start counting once you've turned it on and if enough second pass (check with millis()) you can the turn it off bit the value from the sheet will be different.
Could you use Google Forms along with this and build a nice graphical interface to control the lights?
That is an interesting idea. I'm not familiar with Google Forms to such an extent to know this directly but it sounds possible. By default Google Forms adds all of the entries in a new row so you will need a way to modify this to edit one row instead. Maybe by editing your own response?
@@TasteTheCode I'm not knowledgeable enough to figure that out. I'm just an old builder, near retirement and maybe I will try programming when I have more time. It's hard to teach an old dog new tricks.
awesome. I am planning to use this for control of engine heater and cabin heater in a boat working on random times.
Sometimes 5 starts in a day. Sometimes one or no.
I have a Google Sheet where the Ship List is manually updated whenever a boat trip is planned.
For this purpose I amended the code as to control two relays. The project seem to be working fine. However - knowing the boat will loose wifi connection at a point during the trip at sea I have been stress testing and killed the WiFi.
This seems to be a problem - the ESP will actually reconnect automatic - but will not return to running the code if the cutout happens during the GET request - in a way that the response is not received. Do you have a workaround on this problem?
Hey, that is a really nice use case. Unfortunately, the code I have does not account for network issues as you mentioned. You will need to modify the code in the error handling section where it checks for 3 consecutive errors and goes to sleep. I'm guessing you can remove that part entirely so it constantly checks the doc for value. Maybe you can even add a check to see if wifi is connected and only then check for the value in google docs. Good luck!
@@TasteTheCode Thanks again for your excellent work. I have come to a solution, where I as you suggest delete the error handling and implement a watchdog - #include Ticker.h
@@larsvlarsen2096 Awesome, I'm glad that you managed to implement it! If you decide to publish your code send me the link and I'll update the video desc to also include that for anyone else trying the same.
@@TasteTheCode I love the idea of sharing. However I am on a low skills level when it comes to coding - and my coding is a mess, so I think it will be very confusing for anyone else. Besides that I dont have the overview anymore for all the credit I owe to other contributers...
Also I am still trying to improve and still struggling to make a code that is suitable for receiving 2 - or more DS18B20 temp sensors (on bus) and sending to gsheet. (the aim being to have the gsheet acting as remote thermostat). Here my challenge is to place the sensor readings in 2 - or more variables that are send as payload.
@@larsvlarsen2096 to read multiple cells, you will basically need to have multiple GET calls for each of the cells. You will need to dynamically construct the URL before the calls and substitute cellAdress appropriately.
Hi is this procedure available even when the spreadsheet is not in use ?
It is but that will not affect the output unless something else changes the value.
Can you do a video on setting up individually addressable Leds on a strip based on data from Google sheet
For example we create a master of a physical location name and link them to specific leds on a strip
And whenever these locations are entered upon on Google sheet , the assigned specific leds light up
Hey, that is an interesting idea. However, my schedule is kind of full right now so I'm not sure when I can work on it.
Hi, can i use esp32s instead of esp8266 for this code ? Is there any change in the codes ? Thank you
I have not tried it on ESP32 but there is no reason why it should not work.
молодец
Thanks!
Hi, is it still working??
No idea. I haven't used this in a while so I'm not sure if the API has changed.
Hello sir, i keep getting error when fetching data
Can you be more specific?
@@TasteTheCode my nodemcu connects with wifi, and is able to do blinking operation. But when i execute this code it just shows error count till 4 and doesn't respond after. It hasn't been able to fetch data from the site yet
@@thalavedana it is difficult to tell what is going on not knowing your exact code. Make sure that the API key and the URLs are all matched and copied well from your google drive.
@@TasteTheCode may i put my code here, if it can be understood by you more?
Better use some code sharing site and paste the link here
imho, this method not recomended, the mcu will make a request to google server every second, and the google script has limitation on how frequent and how many request you can make per day, its a free service by google after all.
Yes, true. I would not recommend this as a production solution but it was fun to do and demonstrate something outside the box.
@@TasteTheCode by chance do you have workaround to overcome the issue mentioned by @xGen720