前兩篇我們透過 Google Firebase 來儲存資料,但對於某部分的人而言,使用 Firebase 可能還是有些難度 ( 或是說不熟悉 ),接下來的兩篇文章將會介紹「Google 試算表」( Google Spreadsheet ) 來實作儲存與讀取的功能,第一篇會介紹「儲存開關燈的時間與狀態資訊」,第二篇則會介紹「讀取某個儲存格,觸發智慧插座反應」。
要使用 Google 試算表同樣的需要有一個 Google 的帳號,而操作的方式主要利用 Google Apps Script 來控制 Google 試算表,製作簡易資料庫,有興趣其實可以參考官方網站的 Reference。
使用 Google Apps Script 的第一步,就是要把你的 Google 雲端新增 Google Apps Script,先點選「連結更多應用程式」( 點選「新增」就會看到 )
搜尋 app script,就會看到 Google Apps Script,點選後方「連接」,就可以加入雲端硬碟。
然後在雲端硬碟的新增裡,就可以看到 Google Apps Script。
點選後會開啟一個新專案的編輯畫面,先把這個專案的名稱改為:test-write。( 自己設定名稱 )
完成後先看程式碼.gs 的編輯,預設有一個名為 myFunction 的流程,將其改為 doGet,一定要是這個名稱,因為這是 Google Apps Script 規定的 ( 參考:https://developers.google.com/apps-script/guides/web#url_parameters ),而這邊讓它多一個 e 的參數作為傳值使用,屆時我們只要把要儲存的資料提交給 e,就可以儲存了。
function doGet(e) {
}
然後裡面就設一個區域變數 data 來接收 e.parameter
所解析出來的值 ( 規範:https://developers.google.com/apps-script/guides/web#url_parameters ),原本想用 e.parameters
就可以處理陣列,誰知道不管怎麼用都沒效 ( 查詢後好像是 bug? ),所以就只能先用 e.parameter
。
function doGet(e) {
var params = e.parameter;
var data = params.data;
}
再來就先宣告對應的試算表網址,以及試算表的名稱,這邊的名稱不是指整份試算表的名稱,而是分頁的名稱 ( 因為容易搞混,我物件屬性用 tag 來代替,不過 API 是 getSheetByName
),而獲取試算表的方法可以用 openByID
或 openByUrl
,如果是 ID 就是指定的 ID,Url 就是整份試算表網址。
var sheetUrl = params.sheetUrl;
var sheetTag = params.sheetTag;
var SpreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
var Sheet = SpreadSheet.getSheetByName(sheetTag);
getLastRow
的方法是讓我們可以獲取最後一行 ( 如果你的資料有 100 行,最後一行就是 100 ),這樣我們儲存資料就可以放在 LastRow+1
行。
var LastRow = Sheet.getLastRow();
主程式的部分很簡單,先把要存入的資料轉成陣列 ( 存入的資料用逗號隔開就比較好處理 ),然後只要用一個 forEach
就可以取出所有內容,放到對應的欄位內,這邊使用 getRange
來將資料放入指定的欄位,最後要記得回傳處理完成的訊息 ( 一定要有,不然就算處理好了,瀏覽器還是會報錯 )。
//寫入資料
data = data.split(',');
data.forEach(function(e,i){
Sheet.getRange(LastRow+1, i+1).setValue(e);
});
//回傳處理完成
return ContentService.createTextOutput(true);
完成後就可以儲存,然後我們再多寫一個 debug 程式來測試一下,新增的方式就是點選左上角新增,選擇指令碼檔案,命名為 debug。
簡單撰寫下面這段程式碼,因為 Google Apps Script 就是用 JavaScript 為基底,所以 JavaScript 可以用的這邊都能用,透過 new Date()
一系列的方法獲取當前的時間,然後就使用剛剛我們做的 doGet
來儲存資料。
function debug() {
var date = new Date();
var now = date.getHours() + ':' + date.getMinutes() + ':' + date.getSeconds();
doGet(
{
parameter:{
data: now+',oxxo,123',
sheetUrl:"試算表網址",
sheetTag:"工作表1"
}
}
);
}
要儲存資料之前,我們必須要先新增一份試算表來儲存,回到 Google 雲端硬碟,左上角新增一份試算表,權限設定為「知道連結的人都可以編輯」。
儲存設定後,看到網址的部分,這一段代碼就是這份試算表的 ID,我們就把它複製起來,如果用 openByID
就貼到剛剛 debug 裡面需要貼上 ID 的部分,如果用 openByUrl
就是整份試算表網址。( 請自己做一份不要用我這份,因為這個範例之後我就會砍掉啦 XD )
完成後點選上方的執行按鈕
一開始會要求一些授權允許。
反正就直接允許就對了 ( 你不允許就不用玩了呀 XD )
每次點選就會看到資料一筆一筆的存入試算表的最後一行了。
如果不想要資料放在最後一行,我們也可以透過 insertRowBefore(1)
在第一行面多插入一行空白,這樣資料就可以放在第一行了。
function doGet(e) {
var params = e.parameter;
var data = params.data;
var sheetUrl = params.sheetUrl;
var sheetTag = params.sheetTag;
var SpreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
var Sheet = SpreadSheet.getSheetByName(sheetTag);
//插入第一行空白
Sheet.insertRowBefore(1);
//寫入資料
data = data.split(',');
data.forEach(function(e,i){
Sheet.getRange(1, i+1).setValue(e);
});
return ContentService.createTextOutput(true);
}
當我們已經可以寫入試算表之後,就要把這個 Script 部署到網路上,這樣我們才可以和我們的智慧插座連動,部署的方法就是在右上角點選「發佈」,選擇「部署為網路應用程式」。
把存取權設定為「任何人,甚至是匿名者」。
按下部署之後,這段就是這個 Script 的網址,我們再來就會用它來儲存資訊。
到這邊基本上我們已經完成了主要的工作,再來就要做一個網頁,裡面放入幾個欄位,按下確認的時候就把欄位的內容存入試算表,先看到 HTML 的部分,就是三個欄位與一個按鈕,並載入 jQuery。
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title></title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
</head>
<body>
第一欄:<input id="a1"><br/>
第二欄:<input id="a2"><br/>
第三欄:<input id="a3"><br/>
<button id="b">儲存</button>
</body>
</html>
JavaScript 會透過 jQuery 的 get
來寫入資料,基本上程式碼和剛剛的 debug 差不多。
$(function() {
var $a1 = $('#a1'),
$a2 = $('#a2'),
$a3 = $('#a3'),
$b = $('#b'),
a = {};
$b.on('click', function() {
a = {
data: $a1.val()+','+ $a2.val()+','+$a3.val(),
sheetUrl: '你的試算表網址',
sheetTag: '工作表1'
};
console.log(a);
$.get('你的 Google Apps Script', a);
});
});
完成之後,在欄位輸入一些數值,按下儲存,就會發現試算表內已經有資料存入囉!
( 程式:http://bin.webduino.io/heraz/1/edit?html,css,js,output )
既然可以用網頁寫入資料,重點來了,我們就可以透過 Webduino 來和智慧插座上的燈泡串接,就可以記錄開關燈的資訊了,這邊我要使用的範例是「光敏電阻」,也就是感受到光的時候就會關燈,沒有光的時候就會亮燈,而 Google 試算表就是紀錄開燈的時間,以及關燈的時間。( 光敏電阻教學請參考我的鐵人賽系列文章:實戰智慧插座 06 - 你關燈我就開燈 ( 實戰小夜燈 ) )
接線圖:
JavaScript 的部分除了光敏電阻、LED 與剛剛的試算表程式宣告外,就是多了兩個變數,這兩個變數的目的在於避免試算表隨時都在記錄燈泡資訊 ( 因為類比訊號由 board.samplingInterval
設定,目前為 0.25 秒偵測一次 ),所以透過這兩個變數的判斷,可以只在開燈或關燈的時候紀錄。
var photocell , led , date , now;
var a = {};
var b = 0;
var c = 0;
var $show = $("#show");
boardReady('你的裝置 ID', function (board) {
board.systemReset();
board.samplingInterval = 250;
photocell = getPhotocell(board, 3);
led = getLed(board, 10);
photocell.on(function(val){
$show.text(val);
if(val<0.2){
b = b + 1;
c = 0;
led.on();
date = new Date();
now = date.getHours() + ':' + date.getMinutes() + ':' + date.getSeconds();
a.data = now+',"打開",'+val;
}else{
b = 0;
c = c + 1;
led.off();
date = new Date();
now = date.getHours() + ':' + date.getMinutes() + ':' + date.getSeconds();
a.data = now+',"關起來",'+val;
}
if(b==1||c==1){
a.sheetUrl = '你的試算表網址';
a.sheetTag = '工作表1';
$.get('你的 Google Apps Script',a);
}
});
});
執行之後,當燈泡亮起,相對應的資訊就會存入試算表,當燈泡熄滅,對應的資訊也一樣會存入。
( 程式:http://bin.webduino.io/besah/1/edit?html,js,output )
以上就是透過 Google 試算表存取燈泡開關資訊的做法,感覺可以應用的層面也是相當廣泛的,燈泡只是其中一種有趣的應用而已,明天將會繼續介紹讀取儲存格資料,點亮燈泡的做法。
參考: