Google 試算表 (6) - 網頁寫入資料

經過上一篇的介紹，要把資料寫入 Google 試算表中應該不困難了，接下來就要來修改 Google App Script，讓我們可以透過網頁，將資料寫入 Google 試算表中，真的把試算表拿來當作資料庫儲存資料使用。

parameter 資料格式

在寫入資料的時候，在 debug.gs 裡面是正常，但要用網頁寫入就會被卡住，追根究底下去發現原來是因為資料格式導致，因為要寫入資料的時候，會向應用程式發送 HTTP GET 的請求，所以必須要按照 Google 所提出的規範來撰寫。

參考：URL parameters

回想一下在 Google 試算表 (4) - 寫入資料到儲存格 裡面有提到setValues()是用二維陣列儲存，但透過網頁傳值如果真的傳陣列，反而是收不到資料的，因此在傳值的時候要把資料變成「字串」傳遞，接收到資料之後再把字串轉換成陣列。

舉例來說，如果要傳二維陣列的資料進到試算表內，就可以先用.toString()把陣列變成字串，進到 Google App Script 的時候再用.split(',')變成陣列丟給試算表就可以，後面會詳細介紹。

var data = [
  ['123', '456', '789'],
  ['x', 'y', 'z']
];
var parameter = {};
parameter.data = data.toString();

Google App Script

建立一個 doGet(e) 的流程，裡面放入一些預設的變數來接資料：url 試算表的網址、name 工作表的名稱、d 原始資料字串格式、row 要插入幾列、column 每一列有幾欄、insertType 如果是 top 就是從最上方插入列，不然就是將列插入在最下方、lastRow 是最後一列的列數，如果要插入在最下方將會使用，最後在宣告三個變數 range、data、arr 待會會用到。

function doGet(e) {
  var params = e.parameter;
  var url = params.url;
  var name = params.name;
  var SpreadSheet = SpreadsheetApp.openByUrl(url);
  var SheetName = SpreadSheet.getSheetByName(name);
  var d = params.data;   // 原始 data，字串格式
  var row = params.row;  // 由原始 data 判斷需要插入幾列
  var column = params.column; // 由原始 data 判斷每一列有幾欄
  var insertType = params.insertType;  // 插入在上方，或插入在下方
  var lastRow = SheetName.getLastRow();  // 讀取最後一列的列數
  var range, data, arr;
}

接著放入一個邏輯判斷，如果有多個列或是多個欄，基本上一定會有「,」，所以如果是有多個列或欄，range 也要跟著做對應，也就在這邊進行判斷，判斷之後就是寫入資料。

function doGet(e) {
  var params = e.parameter;
  var url = params.url;
  var name = params.name;
  var SpreadSheet = SpreadsheetApp.openByUrl(url);
  var SheetName = SpreadSheet.getSheetByName(name);
  var d = params.data;   // 原始 data，字串格式
  var row = params.row;  // 由原始 data 判斷需要插入幾列
  var column = params.column; // 由原始 data 判斷每一列有幾欄
  var insertType = params.insertType;  // 插入在上方，或插入在下方
  var lastRow = SheetName.getLastRow();  // 讀取最後一列的列數
  var range, data, arr;

  if(d.indexOf(',')!=-1){
    arr = d.split(','); // 把原始資料用 , 分割成陣列
    data=[];
    for(var i=0; i<row; i++){
      data[i]=[];
      for(var j=0; j<column; j++){
        data[i].push(arr[i*column+j]); // 藉由 row 和 column 變成二維陣列
      }
    }
  }else{
    data = [[d]];
  }
}

最終完成的程式碼如下，加入 insertType 的判斷，如果是 top 就從第一列插入，至於要插入幾列就由 row 來決定，如果是 bottom 就從最後一列插入，最後還要記得放入return ContentService.createTextOutput(true);。

function doGet(e) {
  var params = e.parameter;
  var url = params.url;
  var name = params.name;
  var SpreadSheet = SpreadsheetApp.openByUrl(url);
  var SheetName = SpreadSheet.getSheetByName(name);
  var d = params.data;   // 原始 data，字串格式
  var row = params.row;  // 由原始 data 判斷需要插入幾列
  var column = params.column; // 由原始 data 判斷每一列有幾欄
  var insertType = params.insertType;  // 插入在上方，或插入在下方
  var lastRow = SheetName.getLastRow();  // 讀取最後一列的列數
  var range, data, arr;

  if(d.indexOf(',')!=-1){
    arr = d.split(','); // 把原始資料用 , 分割成陣列
    data=[];
    for(var i=0; i<row; i++){
      data[i]=[];
      for(var j=0; j<column; j++){
        data[i].push(arr[i*column+j]); // 藉由 row 和 column 變成二維陣列
      }
    }
  }else{
    data = [[d]];
  }

  if(insertType=='top'){
    SheetName.insertRowsBefore(1,row);
    range = SheetName.getRange(1,1,row,column);
  }else if(insertType=='bottom'){
    range = SheetName.getRange(lastRow+1,1,row,column);
  }

  range.setValues(data);

  return ContentService.createTextOutput(true);
}

完成主程式之後，就來寫個 debug.gs 測試，內容寫上要傳遞給 doGet 的數值，row 和 column 是由資料 data 自動計算出來的。

function myFunction() {
  var data = [['123','456','789']];
  doGet({
    parameter:{
    url: '試算表網址',
    name: '工作表名稱',
    data: data.toString(),
    insertType: 'bottom',
    row:data.length,
    column:data[0].length
    }
  });
}

在 debug.gs 點選上方三角形的執行按鈕，就可以看到資料插入在試算表原本內容的下方。

如果把 insertType 的 bottom 換成 top，就會在上方插入。

網頁寫入資料

如果剛剛上面都沒問題，先把寫好的程式碼部署到網路上，將存取權設定為「任何人，甚至是匿名使用者」。

部署完成之後，就要來看看網頁端怎麼進行，網頁一開始在 HTML 先載入 jQuery，然後放入一些欄位，包含試算表網址、Google App Script 網址、工作表名稱、寫入方式...等，資料的話預設寫入一列三欄 ( 因為用 input 寫入陣列要做一些轉換，這邊就用最簡單的方法展示 )。

Google App Script：<input id="appUrl"><br/> 
試算表網址：<input id="sheetsUrl"><br/> 
工作表名稱：<input id="sheetName"><br/> 
寫入方式：
<select id="type">
  <option value="top">從上寫入</option>
  <option value="bottom">寫入在最後一列</option>
</select>
<br/> 
資料：
<table>
  <tr>
    <td>123</td>
    <td>456</td>
    <td>789</td>
  </tr>
</table>
<button id="sendBtn">寫入</button>

JavaScript 和剛剛的 debug.gs 大同小異，主要就是把欄位的資料帶入 parameter，最後用$.get的方法送出，完成後，填入對應的網址，點選送出，應該就可以看到資料出現在試算表內了。

範例：demo-01.html

var appUrl = $('#appUrl'),
  sheetsUrl = $('#sheetsUrl'),
  sheetName = $('#sheetName'),
  type = $('#type'),
  sendBtn = $('#sendBtn'),
  show = $('#show');
var data = [['123','456','789']];
var parameter = {};
sendBtn.on('click', function() {
  parameter = {
    url: sheetsUrl.val(),
    name: sheetName.val(),
    data: data.toString(),
    row: data.length,
    column: data[0].length,
    insertType: type.val()
  };
  $.get(appUrl.val(), parameter);
});

小結

其實寫入資料到試算表內沒有想像的麻煩，只要了解資料的格式，在對這些格式做點轉換，就可以把試算表拿來當作資料庫使用囉。

有興趣瞧瞧其他新文章嗎？