Read and Write to Google Sheet from your ESP8266 device.

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 within 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.

Figure1

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 work silently. There are variants of these devices like ESP8266-01, ESP8266 Node MCU, ESP32 etc.

How ESP Devices Collects Data and Outputs Data:

An ESP device collects data through its sensors connected to its pins and send it to the defined internet address. Similarly, it collects data from the defined internet address and send it to its output pins for actions.

Usages: case 1: Suppose, you have a display unit. You want to display records collected from a remote location through various meas like sensors and other input devices. Here you can use an ESP device as the data collector and provider to your display unit.

Case 2: Say, you organized a conference. You want to get the number of total members present at any time in the conference hall from a remote place. You can deploy ESP8266 here to collect the data through the sensors and send it your mobile device silently.

Case 3: Nowadays, Fastag has been introduced in Indian National Roadways. It works through the RFID sensors at traffic gates. The device modules at the gates get the data about the vehicle and do the toll operations without stopping the vehicle any more. We can achieve this type of performances also with these ESP devices.

 

Now, we will follow the steps below to connect he google spreadsheets and do read/write operations on it.

STEP1: Bill of Materials:

(hardwares)

(i) An ESP8266 module device

(ii) An ESP8266 burner (if it is a node mcu, burner is inbuilt)

[Note: burner device is required only for ESP-01 devices, for others, we just need to connect the ESP Node MCU to the computer USB Cables.]

(iii) 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

[Note: Different versions of Arduino IDE are available for different OS versions]

(ii) We need to install Arduino IDE for our working environment.

STEP2:

(i) Download ReadSheetData zip library from this link.ReadSheetData

(ii) Go to google Sheet. It you can get it in your google.com page inside Google apps at the top right corner region. 

Figure 2

Figure3

(III) create a spread sheet like below

Figure4

(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) add another sheet clicking the bottom left (+) button. (we can toggle between sheets clicking the sheet1 and sheet2 tab).

Figure5

(vi) Go to tools>Script editor

(vii) Script editor will be opened in a new tab.

(viii) copy the contents MyAppScript.gs file from the ReadSheetData.zip library and paste it here.https://github.com/subhramukherjee/ESP8266_Logger.git

(ix) Replace the spreadsheet id with that copied at point (iv) in step2.

Figure6

(ix)Now deploy your app script as a web app and collect the ScriptID.  Click Deploy>New Deployment

Follow the following steps:

Figure7

 Figure 8

Figure9

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 case any harm to any system).

Figure10

 

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:

 Figure11

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 (You can download the ConnectSheet.zip file from this link also, unzip it). Now open the Arduino IDE. Go to Sketch>Include Library>Add .ZIP Library…

Here select the HTTPSRedirect.zip file, the HTTPSRedirect library will added to your library list in Sketch>Include Library.

You can import it as a header file in your .ino code file.

(ii) You can also download the ReadSheetData.zip from the following github resource. https://github.com/subhramukherjee/ESP8266_Logger.git

(ii). Click File>Open. Get the ReadSheetData.ino by unzipping the ConnectSheet.zip file you just downloaded.

(iii) Replace the ssid and password with your wifi ssid and password.

(iv) Replace the GScriptId value with the ScriptID you just collected when deploying your Google AppScript.

(v) 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 spread sheet data 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.

To write to the google sheet input some text in the Serial input bar. And wait. You will see that your input has been inserted in the Google sheet.