利用 Google 試算表當小型資料庫 (4)使用 SQL 語法讓搜尋功能更強大

「利用 Google 試算表當小型資料庫」系列文已經寫過三篇，條列如下：

• (1)製作資料庫
• (2)讀取、儲存資料庫
• (3)簡易查詢與排序 (及新版試算表讀取流程)

上一篇提供了篩選資料的簡易查詢語法，以及對資料排序的方法，相信這對小型的資料庫處理已經足夠。

不過幾年下來，如果你的試算表已經累積了數千筆、甚至更多的資料，或許你需要的會是正規資料庫的 SQL 搜尋語法，才能處理更複雜的需求，甚至是將整理出來的資料以圖表方式呈現，在網頁呈現即時的長條圖、分布圖等等。

這麼多複雜的功能，其實 Google 都已經幫我們把輪子造好了，不必自己研究，就看我們如何取用。那麼本篇就來說明如何呈現 HTML 效果、以及如何用 Javascript 操作。

(圖片出處: pixabay.com)

一、Visualization API 簡易調用方式

1. Sheet API 功能不佳

Google 試算表官方提供的資料處理介面稱為 Sheet API，使用這個 API 可以做到讀取、寫入功能，不過這個 API 我給的評價不高，因為：

• 比起系列文前兩篇，操作方式更麻煩。
• 在功能上來講，能做的事沒幾樣。
• 沒有搜尋(query)功能，連簡易查詢都沒有。
• 而且目前還出到 V4 版，感覺上其他管道不但可以取代這個 API，還能做的更好。

2. Visualization API 可進行 SQL 查詢

還好 Google 另外出了「Visualization API」，除了可以使用資料庫 SQL 語法，還能將查詢結果製成各種圖表，功能實在太強大了，可以說是前端工程師必備技能！

引用 Visualization API 的準備動作很多，正規作法需要預先載入不少程式庫，例如：

• Google JSAPI API
• Google Visualization library
• 圖表模組

網路上有很多教學文章，預先載入模組的方式都差不多，有興趣可參考這篇「在Blogger顯示Google試算表的QUERY查詢結果」。


3. Visualization API 簡易呼叫法

在網路上找到一篇神奇的文章「Google Spreadsheet JSON API: SQL Filtering」，作者不是用正規方法引用程式庫，而且這方法也沒有在官方文件看到，也許是 Google 工程師測試用留的後門。

使用這方法呼叫 Visualization API 實在快速又簡單多了，只要一行就搞定，本篇說明將以這個方法為藍本。

二、準備動作

本篇將以這個「會員系統」的試算表進行舉例，範例網址如下：

• https://docs.google.com/spreadsheets/d/18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c/edit#gid=0

在開始之前，有三個準備動作：

1. 試算表代號：打開你的試算表，看到網址在 "/d/" ~ "/edit" 的字串，就是「試算表代號」。以上面的範例網址為例，就是 "18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c" 這樣的字串，請紀錄下來，之後會用到。

2. 工作表代號：如果你要使用不是第一個工作表，那麼請記下試算表的工作表代號，也就是網址 "#gid=" 之後的數字。以上面的範例網址為例，就是 "0" 這個數字，請紀錄下來，之後會用到。(若不是第一個工作表，就會是一長串數字)

3. 試算表開啟共用：沒有開啟共用的話，Visualization API 將無法存取這個試算表，因此請將試算表右上角「共用」的權限，改為「知道連結的人均可檢視」就行了。

三、SQL 搜尋結果以網頁呈現

1. 網址基本格式

首先說明如何用 Visualization API 叫出試算表資料，並呈現在網頁上：

https://spreadsheets.google.com/tq?tqx=out:html&tq=填入SQL語法&key=填入試算表代號&gid=填入工作表代號
上面網址有三個紅色字串，填入對應的代號及語法後，即可看到效果。


2. 試算表資料範例

https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c&gid=0
上面的範例網址，沒有填入 SQL 語法，將這網址貼到瀏覽器，就可看到這個範例試算表的所有資料。

如果想要將表格貼到自己的網頁或部落格，可以放在 IFRAME 之中，例如以下的語法：

<iframe src="https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c&gid=0" style="width: 600px; height: 500px;"></iframe>
藍色字串請置換為自己的網址語法。

綠色數字請改成 IFRAME 想要呈現的寬、高尺寸。

效果大致如下：




3. 使用 SQL 語法

https://spreadsheets.google.com/tq?tqx=out:html&tq=select * where E = '女生'&key=18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c&gid=0
上面的範例網址，填入了 SQL 語法，意思是搜尋 E 欄性別為 '女生' 的所有資料。

由於 SQL 語法會用到一些比較特別的符號、全形文字、或是中文，放在網址有可能會出錯，因此 SQL 語法的全部內容，最好經過編碼處理，例如使用這個線上服務，將這個字串 select * where E = '女生' 編碼：

• http://www.url-encode-decode.com/

編完碼後，新的網址字串如下：

https://spreadsheets.google.com/tq?tqx=out:html&tq=select+%2A+where+E+%3D+%27%E5%A5%B3%E7%94%9F%27&key=18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c&gid=0
將這網址貼到瀏覽器，就可看到這個篩選後的試算表範例。

若放在 IFRAME 之中，效果大致如下：

四、用 JS 操作 SQL 搜尋結果

接下來示範如何用 jQuery 來操作，使用 SQL 語法取得的試算表資料。

<script src='//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js'></script>
<div id="test"></div>
<script>
(function(window, $) {
var sheetID = "18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c", // 試算表代號
gid = "0", // 工作表代號
sql = "select+%2A+where+E+%3D+%27%E5%A5%B3%E7%94%9F%27", // SQL 語法
callback = "callback"; // 回呼函數名稱

$.getScript("https://spreadsheets.google.com/tq?tqx=responseHandler:" + callback + "&tq=" + sql + "&key=" + sheetID + "&gid=" + gid);

window[callback] = function(json) {
var rowArray = json.table.rows,
rowLength = rowArray.length,
html = "",
i, j, dataGroup, dataLength;
for (i = 0; i < rowLength; i++) {
dataGroup = rowArray[i].c;
dataLength = dataGroup.length;
for (j = 0; j < dataLength; j++) {
if (!dataGroup[j]) {
continue;
}
html += (dataGroup[j].f || dataGroup[j].v || "") + " ";
}
html += "<br/>";
}
$("#test").html(html);
};
})(window, jQuery);
</script>
以上這段程式碼範例，同樣來自「三、SQL 搜尋結果以網頁呈現」→「3. 使用 SQL 語法」，是從會員系統試算表範例中，篩選出所有性別為 "女生" 的資料，在網頁上顯示。將全部程式碼貼在你的網頁，便可看到效果。

比較重要的參數修改，說明如下：

• 紅色字串請改為自己的試算表代號
• 藍色數字請改為自己的工作表代號
• 綠色字串請改為 SQL 語法，如果包含特殊符號的話，請重新編碼，並參考「五、SQL 語法注意事項」。

五、SQL 語法說明

1. 注意事項

Visualization API 所使用的 SQL 語法，有其特別需要注意的地方，不一定能完全照一般 SQL 語法的使用方式，因此請詳讀以下的內容：

• SELECT、WHERE 等指令，不能使用自己命名的欄位名稱，只能選擇 Google 試算表預設的欄位代號 A、B、C... 等等代號。
• 而且欄位代號不能用小寫，一定要使用大寫的 A、B、C...，否則篩選不到任何資料。
• 例如 "SELECT A, C, E WHERE B > 50" 是正確的語法；"SELECT name,sex WHERE age > 20" 是錯誤的語法
• 儲存格中的資料，放在 SQL 語法中比對時，字串的大小寫有分別。
• SQL 參數有數字時不需要括號，參數使用字串時，前後一定要用括號包住。
• 由於 SQL 語法要通過網址來執行程式，為了符合網址字串的規範，建議一律進行編碼，可使用「三、SQL 搜尋結果以網頁呈現」→「3. 使用 SQL 語法」這裡提供的線上編碼工具，否則程式有很大的機率無法正常執行。

2. Google 官方教學

本篇提供的 SQL 語法範例相當簡單，但其實 SQL 可以執行很多複雜的功能、函數，來進行比較高階的搜尋方式，例如取某欄位中最大值的資料、篩選特定字串格式的資料、去除沒有資料的儲存格等等。

詳細的說明可以參考 Google 官方文件「Query Language Reference 」，可以學到更多的應用方式：

• SELECT 省略將取得所有欄位；WHERE 省略將取得每一列。
• Order By 可排序
• LIMIT 可限制取得筆數；OFFSET 可略過指定列數；但兩個一起始用時必須 LIMIT 在前、OFFSET 在後
• 儲存格中的資料，放在 SQL 語法中比對時，字串的大小寫有分別。

六、製作圖表

用 JS 執行 SQL 語法，取得試算表資料後，更進階、實用的作法，是將資料轉換成各種圖表，運用在各種簡報上。不過這部分因為與本篇主題無關，這裡簡單帶過就好。

要製作圖表的話，必須另外載入製表的模組，那麼無法經由本篇介紹的「Visualization API 簡易調用方式」來做到。如開頭提到的，必須一一載入 Google JSAPI API、Google Visualization library、圖表模組才行，建議參考以下流程：

1. 這篇「在Blogger顯示Google試算表的QUERY查詢結果」使用正規方法載入各種官方模組，從試算表使用 SQL 語法篩選資料，用表格的方式印出資料。

2. 上一點載入的是表格模組，如果載入各種圖表模組，就能將資料以各種圖表顯示，可參考這篇「Google Chart 製作各種圖表」。

3. 更詳細的語法操作說明，可參考「Google Visualization API 官網手冊」。

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

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

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