원클릭 수유등 (2/2) - 구글 스프레드시트 연동

이전 포스팅에서 AWS IOT Button 클릭으로 Philips Hue 전구를 켜고 끄는 것을 구현해봤는데 뭔가 심심한 것 같아서 살을 하나 더 붙여보기로 했다. 전구를 켜고 껐을때를 스프레드시트에 기록을 남겨놓으면 아기가 새벽에 언제 깼는지 언제 다시 잠들었는지를 알 수 있지 않을까. 그 정보들이 실질적인 도움이 될지는 모르겠지만 “만들어야 사니까” 일단 해보자.

어떻게 할 것이냐

버튼 클릭 → 전구 켜면서(끄면서) → 구글 스프레드시트에 현재 시간과 ON/OFF 액션을 남기자

바로 들어가보자

Google Sheet API 연동은 구글 개발자 콘솔에서 설정이 다다. 구글 개발자 콘솔에서 프로젝트 생성하고 Credential 정보를 생성해서 이를 이용해 API 호출을 하면 된다.

 

(1) 구글 콘솔에서 프로젝트 생성

 

 

 

(2) Google Drive API 활성화

Library > Google Apps API > Drive API > “ENABLE” 클릭

 

 

 

(3) Credential 생성

Credentials > Create credentials > Service account key 선택

 

 

Service Account 정보를 입력한 뒤 JSON으로 키 타입을 설정 후 Credential 을 생성하면 된다. 이때 JSON 파일이 다운로드 되는데 나중에 프로젝트에서 import 해서 사용할 것이지 잘 가지고 있자.

 

 

방금 생성한 Credential 이 이렇게 목록에 보이면 구글 API 콘솔에서의 설정은 끝났다. 이제 Google Spreadsheet API 를 쉽게 사용할 수 있게 Wrapping 한 Node 패키지를 설치하고 코딩만 하면 된다.

 

 

 

(4) Node Library 설치 (node-google-spreadsheet)

$ npm install google-spreadsheet

$ npm install google-spreadsheet

 

(5) 코드 작성 - ON/OFF 로그 남기기

위에서 생성하며 download 받았던 credential 파일을 client_secret.json 로 변경하며 프로젝트 디렉토리에 일단 넣어두자. Node에서 require 로 로드해서 사용할 것임.

{
  "type": "service_account",
  "project_id": "<your-project-id>",
  "private_key_id": "<your-private-key-id>",
  "private_key": "-----BEGIN PRIVATE KEY-----\n.........",
  "client_email": "<your-client-email>",
  "client_id": "<your-client-id>",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/huebtn-logger%40huebtn-logger.iam.gserviceaccount.com"
}

google-spreadsheet 라이브러리를 초기화하고 첫번째 sheet를 가져와서 row를 하나 추가하는 로직은 대충 아래와 같다. 유의할점은 addRow 에서 추가할 row 데이터는 object 형인데 key 값을 Header(첫번째 row) 이름과 일치시키면 key에 매치되는 열에 맞춰서 row 를 추가해준다.

var GoogleSpreadsheet = require('google-spreadsheet');
var creds = require('./client_secret.json');
var doc = new GoogleSpreadsheet('<your-private-key-id>');
var logToSheet = function(action) {
  doc.useServiceAccountAuth(creds, function () {
    doc.getInfo(function(err, info) {
      var sheet = info.worksheets[0];  // first sheet
      sheet.addRow({ <object, key should match headers> })
    });
  });
};
dash.on("detected", function() {
    ......
    api.setLightState(lId, newStatus).then(function(result) {
      logToSheet(newStatus._values.on ? 'ON' : 'OFF');
    }).done();
  }).done();
});

 

이전에 작성한 버튼 클릭 Detection 과 Hue 컨트롤 코드를 모두 합쳐서 구현된 최종 코드는 아래와 같다. 전체 소스 코드는 여기서도 확인할 수 있다.

var DashButton = require("node-dash-button");
var hue = require("node-hue-api");

var GoogleSpreadsheet = require('google-spreadsheet');
var creds = require('./client_secret.json');

var dateFormat = require('dateformat');

var HueApi = hue.HueApi;
var lightState = hue.lightState;

var hostname = "172.30.1.52";
var username = "XD6RksCB1ms0tUSIVlgsD6XbyB9KLodto7o71L7X";

var api = new HueApi(hostname, username);
var state = lightState.create();

var doc = new GoogleSpreadsheet('16zZKNMrjXOqkoAsxjmD26HasJL5Eiso-bc2RO7osXsk');

var logToSheet = function(action) {
    doc.useServiceAccountAuth(creds, function () {
        doc.getInfo(function(err, info) {
            var now = dateFormat(new Date(), 'yyyy-mm-dd hh:MM:ss');
            var sheet = info.worksheets[0];
            sheet.addRow({ seq: sheet.rowCount, time: now, action: action })
        });
    });
};

var dash = DashButton("88:71:e5:88:78:99", null, null, 'all');
dash.on("detected", function() {
    var lightId = 3;
    api.lightStatus(lightId)
    .then(function(status) {
        var newStatus = status.state.on ? state.off() : state.on();
        api.setLightState(lightId, newStatus)
        .then(function(result) {
            logToSheet(newStatus._values.on ? 'ON' : 'OFF');
        })
        .fail(function(error) { console.log(error); })
        .done();
    }).done();
});

데모

 

참고

https://console.developers.google.com/
https://github.com/theoephraim/node-google-spreadsheet
https://developers.google.com/sheets/api/quickstart/nodejs
https://github.com/felixge/node-dateformat