Wiznet makers

[Project] Industrial Ethernet Data Logger: ESP32(LAN8720) & Pico(W5500)

1. Overview

This project is a wired Ethernet-based Industrial IoT solution that utilizes Google Sheets as a cloud database (DB) and control panel. Targeting industrial environments where wireless connections are impossible or unstable, it supports two distinct wired Ethernet platforms: ESP32 + LAN8720 and Raspberry Pi Pico + WIZnet W5500. This ensures hardware flexibility, allowing users to choose the optimal method between high-performance processing (ESP32) and stable network offloading (W5500) based on site conditions.

2. Google Sheets Core Technology

We applied technical methodologies that go beyond simple text storage, transforming Google Sheets into a fully functional Backend System.

A. "Spreadsheet as a Database" Architecture

CRUD Implementation: Perfectly mimics standard DB functions using Google Sheets API v4.

Create (Logging): Uses the append API to accumulate sensor data in time-series (Row addition).

Read (Status Check): Uses the get API to read values from specific cells (Ranges) to parse device settings or operation commands.

Update (Feedback): Uses the update API to overwrite specific cells with the device's current status or processing results for state synchronization.

JSON Serialization: Equipped with a high-efficiency parsing engine that serializes C++ (Arduino) structure data into complex nested JSON objects required by the Google API.

B. Combination of Serverless & Excel Interface

No-Code Dashboard: Without separate frontend development, on-site managers can visualize and analyze real-time data using familiar Excel functions and charts.

Synchronous Data Integrity: Designed to map 'Sheet Names' and 'Cell Addresses (A1 Notation)' accurately during data transmission, ensuring logs are recorded in the exact location without errors even when thousands of data points are accumulated.

3. Hardware Platform Comparison (Dual Ethernet Strategy)

Both platforms adopt a Wired approach to guarantee industrial reliability, but there is a decisive difference in their internal operation mechanisms.

4. Key Features & Differentiators

A. Industrial Security (Service Account Security) Instead of the ID/PW method, we implemented OAuth 2.0 Service Account authentication with RSA-SHA256 digital signatures. Since the ESP32 and Pico generate and refresh their own Bearer Tokens, secure Machine-to-Machine (M2M) communication is possible without the risk of exposing security keys.

{
    "type": "service_account",
    "project_id": "...",
    "private_key_id": "...",
    "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
    "client_email": "...",
    "client_id": "...",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "..."
}

B. Firewall-Free Bidirectional Control We applied an HTTP Polling technique to ensure operation even within closed factory networks where external access is blocked. Since the device proactively connects to the Google server to fetch commands, remote control is immediately available by simply plugging in the internet line, without complex firewall settings or port forwarding.

5. Expected Effects

Immediate Field Deployment: By providing a verified wired connection via the W5500, zero-data-loss monitoring is possible even inside factory facilities with Wi-Fi dead zones or severe radio interference.

Development & Maintenance Efficiency: Drastically reduces total system construction costs through benefits such as 'Zero' server setup cost, no need for database design, and easy data processing via Excel.

1. 프로젝트 개요 (Overview)

본 프로젝트는 Google Sheets(구글 스프레드시트)를 클라우드 데이터베이스(DB) 및 제어 패널로 활용하는 유선 이더넷 기반의 산업용 IoT 솔루션입니다.

무선 연결이 불가능하거나 불안정한 산업 현장을 타겟으로 하여, ESP32 + LAN8720과 Raspberry Pi Pico + WIZnet W5500 두 가지 유선 이더넷 플랫폼을 모두 지원합니다. 이를 통해 현장 상황에 맞춰 고성능 처리(ESP32)와 안정적 오프로딩(W5500) 중 최적의 방식을 선택할 수 있는 하드웨어 유연성을 확보했습니다.

2. Google Sheets Core Technology (핵심 기술)

단순히 데이터를 텍스트로 저장하는 수준을 넘어, 구글 시트를 완전한 기능의 백엔드 시스템(Backend System)으로 변환시키는 기술적 방법론을 적용했습니다.

A. "Spreadsheet as a Database" 아키텍처

CRUD 구현: Google Sheets API v4를 활용하여 일반 DB의 기능을 완벽히 모사했습니다.

Create (Logging): append API를 사용하여 센서 데이터를 시계열(Time-series)로 누적 기록 (Row 추가).

Read (Status Check): get API로 특정 셀(Range)의 값을 읽어와 장치의 설정값이나 동작 명령을 파싱.

Update (Feedback): update API를 통해 장치의 현재 상태나 처리 결과를 특정 셀에 덮어쓰기하여 상태 동기화.

JSON Serialization: C++(Arduino) 상에서 수집된 구조체 데이터를 Google API가 요구하는 복잡한 중첩 배열 형태의 JSON 객체로 직렬화(Serialization)하여 전송하는 고효율 파싱 엔진을 탑재했습니다.

B. 서버리스 & 엑셀 인터페이스의 결합

No-Code Dashboard: 별도의 프론트엔드 개발 없이, 현장 관리자가 익숙한 엑셀(Excel) 함수와 차트 기능을 사용하여 실시간 데이터를 시각화하고 분석할 수 있습니다.

동기식 데이터 무결성: 데이터 전송 시 '시트 이름'과 '셀 주소(A1 Notation)'를 정확히 매핑하여, 수천 개의 데이터가 쌓여도 오차 없이 정확한 위치에 로그가 기록되도록 설계했습니다.

3. 하드웨어 플랫폼 비교 (Dual Ethernet Strategy)

두 플랫폼 모두 유선(Wired) 방식을 채택하여 산업 현장의 신뢰성을 보장하지만, 내부 동작 방식에서 결정적인 차이가 있습니다.

4. 핵심 기능 및 차별점

A. 산업용 보안 인증 (Service Account Security)

ID/PW 방식이 아닌, OAuth 2.0 서비스 계정(Service Account)과 RSA-SHA256 전자 서명 방식을 적용했습니다.

ESP32와 Pico가 스스로 인증 토큰(Bearer Token)을 생성하고 갱신하므로, 보안 키가 노출될 위험 없이 기기 간(M2M) 안전한 통신이 가능합니다.

{
 	"type": "service_account",
 	"project_id": "...",
 	"private_key_id": "...",
 	"private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
 	"client_email": "...",
 	"client_id": "...",
 	"auth_uri": "https://accounts.google.com/o/oauth2/auth",
 	"token_uri": "https://oauth2.googleapis.com/token",
 	"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
 	"client_x509_cert_url": "..."
}

B. 방화벽 프리(Firewall-Free) 양방향 제어

외부 접속이 차단된 공장 폐쇄망에서도 작동하도록 HTTP Polling 기법을 적용했습니다.

장치가 주도적으로 구글 서버에 접속하여 명령을 가져오는 방식이므로, 복잡한 방화벽 설정이나 포트포워딩 없이 인터넷 라인만 꽂으면 즉시 원격 제어가 가능합니다.

5. 기대 효과

즉시 현장 투입 가능: W5500을 통해 검증된 유선 연결을 제공하므로, Wi-Fi 음영 지역이나 전파 간섭이 심한 공장 설비 내부에서도 데이터 유실 없는 모니터링이 가능합니다.

개발 및 유지보수 효율: 서버 구축 비용 '0원', 데이터베이스 설계 불필요, 엑셀을 통한 손쉬운 데이터 가공이라는 이점을 통해 전체 시스템 구축 비용을 획기적으로 절감합니다.