2016年11月30日

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

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


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

不過幾年下來,如果你的試算表已經累積了數千筆、甚至更多的資料,或許你需要的會是正規資料庫的 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 實在快速又簡單多了,只要一行就搞定,本篇說明將以這個方法為藍本。



二、準備動作


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


在開始之前,有三個準備動作:

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 = '女生' 編碼:


編完碼後,新的網址字串如下:

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 」,可以學到更多的應用方式。



六、製作圖表


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

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

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

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

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


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

2 則留言:

  1. 這篇真是太棒了!這會改變Backend as a Service或是Serverless的整個架構!
    能用SQL令人無比安心啊!

    回覆刪除
    回覆
    1. 看來這技術對布丁大有幫助~ ^^

      不過試算表還是只能當小型資料庫用,效能無法跟真的資料庫相比,也有所限制,將來相關的系列文會提到。

      但如果是教育方面的用途,例如老師給全班學生、或更多的同學使用,那麼是足夠的!

      刪除

張貼留言注意事項:

◎ 勾選「通知我」可收到後續回覆的mail!
提問請附網址、詳細描述狀況,如提供的資訊不足,則無法回覆。
◎ 請在相關文章留言,與文章無關的主題請至「Blogger 中文論壇」。
◎ 若詢問 CSS 、非官方範本問題、或貴站為商業網站 ,請參考「本站諮詢頁面」→「1. 諮詢服務」
◎ 若留言要輸入語法,"<"、">"這兩個符號請用其他符號代替,否則語法會消失!
◎ 若發現留言不見了,通常是因為 "複製貼上" 的內容常被系統判定為垃圾留言,請不用擔心,我會定期將留言恢復。
◎ 本站「已關閉自刪留言功能」。