Send Data from NodeMCU to Google Sheets
ฝัง
- เผยแพร่เมื่อ 16 ก.ค. 2020
- This video tutorial describes how to send sensor data from NodeMCU to Google Sheets without requirement of any third Party Software.
Complete project details here: bit.ly/3jifehB
Follow us on Instagram : / thatiotguy
Song: Marin Hoxha & Alexis Donn - Saving Me - วิทยาศาสตร์และเทคโนโลยี
![[E07] Sending sensor data to google sheet using esp32 | How to send sensor data to google sheet](http://i.ytimg.com/vi/RpQxJkEZ-fA/mqdefault.jpg)
![[TH]FFWS SEA Spring 2024 Knockout Stage - Day 14](http://i.ytimg.com/vi/CU9MV8EEIjE/mqdefault.jpg)
Haven't tried it yet, but the video is awesome, very descriptive, instructions are very clear and. Thanks !!
Super bro. Great self-explanatory video.♥
Great Job bro, this helped a lot!
It’s possible to adjust script so if any value is existing,for example a row have the same data, to now write. So no duplicates of data?
Thanks a lot! The work is clearly illustrated and greatly documented!
Thank you so much for this video. It's help me a lot🥰
Always welcome 🤗
Update 2021: The library works for 9 parameters as of now. It's published in the GitHub Repo.
Happy Working!!
Update 2020:
The library file I made is currently working for 4 parameters only, I will be generalizing it for 'n' no of parameters, thanks for all who tested and let me know, I will update in my GitHub once the work is done, meanwhile if you want to send > 4 parameters at once, feel free to try the other tutorial (2019) on my hackster.io/thatiotguy page.
In case you want it within this library, you can ping me over ig, I will provide you with a beta version of the library.
Thanks a lot clear information is here
Great work , i´m new and stuck, when I test I recieve "Script function not found: doGet" back, please help.
Hi, thank you so much for the video. It helps me a lot. I have followed all the steps but when test for the working URL the values aren't written successfully, how could it happen? Would you mind giving me a hand? Thanks.
Where do you get the trigger wifi library
Hi, I like the video very much. I am new to this kind of code and your video is very helpful. i am using your code to send GPS coordinate to the spreadsheet. after some modification, the code works. Now, I want to change the column of where the value will be posted to the spreadsheet, but after changing the value in the google script, it did not change at all. could you please tell me how to change the value to paste to another column ?
thanks
what's your board library version
thanks a lot bro
Hope you like the library file.
Thanks for your video~.... Some modifications were needed, but I checked it while testing it as I followed it. ..Thank you. It takes about 3 seconds to save the data to check, but I am curious if it is possible to save it faster.
how you solved the error of esp8266/Arduino CI has failed
,closing connection
,?
great job
How can I send String or text from esp8266 to spreadsheet
because right now I'm just limited to send float data
Many many thanks. Works fine after changed: void Google_Sheets_Init(char array_2d[10][24], String sheets_gas_id, int param_size); // from 6 to 24
and : char column_name[ ][24]={"x","y"};; // 6 to 24 And put the TriggerLibs as local tabs.
I don't want to have seconds in second raw. How can I reduce that? Tried a lot!!
My application is my Robot LawnMover. If it is out or in and how many minutes in or out. If I am not at home I can see if the mover has been out for long and I can call a neighbour to check the problem.
brother, may I request to measure wind speed and direction using a hall effect sensor with a method like this🙏🏻
amazing
How to Solve this error??:
cannot convert 'char (*)[20]' to 'char (*)[6]' for argument '1' to 'void Google_Sheets_Init(char (*)[6], String, int)'
Could u solve this issue?
@@user-ur7rt7go3z So I solved this, by changing the character number from 6 to 24 in the library file. And the specification in the Arduino file needs to be 24 also and no space or special characters as well. Hope this helps!
@@yanliu6914 how was the code ?, I'm having the same difficulty
@@yanliu6914 How to do that ??
You must modify the googhlesheets routine and put 20 too. I mean not only in the .ino file.
initializer fails to determine size of 'column_name_in_sheets'
How to solve it? Please help
Excellent project. Made it successfully. But in sheets it updates 6 values and then nothing. Any idea why? I restart the Arduino and then i start getting updates again. I need to get updates continuously. TIA
When using “Deploy as web app” what to do if permissions is set as “Only myself”
Hi, great job. I have followed the steps and it works perfectly. Just one doubt, the numerical data (float) is sent to the spreadsheet using the point as a decimal separator), is it possible to send it using a comma?
Yes, you can make it as string and send including comma, then separate the same in the script in case you want, else can be as it is.
@@thatiotguy8837 can you build a spreadsheet and wifi trigger library using esp32?
@@user-jl6cq6tq5l I have shown it in the above video, you can try it out yourself, if you encounter any error, I am happy to help.
@@thatiotguy8837 i have an error here, can you help mee?
will it work if i am using arduini uno instead of esp8266??????
no you need a wifi module
Can u show the connections of node MCU please
I don't have the schematics with me now, but it can be a generic connection depending on what sensor you are interfacing.
The tutorial is generalised to use any sensor with it, and it will work.
Thanks Bro. Worked for me very well. but I want to send data to only row everytime(overwrite the value) but couldn't get success
@@thatiotguy8837 Yes i worked around by using counta () function to get only the final value. thanks a lot brother
bro what to do for ESP32 ??
good job
how can i do this with an ir sensor plss
How do I resolve the below error ?
/Arduino/libraries/Nodemcu-to-Google-Sheets-master/TRIGGER_GOOGLESHEETS.h:156:14: error: 'class BearSSL::WiFiClientSecure' has no member named 'verify'
156 | if (client.verify(fingerprint, host))
| ^~~~~~
exit status 1
Error compiling for board NodeMCU 1.0 (ESP-12E Module).
did u solve it???
Have you solved this error
Same issue. Did you resolve it?
There seems to be backwards incompatibility with the new 3.0.0 update of the ESP8266. Go to Boards Manager, uninstall your current ESP8266 version and install an older version. Version 2.6.0 seems worked for me
How can I implement this using Arduino Uno and esp8266-01 WiFi module?
it worked for me finally but unable to add more columns....plz help
hi, great explanation. How can i get the data for every second? is it possible?
Yes, that is possible, just remove the delays used in the library file and other places.
Great Job, I uploaded the code without error but the parameters are not getting updated in sheet
When I update the values through the browser, it gets reflected.
please help me to send with out error code
how to fix :-
'class BearSSL::WiFiClientSecure' has no member named 'verify'
Did you solve this error?
It is very useful but it gives me error on code please help me to add trigger libreary
hi, i am really new to this. At the end, if i disconnect it from the PC and use a powerbank to power the NodeMCU, will it still automatically log the data on google sheets without the computer?
Yes
'class BearSSL::WiFiClientSecure' has no member named 'verify'.
Iam getting this error
Hi please I get this error, Script function not found: doGet please get back
Super bro
great work!!
Thanks
I got some error
in serial monitor can't connect google script plz any one help me i get tired to searching a error 😪
como puedo enviar y después borrar los datos recolectados de mi sensor en Google sheets cada 24 horas?
TRIGGER_WIFI.h: No such file or directory
. how to fix ? thanks you
Can you please help me to send coin mechanism data from Arduino to nodemcu and Excel sheets
Can you elaborate?
Great job, I need to send > 4 parameters, how I should do.
You can ping me on Instagram, I will share the library file with you over there.
@@thatiotguy8837 my ig is sugar_rush39, thank you very much.
@@user-tl7xx4dv9m File is Shared!! Let me know for any modifications required.
@@thatiotguy8837 The account is canceled, could you also pass me the library of +4 parameters?
@@erictapiagarcia4395 Sure, I have shared the same in Hackster with you, check and update me.
Hello.. im sorry i want to ask.. first i used ur tutorial for sending mlx reading data and it works.. thanks to u! now i want to send rfid uid tag.. i try but it won't work cause the serial monitor said the data from mfrc reader its in string mode.. and it cant be send cause the data we send is in float type
can u help me solve my problem? thanks before. :)
Yeah, currently the data to sheets accept the float values only.
You can modify it to pass a structure which will solve your issue.
i have changed the library
Thank You
You are welcome my dear friend!!
hey sir, this is a great project! I have a project fr my tesis bout this program, bt i have a problem with the script. it always said "Cannot read property 'parameter' of undefined". How can i solve this? i really need ur help sir>
Can you share the script you used?
Ahh same im doing a thesis about this🙌🏿
@@productivindoid9069 I would love to know, what are you actually doing, if you are allowed to tell.
@@thatiotguy8837 sure thing man, in short its just creating temp/humidity/pressure portable sensor which transmits data to thingspeak while also writing to sd card (in case net broke down)
@@productivindoid9069 It's cool.
Nice
No hope to get reply 😅. Bro awesome its working but i wanted to send characters/text how can i do that...?? Pls reply🙄
You can send them as a string, in firmware I am converting float to string, and then sending those.
D:\Arduino\libraries\TRIGGER_Library/TRIGGER_GOOGLESHEETS.h:95:3: error: 'client' was not declared in this scope
how to solve this error
any one with anonymous option not available
156 | if (client.verify(fingerprint, host))
| ^~~~~~
Using library Nodemcu-to-Google-Sheets-master in folder: C:\Users\Rabindra\Documents\Arduino\libraries\Nodemcu-to-Google-Sheets-master (legacy)
Using library ESP8266WiFi at version 1.0 in folder: C:\Users\Rabindra\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.2\libraries\ESP8266WiFi
exit status 1
Error compiling for board NodeMCU 1.0 (ESP-12E Module).
Hello... Excellent video.. I tried to to it but shows me an error in both sides.. can u help me?
Sure, let me know the error
your code get more errors send fullely compalied code link
Congratulations for the great work, with three values if I load data to sheets, but when I expand the parameters in the script as in the ino code, it does not load them, it remains blank. Could you give me a hand? Thanks a lot
Sure, I will let you know within an Hour.
@@thatiotguy8837 Thank you very much, I look forward to your answer. I don't know if it's the appscript or the ino code ... I still do it wrong ...
@@lindabotas1844 Please find the updated one in GitHub for the same in Pull request, verify for 9 parameters.
@@thatiotguy8837
I'm going to look at github and check that I can load 9 parameters to the spreadsheet.
Thank you very much for your help, whatever happens I answer you.
@@lindabotas1844 Yeah, do let me know if any issue is there. I don't have the board with me right now, I will try and still update.
Bro Your Video is very Nice. Thanks a lot for that. But i got this error when compiling the code in Arduino. Please help.
error: initializer-string for array of chars is too long [-fpermissive]
char column_name_in_sheets[ ][6] = {"value1","value2","value3"};
^
DHT11:5:63: error: initializer-string for array of chars is too long [-fpermissive]
DHT11:5:63: error: initializer-string for array of chars is too long [-fpermissive]
exit status 1
initializer-string for array of chars is too long [-fpermissive]
did you solve it ?
@@user-cv5rs5ek3v solved.
@@PSTalk can you explain how you solved it?
relpace "value1" by "a", b, c .... but I uploaded code to my ESP8266 and it didn't send data to Sheet... sad
hey can you tell how you solved this error i am getting the same one...
jey i was trying the arduino code but i can't seem to get the clientsecure library
You proper wifi library before using google sheets, or use trigger wifi, and make sure you read each and every line of comments.
@@thatiotguy8837 damm nice okay fixed it it works just fine now but it says esp8266/arduino CI failed how do i fix that ?
@@mistahchap6972 that's not required, it will work without that.
@@thatiotguy8837 okay thanks ur the best man keep it up
it doesn't print the values though
thnx
You are welcome.
How can i send integer data?
It has to be converted to equivalent string.
Por favor actilize el video ,
Can we send Google sheet data to esp8266
You can tweak it in Script of sheet, and have one api to fetch the data.
Getting Error: 'class BearSSL::WiFiClientSecure' has no member named 'verify'
Same
Stuck on client .verify(fingerprint, host)
do you solved it??
if I wanted to send data to six rows then would I just change the No_of_Parameters to 6? would I have to do anything else?
Yes, and in script also you should have atleast that many no of parameters included.
its a Great job while working with same project we face an error "send_data_n_Gge_sheet:7:35: fatal error: TRIGGER_GOOGLESHEETS.h: No such file or directory
#include "TRIGGER_GOOGLESHEETS.h"
^
compilation terminated.
exit status 1
TRIGGER_GOOGLESHEETS.h: No such file or directory
"
can you plz Guide me ho to resolve this issue
Thank you
did you solve ? my error is: "TRIGGER_WIFI: NO SUCH A FILE OR DIRECTORY"
Would love for this to work, but none of the data is transmitted to the google sheet. This is my serial monitor result:
................
WiFi connected
IP address:
10.0.0.152
column_name= value1
column_name= value2
column_name= value3⸮
Sensor Values : 1.00
Sensor Values : 2.00
Sensor Values : 3.00
connecting to script.google.com
certificate matches
requesting URL: /macros/s/myGAS ID is in here correctly/exec?value1=1.00&value2=2.00&value3⸮=3.00
request sent
headers received
esp8266/Arduino CI has failed
reply was:
==========
284
==========
closing connection
What is a reply of 284 and what does the "esp8266/Arduino CI has failed" actually telling me to change?
It should work, check around the sheets you used.
sir please sent us arduino code
Sensor Values : 1.00
Sensor Values : 2.00
Sensor Values : 3.00
connecting to script.google.com
certificate matches
requesting URL: /macros/s/AKfycbzj9g9U6iOjmGoBgrVzGmRuGSmYzlHIi55YY3Amz21n/exec?value1=1.00&value2=2.00&value3=3.00
request sent
headers received
esp8266/Arduino CI has failed
reply was:
==========
236
==========
closing connection
INFORMANDO
PORÉM NÃO ATUALIZA NA PLANILHA GOOGLE SHEET, AJUDA .
How would we change the code if we were using an arduino with wifi built in such as arduio Uno wifi rev2 or arduino yun?
You can remove Trigger WiFi Library, and include WiFi Library for your board, just the NOTE is you need to declare WiFi Client Secure before including Trigger_GoogleSheets library cause GoogleSheets Library uses WiFi Client Secure, so just make sure whatever you do, define wifi client secure before using google sheets library.
For more details, comments are there in the provided code.
I got so many Errors following up on the instruction here.
1. Testing the Deploy link: Sorry, unable to open the file at present.
2. Compiling the Arduino codes: cannot convert 'char (*)[20]' to 'char (*)[6]'
I tried a few ways of course the solve the issues but surprisingly I could not.
I can not go further while issues need to be solved. Can you please help?
Getting this error at line 5.
initializer-string for array of chars is too long [-fpermissive]
char column_name_in_sheets[ ][6] = {"value1","value2","value3"}; This line.
Risolto devi trasformare value1 value2 e valute 3 in va1 va2 va3
You also need to change in the lib "TRIGGER_GOOGLESHEETS.h"
When I changed the variable in the programation, I need to change too in the web code, in google sheets.
@@thalesfelipe5928 Tales campeão, você conseguiu fazer funcionar ? Estou com o mesmo problema do rapaz e não consigo achar solução, por favor se puder me ajudar eu ficaria agradecido.
@@gepeto1075 tenta mudar o código igual Davide falou: em vez de colocar value1, value2... Coloque va1... No arduíno e no editor de script. Dps de mudar no editor de script, precisa salvar e enviar o app para web novamente. Tenta e dps me responde
i can not belive i can't make it work... =(
it works. but i do not know why i can not visualize in real time... unless i go to drive and open the sheet again
@@fireworm91 that should not happen, make sure you have proper internet connection, and try making a fresh sheet if it doesn't work.
@@thatiotguy8837 yes. I have read about it. Other users have similar problems.
They made new gaccounts and it solved the problem
PERO EXPLICA MINIMO...
Ese wey
I need some urgent help, where can I contact you?
It´s a problem when I try to implement 9 parameters to send.
Pls I need it urgently, I already tried contacting you via Instagram
Datora.nope
Nit.naudu.
Great work , i´m new and stuck, when I test I recieve "Script function not found: doGet" back, please help.
PRIMEIRO VOCÊ SALVA PARA DEPOIS PUBLICAR, ISSO IRÁ FUNCIONAR