Google Sheets IoT

Overview

We will create an IoT environment sensor using Leafony Beacon1) and Raspberry Pi, and visualize the sensor data using Google Spreadsheet.

  1. Beacon is a facility for acquiring various information including location by receiving radio waves on the ground with a specific device. in the case of Bluetooth Beacon devices, a module equipped with sensors such as temperature and air pressure can be installed at any location to observe the environment of that location.

about

What to prepare

  • Leafony Basic Kit
  • Raspberry Pi (3, 4, zero, etc.)
  • PC or smartphone with browser access
  • Arduino IDE

Sample app code

Preparing the Raspberry Pi

  • If you are new to Raspberry Pi, the Raspberry Pi 3 B+ Starter Kit (https://www.switch-science.com/catalog/3880/) is useful. The microSD containing the Raspbian OS image is included, but the display, keyboard, and mouse are not. The display, keyboard, and mouse are not included.
  • The Raspberry Pi will be connected to the Internet via a wireless LAN. Please prepare this in advance as well.

Leafony Configuration

Type Name Q’ty
AZ62 Connector Cover 1
AI01 4-Sensors 1
AZ01 USB 1
AP01 AVR MCU 1
AC02 BLE Sugar 1
AV01 CR2032 1
- CR2032 coin cell battery 1
- M2*18mm screw 2

Assembly

Writing sketches

  • The BLE and 4-Sensors libraries are required for writing. Please include the libraries beforehand. See here for details.
  • In the Arduino IDE, write the following sketch on the microcontroller board. ・leafony_beacon.ino
  • We have confirmed that it works with Arduino IDE 1.8.9. If you get a write error, please check your IDE version.

Building the Raspberry Pi environment

We will use a Raspberry Pi as the receiver for the BLE Beacon. In the sample code we will be using, we use Node.js to receive the BLE Beacon using noble. The received data is written to a Google spreadsheet via Google API.

Execute the following script (6 items) to build the environment. https://github.com/Leafony/leafony-beacon/tree/master/spreadsheet-logger

sudo apt update
sudo apt upgrade
sudo apt install -y bluetooth libbluetooth-dev libudev-dev git
git clone https://github.com/Leafony/leafony-beacon
cd leafony-beacon/spreadsheet-logger
npm install

Note: WARNING will be displayed, but please continue.

gs_pi_1

Getting the Google Sheets API

In order to manipulate Google Spreadsheets in Node.js, we need to obtain an API. First, in Step 1 of Node.js Quick Start below, click Enable the Google Sheets API, and download credentials.json. Next, copy the credentials.json to the leafony-beacon/spreadsheet-logger directory.

Node.js Quickstart | Sheets API | Google Developers : https://developers.google.com/sheets/api/quickstart/nodejs

Setting Google Sheets ID and sheet name to index.js

Open the Google Spreadsheet and check the “Sheet ID” and “Sheet Name” in the red line of the URL.

gs_pi_2 gs_pi_3

leafony-beacon/spreadsheet-loggerディレクトリ「index.js」があります。「index.js」の18, 19行目にある「spreadsheetId」と「sheetName」に、上記の「シートID」と「シート名」を貼り付けることで、データが書き込まれます。

gs_pi_4

アプリの実行

Raspberry piのターミナルに「sudo node .」と入力してEnterをクリックしてください。

gs_pi_5

上記URLにブラウザでアクセスすると、Googleアカウントに移動し、ご自身のアカウントを選択します。

gs_pi_6

「詳細」をクリックしてください。

gs_pi_7

「Leafony Beacon Logger「安全ではないページ」に移動」をクリックしてください。

gs_pi_8

「詳細」をクリックしてください。

gs_pi_9

「Leafony Beacon Logger「安全ではないページ」に移動」をクリックしてください。

gs_pi_10

「許可」をクリックしてください。以下のコードをコピーしてください。ターミナル内に貼り付けると、「token.json」というファイルが生成されたあとに、beaconのスキャンが始まります。

gs_pi_11

Beaconが見つかるとログが表示されると同時に、スプレッドシートに同じ内容が追記されるので、スプレッドシートを開いてデータを確認してください。

gs_pi_12


Last modified 21.12.2020