In this manual I’ll explain how to use a Raspberry Pi Zero to read the sensors for temperature and humidity of Xiaomi via Bluetooth and write them directly into a Google Spreadsheet using the Google Drive API.

Raspberry Pi meets Xiaomi Sensor

Because of the temperatures in our attic I had bought the Xiaomi Mijia Bluetooth Thermometer favorably . These can be read out at any time by mobile phone. Unfortunately the Xiaomi sensor does not store the data locally continuously. The result is a missing history of the data. With a Raspberry Pi Zero under 30 € the sensor can be read every minute via Bluetooth Low Energy.

The data is transferred to the spreadsheet in real-time via the API
The data is transferred to the spreadsheet in real-time via the API

Google Spreadsheet instead of MySQL or Elastic Search

I wanted to write the gained data intuitively in Elastic Search on the Pi or in a MySQL database on my web server. However, it was too lazy to read this data again and then write a frontend for it. Finished solutions like Grafana or Home Assistant about Docker were too expensive for me for this one application. I believe less in the data security of a MicroSD card and trust more in the storage directly in the cloud. Then I had the idea with the Google Spreadsheet from Google Drive. There is a Google Drive API which can be used to read and write values to a Google spreadsheet over the Internet.

Instruction step by step

Required Hardware

  • Raspberry Pi Zero Kit with power supply and housing – under 30 €
  • Good MicroSD card – Buy cheap
  • Xiaomi Mijia Bluetooth temperature and humidity sensor v2 – under 15 €

Required Software

  1. Raspberian Stretch Lite (Operating system for the Pi)
  2. etcher (Tool for writing SD cards)
  3. The shell (included with Mac) or PuTTY for Windows.
  4. A Google Account

Step 1: Install Raspbian Stretch lite

Raspbian is the operating system for the Raspberry Pi. We only need the lite version without desktop environment because we only want to connect to the Pi via SSH and Wifi. With etcher.io we write the Raspian Lite image to the SD card. However, we have not yet put these in the pi.

Step 2: Activate SSH and Wifi

After the successful write we remove the card again and insert it again because we now create two files on it: ssh and wpa_supplicant.conf. Under MacOS you can simply use the shell. Under Windows you take the editor of your choice.

touch ssh /Volumes/boot/ssh
vi /Volumes/boot/wpa_supplicant.conf 
# File wpa_supplicant.conf in the boot partition contains the following content:
country=DE
ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1
network={
       ssid="wlan-bezeichnung"
       psk="password"
       key_mgmt=WPA-PSK
}

This connects the Pi to the WLAN at startup and we get to the Pi.

via command line.

Step 3: Connect via SSH

Under Windows you use Putty and under MacOS you do it directly on the shell. The IP address of the Pis can be found out via the router. The password is “raspberry”.

ssh pi@192.168.178.66

Now you should do the following with the command “sudo raspi-config”:

  1. Change the password (Change User Password)
  2. Configure the time zone (Localisation Options > Change Timezone)
  3. SSH enable access permanently (Interfacing Options > SSH)
sudo raspi-config

After the preparation the fun can begin.

Step 4: Install the software

First we update the sources and install git. Now we clone my GitHub repository with the preconfigured files and install Python with the dependencies and make the files executable.

sudo apt-get update
sudo apt-get install git python3-pip libglib2.0-env
cd 
<font color="#ffff00">-=https://github.com/mtoensing/mi2sheet.git=- proudly presents
sudo pip3 install gspread oauth2client
pip3 install --upgrade pyasn1-modules
sudo pip3 install bluepy
chmod +x mi2sheet.py pollandwrite.sh 

Step 5: Search BD_ADDR of the sensor

We search via Bluetooth for the Xiaomi sensor which should be near the pis with the following command:

sudo blescan 

Scanning for devices...
Device (new): 4c:66:a8:ab:c3:9c (public), -81 dBm 
	Flags: <06>
	Complete 16b Services: <0002332-0000-1000-8000-00805f9b34fb,0000180a-0000-1000-8000-00805f9b34fb>
	16b Service Data: <ffffc09c0c3547cc>
	Complete Local Name: 'MJ_HT_V1'

We are looking for a device with the name “MJ_HT_V1”. The Bluetooth Device Address looks something like this and is important for the next steps: 4c:66:a8:ab:c3:9c

Step 6: Test query via Bluetooth

For testing purposes we ask for the data via Bluetooth:

python3 /home/pi/mi2sheet/mi2sheet.py --backend bluepy poll 4c:66:a8:ab:c3:9c

['06/23/2019 22:35:24', '26.2', '47.0', '73']

If an error should appear, please check the commands above first, think about what went wrong or ask here in the last instance. If everything ran correctly, then the array appears with the Datetime, Temperature, Humidity and the Battery charge.

Step 7: Enable Google Drive API

Now we’re paving the way to the Google Cloud. To do this, call the Google API Console in the browser like Google. Here you can make a lot of mistakes, all with the fact that we can’t write into the spreadsheet with the Python script afterwards. Messages like “Permission denied” or “Spreadsheet not found” are then the result.

Video: How do I get the client_secret.json file?
  1. Open the Google API Console
  2. Create a new project
  3. Activate the API
  4. Search and activate the Google Drive API
  5. Create credentials for the web server
  6. Create a service account Give it the Project Role called Editor.
  7. Download the JSON file
  8. Activate the Google Sheets API additionally (don’t forget!)
  9. Copy the JSON file as “client_secret.json” in the following directory on the Pi: /home/pi/mi2sheet/

Step 8: Create a spreadsheet

We create a spreadsheet under drive.google.com with the name “sensor-data“. In the JSON file from step 7, “client_email” contains a cryptic address such as “mrwrite-460@sensors-344317.iam.gserviceaccount.com”. We load this address into the Google Spreadsheet with write permission under “Release”.

Our “Mr. Write” has to be invited to get access.

The spreadsheet should have the following structure for the data. The first line is the fixed header .

TimestampTemperatureHumidityBattery
[empty][empty][empty][empty]

Step 9: Write to spreadsheet via API

Now we write the data from the sensor as a line into the spreadsheet. We need the BD_ADDR from step 5 and the path to the client_secret.json from step 7 which should be in the code directory on the Pi. The parameter “sensor_data” is the name of the spreadsheet. The numeric values 0 and 2 specify the worksheet and the row to which the data is written.

python3 /home/pi/mi2sheet/mi2sheet.py --backend bluepy pollandwrite 4c:66:a8:ab:c3:9c '/home/pi/mi2sheet/client_secret.json' 'sensor-data' 0 2

The process takes me up to 31 seconds with readout and write via the Google API. If an error occurs here, please check the points from step 7.

Step 10: Set up cronjob

The file /home/pi/mi2sheet/pollandwrite.sh should be modified with the command from step 9 . Last but not least, we make sure that the cronjob is executed every 10 minutes.

crontab -e

There the following line is entered at the very end.

*/10 * * * * * /home/pi/mi2sheet/pollandwrite.sh

Now the Pi will do the task itself after a restart. In the spreadsheet you can now insert diagrams and make them available everywhere via “File” -> “Publish to the web”.

Read Xiaomi sensor via Bluetooth into a spreadsheet
Read Xiaomi sensor via Bluetooth into a spreadsheet

Conclusion and outlook

Within a very short time it is possible to read out the temperature via a favorable Raspberry Pi Zero and to present it historically. Without apps or paid services. In addition to querying data from the Xiaomi thermometer, this manual also provides a basis for many other applications in which you want to write data from Bluetooth devices into spreadsheets. It was important to me to write down this path once for all. I learned a lot in the process. Have fun with the knowledge.

In case someone asks when the table is full: A Google Spreadsheet stores up to 5 million cells. My spreadsheet has 4 columns. Since I write a line 6 times per hour, I should be able to get along with it for 20 years.

Leave a comment

Your email address will not be published. Required fields are marked *