Specification - What I want to do
=================================
I would like to input in-out records at my company automagically by using Raspberry PI and detecting my iPhone's Bluetooth signal. That is, I want to make Bluetooth proximity checker with my Raspberry PI. The specification is:
Detect Bluetooth signal by Raspberry PI
When I come into my office, the system records the time to come into the office
When I leave from my office, the system records the time to leave out from the office
Integrate Google Sheet to record the time
That is only what I want to do. Think about the spending time to input the in/out time manually. We can reduce our precious 12-20 total hours through a year (Assuming 3-5 min per a day and 240 working days through a year in order to input the records). It can be converted to a half or a day.
Then, OK, Let's see how we can make it!
Setup
=================================
First, update Raspberry PI
sudo apt-get -y update; sudo apt-get -y upgrade; sudo apt-get -y dist-upgrade; sudo apt-get -y autoremove
Install the following libraries onto Raspberry PI (pip, python-bluez and the Python client library of Google API)
sudo apt-get -y install python-pip python-bluez
pip install --upgrade google-api-python-client
Integrate with Google Authentication System
=================================
Access to https://console.developers.google.com and create an OAuth 2.0 client ID.
Download Client Authentication Information (JSON) The filename must be saved as client_secret.json to Raspberry PI. Note that you must put the file in the same directory wherever you put your Python script file.
Feasibility Study of Google Authentication System
=================================
Refer to Google Sheets > API v4 | Python Quickstarts and learn about Quickstart on your Raspberry PI. Again, note that the filename must be client_secret.json(This is a fixed filename, don't change the name to whatever your want, and it must be placed in the same directory as you execute Quickstart (The Quickstart sample script will automatically create .credentials directory under e.g. /home/pi)
Check Bluetooth Device ID
=================================
Download a script from https://github.com/karulis/pybluez/blob/master/examples/simple/inquiry… onto your Raspberry PI.
Execute inquiry.py and look into your Bluetooth device ID
wget https://github.com/karulis/pybluez/blob/master/examples/simple/inquiry…
The following script is inquiry.py
# file: inquiry.py
# auth: Albert Huang <albert@csail.mit.edu>
# desc: performs a simple device inquiry followed by a remote name request of
# each discovered device
# $Id: inquiry.py 401 2006-05-05 19:07:48Z albert $
#
import bluetooth
print("performing inquiry...")
nearby_devices = bluetooth.discover_devices(
duration=8, lookup_names=True, flush_cache=True, lookup_class=False)
print("found %d devices" % len(nearby_devices))
for addr, name in nearby_devices:
try:
print(" %s - %s" % (addr, name))
except UnicodeEncodeError:
print(" %s - %s" % (addr, name.encode('utf-8', 'replace')))
python inquiry.py
Feasibility Study of Bluetooth Device ID Detection
=================================
Download a script from https://raw.githubusercontent.com/paulbarber/raspi-gpio/master/inoutboa… onto your Raspberry PI
#!/usr/bin/python
import bluetooth
import time
print "In/Out Board"
while True:
print "Checking " + time.strftime("%a, %d %b %Y %H:%M:%S", time.gmtime())
result = bluetooth.lookup_name('1C:66:AA:CF:DD:35', timeout=5)
if (result != None):
print "John: in"
else:
print "John: out"
time.sleep(60)
Copy and paste your Bluetooth device ID to bluetooth.lookup_name('1C:66:AA:CF:DD:35', timeout=5)
Execute inoutboard.py
python inoutboard.py
Integration - Create a Python Script
=================================
Let's integrate/combine the Google Authentication and Bluetooth detection script to the one together.
The following is the whole script. Copy and paste your Bluetooth device ID that you get above to DEVICES=['12:34:56:78:9A:BC'].
You can put multiple device IDs such as DEVICES=['12:34:56:78:90:AB', 'CD:EF:12:34:56:78' ].
SPREADSHEET_ID is your Google Sheet ID. Hint: you can fine the ID in the Google sheet URL.
DO NOT make readonly in a SCOPES variable such as https://www.googleapis.com/auth/spreadsheets.readonly. This is readonly, we need to specify https://www.googleapis.com/auth/spreadsheets in our case.
#!/usr/bin/python
# -*- coding: utf-8 -*-
from __future__ import print_function
import httplib2
import os
import time
import bluetooth
from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage
SPREADSHEET_ID = '1Yz6R_b_ff7zFUu16Mo4YI6IDBA0U6hteb6pPf9PoYus'
DEVICES=['D8:BB:2C:85:22:17']
SLEEP_INTERVAL=30
SERVICE = '';
FIRST_ROW=3
try:
import argparse
flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
flags = None
# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
# SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Inout Client'
def get_credentials():
"""Gets valid user credentials from storage.
If nothing has been stored, or if the stored credentials are invalid,
the OAuth2 flow is completed to obtain the new credentials.
Returns:
Credentials, the obtained credential.
"""
home_dir = os.path.expanduser('~')
credential_dir = os.path.join(home_dir, '.credentials')
if not os.path.exists(credential_dir):
os.makedirs(credential_dir)
credential_path = os.path.join(credential_dir,
'sheets.googleapis.com-python-inout.json')
store = Storage(credential_path)
credentials = store.get()
if not credentials or credentials.invalid:
flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
flow.user_agent = APPLICATION_NAME
if flags:
credentials = tools.run_flow(flow, store, flags)
else: # Needed only for compatibility with Python 2.6
credentials = tools.run(flow, store)
print('In-Out: Storing credentials to ' + credential_path)
return credentials
def get_service():
# Creates a Sheets API service object
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
try:
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
'version=v4')
return discovery.build('sheets', 'v4', http=http,
discoveryServiceUrl=discoveryUrl)
except:
print('In-Out: Error: Authentiction')
return None
def run():
# while True:
print ('In-Out: Checking ' + time.strftime('%Y/%m/%d (%a) %H:%M %Z', time.localtime()))
for device in DEVICES:
result = bluetooth.lookup_name(device, timeout=5)
if (result != None):
print('In-Out: In: %s (%s)' % (result, device))
write_in_out()
else:
print('In-Out: No Bluetooth device detected: ' + device)
# time.sleep(SLEEP_INTERVAL)
def write_in_out():
service = get_service()
if service == None:
return
values = None
try:
rangeName = '%s!A%s:D35' % (time.strftime('%Y-%m', time.localtime()), FIRST_ROW)
result = service.spreadsheets().values().get(
spreadsheetId= SPREADSHEET_ID, range=rangeName).execute()
values = result.get('values', [])
except:
print('In-Out: Error: write_in_out: service.spreadsheets().values().get()')
return
if not values:
print('In-Out: No data found.')
else:
# print('=================')
# print(result)
sheet_name = time.strftime('%Y-%m', time.localtime())
body = {'values': [[time.strftime('%H:%M', time.localtime())]]}
i = FIRST_ROW
for row in values:
# print('In-Out: %s | %s ' % (row[0], i))
if time.strftime('%Y-%m-%d', time.localtime()) == row[0]:
# Write the time at an 'in' column is empty
if len(row) < 3:
rangeName = '%s!C%s' % (sheet_name, i)
print ('In-Out: Wrote: rangeName: %s | %s | body: %s' % (rangeName, time.strftime('%Y-%m-%d', time.localtime()), body))
result = service.spreadsheets().values().update(
spreadsheetId=SPREADSHEET_ID, range=rangeName,
valueInputOption=u'USER_ENTERED', body=body).execute()
try:
# Always write the time at an 'out' column while the bluetooth device is detected
rangeName = '%s!D%s' % (sheet_name, i)
print ('In-Out: Wrote: rangeName: %s | %s | body: %s' % (rangeName, time.strftime('%Y-%m-%d', time.localtime()), body))
result = service.spreadsheets().values().update(
spreadsheetId=SPREADSHEET_ID, range=rangeName,
valueInputOption=u'USER_ENTERED', body=body).execute()
except:
print('In-Out: Error: write_in_out: service.spreadsheets().values().update()')
i = i + 1
def main():
run()
if __name__ == '__main__':
main()
Daemonize
=================================
Setup systemd service.
Copy or create a symbolic link of your Python script that you created above to /usr/bin
cd /usr/bin
ln -s <the Python script filename that you created above (fullpath)> inout.py
vi /lib/systemd/system/inout.service
Honestly, I don't know the detail of systemd but my inout.service script is as follows
[Unit]
Description=In-Out Checker
After=hwclock-save.service dhcpcd.service bluetooth.target hciuart.service systemd-hostnamed.service
Requires=hwclock-save.service dhcpcd.service bluetooth.target hciuart.service systemd-hostnamed.service
[Service]
Type=simple
ExecStart=/usr/bin/inout.py
Restart = always
[Install]
WantedBy=multi-user.target
Reload systemd daemon
systemctl daemon-reload
Start inout.service daemon
systemctl start inout.service
Enable inout.service daemon
systemctl enable inout.service