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 官網手冊」。


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

3 則留言:

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

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

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

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

      刪除
  2. 以網址查詢,有官方說明 https://developers.google.com/chart/interactive/docs/querylanguage

    Query Language Reference (Version 0.7)

    The Google Visualization API Query Language lets you perform various data manipulations with the query to the data source.

    Table of Contents

    Introduction

    Typically, visualizations expect data in some specific form. For example, a pie chart may expect data as two columns: a text label and a numeric value. The data within the data source may not exactly match this structure. For example the data source may have more than two columns, or the order of the columns may not match the order expected by the pie chart.

    The query language provides the ability to send data manipulation and formatting requests to the data source, and ensure that the returned data structure and contents match the expected structure.

    The syntax of the query language is similar to SQL. Developers familiar with SQL should be able to quickly learn and use this query language. There are many SQL tutorials available on the Web. There are some differences between this query language and SQL which are described in the syntax section.

    Note that data sources are not required to implement the query language, or if they do, to implement all features of the language. Unless you have reason to believe otherwise, you should not depend on a data source to implement all features of this language.

    Using the Query Language

    You can attach a query string to a data source request in two ways: by setting the query string from within JavaScript code, or by setting the query string as a parameter in the data source URL. If your request does not include a query string, the default behavior for a data source is to return all rows and columns using its default row/column order and formatting. You can change that by including a query string in your request to a data source.

    Setting the Query from JavaScript

    To set the query string from within JavaScript code, call the setQuery method of the google.visualization.Query class.

    var query = new google.visualization.Query(DATA_SOURCE_URL);
    query.setQuery('select dept, sum(salary) group by dept');
    query.send(handleQueryResponse);
    Setting the Query in the Data Source URL

    The query string can be added to the data source URL using the tq parameter. Setting the query in the URL parameter instead of in JavaScript allows you to easily use visualizations written by other developers, and still be able to customize the query.

    回覆刪除

張貼留言注意事項:

◎ 勾選「通知我」可收到後續回覆的mail!
提問請附網址、詳細描述狀況,如提供的資訊不足,則無法回覆。
◎ 請在相關文章留言,與文章無關的主題請至「Blogger 中文論壇」。
◎ 若詢問 CSS 、非官方範本問題、或貴站為商業網站 ,請參考「本站諮詢頁面」→「1. 諮詢服務」
◎ 若留言要輸入語法,"<"、">"這兩個符號請用其他符號代替,否則語法會消失!
◎ 為了過濾垃圾留言,所有留言不會即時發佈,請稍待片刻。
◎ 本站「已關閉自刪留言功能」。