Google 試算表 ( 原理 )

接下來的兩篇文章將會介紹「Google 試算表」( Google Spreadsheet ) 來實作儲存與讀取的功能,第一篇會介紹「儲存開關燈的時間與狀態資訊」,第二篇則會介紹「讀取某個儲存格,觸發智慧插座反應」。要使用 Google 試算表同樣的需要有一個 Google 的帳號,而操作的方式主要利用 Google Apps Script 來控制 Google 試算表,製作簡易資料庫。

參考:官方網站的 Reference

Google Apps Script

寫入資料到 Google 試算表

使用 Google Apps Script 的第一步,就是要把你的 Google 雲端新增 Google Apps Script,先點選「連結更多應用程式」( 點選「新增」就會看到 )

Google 雲端硬碟新增 Google Apps Script 應用程式

搜尋 app script,就會看到 Google Apps Script,點選後方「連接」,就可以加入雲端硬碟。

將 Google Apps 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 ),而獲取試算表的方法可以用 openByIDopenByUrl,如果是 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。

新增一個 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 試算表就是紀錄開燈的時間,以及關燈的時間。

接線圖:

智慧插座與光敏電阻接線圖

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 試算表中

小結

以上就是透過 Google 試算表存取燈泡開關資訊的做法,感覺可以應用的層面也是相當廣泛的,燈泡只是其中一種有趣的應用而已,明天將會繼續介紹讀取儲存格資料,點亮燈泡的做法。

參考: