In this article we access our google sheet data from our google drive with our ESP8266 device.
Google Sheet
It is a spreadsheet almost similar to Microsoft Excel as we are accustomed with. The google sheet has its own format, the GUI interface is very user friendly. It is available in Google apps in google.com page at the top-right corner, just left to your sign in icon. You can access it once you signed in to your google account.
ESP8266
Esp8266 is a wifi module along with a microcontroller widely used in IOT (internet of things) technology. As the ESP devices has inbuilt microcontroller, this small device can be used as a small computer (without monitor and terminal) at any place at convenient. It just works silently. There are variants of these devices like ESP8266-01, ESP8266 Node MCU, ESP32 etc.
Now, we will follow the steps below to connect he google spreadsheets and do read/write operations to it.
STEP1:
Bill of Materials:
Hardwares
(i) An ESP8266 module device
(ii) A Personal Computer to prepare the software and upload it to ESP
Software
(i) We will use Windows 10 to prepare & upload it to ESP8266
(ii) We need to install Arduino IDE as our working environment.
STEP2:
(i) Download HTTPSRedirect library from this
link
(ii) Go to google Sheet. You can get it in your google.com page inside Google apps at the top right corner region.
(III) create a spread sheet like below
(iv) copy the spread sheet id from the url address like below:
See the address bas: docs.google.com/spreasheet… From here copy the part within ‘../d/’ and ‘/edit..’. This will be used as the spreadsheet id in the app script.
(v) we can add another sheet clicking the bottom left (+) button. (we can toggle between sheets clicking the sheet1 and sheet2 tab).
(vi) Go to tools>Script editor
(vii) Script editor will be opened in a new tab.
(viii) copy the contents GoogleScript.gs file from the HTTPSRedirect library and paste it here.
(ix) Replace the spreadsheet id with that copied at point (iv) in step2.
(x)Now deploy your app script as a web app and collect the ScriptID. Click Deploy>New Deployment
Follow the following steps:
Now select Anyone in ‘Who has access’ drop-down list. Click Deploy. The system will ask for your authorization. Follow the subsequent steps. In the process following a security warning may come. Go ahead and give authorization (as you know, it is your app script & it is not going to cause any harm to any system).
We can now test our App Script in any web browser. Just copy and paste the web address (we get it on Manage Deployment tab in the drop-down list we get by clicking Deploy button of the App Script window) to the address bar of any web browser.
Remember: Whenever we make any change to our app script and again go to deploy our app, we must select ‘New Deployment’ from the list.
After all are done, we will get the following window:
Now copy the Google App ScriptID to your secure space. This ID will be required in our ESP8266 code.
Now, Google Script setup is over. Follow the net step.
STEP3:
(i) Download HTTPSRedirect zip library from Github. Copy the HTTPSRedirect.cpp and HTTPSRedirect.h, these two files in your project folder (where you will create your .ino file).
Now you can import it as a header file in your .ino code file.
Now copy the GoogleDocs.ino file in your project directory and open the Arduino IDE.
(ii) Replace the
ssid and
password with your wifi ssid and password.
(iii) Replace the GScriptId value with the ScriptID you just collected when deploying your Google AppScript.
(iv) Do any necessary changes as per your need.
(vi) upload the .ino file to your ESP8266.
(vii) Go to Tools>Serial Monitor
We will see that the data on your Google spread sheet, is being displayed on the serial monitor of Arduino IDE.
You can use these data in many purposes like operating other devices, display units etc. We can also send valuable data collected through sensors to the Google sheet.
