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

A+
接續上一篇「用 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 操作試算表系列文章:

沒有留言:

張貼留言注意事項:

◎ 勾選「通知我」可收到後續回覆的mail!
◎ 請在相關文章留言,與文章無關的主題請至「Blogger 中文論壇」。
◎ CSS 相關問題非免費諮詢,建議使用「Chrome 開發人員工具」尋找答案。
◎ 手機版相關問題請參考「Blogger 行動版範本的特質」→「三、行動版範本不一定能執行網頁版工具」。
◎ 非官方範本問題、或貴站為商業網站 ,請參考「本站諮詢頁面」→「1. 諮詢服務」
◎ 若留言要輸入語法,"<"、">"這兩個符號請用其他符號代替,否則語法會消失!
◎ 為了過濾垃圾留言,所有留言不會即時發佈,請稍待片刻。
◎ 本站「已關閉自刪留言功能」。

TOP