ฝัง
- เผยแพร่เมื่อ 10 ธ.ค. 2019
- new version for esp32 • ESP32 RFID: Google She...
ESP: Google Sheets as a database for rfid cards
In this project Google Sheets are used to store access data for rfid cars and for attendence logging
code:
github.com/unreeeal/ESP/tree/...
libs:
RFID RC522 github.com/miguelbalboa/rfid
liquid crystal for esp family github.com/lucasmaziero/Liqui...
Aliexpress
mfrc522 ali.pub/4dkuz4
esp8266 ali.pub/4dkuzb
lcd1602 ali.pub/4dkuzi
breadboard ali.pub/68gwyh - วิทยาศาสตร์และเทคโนโลยี
![[UNCUT]”หมอปลา และทนายแก้ว” ลากไส้ขบวนการ เด็กเชื่อมจิต จ่อเอาผิดทั้งครอบครัว I คนดังนั่งเคลียร์](http://i.ytimg.com/vi/9zXrAIVBQFo/mqdefault.jpg)
Thanks man, it really helps me!
awsome project and explanation love it
that's really cool, thanks for the knowledge, good job sir
Exellent work thank you very much
Thank your for this tutorial, this is what what I need for my system .
Very cool! I have a question, can I not make it instantaneous and make it so you need to press a button for it to send on the excel? Will the code change that much?
BIG THANKS BRO
Hi. Did you use active or passive buzzer?
Which version of esp8266 should be used ? I mean model number? Your ali express link shows 3 types of boards ?
Thanks Man... It works
Really usefull tutorial..
In my project,when a card is scanned,the location of cardholder will send to google sheet.here i want to add gps url along with rfid datas,how do i add another column in the google sheet for url ?
Thanks in advance
@anyboards
Hi, may i ask something?
If i just want to sent UID to Google Sheets, where parts of the code and script that i have to delete? Thanks,
Thanks.
i am working on project now i have 1 month left for rfid finger validation code to expire so i want to update after next month what should i do
Can you make a tutorial like this using Esp8266 mfrc522 and adafruit oled display with database 🙏🙏🙏
Hi Anyboards
Great job on the tutorial. Can u tell me the advantage of using Arduino over just simply hooking up an RFID reader to a computer that runs Google sheet? To me it seems it would result in the same outcome. Thnx in advance
Hi,
depends on your goals,a computer is fine to just log, but it's not the best choice to open doors, turn off/on smth etc
also physical size, price, power consumption
How could I modify this to use NFC cards and a reader plugged directly into my computer to run an attendance system?
i dont get it if the spreadsheet with UID and names are stored in different sheet then how the nodemcu able to detect the name?
Hi, excellent work ! I have no I2C LCD here but only SPi LCD 16x2 or Oled 128X64 SSD1306. Could you help me to adapt the sketch?
Hi, I don't have these modules,
nope
Como puede modificarse para que la lectura sea mas rápida entre usuario y usuario?, se puede?, saludos y gracias por el aporte.
Hi anyboards.
First of all great work its a really nice project.
i am trying to use your project and i am from portugal and the time zone here is UTC +0 or GMT i already tried both and the time on the google sheets continues to appear the hours that is in CST. Can you help me pleaze.
Hi, yeah time zone doesn't work as used to, now its like this
var timeZone="America/Winnipeg";
// get your time zone here developers.google.com/adwords/api/docs/appendix/codes-formats#timezone-ids
var dateTimeFormat="dd/MM/yyyy HH:mm:ss";
var dateTime=Utilities.formatDate(new Date(), timeZone, dateTimeFormat);
Thanks for your work
I have question what is that fingerprint? and we use it for what?
the fingerprint is for ssl connections
@@anyboards8006
Thanks for fast reply
Is the SSL connection value is always the same or it is differ from one to other .if it is how can I now know mine
it depends on the domain name, but most of the google services has the same, and in that code it can sometime work without changing it since there is client.setinsecure() option, google fingerprint ssl certificate there are lot of tutorials how to get a fingerprint
@@anyboards8006
Thanks you are amazing
Subscribe and like for your channel
Card uid is not appearing on log google spreadsheet when RFID tag is shown. What can I do? Please suggest answer
Sir WiFi name and Password konsa dalna hai?
I'm having a problem with the arduino code in position
client. setInsecure();
how to solve it?
I just want to store the rfid scans into google sheets. Can you help me ?
Thanks, I giveme: Exception: Argument no valid: id (línea 83, archivo "Código"). What is the problem?
Hi there, I connected everything accordingly but Arduino IDE error warning: "Communication failure, is the MFRC522 properly connected?" Keeps appearing, please help
Hello sir, I have a problem in reading the card UID (It's actually reading but the reading is in the serial monitor) and if I tap any card or tag the buzzer keep making a sound. How can I fix this problem ??
excellent work!! you are awesome!
just one thing... how can I add a log out time when they scan it again? i want to use it in my hotel for check in's and check out's... to know how many nights the people stay in.
Hi, thank you!
hm,there are 2 tables one for permission data and the second with logs, where are you planing to have the check out column ?
@@anyboards8006 thanksfor your reply! I think the logs table would be the best option.
are there going to be only ins and outs records? tell me more, I don't know enough to answer your question
thanks!
@@anyboards8006 I want to give like rfid golden cards to our best customers, and i want that when they scan their cards, the spreadsheet capture their name, their log in time and open a gabinet. after that, when they want to leave the hotel, scan again their cards and take their log out time so then I can count the total of nights that they stay in the hotel.
Thanks a lot brother!
how are you going to determine which scan is the last one (check out)?
*they can scan they cards 100 times a day just to open the room
really helpful tutorial, if i wanted to add a column into the use profile sheet for email address and then check if the user-relative to their UID-has provided a valid email address and then allow them to scan if they have provided an email address and deny if they havent how would I do this?thanks
it is expected the users sheet be edited by admin, to check if a user record contains email at GoogleScript.js line 53 instead of if (data[i][0] ==uid)
insert if (data[i][0] ==uid && data[i][4].length>0).
To validate emails you can add one more column with randomnly generated token, then email a user with a link like sctipts.google....exce?token=theirTOken
if they visit the link replace token with 1 and it will be "if (data[i][0] ==uid && data[i][5]=="1")" in the main script
@@anyboards8006 thanks for the reply, will try today! Currently running on ESP8266, would upgrade to ESP32 improve speed and reduce wait time over the 2 httpsrequests?thanks
It would, but not that much, to really reduce it you can hold Uids in RAM and update the list every ... minutes, hours
it needs just 4 bytes per UID and only one request will be needed for attendance record, but it can be done after letting a user in,
so for user it will be instantly
Hi Guys
on one mac i'm getting an error:
In function 'String sendData(String, char*)':
warning: ISO C++ forbids converting a string constant to 'char*' [-Wwrite-strings]
on other is working like a charm. Why?
Help pls
i would be happy if you could share your circuit connection diagram
OMG IT WORKS. THANKS BROOOOOOOOOOOOOOOOOOOOOOOOOOO. THX THX THX. Alhamdulillah..
the password and the ssid where exactly needs? Because my lcd is typing just loading so I thing the wrong is there.My wifi is nodemcu?
hi Anyboards thank you for the tutorial, it's awesome.
i've try your code with esp32, sending data to google sheets log file working just fine.
But i can't receive any data from google sheets to my esp32. It seems that the function 'sendData' has no return value that can processed next to function 'HandleDataFromGoogle'. i've tried to print this 'sendData' return value to arduino serial monitor but only got a blank value.
if you have any suggestion to this problem can you please tell me?
Thankyou,
bro did u solved this problem ?
If you solve tell me please
HI,
can you help me in this!!
iam getting this error while compiling
>>
I'm getting a error msg scrip not found:doGet()
What to do?
Hello! Sir How do I remove LCD function?
hello sir sorry i got this error any idea on how to solve it
'class BearSSL::WiFiClientSecure' has no member named 'verify'
can i know how to add variable like i want to put Stuednt id
how can you send text or words to the google sheets from esp8266??
because right now, I can only send float data
absolutely the same as float, but you need to decode special characters (spaces % $ etc)
for example to send data your esp makes a request with float data and the link looks like ..****.com/?floatdata=0.3
to send text it should be ..****.com/?floatdata=yourtext
"floatdata" is a query param name you may use any name
what i need to if nodemcu dont found google sheets
at lcd wrote "was not found otB s all we know
Will it work while Internet is down for some time ?
Will it update the data later when internet resume or will loose the information
nope, it doesn't work without internet
Hi, can I use OLED display instead of LCD display? If yes then what changes has to be made in code.
Also how can I give different name for each tag?
Please reply as soon as possible
Hi,
probably you can, but I spent some time to a similar task and had no luck,
you need to know everything of SPI to do it.
What do you mean by a name for each tag?
there are names in both tabs
@@anyboards8006 I mean for all UID it shows as 'who are you?'. So can't I give different name for UIDs.
For example, UID 12345 = student1,
UID 67890 = student2
everything is in the video
you can set names in the main tab, add an uid to the main sheet (not a log one) and set name
anyboards
I'm sorry, I know that I bother you with a lot of questions. But can you tell me how I can send only one column content which is the card number. It is in my possession and stored as a variable. Please tell me how I can send it alone.please
hi, you can comment out lines with extra data on the google sheet side to ignore everything except uid, you will need to do it anyway
Thx man could you please share the wire diagram
which library we have to include if we have esp32 instead of esp8266? please tell me it's urgent!!
change the wifilib to "WiFi.h" and you will need to change the pins of rfid
Hello, great tutorial, what do I do for a new account in google sheets, I want to add new information in the list, for example, the customer's phone number
I do it, thank you 👍
Thanks man , it was helpful i just modfied by adding servo into this project ,it works ! and can i have a in and out time duration code?
hey there can i see your code for the servo project?
@@HorsieGtp just remove the led and use the led signal as input for Arduino,
If signal is 1, in the block
type the position you need.
But you need Arduino for it
Can you please help me
When I upload the code on arduino ide it said
leaving ....
Hard resetting via RTS pin
Please answer me soon.
you're looking at the uploading output, it's not a serial terminal, and that text means the code was uploaded successfully
SS_PIN 2
I think you should upload code before set sensor and other componen
Hello, I made the adaptation to ESP32, but it does not give me feedback on the RFID parameters that are stored in Google Sheets, it only saves them. What would I have to do to read them ... in advance thank you very much
Hi, you've to make 2 request to get data, the first reply with code 302 and with the url for the second request
@@anyboards8006 i don't understand how can i do it please help me
Thanks sir, it helps me a lot. And also i want to add sg90 servo can you teach me how?
hi, what's the exact problem ? there's a default sg90 example right in arduino ide, Thank you
Hey i don't know you can change the name associated to the UID in untilted Spread Sheet.
hi, I'm sorry, Could you please rephrase that?
@anyboards how to give name in uid? And how to send data from Google spreadsheet to LCD of the nodemcu?
name in uid?
you can't send data from spreadsheet unless you have an external ip.
but you can make requests from your nodemcu to the spreadsheet every 5secs etc to get updates
Good work, can you make a tutorial using the rdm600?
Hi, I don't have this module, the code relies on UID only, you just need to find a way to read it, all the rest will work fine
Can a rfid card be programmed to a remote control that can be pressed from a distance?
i don't know
I have tried this program use esp8266. But i wanted to ask to you , why the data have delay when sent data to google spreadsheet. I calculated this delay 6-10 second after tapping rfid card. What the solution for this prgram . Maybe it have delay only 1-2 seconds.. thank you
6-10 seconds is a great result for esp8266, you may consider to recode it for esp32 or keep favorite uids in ram
@@anyboards8006 how to keep its in ram?
can you make it again using esp32 ?
I adapted it to esp32, but no information is coming to esp32
why is my spreadsheets doesnt have scrip editor
me sale error "no matching function for call to 'LiquidCrystal_I2C::begin()'" que hago??
I suggest that you try the hd44780 library for I2C LCDs
Hello, currently i have a problem where it doesn't send my data to the spreadsheet. In the serial com, it says The requested URL /1.1 was not found on this server. what can i do to fix this?
Hi, it doesn't look like a reply from google, maybe a missprint somewhere?
@@anyboards8006 i did exactly the way you teach in the video. I just erase the liquid crystals part because im not using it. Is there a way for me to contact you and talk about this?
hello there i'm having a problem that says 'class BearSSL::WiFiClientSecure' has no member named 'verify
any help?
you can instal esp8266 board version 2.74. it doesn't works because you installed version 3.0
i have a problem "ReferenceError: emailAddress is not defined" this problem at google script . what need i do?
I know it has sendEmail function, but it is not used in code by default, if you want to use it add var emailAddress='youremail@example.com'; right after var logSpreadSheetId
actually i get the card uid when i add this command Serial.print(uid); everything seems alright but i cant get my card id entered into the spreadsheet.. like u do at 4:41 please help me..
i think its because this error .... when i paste my webapp link in a net tab i get this error from google = Script function not found: doGet
this funtion is not present in the code.gs
hi why it's not present ? line 12
@@anyboards8006 thanks alot for replying back me sir, I realised its my mistake in codes and I forgot to edit the comment I posted here... Sorry for that.. Your project helped me alott, thank you soooooooooo much! Expecting more videos with full support 😁
hello, what should I do if I want to add another variable in the data to be transmitted on the drive?
from esp to sheets or sheets to esp?
@@anyboards8006 esp to sheets. Tanks
esp side
String oneMoreVal="Pasqualino";
sendData("id=" + unitName + "&uid=" + uid+"&newvar="+oneMoreVal,NULL);
//but better use spritf to build the string
google side
find line "switch(param)"
and add
case 'newvar':
var newvar = value;
//newvar is ready to be used
break;
@@anyboards8006 hello, I did your suggestions to the letter, the oneMoreVal variable is not added to the sheet ....
@@Paco2584 Hi,
Yep, I expected you to manage it....
but nevermind, I uploaded a google code for you to github
the same link
Nice bro! But... WIN7???
Hello can you help me i got something problem
Cannot read property 'getActiveSheet' of null (baris 46, file "Kode")
var sheet=SpreadsheetApp.openById('some id').getActiveSheet();
try to get the speadsheet by providing its id
Hello,
I'm not sure if you're gonna respond or not but if you do, it'll really help. I keep getting this error
'class BearSSL::WiFiClientSecure' has no member named 'verify'
when trying to compile the code. Any idea how to fix it? If you need more info on the error, I'd be more than happy to email you if you don't mind.
Hi,
"BearSSL" ... it looks like you have some third party libraries
@@anyboards8006 I see. so how do I deal with this error? Do I have to reinstall my IDE and get proper libraries?
@@idragneeel3682 you can instal esp8266 board version 2.74. it doesn't works because you installed version 3.0
@@rizalilbas9480 I see. I'll give it a try. Will let you know if it works
@@rizalilbas9480 Hello, your suggestion has worked and I'm very grateful for it. thank you so much. may you have a wonderful life living forward.
Hi
I really like your project and would like bit of more help from you in regards to adding some more features to it. If you can help me then please let me know and I will pay for it. Thanks
Is it possible to read google sheet data from the Arduino side? Thanks
yep, you just need to use google app script
@@anyboards8006 Hi, thanks for the feedback.
Have you got any reading or script example to get me started? Thanks
this project has it, google replies with "access type" value from a cell
Is it possible to log IN and OUT entries ?
without changing code
you can make two terminals with name "in" and "out"
Hi , How can I set the time zone according to Turkey?
Hi, yeah time zone doesn't work as used to, now its like this
var timeZone="America/Winnipeg";
// get your time zone here developers.google.com/adwords/api/docs/appendix/codes-formats#timezone-ids
var dateTimeFormat="dd/MM/yyyy HH:mm:ss";
var dateTime=Utilities.formatDate(new Date(), timeZone, dateTimeFormat);
pls reply bro i have project expo
hello can u share the code for esp32 if we want to perform this experiment using esp32
at the moment, I have no ready to share code for esp32, but I will
@@anyboards8006 i need it as soon as possible,it would be a great favor if you provide within this week
@@anyboards8006 ESP32?
@@anyboards8006 please, you share the code for esp32?
hello i'm working on a project like this and i cannot link the name to each iud please help me
hello, so what's the exact problem?
I can’t link the card iud to the name of users
anyboards if i do read card i only have ‘who are you’instead of the real name
does a card uid appear in the log spreadsheet?
Yes it appear but i can’t link name to this
i can't rename "Who are you?" in program ,how to rename ?? please help me
GoogleScript.js 15th line
Can I get a circuit diagram for this sir?
58th second
I got error class bearssl::wifi client secure has no member named verfiy what can do i tried many times
old code, try to install esp8266 board version 2.74.
@@anyboards8006 how to install old version while using new version can u please help
everything works. but can't read data back from the sheet anyone know the solution?
solved
sir only date added in google sheet and in com port wrong parameter show
Hi, sounds like it can't read uid what do you see in com terminal?
@@anyboards8006 Dear sir is it possible that rfid and fingerprint sensor used with arduino by any one access led control means if card scan then led on and if finger scan then led on
With both Rfid and finger door lock work.
I tried it and it didn't work nothing was sended to Google sheet
Any idea
Please
open the links in a browser, if it works well something wrong with wifi or with spreadsheet id
@@anyboards8006
I tried and it didn't work
The led on rfid and nodemcu is not working
It just flashing at the moment I connect it with my computer for less than one second then its turning off .
I know I was a bother .
Please 😢
what happens when you open the links in a browser?
@@anyboards8006
It's open script.google
And the page all empty and just "undefined:undefined:undefined "
Was written
you didn't pass any parameter, it should look like yourlink?id=1&uid=111
can i replace rfid with fingerprint sensor ?
yep, doesn't matter what to send
can it works without lcd and buzzer?
yep It should work
Can you please make a video about using RFID and arduino and nodemcu to send data to Google sheet (student attendance system) I need it very soon .its for my graduation project...
Thank you
the project is exactly about the same
hello.. have u finished it? i got the same project .. can i contact you for assistance?
@@kyukyu1097
Actually no, I started again and used Arduino with the rfid and rtc and through it I get the card number and the time and then I send it to the nodemcu through the software serial and now I am trying to send the card number to the Google sheet but I face many problems
When I uploaded the program my nodemcu doesn't on its blue light
Any solution
no lcd backlight ?
@@anyboards8006 means
@@anyboards8006 no bro
I want to say that my nodemcu's blue light not on when I uploaded the program
I don't understand the problem
Can i get the esp32 version of this
yep, I will upload a new project soon
Please include the lcd library
how to set the timezone
developers.google.com/adwords/api/docs/appendix/codes-formats#timezone-ids
can i use nodeMCU esp8266
NodeMCU ESP8266 CP2102 Wifi IoT Lua AMICA
yep
@@anyboards8006 for the SSID and password what if the wifi is connected to a network that uses a proxy
sorry I don't know
C:\Users\sathwik reddy\OneDrive\Desktop\aurdino
fid
fid.ino:2:10: fatal error: MFRC522.h: No such file or directory
2 | #include
| ^~~~~~~~~~~
compilation terminated.
exit status 1
Compilation error: MFRC522.h: No such file or directory
i got error
nonsense
We ain't going to learn if we're just going to copy your code. 👎