2014年10月16日

利用 Google 試算表當小型資料庫 (3)簡易查詢與排序 (及新版試算表讀取流程)

A+

(Pic from: veryicon.com)
免費的部落格平台,結合 Google 試算表後,使用簡單的 javascript 操作,就可對試算表資料庫進行存取,在網站上實現各種應用,例如初期發表的「部落格即時留言板」,一直到近期的「會員系統」。

不過資料庫若只做到儲存與寫入,則無法發揮其長處。如能對大量資料進行排序及查詢,那麼網頁就能滿足訪客的各種特定需求,例如從龐大的手機商品資料庫中,篩選出「iPhone」這個型號的列表,接著使用排序功能,依照價格由高至低排列,諸如此類的情境。以下將舉實例來說明,如何使用試算表資料庫,來做出簡易的查詢及排序。



一、Google 試算表的讀取速度


1. 基本觀念

基本上本篇文章設定給懂 js 的族群,因此說明的算是進階性的解決方案,所以假設讀者也會一點簡單的 jQuery、處理過 Google 試算表資料庫。

那麼懂 js 的讀者或許會問一個問題:「對 Google 試算表的資料做查詢及排列,只要會寫 js 就做得到,需要特別說明這件事嗎?」的確是如此,使用了系列文第二篇「利用 Google 表單[試算表]當小型資料庫 (2)讀取、儲存資料庫」的語法後,把所有資料讀取出來,再用 js 就能做查詢及排序。


2. 試算表的讀取速度

在資料量不大的情形下,的確每次處理試算表,只要將全部資料讀取出來再做處理即可。不過根據 WFU 的經驗,只要試算表的資料超過一、兩千筆時,那麼讀取的速度就非常能感受出來(的慢)。

其實,每次都讀取全部資料也不是正確的處理方式,我們可以想像一下,到了通訊行只想瞭解「iPhone」的相關機型,但店員總是搬出所有手機的樣品,這樣做事就太沒效率了。

因此,正確的作法是在資料庫端就把資料篩好,再送到前端網頁來處理。請倉管人員預先挑選出正確的樣品,才不會讓門市人員也做到累趴。本文就來看看,這樣的流程需要什麼技巧。




二、讀取新版試算表


由於前幾天 Google Drive 全面將所有舊版試算表 "強制升級",這陣子有使用試算表的讀者應該會陸續發現各種異狀。所以再花一點篇幅說明如何讀取最新版的試算表。

本文以最近的「會員系統」試算表資料為例,刪除所有個資後,這個範例檔的網址如下:



開啟我們預備要讀取的試算表檔案後,依序按下「檔案」→「發佈到網路」→「發佈」,會出現下圖新版試算表的畫面:



注意圖中 "/d/ " ~ "/pubhtml" 之間的字串,如紅色箭頭所指的字串範圍,就是這個試算表的 key 代碼。例如本文的範例檔 key 代碼為 "18sp-S2iOQ9uRiuLprZZUDf9emipGesZ81Gfy5LPIR6c",請記住自己的試算表 key 代碼。

範例檔的內容如下面這個內嵌檔案:




如果想用 js 讀取這個試算表,以下是一個 jQuery 的範例:


請參照以上程式碼行號:

  • C:紅色字串請改為自己的試算表 key 代碼
  • G:這一行有五個藍色字串,取自範例檔的其中五個欄位名稱。必須注意的是,這五個字串,在程式碼中所有出現的位置,請各自改為自己想要顯示的試算表欄位名稱
  • J~N:這五行的藍色字串記得也要改到。

可以開一篇空白文章貼上程式碼,就可看到效果。需要注意的是,試算表裡欄位名稱最好全部是英文小寫、不要有空格,避免程式執行出錯。

而其原理為了篇幅就不多著墨,請直接參考上一篇的說明「利用 Google 表單[試算表]當小型資料庫 (2)讀取、儲存資料庫」→「二、讀取資料庫」。



三、基本網址篩選參數


以下我們要模擬倉管人員的各種工作,試算表資料庫提供了基本的篩選功能,舊版的說明也曾有提及,這裡簡單帶過一下。

程式碼 C 行的紅綠字串,為讀取試算表的網址。傳送回來的 json 資料,是該試算表的全部資料。如果我們接在紅綠字串之後,加上 &q=wayne fu 這樣的參數,代表篩選出包含 "wayne fu" 這樣的字串(大小寫不區分)的所有資料。

範例檔 20 筆資料中只有一筆包含這個字串,因此前面程式碼的執行結果,將如同下圖一般:



這也代表我們前端不必接收其餘無用的 19 筆資料。



四、指定欄位昇冪降冪排序


根據最新版的「Google Sheets API 3.0」,我們還有三種參數可以使用,來做各種篩選、排序的動作。首先介紹指定欄位排序。

orderby=column:欄位名稱
reverse=true

  • orderby 這行的參數代表所有資料依據該欄位名稱來排序,預設為昇冪排序。
  • reverse 這行的參數為 true 時,代表改為降冪排序。去除這行的參數、或是改為 false,就是昇冪排序。

看實例很快就能理解,程式碼 C 行,如果我們接在紅綠字串之後,加上以下參數:

&orderby=column:register&reverse=true

代表所有資料依照 register 這一欄、使用降冪排序。前面程式碼的執行結果,將如同下圖一般,資料將依照註冊時間,從最新的一筆開始列出





五、中階篩選參數


前面介紹的基本篩選參數 q,只能篩選特定字串,也就是只能做精確搜尋。如果我們要做商品陳列的功能,讓訪客自訂各種篩選條件,那就辦不到了。

Google Sheets API 提供了 sq 這個篩選參數,不但可以做邏輯判斷式,還可多欄位篩選。以下是官方提供的例子:

age > 25 and height < 175
這代表篩選出的資料,符合欄位 "age" 大於 25,且欄位 "height" 小於 175。

還是來看實例比較快理解,程式碼 C 行,如果我們接在紅綠字串之後,加上以下參數:

&sq=serial>W00010 and sex=女生
代表篩選出 "序列號" 大於 "W00010"、且 "性別" 為 "女生" 的所有會員。不過以上參數的執行結果是無效的,因為含有中文字元。

我們可到這個網址進行轉碼,貼上所有字串後,按下「URL編碼」即可:


重新編碼後的參數如下:

&sq=serial>W00010 and sex=%E5%A5%B3%E7%94%9F

程式碼的執行結果,將如同下圖一般:





六、小結


本文介紹的各種參數,還可同時合併使用,做出更多變化。不過若讀者使用過資料庫 SQL 查詢語法的話,其實本文的這些參數,其功能相較之下算是小巫見大巫。

Google 試算表其實也支援 SQL 語法,不過這算是另一個完全不同的系統。如果本文的這些參數還無法滿足需求的話,下一篇將會介紹支援 SQL 的語法體系使用方式。


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

10 則留言:

  1. 大師您好、您的文章讓我非常受用、但小的有一事不解
    &sq=serial>W00010
    以上這個篩選方法,可以根據""沒有值""的參數欄位篩選嗎?
    我有試過 serial!=null and serial!=undefined
    但好像無法成功篩選
    而serial!='',好像沒有辦法使用
    還請您抽空回復、謝謝您

    回覆刪除
    回覆
    1. 你好,說明書沒有提到的用法,如果試的出來,那恭喜你賺到了。
      如果試不出來,那代表就是沒有這樣的用法囉!

      刪除
  2. 我試著加入到Google Site中的HTML方塊,沒有出現錯誤訊息,可是完全沒有資料顯示。
    請問這段CODE是否可以在Google Site上使用? 謝謝。

    回覆刪除
    回覆
    1. 我沒研究 google site, 因此這件事請自行測試可行性囉
      你可以先在blogger測試,如果沒問題的話,那就是 google site 的問題。
      如果在 blogger 就有問題,那可能是你的 code 沒寫好、或試算表沒設定好

      刪除
  3. 想不到google試算表有這麼強大,還支持sql.

    感覺上blog之類應該也能勝任...

    帳號存sqlife,文章留言存google試算表.

    回覆刪除
    回覆
    1. 您可能誤會了,「六、小結」這裡提到的是 "Google 試算表其實也支援 SQL 語法" → 是支援 SQL 語法,並不是說 google 試算表像真的資料庫、或可以像正規資料庫那樣運作喔,請還是把它當成一個像 EXCEL 試算表一樣的東西比較好。

      刪除
  4. 請問… 上面那個示範的例子是網頁一打開就自動執行撈資料顯示~
    如果是要改在網頁上用一個BUTTON,作送出查詢的話(執行script),應該怎麼改…
    麻煩了~^^

    回覆刪除

張貼留言注意事項:

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