Kadence

Google 試算表 ( 前後端實作 )

已經會寫入資料到試算表之後，接著就是要讀取指定儲存格的資料，並用這些資料來操控智慧插座上的燈泡。

讀取指定儲存格內容

詳細準備步驟可以參考上一篇 Google 試算表 ( 原理 )，主要步驟是先建立一份試算表，然後隨便打一些內容，包含文字與數字。

建立一份試算表

權限設定為「知道連結的人都可以檢視」。

修改權限設定

建立 Google Apps Script 專案，把檔名設定為 test-read。

建立 Google Apps Script 專案

程式一開始將 myFunction 改為 doGet，內容同樣使用 e.parameter 來接資料，資料有四個屬性，分別是 sheetUrl 試算表網址、sheetTag 試算表標籤名稱、row 縱列、col 橫欄。

function doGet(e) {
  var params = e.parameter;
  var sheetUrl = params.sheetUrl;
  var sheetTag = params.sheetTag;
  var row = params.row;
  var col = params.col;

  var SpreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
  var Sheet = SpreadSheet.getSheetByName(sheetTag);

  //主程式
}

接著透過 getSheetValues 儲存格的值，getSheetValues 有四個屬性，前兩個是起始儲存格的欄位，後面兩個則是向下以及向右延伸的格子數量，如果都設定為 1，就可以單純獲取當前儲存格的值，獲取之後用一個 return ContentService.createTextOutput(data); 回傳值。

var data = Sheet.getSheetValues(row, col, 1,1);
if(data[0][0].length<1){
  data = '沒有資料啦';
}

return ContentService.createTextOutput(data);

測試的方法就是寫一個 debug 程式來檢驗看看。

function debug() {
  var a = doGet( {
    parameter : {
      sheetUrl : '你的試算表網址',
      sheetTag : '工作表1',
      row: 1,
      col: 2
    }
  });
  Logger.log(a);
}

點選執行 ( 如果有跳出權限驗證就全部都允許就對了 )，從「檢視紀錄」裡面就可以看到抓到 (1,2) 儲存格的內容 oxxo 了。

從「檢視紀錄」可取得 (1,2) 儲存格的內容

讀取範圍內的儲存格內容

如果要獲取一個範圍的儲存格，只要稍微做點變化就可以，例如在 parameter 的屬性多增加 endRow 和 endCol 就是很簡單的做法，只不過這邊要多使用 .getLastRow() 和 .getLastColumn() 的方法，這樣避免我們自己指定的範圍會超過試算表資料的範圍。

function doGet(e) {
  var params = e.parameter;
  var sheetUrl = params.sheetUrl;
  var sheetTag = params.sheetTag;
  var row = params.row;
  var col = params.col;
  var endRow = params.endRow;
  var endCol = params.endCol;
  var rowRange = endRow - row + 1; //因為自己也要包含所以 + 1
  var colRange = endCol - col + 1;

  var SpreadSheet = SpreadsheetApp.openByUrl(sheetUrl);
  var Sheet = SpreadSheet.getSheetByName(sheetTag);

  var lastRow = Sheet.getLastRow();
  var lastCol = Sheet.getLastColumn();

  if(rowRange>lastRow){
    rowRange = lastRow;
  }

  if(colRange>lastCol){
    colRange = lastCol;
  }

  var data = Sheet.getSheetValues(row, col, rowRange,colRange);

  return ContentService.createTextOutput(data);
}

當然輸入進去的物件也必須有 endRow 和 endCol。

function debug() {
  var a = doGet( {
    parameter : {
      sheetUrl : '你的試算表網址',
      sheetTag : '工作表1',
      row: 1,
      col: 2,
      endRow : 2,
      endCol : 3
    }
  });
  Logger.log(a);
}

點選執行，從「檢視紀錄」裡面就可以看到抓到 (1,2) 到 (2,3) 儲存格的內容了。

從「檢視紀錄」可取得 (1,2) 到 (2,3) 儲存格的內容

網頁讀取資料

要讓網頁使用就要先部署，部署請選擇給「任何人，甚至是匿名者」使用。

設定應用程式存取權

$(function(){
  var $show = $('#show');
  var a = {
      sheetUrl : '你的試算表網址',
      sheetTag : '工作表1',
      row: 1,
      col: 2,
      endRow : 2,
      endCol : 3
  };
  $.get('你的 Google Apps Script',a, function(data){
    var d = data.split(',');  //把傳出來的字串分割成陣列
    var arr = [];
    for(var i=0; i<(a.endRow-a.row+1); i++){
      arr[i] = d.splice(0, (a.endCol-a.col+1));
      $show.append(arr[i]+'<br/>');
    }

  });
});

打開網頁後，應該就可以在網頁上看到對應的儲存格資料了。

打開網頁，在網頁上看到對應的儲存格資料

結合智慧插座

已經可以從網頁上顯示資料後，接著就要透過 Webduino 和智慧插座結合，目標是如果儲存格內的數值如果大於 60，就讓智慧插座亮燈，首先來寫 HTML 的部分，先引入 jQuery、webduino-all.min.js 和 webduino-blockly.js，然後放入一個 div 來顯示數值。

<!doctype html>
<html>

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
  <title>Webduino Blockly Demo 01</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script src="https://webduino.io/components/webduino-js/dist/webduino-all.min.js"></script>
  <script src="https://blockly.webduino.io/webduino-blockly.js"></script>
</head>

<body>
  <div id="show">數值顯示在這邊</div>
</body>

JavaScript 的部分跟剛剛網頁讀取資料一樣，差別只在於加入了裝置的 boardReady，判斷如果這張試算表內，有大於 60 的分數，就顯示姓名與分數。

$(function(){
  var $show = $('#show');
  var a = {
      sheetUrl : '你的試算表網址',
      sheetTag : '工作表1',
      row: 1,
      col: 1,
      endRow : 10,
      endCol : 2
  };
  $show.text('資料載入中...');
  $.get('你的 Google Apps Script',a, function(data){
    var d = data.split(',');  //把傳出來的字串分割成陣列
    var arr = [];
    for(var i=0; i<(a.endRow-a.row+1); i++){
      arr[i] = d.splice(0, (a.endCol-a.col+1));
    }
    $show.text('裝置準備中...');
    boardReady('你的裝置 ID', function (board) {
      $show.text('');
      board.systemReset();
      board.samplingInterval = 250;
      led = getLed(board, 10);
      led.off();
      setTimeout(function(){
      arr.forEach(function(e){
        if(e[1]>60){
          led.on();
          $show.append(e+'<br/>');
        }
      });
      },1000);
    });

  });
});

讓裝置上線，打開網頁，就可以看到網頁已經自動去抓取試算表資料，並把大於 60 分的人名與分數顯示在網頁上，同時燈泡也被點亮。

當然如果你不想用燈泡，想要透過其他方式顯示數值的話，Webduino 也有提供 LED 點矩陣的用法，首先一樣先把資料抓到網頁裡，透過資料產生對應的按鈕，接著就可以在點選按鈕的時候，由 LED 點矩陣和網頁同時顯示分數囉！

$(function(){
  var $show = $('#show');
  var $body = $('body');
  var a = {
      sheetUrl : '你的試算表網址',
      sheetTag : '工作表1',
      row: 1,
      col: 1,
      endRow : 6,
      endCol : 2
  };
  $show.text('資料載入中...');
  $.get('你的 Google Apps Script',a, function(data){
    var d = data.split(',');  //把傳出來的字串分割成陣列
    var arr = [];
    for(var i=0; i<(a.endRow-a.row+1); i++){
      arr[i] = d.splice(0, (a.endCol-a.col+1));
      $body.append('<button value='+arr[i][1]+'>'+arr[i][0]+'</button>');
    }
    $show.text('裝置準備中...');
    boardReady('你的裝置 ID', function (board) {
      $show.text('顯示分數');
      board.systemReset();
      board.samplingInterval = 250;
      matrix = getMax7219(board, 9, 10, 11); //宣告 LED 點矩陣
      matrix.on("0000000000000000"); //讓 LED 點矩陣全黑
      $('button').on('click',function(){
        var score = this.getAttribute('value');
        matrix.on((max7219_number(score))); //透過 LED 點矩陣顯示分數
        $show.text(score);
      });
    });

  });
});

網頁抓取 google 試算表資料並點亮智慧插座燈泡

小結

到這邊為止，我們應該已經可以輕鬆自如的把 Google 試算表當作簡單的資料庫來使用，並且更進一步和實際的裝置結合囉！

參考：

分享

本篇內容由 Webduino 提供，感謝您的閱讀！