Shulou(Shulou.com)06/01 Report--

How to send the data to the Google table, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

On a pleasant summer weekend, instead of going to the beach or anywhere else, I stayed at home, wondering if I could use my favorite MicroPython to send data from the ESP board to the Google form. Suppose it can send the temperature and humidity measured by the DHT22 sensor. This is the beginning of the project.

A quick search on Google shows that this is not a new idea and that someone has implemented sending data from the ESP board to the Google table. I read several articles on this topic and found that all projects do two things: either the project needs to publish Google tables publicly on the Internet, or the project uses middleware such as PushingBox or IFTTT to insert data into the table.

But Google tables provide great HTTP API, so why can't we just send a POST request to insert data into the worksheet? The reason is that the Google form API is concerned about security and requires authentication. In particular, Google Sheet API supports two authentication methods: API keys and OAuth3 tokens. Using an API key sounds simple: we can create an API key in the Google IAM console, encode it on our ESP board, forget to rotate the key and security, and use the key forever. Unfortunately (or fortunately) it won't work. Google Sheet API only allows API keys to read. If you want to write to a worksheet, you must use OAuth3.

There's a problem here. First, you need to create a service account in the Google IAM console, and then create a private RSA key for the service account. Okay, that's not that hard. Your ESP board must then build an JWT request, put the current timestamp in it, and sign the request using the key using the RSA-SHA256 signature algorithm. Next, the board must send the signed JWT request to the Google OAuth3 service, which eventually returns the OAuth3 token. Finally, use the token to invoke the Google form API.

Let me quickly summarize what the ESP board needs to insert a row into an Google table: a HTTP client, a JSON parser, a real-time clock or NTP client with the right time, and an algorithm for implementing RSA-SHA256 signatures. Fortunately, MicroPython provides most of the content except for RSA-SHA256 signatures. Specifically, MicroPython can calculate the SHA256 hash, but cannot use the RSA algorithm to sign the data. In addition, RSA signing is an expensive operation and may require a considerable amount of time and memory, but the ESP board is not as powerful as PC and does not have much memory.

I suspect that the complexity of Google authentication and the lack of MicroPython in RSA allow people to use a public form or a middleware service that implements the authentication process. Of course, as a security engineer, I can't accept a solution that contains publicly available forms that contain sensitive data such as temperature and humidity in my room. Because I am a safety engineer, I don't like middleware. Finally, the only option is to implement the above authentication process on the ESP board in some way.

As I mentioned earlier, MicroPython already provides most of the necessary things:

Ujson allows parsing of JSON

Ntptime.py provides NTP client

Uhashlib can calculate the SHA256 hash value

Http.client provides a HTTP client

The only missing part is the RSA signature. One of the main rules of cryptography is: don't implement any encryption algorithms yourself, but use existing algorithms. I know this rule and find that the python-rsa package implements the RSA algorithm in pure Python. In addition to using RSA for signing, the library supports other operations, such as verifying signatures, encryption and decryption, loading and storing keys, and so on. This library is too heavy for a small ESP board, so I decided to keep only the implementation of the RSA signature and delete the rest of the code. I even removed the code that loads the RSA key in PKCS1 format because it needs to migrate a large pyasn1 package. I also had to implement modular exponentiation based on right-to-left binary methods and other operations, because they turned out not to be provided by MicroPython. It produces a new micropython-rsa signature library.

I used a previous project from ESP8266 and MicroPython as a starting point. After implementing the authentication process and testing it on my laptop, I finally succeeded in running the code on the ESP8266 motherboard. Unfortunately, I found that the code runs very slowly, and the main problem is that the ESP8266 motherboard does not have enough memory to complete the RSA signature. I tried some optimizations and even tried to embed the application code in the MicroPython firmware, but it didn't help.

The last hope is to run the code on ESP32. I haven't used ESP32 before, so I ordered my first ESP32 development board. Fortunately, it succeeded! The application no longer complains about running out of memory, in fact it runs faster.

This code is available on GitHub. README briefly describes how to build a project. I also created a project on Hackaday.io. I hope I can find some time to provide more details in subsequent posts.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.