ESP8266 Sending Data to Google Spreadsheet w/o Using Any 3rd Party Tool

hey hello iod magicians hope you are able to see my screen and today we are here for another interesting project where we will understand how we can send data or send any information from our esp8266 to google spreadsheet that also without using any third party tool yes magicians so this is this this is going to be very interesting project and this will be helpful for you in your future projects where you need to send any information or you need to live update the google spreadsheet from esp8266 so the the title is esp8266 sending data to google spreadsheet without using any third party tool and i am going to explain this in uh nine page pages of this document i have mentioned all the steps and everything with proper screenshot in this document if you need this document and if you need the code that i have used in this project let me know in the comment section of this video or ask me in my facebook group or in my whatsapp my number is already there in the description of this video so magicians before starting this projects let me show you what will be the result of this what will be the outcome of this so that you will have an idea what actually we are going to achieve it so for that let me first open the code that i have here in front of you which i will upload to my esp8266 and i have my google spreadsheet on the right hand side you are seeing that i have google spreadsheet here with four headers date time value zero value one and value 2 and you are seeing that currently there is no data in the google spreadsheet and on the left hand side the code which you are seeing this is the code written on written for esp8266 which will send some data to the google spreadsheet every after 15 seconds so before that i need to upload it so let me click on upload button so that it will uploads to my esp8266 so you are seeing that the upload will start soon it is still compiling the sketch once the compilation is done you can see the upload process yes now it has started uploading so now the code is getting uploaded in my esp8266 and on right hand side you are seeing that google spreadsheet with five headers date time value zero value one and value two currently there are no values no data in the spreadsheet and we are expecting the data to come from esp8266 the upload is done and let me open the senior monitor so you are seeing that on serial monitor it has connected to the wi-fi and it is trying to publish a data voila so you are seeing that my esp has successfully published some data with the value is 1 8 42 and 5 9 3 9 8 and the same value you are seeing here under value 0 value 1 and value 2. in addition to these three values it posts date and time as well so again you are seeing that after 15 seconds esp has published another data with the value 2729 and 436 68. so you are seeing that this is the current data that has been uploaded so again you are seeing that after 15 second one more row has been inserted in my google spreadsheet so you are seeing that esp is sending data which is automatically getting refreshed or updated in my google spreadsheet and at the same time on the serial monitor also you are seeing the successful message after every publish so like this i have four data being refreshed in my google spreadsheet so this is the outcome and this is the results that we are going to achieve in today's session hope this will be interesting for you and you will learn something new and you can make this project useful for your some of your future projects so okay magicians let me close this serial monitor and i don't want my esp to continuously send the data to my google spreadsheet so i just need to upload a blank sketch so i am i have opened a blank sketch and i am uploading this blank sketch to my esp so that the current upload program will be overwritten by this blank sketch and esp will not send any data to the google spreadsheet because i don't want my google spreadsheet to be flooded with the data every 15 second okay so now this has been uploaded i can easily close this and let me minimize this and let me minimize this as let me close this also okay so this is the document magicians which i am going to explain you in detail where we will understand how we send data from esp8266 to our google spreadsheet aim our aim is sending data from esp8266 to google spreadsheet every after 15 seconds without using any third party tool again magicians if you need this document or if you need the code which i have used in this project if you need those let me know in the comment section or ask me in my facebook group or in my whatsapp group i can provide you and i can help you if you face any issues or any challenges it's our approach so this approach is actually divided into below steps three there are three steps we will going to perform in order to do this project the step number one will be we need to create a google spreadsheet yes first and foremost we will create a google spreadsheet where we need to post our data so this will be the step number one and after step number after step number one the next step is creating script for the spreadsheet yes once we have created a spreadsheet next step will be creating the script for that we need to create the google script that will do all the magic for us this script is more or less like the macro we write for any micro microsoft excel worksheet so the script that we will write it it is more or less like a macros that we write generally right for our microsoft excel worksheet in that script or the macro will actually responsible will be responsible to receive data from esp8266 it will it can perform any desired action before inserting and finally the script will insert that data into our google spreadsheet so as you can see this google script will be the main script which is responsible for writing data into our google spreadsheet yes and the last step will be creating the ino code and finally we need to create the esp code which will call the above google script every after 15 seconds yes of course the script which we have created in the step number two our pi our ino code which we will upload in esp that i know code will call our google script every after 15 second with the desired data the data that we want to upload that need to be inserted yes so in this case i am just uploading any random data for values value 0 value 1 and value 2 but yes you can use to upload any data and any number of values whatever you want so in this case i am just uploading 3 values value 0 value 1 and value 2 these are just random values which i am using but you can use sensor data as well let's follow the below steps to execute this project so so all the magicians hope you are clear with the three steps that we are going to achieve so one by one we will going to see how do we achieve this so the step number one will be creating google spreadsheet yes so for that we need to just follow our this document please follow the below steps to create the needed google spreadsheet you need to click on this this is the link to open the google spreadsheet dashboard and if you ask for the sign in then you have to use your gmail credentials to sign in okay let me get this url and let me open this url here on the if you will uh use url it will directly redirect you to the spreadsheet dashboard something like this you are seeing here and first let me delete which any of the earlier sheets which i have i am deleting this sheet so that i can create a new sheet okay so i am not having any other any other sheet now so you're seeing that i have opened the google spreadsheet dashboard if you ask for the sign in you just sign in with your google and then next step will be you need to click on blank to create a new spreadsheet like this you have to click on this plus sign or this blank here to create a new spreadsheet you will go here you will click on this to open a newspaper this will open a spreadsheet something like this so next you follow the step number three this will open up a blank google worksheet you need to give it any name so let's say we are giving it a name as esp82 receiver at the top see below so at the top here you can give it a name called esp8266 receiver so i will use the same name so let me copy this name and i will go to the spreadsheet here so at the top where you are seeing untitled spreadsheet so you you need to just click here and you need to give a name esp8266 receiver you just need to click whenever seeing that i have created spreadsheet with the name esp8266 receiver so now let's follow the document since we need to know the spreadsheet ide for future use in our future uh for future use we need this spreadsheet id so where do we get this spreadsheet id yes you just need to copy the spreadsheet url and note down the spreadsheet id actually the spreadsheet id is already there in the url of your spreadsheet which is which lies between d slash and between d slash and edit slash edit so between these two words our spreadsheet id is located so you see here this is the url of our spreadsheet and within this url uh here the yellow marked portion is our spreadsheet id so let me uh show you here so here you are seeing the url of our spreadsheet and inside this you can see i already have this as my spreadsheet id so you need to copy this you need to copy this and keep it safe let me keep it here in my notepad so i'm just keeping the notepad so that i will use the extracted id in in my one of my script okay so let's follow that so once you have copied your script uh your spreadsheet id you need to keep this safe as we need it for our script so next step is now let's create headers on this so we need to provide headers to our spreadsheet so let's provide like date time value 0 value 1 and value 2 as headings okay so let me go to the spreadsheet so here easily you can provide headers here so first standard is date and it is time then is value zero value one value two since i am passing these three values from our esp so here i have used three columns c d and e with the head headings as value zero value one and value two and now these two headers date and time these two values will be populated from our script we will populate it from script and these three values we will populate from our esp okay so let's follow that so note you are seeing a note here the good thing is you don't need to manually save this spreadsheet google saves it automatically for you since we have uh created a couple of headers here like day time value 0 value 1 and value 2 and you don't need to save this on manually because here we are seeing that it already stores or already saves automatically every second so our script is our spreadsheet is already saved in cloud let's follow this that's it our template spreadsheet is ready we will receive data in this sheet from esp8266 now let's move to the second step malicious saw that how easy it was for creating a spreadsheet google spreadsheet with five headers so that's it for step number one okay now let's move the step number two so remember our step number two was creating the script for our spreadsheet okay so the step number two is creating a script for our spreadsheet now is the time to create a google script which is the backbone of this project since i already informed you this script will be the backbone script which you can think of this script as a macro which we usually write for our microsoft actual worksheets so for that you need to follow below steps this script will be the backbone because this will be the response this will this script will be responsible for getting the data from esp2266 pre-process it and finally insert that data to our spreadsheet so all these activities will be performed inside this google script so how do we create it so for that you need to follow the steps step number one click on extensions and then apps script okay so go here you are seeing that we have menu so on under that extensions you need to click on apps script so you need to go here uh from extensions menu you need to click on apps script so you need to click on this so when you will click this let's follow our document step two this will open another browser tab with blank code.js and untitled project see below so when you will click on this so you will see a new tab should open and here you will see some code.js and untitled project will open so let me show you here so here yes so our untitled project has been opened code.js has been opened with some default code here just a function name is there so currently this script is not having any code we have to write some code here which will become our main code for this google script okay that's for our document so step number three this is the place where we need to write the necessary code which will take care of receiving data from esp and pre-process it and finally insert it into spreadsheets so this script this google script is the main script for that so now what you do have to do so first you delete all the default code whatever is there and you have to copy paste our code google script image sample dot gs so this is the script that you have to copy and paste in your google script so where will you get this script same code yes of course i will provide you this google script code ask me in the description of this video or connect me to my facebook or in my whatsapp i will provide you this sample google script code and you just need to copy and paste it over there my whatsapp number is already mentioned in the description of this video so go there and ask me for the sample code so what do you need to do first you need to delete it delete the default code and then copy paste our code so let me go here so first i will delete entire code and i will copy and paste my code i will open the code here for you so my code is where is my code so okay let me search my code okay so here magicians this is the code google scripts imx sample.gs which you are seeing on the screen so you need to just open this code entire code copy this and you just after copy you can close this not required i have copied and i'm closing it and then you can go here and paste it here so you have copied and pasted our code here on google scripts okay let's follow the document so once you have copy and paste it and you need to then you need to provide your spreadsheet id as noted above remember you had uh noted down your spreadsheet id so now is the time you have to provide the spreadsheet id inside this google script code so there is the right line number two where you need to provide your spreadsheet id and then after that you need to press ctrl s to save this google script code okay so i will go here so in in this google script code you are seeing that online number three here is the place where you have to use your spreadsheet id so that spreadsheet id should be inside uh single quotes so first you will delete this and you will paste the expression id which you had just copied so i will go here and i will paste it here so you are seeing that i have pasted my spreadsheet id here and then after that you just need to press ctrl x so magicians that's it this is the google script is ready for you no other changes you need to do inside this google script if you if you are interested seeing this script what all we have done so you here you can see that i am publishing uh on the column number zero is the date column number one this is the time column where i am uploading the time and then i am uploading value zero value 1 and value 2. so all these values are all these three values value 0 value 1 and value 2 these three values are directly coming from my esp and this date time and time i have calculated here you are seeing here but all these three values are coming from esp8266 so i am not going into detail about this script over here but if you have any confusion or any issue understanding this script let me know i will explain you in detail in in person but yeah now for now you don't need to do any other changes other than giving the spreadsheet id over here okay magician so once you have copy and pasted your spreadsheet id you can safely follow the next step which is now let's give this project a name currently you are seeing that your project is project name is untitled project you are seeing here so let's give it a name called esp8266 receiver script let me call uh this project name as this at the top left corner instead of untitled projects i will copy this here you are seeing that it is written untitled project you will click here and it will ask you to give a new name for this you will then give any name i am giving it esp820 receiver script and then just create click on rename so as soon as you click on rename your project renamed to this okay magician so far so good we have created a google script which is responsible for getting values from our esp8266 and pre-process it and finally it will upload or it will send that data to our spreadsheet finally it will look like below yes our script is looking like this and now is the time to deploy our google script so for that step number six is now from the top right you need to click on deploy and then new deployment let me show you so once you have renamed your trip now is the time to deploy it so here on the right you are seeing a deploy button you will click on this you are seeing a new deployment here you just need to click on this new deployment so let's follow the document so this will open a new deployment window yes it has opened and you have to select the gear icon and from there you need to select the web app okay let me go here so here you are seeing that a gear icon you will click from there you need to select on web app because i am deploying my script as a web application that's why i have to select the web application i will click on this yes so when you will click on the web app so it will ask for the description some other options so what are all these what values we have to provide let's follow the document now for description you need to provide any description whatever you want to provide so i am providing esp8266 receiver script so this is the description which i am providing and execute as for execute as you have to provide as me and who has access you have to provide us anyone so all these three values you have to provide and finally you have to click deploy see below as you are seeing here on the screenshot for description you are providing esp8266 receiver script for execute as you're seeing for this you have to provide me and you can see your gmail id will be automatically there populated there and who has access for this you have to provide any one and then you have to just click on deploy okay so let's do that so here for description i will provide a description called esp8266 with the word script or any description which you want web app execute as here you have to you have to select me and who has access you can see a couple of options here one only myself anyone with google account anyone so you have to select anyone here guys magicians remember do not select anyone with google account you must select anyone here okay so this is the thing you have to you have to understand that who has access anyone and then you have to click on deploy okay magician so far so good so we are good for deployment and i hope this will be deployed successfully it just takes some time to deploy okay so once the deployment is done you are seeing a screen um it is asking for the authorized access so what do we do let's follow the document so you are seeing the magicians how easy this document is i have mentioned each and every step very clearly with proper screenshots so that it will be very helpful for you so if you need this document if you need all the code that i have used here please let me know another description or of this video you can ask comment section of this video or ask me in the facebook or in my whatsapp already my number is there with you in the description of this video so then it will ask for the authorization you need to click on authorize access like this you just need to click on this let me click on this so when you will click on this it will open and it will ask to choose an account so you have to choose an account using which you want to give the authorization access so what do we do let's follow here this will open the sign in screen yes you need to choose your google account like this and and then it will show you up your warning window you just need to okay let's first click on this the account which i want to use for authorization this is the account and now here you will see the screen where you will see that google has not verified this app yet so what do we do just follow the document so it will show up a warning window like this you need to click on advanced see below so here you will see a button called a link called advanced you have to click on this ad once so here you are seeing that link is there you will click on that once then it will open some another information here then what do we do and then you need to click on go to esp8266 receiver script and save see below like you are seeing that it will open another link like it is asking you whether you really want to go go for that script you need to say you need to just click on this which is unsafe but yeah that's okay for us to move ahead so here you will click on go to esp script and if so now it will it is asking that esp8266 receiver script wants access to your google account so here you have option cancel or allow definitely we are not going to cancel it we will allow it so let's follow the document what it says yes it will ask for the permission you need to just click on allow like this so here you will see you need to click on allow do not click on cancel otherwise your script will not be called you cannot call your script using your google account so you can click on allow so as soon as you will click on allow you should be able to see the successful deployment of your project so what you will see finally the deployment will be done and you will see the deployment id on the screen uh with a success message so let's let's see that screen so here you are seeing the screen after deployment the deployment successfully updated and the deployment id is this so remember again you need to copy this deployment id either you copy from here or you just click on the copy because this deployment id will be useful we will use this deployment id in our esp8266 ino code so you need to again copy this and keep it safe so i will keep it here this is my deployment id so this id i will use soon i will use it so okay so let's follow the document finally the deployment id will be done and you will see the deployment id on the screen and a success message yes we already saw that kindly note this deployment id as we will need this ide in our esp8266 code then you just need to click on done so you are seeing that after copying you need to click on done so once copy just click on done that's it magician so as soon as you click on done you will see you will get back to your script and the deployment is already successfully done okay so in future note in future if you need to know the deployment id you can you can check it from the deploy and manage deployments if you forgot to copy the deployment id and if you want to know the deployment id you can directly go here on the deploy and manage deployments if you click on this from here also you can see your deployment id and then you can copy it again from here okay next step let's follow the document now you can simply click the run button so you are seeing that uh inside this script editor you will see a run button here if you want to execute this script you can simply click on the run button but uh at this point nothing will happen uh with executing this script as of now it will simply show the log messages like below it will show some log messages and you should not see actually we are trying to see whether we get any error or there is some issue with the script or not so for that we are just trying to run it if your script is is not having any errors or uh issues it will simply show you the log something like that so let me show you so here you will just click on this run button so it is executing and then you can see the execution log here so the log is saying yeah we don't see any issues with our script it means our script is working fine and we are good to use this script or to call this script from our esp8266 now okay magicians again i have a note here before moving ahead please make sure that you are not getting any error after running the above script like we ran this that script if you are getting any error or any issues with the script do not move ahead because the script is erroneous and esp8266 will not be able to call that a script so make sure that your script is without any errors now if you wish you can close this google script browser tab as we are done with the necessary coding yeah so if you want to close this tag you can easily close this clicking here yes so i have closed my google script screen now i am back to my google spreadsheet okay magician so congratulations for successfully completing the two third portion of your projects you have successfully created the google spreadsheet you have successfully created the script that is needed and now the final step is to just create the esp code and we will start sending data to our google spreadsheet or another way you can say we will call the script the google script from our esp8266 and we will provide the necessary data the script needed okay magician so hope uh till now um you are with me and you are getting getting it easier while following this document but asleep you have any issues confusion questions do let me know in my whatsapp group or my facebook group or under the comment section of this video and again if you need this document and the code everything whatever i'm using in this if you need that let me know i will provide it to you my whatsapp number is already there in the description of this video so magician the final step is step number c sees third step is now we will create the ino code so how do we do that yes we all are expert creating for ino code so let's do that so kindly follow the below steps to create esp8266 code open arduino ide connect your esp8266 to a laptop yes you identify the com port mine is com4 select the board as generic esp8266 port as comport so step number one is the user step that we do while writing any code on our esp so i have already done let me open my arduino ide so this is the arduino ide i have opened the arduino ide and here you are seeing i have already selected the board as generic esp8266 i have already connected with the port as com4 okay let's follow the document now what you have to do the next step is you have to copy the code esp8206 google spreadsheet posting dot io so you have to use this code and paste there on your es on your arduino ide where you will be seeing a new script blank description to delete that script and use copy and paste our script so again from where you will get all this script yes i will provide you this uh code i know if you ask me under the comment section of this video or in my whatsapp group or in my facebook group so let me open this code so here is the code so this is the complete code so you need to copy and paste this code so this is the code we are going to use the only changes that you have to do in this code are your wi-fi ssid your wi-fi password and the google script deployment id remember we had just copied the deployment id you're seeing that this one we had just copied this deployment id so you need to update your i know code with with these three things one wi-fi ssid second password and third your google script deployment id so from here you can give your ssid you can give your password and you can give your google script do not try to use my ssid passwords or google slidey because this will not work for you this is just for this demo purpose and after that all these things will be deleted and will not be working anymore so you try to use your own values of ssid's passwords and your google script id okay so once you have copied your ssid your password and your google script id you need to just save it and then next step is you need to copy our below three files and put them along with esp8266 google spreadsheet posting. the i know the i know file which you had just created means this file you had just created this file you need to now use these three files https redirect.cpp debug macro.h and https redirect.h these three files you need to keep along with your i know file again from where you will get these three files yes magicians you got it i will give you these three files ask me for these three files i will provide you these files if you ask me in my whatsapp in my facebook or under the comment section of this video all these three files i will provide it to you and then you need to copy and paste and keep these files along with your i know code so let me show you so it should it should show like this you are seeing there my ino code is here under the i know folder so this is the code folder and here i have the i know code and inside this you have to keep these three files okay so let me show you in my screen here so this is you're seeing that this is my code folder this is the code folder and here is the i know file and along with that i have these three files that you have to keep three files and it is and you don't need to make any change on these three files you just need to copy and paste as it is so you should have all these four you will not have template files forget about that this is not required for you you just need to have after copying these three files your folder code folder should have these four files one the i know code and these three files two header files and one cpp file okay magician so once you have done that that's all magician we are now ready to upload our code to our esp8266 and ready to see the magic so you need to how do we upload it we already know that you need to click the upload button to upload the code to esp8266 once your upload is done then you can open the serial monitor and then press the reset button of esp to re-execute the code and then you can observe the serial monitor output in the same same time you can observe your google spreadsheet also so when you will open your serial monitor you can see that esp is publishing some data and the same data is getting reflected in our google spreadsheet so let's try to do that so now is the time to execute this so let me make it little smaller and let me open our yes our spreadsheet also at the same time so now you see that on the right hand side i have the spreadsheet let me make it a little bit more bigger so that all values are visible to you okay okay so right hand side i have opened my spreadsheet on the left hand side i have the code which i have which i am going to upload here in my esp but remember that you need to use your ssid password and the google script deployment id so my google script id is this so i will copy this and here you need to paste your deployment id between the two double quotes delete that and you will copy and paste the deployment id which you had just copied again remember don't use my wi-fi id passwords credentials anything it will not work for you use your own so once you have copied and is the time to upload it so i will just click on the upload button and i will wait for the sketch to be compiled the compilation is in progress yes it is it has been compiled now it is uploading as you see here the progress which is uploaded 25 is done and you are seeing that 40 upload is done now 50 upload is done now our 65 upload is done 75 upload is done soon it will be uploaded hundred percent yes ninety percent upload is done ninety five percent approach is done yes hundred percent uploaded so now i can open my serial monitor to show you what happens on the serial monitor yes it has got the ip address and it has connected to that wi-fi it has published the data with the value value 0 is 1 value 1 is 4.9 and value 2 is 794.60 so you see let me make it little small so you're seeing that excel spreadsheet google spreadsheet has been updated with the value again after 15 seconds it uploaded the value 2 6 0 6 9 20 59 as you are seeing that i have highlighted this row which has been populated from our esp8266 again row number three you are seeing that the third data after 15 second this data is also inserted here in my google spreadsheet with the value 3 value 0 is 3 value 1 is 6 1 9 and value 2 is interval 3 4 0 let's try to wait for another data yes sheet another row number four has been added which is with the values four four zero nine and three one five four and you're seeing that after every 15 seconds my google spreadsheet is being refreshed and the new data is inserted at the top of our spreadsheet so you can you can write the code anyway wherever you want to insert i am just inserting at the top but you can write to insert at the bottom as well and here i am just inserting only three values value 0 value 1 and value 2 all these three values are coming from esp and these two values are getting calculated from our script itself so this is just a way or a demo or how you can say this is the way we we are trying to write some data from our esp8266 you can write you can send any sensor value or any live value that you want to be recorded on your google spreadsheet you can easily do that so hope positions this will be helpful for you let me again open the document see if anything is there yes magicians if you don't want your esp to send regularly the data to a google spreadsheet you need to open a new sketch which will be a blank sketch you are seeing that this is the blank sketch and you need to upload it to your esp so i am uploading the blank sketch to the esp because i don't want my esp to send data to google spreadsheet every 15 seconds because i don't require it now this was just for the demo purpose and for the learning purpose that how do we send something back over on google spreadsheet hope this is clear to you and that was a useful session for you if you need any information if you have any doubt or any confusion regarding this let me know i will provide you the details ask me in my whatsapp group or in my facebook group or ask under the comments section of this video and i can provide you the details there okay magicians so congratulations magicians to access google spreadsheet without using any third party tool yay so you are really becoming the great magicians day by day another feather on your cap yes magicians so that's all for the day and hope you enjoyed and learned something new if you need anything else let me know and have a nice time and take care of yourself arigato soon i will set up some another section and another interesting projects for you that will be helpful for you for your future projects thank you magicians take care of yourself have a nice time arigato

Channel: IMH Videos

Views: 48

Keywords: IOT, Raspberry Pi, IOT Course, Online IOT projects, Online IOT session, Amazon AWS IOT, MQTT IOT, Wireless IOT, XBEE, Home Automation, Learn IOT Online

Id: VD-nQpQ53-8

Length: 42min 38sec (2558 seconds)

Published: Fri Dec 10 2021