Google 試算表 (3) - 網頁顯示儲存格數值

只有在 Google App Script 裡讀取到數值是不夠的,能夠透過網頁顯示數值,或是把這些數值做視覺化的呈現,才能夠發揮這些數值的價值,所以在這篇要來介紹如何透過網頁,讀取並呈現 Google 試算表內儲存格的數值。

使用 debug 來測試

回想一下,在 Google 試算表 (1) - 讀取試算表資訊 有介紹 debug 的用法,這裡我們把前一篇的程式碼做點小修改,變數的部分全部由 debug.gs 提供,這樣也才可以比較有彈性的指定不同儲存格或是指定不同試算表。

先看到 debug.gs,把要抓取的儲存格 row 與 column 號碼變成 parameter 的屬性,網址和工作表名稱也變成變數,執行後把 parameter 傳給 doGet 即可。

function debug() {
  doGet(
    {
      "parameter":{
        startRow:1,
        startColumn:1,
        url: "試算表網址",
        name: "工作表1"
      }
    }
  );
}

因為把網址和儲存格都改成變數了,程式碼.gs 理所當然也要做點修改,除了把網址、名稱和儲存格設為變數外,也把結束的儲存格設成變數,同時用個三元運算子判斷,如果我們提供的 parameter 沒有這個屬性,就自動將其設為 1,最後又因為我們要在外面的網頁去接資料,所以必須要利用ContentService.createTextOutput把資料拋出。

參考:Class ContentService

function doGet(e) {
  var params = e.parameter;
  var url = params.url;  
  var name = params.name;  
  var startRow = params.startRow; 
  var startColumn = params.startColumn; 
  var endRow = (!params.endRow)?1:params.endRow; // 如果沒有 endRow,就讓 endRow=1
  var endColumn = (!params.endRow)?1:params.endColumn;  // 如果沒有 endColumn,就讓 endColumn=1
  var SpreadSheet = SpreadsheetApp.openByUrl(url);
  var SheetName = SpreadSheet.getSheetByName(name);
  var data = SheetName.getSheetValues(startRow,startColumn,endRow,endColumn);
  Logger.log(data);
  return ContentService.createTextOutput(data); // 將資料透過 ContentService 拋出
}

正式開始前,先回到 debug.gs,點選上方三角形的執行,如果沒有問題,應該就可以在紀錄裡面看到取得 A1 儲存格的數值 123。

部署 Google App Script

要在外部的網頁透過 Google App Script 讀取資料,就得先把程式部署到網路上,第一個步驟是點選上方的「發佈 > 部署為網路應用程式」。

注意,如果要透過外部網頁操控,Google App Script 有規範名稱一定要是 doGet(e) 或 doPost(e),如果名稱不是這兩個請返回修改。參考:URL parameters

開啟部署畫面後,將具有應用程式存取權的使用者,設定為「任何人,甚至是匿名使用者」。

點選「部署」後,就完成部署的手續,這段應用程式的網址,就可以用來進行用網頁讀取試算表資料了。

網頁顯示試算表的資料

要使用網頁讀取,一開始先把 parameter 像剛剛 debug.gs 一樣,設定為物件,裡面屬性有試算表的網址、工作表名稱、startRow 和 startColumn,然後透過 jQuery 的$.get來把資料傳入並讀取,第一個參數放上剛剛 Google App Script 的部屬網址,第二個參數是要傳入的 parameter,第三個則是傳入之後,Google App Script 會拋回來數值,透過 console 印出。

因為使用$.get(),網頁要記得要載入 jQuery。

var parameter = {
  url: '試算表網址',
  name: '工作表1',
  startRow: 1,
  startColumn: 1
};
$.get('Google App Script 網址', parameter, function(data) {
  console.log(data);
});

網頁執行之後,應該就可以看到 console 的地方顯示 A1 儲存格的資料 123 了。

我們可以把彈性做得再大一點,在網頁 html 的部分放入一些欄位,這樣就可以用填入的方式填入網址、欄位...等資訊,而不是直接寫死在程式碼內,然後按下按鈕的時候,可以把資訊顯示在 h2 的標籤內。

Google App Script:<input id="appUrl"><br/> 
試算表網址:<input id="sheetsUrl"><br/> 
工作表名稱:<input id="sheetName"><br/> 
列:<input id="startRow"><br/> 
欄:<input id="startColumn"><br/>
<button id="sendBtn">查詢</button><br/>
<h2 id="show"></h2>

JavaScript 的地方一開始先把欄位、按鈕、顯示區域都宣告成 jQuery 變數,按下按鈕的時候,把欄位的數值傳給 parameter,然後執行$.get,在$.get裡面多加一個邏輯判斷,如果沒有資料,就顯示「無資料」,不然就把資料數值顯示出來。

var appUrl = $('#appUrl'),
    sheetsUrl = $('#sheetsUrl'),
  sheetName = $('#sheetName'),
  startRow = $('#startRow'),
  startColumn = $('#startColumn'),
  sendBtn = $('#sendBtn'),
  show = $('#show');
var parameter = {};

sendBtn.on('click',function() {
  parameter = {
    url: sheetsUrl.val(),
    name: sheetName.val(),
    startRow: startRow.val()*1,
    startColumn: startColumn.val()*1
  };
  $.get(appUrl.val(), parameter, function(data) {
      if(!data){
        show.text('無資料');
      }else{
        show.text(data);
      }
  });
});

沒有意外的話,執行網頁,填入對應的網址、欄位數字之後,按下查詢按鈕,就可以看到資料顯示在網頁裡了。

範例:demo-01.html

小結

可以用網頁查詢試算表資料是很方便的,因為在不會資料庫的情況下,我們也可以把 Google 試算表當作是簡單的資料庫使用,甚至透過網頁做出更多不同的變化。

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