ESP32: Sending data directly to Google Sheets, sending alarm emails

[@jaredhanson9758]

Great job. Working for me on 10/Aug/2023!

[@Parker-hn2ry]

Has anyone recently (5.26.2022) had any success with this tutorial? I still have made no progress with getting the code to show up on google sheets. Not sure if this has to do with the recent update to google sheets as Ganesh G also recently mentioned. The ESP code works great and attempts to communicate with the google sheet but no data is transferred. Other than this current challenge, this tutorial was very well done and helpful. Any assistance or suggestions in resolving this challenge would be greatly appreciated.

[@TUECAAKASHKH]

how do u solve this bro @parker

[@ganeshg1870]

14.05.2022 tested the above not working, there are some changes in new googlesheets.

[@Parker-hn2ry]

Great and easy to follow tutorial but I too am having troubles with getting the data to show up on google sheets and am not sure if it has to do with new update. I also noticed that there has been a change in google sheets and was able to find App script under the extensions tab. The tutorial from there seemed to work well with deploying/authorizing the web app and then loading the Arduino code. Serial monitor displays the data attempting to send but it is not received in the google sheet. Any help on this would be greatly appreciated. - 5.22.22

[@ganeshg1870]

@@Parker-hn2ry yeah Please let me know if you resolved the issue or find the alternative way to send the data to googlesheets.

[@zxn_wsnr]

Hi! So this code seems to work perfectly for most of the times. But sometimes I notice that ESP32 is unable to send request to the web. Like by debugging from serial output I figured that it is successfully creating query link, but apparently it fails to put that request into web. What can be wrong here??

[@dspsystem00]

I have the same problem. Can anybody give us a idea?

[@miklab9097]

Great Work! Only problem is the time in the google spreadsheet is incorrect. How can I get it right to UTC Offset: UTC +8? thanks SOLVED: I changed the timezone to "Asia/Jakarta" from "CST"

[@MuhammadIlham-iq3zf]

I have connect with ifttt and send data automatically to Google sheet,but now I don't know how to make the notification?any help,please?

[@futuretechnologyforindustr4441]

the sheet did not receive any data so how we can solve that we should update the certificate what we can do pls advice

[@anyboards8006]

Hi, I've just updated the certificate, everything works fine now, thank you!

[@plusmartini]

Every time I publish to my sheet, it takes about 7 or 8 seconds to appear and the response I get takes the same and it is 302 (shouldnt it be 200?) How can I make the publishing take shorter time? id love to publish every 500ms or 1000ms. thanks

[@christopherlawes9286]

SpreadsheetApp.flush() in GAS

[@anyboards8006]

Hi, it wont be faster than ~ 5seconds, yep 302 is ok for google sheets, you may send a batch or records to speed it up, 60-120 records in one request every minute

[@조기연-r7t]

Nice Nice so good good good. I tried so many other people methods . but I failed. now I succeed because of this tutorial. Thanks anyboards!

[@anyboards8006]

awesome, Thank you!

[@fernwachannel651]

You're very good. 👍🏾

[@AlainSamoun]

Looking at the output of the program on the monitor, I see that the first four lines of the certificate are missing. Maybe because not enough memory when declaring root_ca? What do you think and what could be the solution? Thanks

[@anyboards8006]

esp doesn't really use it, did you you try with requestcatcher ?

[@AlainSamoun]

@@anyboards8006 Yes I tryed the requestcatcher but I do not see anything exept that "No requests has been received yet"...

[@anyboards8006]

it can't connect... try default https example and in arduino ide esp32 version 1.04

[@obebharoigoni7449]

Mine is not working. How did you setup your router Network? I believe my problem is from there.

[@AlainSamoun]

@anyboard Any news from the catcher site?

[@zouhirmsahli1694]

Hi is this procedure available even when the spreadsheet is not in use ?

[@uniqueautomationelectronic5696]

Sound please

[@AlainSamoun]

It seems that this movie has no sound?

[@anyboards8006]

yea, some boring tutorial with comments

[@AlainSamoun]

@@anyboards8006 OK I understand,it's a silence ovie ;-)

[@christopherlawes9286]

Wow! I am a complete programming/ESP32 noob and I got this to work. I can't believe it. What is the certificate bit about? Do I need it? Next milestone: connect temp sensor and send real data.

[@anyboards8006]

hi, do not care about certificates it just works

[@victorhugosantiago8550]

When I put a call for sendData in other method, my sheet is not updated. Do you know how can I fix it?

[@anyboards8006]

hi, please share this part of you code

[@fernwachannel651]

Do I have to put the URL in Code esp? In order to send data to Google sheet, what part of google sheet must be set?

[@anyboards8006]

not url, but script id

[@Build_the_Future]

How would you read the info in a cell and display it on the serial port on the Arduino?

[@anyboards8006]

Hi, ----Google script part-----> function doGet(e) { var cellVal=SpreadsheetApp.getActiveSheet().getRange(1,1).getValue(); return ContentService.createTextOutput(cellVal); } then publish and get the link HTTPClient http; const char *location = "Location"; http.begin(YOUR_Link, root_ca); const char *headerKeys[] = {location}; http.collectHeaders(headerKeys, 1); int code = http.GET(); Serial.printf("code %d ", code); if (code == 302) { String newUrl = http.header(location); http.end(); Serial.println(newUrl); http.begin(newUrl, root_ca); code = http.GET(); Serial.printf("status code %d ", code); String cellVal=http.getString(); Serial.println(cellVal); }

[@Build_the_Future]

@@anyboards8006 Thanks I'll try it out

[@roadsandtransportation2517]

Can we send Google sheets data to esp8266

[@charlesk7623]

Thanks for the video! Will the GOOGLE_SCRIPT_ID link or root certificate expire?

[@anyboards8006]

certificate...I don't think so, esp doesn't really use it

[@anyboards8006]

SCRIPT_ID will not too, but authentication is valid for ~6 months or a year

[@charlesk7623]

@@anyboards8006 what is authentication? Thank you for the reply

[@anyboards8006]

when you publish your script first time a pop up with permissions appears, the same will happen in a year or 6 months

[@charlesk7623]

@@anyboards8006 I looked into what you said. this link: security.google.com/settings/security/permissions lets you manage permissions. I will make sure to delete and add permissions regularly. script ID does not change when renewing permissions.

[@douglas09010]

Sadly didn't worked for me. Can someone help to get this to work?

[@christopherlawes9286]

Where did it fail?

[@anyboards8006]

Hi, Tell us more

[@waihan6772]

I'm getting this error, can you help me? 'WiFiClientSecure' does not name a type

[@anyboards8006]

Hi it's a default lib Arduino core for the ESP32 v1.0.4 was used, try to reinstall it. and Did you select dev32 module ?

[@diegostefanello]

Great job. Tks

[@yonanprihhapso1572]

Hi, this is a very good video. The codes work on DOIT ESP32 DEVKIT v1, I also managed to connect DHT22 with temperature readings. However, I have a problem with the time zone. It keeps showing UTC time instead of the time zone entered in the code. Do you have any suggestions?

[@anyboards8006]

hi, if you "CST" does it keep showing utc time ?

[@yonanprihhapso1572]

@@anyboards8006 yes, it keeps giving UTC time regardless the time zone entered.

[@anyboards8006]

@ yonan prihhapso yes, the same for me, but it used to work, now your time zone should look like 'America/New_York' the list is here developers.google.com/adwords/api/docs/appendix/codes-formats#timezone-ids Thank you!

[@nhm7530]

How did you get your certificate?

[@anyboards8006]

esp doesn't use it, you don't need to change or update it. but if you want you may load a page in firefox and click on a padlock in firefox's address bar

[@nhm7530]

@@anyboards8006 Out of curiosity do you think this method will work with getting events from google calendar, I have been trying all sorts of examples to get something from google calendar on the ESP32 and none have worked. I tried your code to mess with google sheets and this code did work, I was using your code because it was also the only google sheets code that worked for me (and showed me I could communicate with google scripts). Would you be willing to make a video on how to get google calendar events using this method? (also thanks for the quick reply)

[@anyboards8006]

Hi, I've never used google calendar, but you'll have no problem to do it, just use these methods developers.google.com/apps-script/reference/calendar in the same spreadsheet project (in doGet function)

[@davideiotti9725]

Very helpful

[@MathematikO0]

Is it possible to add the seconds? Thx for this video!

[@anyboards8006]

yep, use ss ~ "dd/MM/yyyy HH:mm:ss";

[@MathematikO0]

@@anyboards8006 Thx! :D

[@faisalalquaied6458]

The message that i have received from the get function is (302) which is redirect message. Do you have any solution ?

[@anyboards8006]

302 is ok, thats how google api works

[@faisalalquaied6458]

@@anyboards8006 that's weird because i couldn't find any update in the excel sheet

[@anyboards8006]

try to open the link in browser

[@faisalalquaied6458]

@@anyboards8006 it opens and everything is good in the browser only:(

[@anyboards8006]

maybe you have some special characters? you cant pass data like 33% without decoding it

[@MIbtehajNasar]

can you please make a video on how to receive data from goolge sheets to esp32?

[@anyboards8006]

Hi, yeah I will try

[@christopherlawes9286]

anyboards any progress on this? Would be great to control ESP32 with google web app

[@diegostefanello]

Great ideia.

[@aqib4235]

It returns -1 httpCode for me. I did everything like you showed. I don't know where i am going wrong. It returns OK if i make the same request from the browser.

[@anyboards8006]

this is a connection error try requestcatcher.com get an url there, for example if you get aqib.requestcatcher.com add a line url="aqib.requestcatcher.com"; right after String url=".... in sendData function if no request appears in the catcher something wrong with your internet

[@aqib4235]

@@anyboards8006 Thanks for the quick reply. No requests appear in the catcher. I even tried with a different internet connection. Still nothing. If I try the original code, it does not print response (line 102) in the serial monitor. It only prints response if i write the "Serial.println(": done "+httpCode);" above "http.end()"

[@anyboards8006]

try something not https, example http.begin("api.ipify.org/?format=json");

[@cej1988]

can you share links for the libraries you use plesase? thx

[@anyboards8006]

hi, no third-party libraries were used

[@cej1988]

anyboards hi, thanks for the answer but my IDE wifi.h and httpclient.h cant find. where it should has been located?

[@stlo0309]

besten dank!

[@ชัยว้ฒน์ผุยหัวโทน]

How to configuration Time Zone

[@anyboards8006]

var timeZone = "CST"; replase cst with your time zone

[@markjohnson7510]

you can find your timezone by using the search box at the top right of this page and searching for the biggest city that you know is in your timezone. www.timeanddate.com/time/zones/ (which is linked in the google script that anyboards has on that line of code) you could also just scroll through that page and look for the code in the first column that corresponds to your locale as described in the second column

[@chenchingwei471]

Or you can try to change var timeZone = "CST" to var timeZone = "Asia/Kuala_Lumpur" by referring the Timezone IDs developers.google.com/adwords/api/docs/appendix/codes-formats#timezone-ids It works for me

[@nanduchandran5056]

Changing the timezone didn't work on the example code. So created a new spreadsheet and a new script with the new timezone. That did work for me

[@christopherlawes9286]

How to send temp data please?

[@anyboards8006]

temperature? there are tons of different temperature sensors you need to combine the code with a working sketch of your sensor.

[@christopherlawes9286]

@@anyboards8006 I thought ESP32 came with built-in temp sensor?

[@anyboards8006]

It used to, modern esp32 chips don't have this feature

[@alirizalchaidir9171]

terimakasih, sangat membantu

[@markjohnson7510]

If someone could help, I would appreciate it much! I have been working on this all day. I've tried it in the Arduino IDE, and i don't get any errors there, but it also doesn't work. it prints: Connecting.Ready to go script.google.com/macros/s//exec?info1=somedata&info2=56.00&temp=14.00Making a request script.google.com/macros/s//exec?info1=somedata&info2=40.00&temp=1.00Making a request When I load up the code from platformIO ide and watch the monitor there, it prints this Connecting.Ready to go script.google.com/macros/s//exec?info1=somedata&info2=36.00&temp=12.00Making a request[E][WiFiGeneric.cpp:654] hostByName(): DNS Failed for script.google.com [E][WiFiClientSecure.cpp:132] connect(): start_ssl_client: -1 script.google.com/macros/s//exec?info1=somedata&info2=24.00&temp=0.00Making a request[E][WiFiGeneric.cpp:654] hostByName(): DNS Failed for script.google.com [E][WiFiClientSecure.cpp:132] connect(): start_ssl_client: -1 I'm thinking maybe the certificate has expired?

[@markjohnson7510]

I tried a previous suggestion of putting String url = "aqsadfib.requestcatcher.com"; in at line 81 or so, right after String url="script.google.com/macros/s/"+GOOGLE_SCRIPT_ID+"/exec?"+params; which I commented out Currently printing Connecting.Ready to go aqsadfib.requestcatcher.comMaking a request[E][WiFiGeneric.cpp:654] hostByName(): DNS Failed for aqsadfib.requestcatcher.com [E][WiFiClientSecure.cpp:132] connect(): start_ssl_client: -1 trying the same in python, it's clear that it isn't strictly an internet connection issue url = "aqsadfib.requestcatcher.com/test" a = requests.get(url) I also tried the suggestion of using http instead of script.google... which prints this but no requests caught at requests catcher Connecting.Ready to go aqsadfib.requestcatcher.comMaking a request aqsadfib.requestcatcher.comMaking a request

[@markjohnson7510]

Another detail, today after testing that I can ping my router with the esp32, I decided to try a publicly accessible IP address, that worked - so I think I can at least say that it is a DNS issue. Still, no idea how to get DNS working or whether that is the full extent of the remaining issue

[@anyboards8006]

@Mark Johnson Hi, a subscriber had the same issue, he had some old version you should use v1.0.4 of esp32 arduino if no luck try to upload a ssl sketch from examples, Does it work ? Thank you!

[@fernwachannel651]

I'm sorry for asking you repeatedly. I'm worry. I don't really know. 😥

[@practicalofanything218]

This does not work at all

[@anyboards8006]

please, share more details

[@practicalofanything218]

@@anyboards8006 the serial monitor shows that at "making a request". it keeps on looping. no data is shown on the spreadsheet. i did evertything as explained. please help.i am doing an academic project and sofar im stuck. i used ifttt to post values succeffully to the spreadsheets but ifttt, for some odd reason only sends three values,whilst i have to send about 6. I have tried sending directly using similar methods like yours but my ESP32 just does not seem to be connecting to the servers. any help is much appreaciated thanks

[@anyboards8006]

before "making a request" it should print a link like script....../exec?..... try to open it by any browser if no luck please share the rest of the link (after exec or the entire link)

[@practicalofanything218]

When I paste the link in the browser. Data flies in the sheet but for some reason it's not doing it automatically

[@anyboards8006]

without encoding you can pass only digits and letters in the link, things like .,% etc should be encoded (& is ok) Maybe this is the problem? Also try to connect to any other website add a line url="requestcatcher.com" before "making a request" , it should print code 200, (with google script link it should print 302)

[@AlainSamoun]

After following directions, I get an error when opening the web site: Script function not found: doGet What did i do wrong?

[@AlainSamoun]

I chaned the board to Dev32 but now I have another error: TypeError: Cannot read property 'replace' of undefined (line 80, file "Code")

[@anyboards8006]

probably you're loading url with no params

[@AlainSamoun]

@@anyboards8006 This is what the sketch send (first line): /exec?info1=somedata&info2=3.00&temp=1.00Making a requestp.... So it 's sending parameters I believe? If I copy the all line from the monitor,and send it from the web address it populates the sheet. Note that I did not change your original .js file - except the ID .

[@anyboards8006]

I don't really remember all details, it was more than a year ago, but I can't see any id in the js. file, it works fine with a real browser(chrome, firefox etc) but not with your esp?

[@AlainSamoun]

@@anyboards8006 Yes, my writting mistake, the ID is in the arduino sketch of course. My esp is a wroom32 if that makes any difference, but the error seems to come from the stripQuotes function. Any ides why?