用 Google Apps Script 操作試算表 (2)搜尋、讀取資料庫

接續上一篇「用 Google Apps Script 操作試算表 (1)製作資料庫 + 寫入資料」的流程後，本篇來看如何用 Google Apps Script (GAS) 搜尋、讀取寫入試算表的資料。

(圖片出處: pixabay.com)

一、使用 GAS 搜尋試算表的方案

1. 試算表內建函數

很不幸的，試算表沒有內建任何跟搜尋有關的 GAS 函數，我想這無可厚非，畢竟是免費的服務，如果把搜尋功能做得太強，那會把 Google 自己的伺服器搞得累死，還沒有錢賺。

不得已，關於利用 GAS 來搜尋試算表的資料，就要看個人的功力了，多半只能每次都讀取整個試算表的資料，然後慢慢篩出自己要的資訊來。


2. BigQuery

另外一個選擇是引用 Google 提供的「BigQuery API」，那麼就可以使用 SQL 語法來查詢試算表資料，程式寫起來比較方便。

不過 BigQuery 在免費使用下是有每日配額限制的，可參考官網「Quota Policy」，可以想成每日免費查詢 1000 次(細節以官網為準)。

在使用量不大的情況下，引用 BigQuery API 是 ok 的，那麼可參考這篇「Big Query And Google Spreadsheet Integration」的教學來操作。

如果使用量非常大的話，那麼我相信你的服務是可以賺錢的，花點錢提升 Google 的配額限制，是應該的、且划算的。

二、使用 GAS 讀取試算表

本篇在免費的前提下，未採用 BigQuery 的查詢方式(避免偶爾的流量爆衝而導致網頁程式失效)，改為提供一個簡易的查詢範例。

1. read_data 函數

上一篇的 GAS 程式碼範例，寫了兩個函數：doPost 及 write_data。我們可以接在原本的程式碼後面，新增一個 read_data 函數，內容如下：

function read_data(para) {
var query = para.query,
rowLength = sheet1.getLastRow() - 1, // 列數
columnLength = sheet1.getLastColumn(), // 欄數
allData = sheet1.getRange(2, 1, rowLength, columnLength).getValues(), // 取得所有儲存格資料
queryData, queryMessage, i;

for (i in allData) {
if (allData[i].indexOf(query) > -1) {
queryData = allData[i];
break;
}
}

queryMessage = "稱謂：" + queryData[1] + " 性別：" + queryData[2] + " 註解：" + queryData[3]; // 回傳字串
return ContentService.createTextOutput(queryMessage);
}
簡單說明一下運作方式：

• 利用試算表內建函數找出儲存格的欄數、列數
• 撈出所有儲存格資料
• 逐列比對，如果某列包含了要搜尋的字串，取出該列所有資料
• 返回處理過的字串，包含該列儲存格資料
• 字串要怎麼處理，請自行修改程式碼
• ContentService.createTextOutput() 的使用方法，後面會說明。

2. 呼叫 read_data

接著是呼叫 read_data 這個函數，以下是修改後的 doPost 函數內容：

function doPost(e) {
var para = e.parameter, // 存放 post 所有傳送的參數
method = para.method;

if (method == "write") {
write_data(para);
}
if (method == "read") {
return read_data(para);
}

}
其實就是新增紅字這一行就可以了，這行程式碼會回應 post 請求，送出查詢結果的字串，返回網頁端。

接下來的步驟請參考「上一篇」→「四、部署為網路應用程式」的流程，將新的 GAS 版本發佈為網路應用程式。

三、用 jQuery 送出 post 請求

以下範例程式碼，用 jQuery 的 Ajax 方法來傳送 post 請求，查詢範例試算表中，包含儲存格有 "Mary" 字串這一列的資料：

<script src='//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js'></script>
<script>
$.ajax({
type: "post",
data: {
"method": "read",
"query": "Mary"
},
url: "https://script.google.com/macros/s/AKfycbxN6sBikcLtDNxzZU1-hrylbGUzJ1qD2yXjZjmiw9ra7hPHPgo/exec", // 填入網路應用程式網址
success: function (e) {
alert(e);
}
});
</script>
以上 url 參數紅色網址字串，請改為自己的網路應用程式網址。

method、query 等參數名稱，需要與 GAS 的設定一樣。

想要測試效果，直接執行以上程式碼即可，查詢成功時，瀏覽器會 alert 訊息，也就是前面 GAS 所設定返回的字串訊息，就像下面這個範例試算表中，"Mary" 那一行的資料。

四、讀取的資料型態

現在來說明 ContentService.createTextOutput() 這個內建函數要如何使用。

1. 回傳字串：回傳的資料如果需要是 "字串" 型態的話，使用預設的語法 ContentService.createTextOutput(字串) 來處理即可。

2. 回傳其他型態的資料：實際上，大部分會需要回傳的資料型態，往往不是字串這麼簡單。官網的說明文件「Content Service」提供了很詳細的操作說明，例如 RSS、JSONP 等。

如果是 JSON 物件，對應的回傳語法為：

ContentService.createTextOutput(JSON.stringify(這裡是整理好的 JSON 物件)).setMimeType(ContentService.MimeType.JSON)

如果處理 JSONP，對應的回傳語法為：

ContentService.createTextOutput("回呼函數字串(" + JSON.stringify(這裡是整理好的 JSON 物件) + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT)

以上 GAS 最重要的讀取、搜尋、寫入功能，都已說明完畢，下一篇會說明其他處理試算表要注意的地方。

用 Google Apps Script 操作試算表系列文章：

製作試算表資料庫系列文章：

• 利用 Google 試算表當小型資料庫 (1)製作資料庫__更新
• 利用 Google 試算表當小型資料庫 (2)讀取、儲存資料庫__更新
• 利用 Google 試算表當小型資料庫 (3)簡易查詢與排序 (及新版試算表讀取流程)
• 利用 Google 試算表當小型資料庫 (4)使用 SQL 語法讓搜尋功能更強大