2011年11月21日

利用 Google 問卷(試算表)當小型資料庫__(二)讀取、儲存資料庫

A+

(Pic from: veryicon.com)
接續「上一篇」,本篇的內容為讀取、儲存資料庫的 Step by Step。

2013. 2.6 公告

由於 Google Form(問卷表單) 近日改版,儲存資料的格式跟舊版不同,因此本文的步驟只適用於舊版。若使用新版的 Google 問卷當資料庫,請參考「利用 Google 問卷(試算表)當小型資料庫__更新版__(二)讀取、儲存資料庫」的操作步驟。



一、讀取資料庫

Blogger 要讀取外部資料數據,比較方便的方式為使用 Json 格式,基本上 Google 旗下的產品大多可轉換為 Json 格式,例如 Blogger 的後台資料庫像文章、留言、標籤、網址等等都能用 Json 撈,還好之前為了「全文列表(TOC)+讀者反應系統」 這樣的功能把 Json 給弄懂了,現在要處理 Google 試算表的資料,再度使用 Json 已經不陌生了(如果對 Json 不瞭解,想有個基本概念,可參考以下網頁:「Json 參數、範例」、「應用 JSON 實現最新文章、最新回應和Blog聯播」)。如果不懂 Json 也沒關係,直接看本文的圖片及說明也能簡單知道 Json 的資料儲存方式及叫出方式,以下為讀取 Json 的實作 step by step:

1. 轉換為 Json 格式:根據「上一篇」→「三、製作資料庫」→ 第 10 個步驟,我們得到「https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/basic?alt=rss」這樣的字串。

根據「使用 Google Spreadsheets API 搭建在线应用」 以及「Using Google spreadsheets as your database」,我們將這一行改為以下字串:

https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/values?alt=json-in-script
重點為 A. 將「basic」改為「values」,可得到進一步的數據; B. 將「rss」改為「json-in-script」,資料便轉換為 Json 格式了。


2. 簡單分析 Json 內容:將改過的字串貼到網址列,按下 Enter 瀏覽器會將資料庫的內容轉換為以下 Json 格式的內容:

gdata.io.handleScriptLoaded({"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$gsx":"http://schemas.google.com/spreadsheets/2006/extended","id":{"$t":"https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/values"},"updated":{"$t":"2011-11-20T14:32:51.745Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"工作表1"},"link":[{"rel":"alternate","type":"text/html","href":"https://spreadsheets.google.com/pub?key\u003d0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E"},{"rel":"http://schemas.google.com/g/2005#feed","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/values"},{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/values?alt\u003djson-in-script"}],"author":[{"name":{"$t":"waynefu.G"},"email":{"$t":"waynefu.g@gmail.com"}}],"openSearch$totalResults":{"$t":"1"},"openSearch$startIndex":{"$t":"1"},"entry":[{"id":{"$t":"https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/values/b8lvi"},"updated":{"$t":"2011-11-20T14:32:51.745Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"2011/11/20 下午 4:46:09"},"content":{"type":"text","$t":"url: http://wayne-fu.blogspot.com/, ipush: 1, name: WFU, text: 使用問卷表格"},"link":[{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/values/b8lvi"}],"gsx$時間戳記":{"$t":"2011/11/20 下午 4:46:09"},"gsx$url":{"$t":"http://wayne-fu.blogspot.com/"},"gsx$ipush":{"$t":"1"},"gsx$name":{"$t":"WFU"},"gsx$text":{"$t":"使用問卷表格"}}]}});
大部分內容看不懂無所謂,只要注意紅字、藍字部分即可:

我們發現問卷中四個問題的標題剛好都接在 "gsx$" 這樣的字串後面,該標題後面跟著的資料就是我們當初輸入的內容,如此要抓資料就很簡單了。因此,四個問題的資料分別存在以下的位置:

json.feed.entry[0].gsx$問題標題.$t
A. 「entry」是儲存每組資料的陣列,後面的括弧[0]為輸入問卷的第一組資料,[1]代表第二組資料、[2]代表第三組資料,以此類推。

B. 我們的問卷設定幾個問題,每組資料就有幾筆資料,例如 entry[0] 共有四筆資料,分別為:

json.feed.entry[0].gsx$url.$t
json.feed.entry[0].gsx$ipush.$t
json.feed.entry[0].gsx$name.$t
json.feed.entry[0].gsx$text.$t



3. 印出資料庫內容:接下來使用以下簡單的 javascript 就能呼叫試算表資料轉換的 Json 格式,呈現在自己的 Blogger:


B:設定處理 Json 的函數名為「Test」,括弧內的參數建議設定為 json。

C:變數「a」抓出資料庫內儲存的資料組數。

E~H:設定變數「b~e」抓出每筆資料的儲存欄位

J:印出每筆資料

O:最重要的一行,綠色字串就是步驟 1 我們更改的字串;紅色字串代表把抓出來的 Json 格式資料傳送給要執行的函數;藍色字串必須填上處理 Json 資料的函數,就是 B 行的 Test 函數。

可以開一篇文章貼上程式碼看看跑出來的結果。


4. 額外參數:使用 Google Spreadsheets API 搭建在线应用」這篇文章還提到一個非常實用的參數「?q=value」,可以篩選出我們需要的資料,而不必讀入所有的資料,這大大提升了處理的效率,例如我們要篩選出所有含「"WFU"」字串的每組資料,第 3 點中呼叫 Json 的網址可改為「https://spreadsheets.google.com/feeds/list/0AikclfTTti-0dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E/od6/public/values?q=WFU&alt=json-in-script」,注意網址列每個參數要用 "&" 隔開。

如果我們要在 Blogger 做一個「推文系統」,由於每個文章網址必須儲存各自的推文,那麼叫出資料時總不能整個 Blogger 所有的文章的推文用 Json 全部叫出吧,那會花比較多處理時間,此時使用「?q=value」參數加入文章網址篩選,就能大大增加處理效率。


二、寫入資料庫

這是最困難的一部份了,千辛萬苦搜尋到這篇文章「How do I send external data from a non-Google form field into a Google Spreadsheet to be stored? 」才終於有了眉目。


1. 利用網址傳送資料:根據此文提到的方法,在網址列貼上以下字串可寫入資料:

https://docs.google.com/spreadsheet/formResponse?hl=en_US&formkey=dFRjT0hwYXNXbVRfUU9KN1hDSkk3MlE6MQ&ifq&entry.0.single=Test&submit=Submit
我們將之做點修正,刪掉「hl=en_US」,把「formkey=」後面改為我們自己的試算表代號「dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E6MQ」,這個代號就是「上一篇」→「三、製作資料庫」→ 第 2 個步驟得到的試算表身份證;「entry.0.single=」後面的 "Test" 就是要寫入的字串,修正後的字串如下:

https://docs.google.com/spreadsheet/formResponse?formkey=dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E6MQ&ifq&entry.0.single=Test&submit=Submit
將以上字串貼到網址列按下 Enter,再回到試算表一看:



真是太棒了,真的可以寫入資料! "Test" 字串出現在第一欄「url」下方。


2. 一次傳送所有資料:不過我們一組資料共需要四個欄位的資料,那要怎麼一次輸入四筆呢?試著舉一反三,猜測「entry.1.single」、「entry.2.single」、「entry.3.single」應該就是代表第二~第四欄位的儲存格,將上一步驟的網址字串改為以下:

https://docs.google.com/spreadsheet/formResponse?formkey=dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E6MQ&ifq&entry.0.single=1&entry.1.single=2&entry.2.single=3&entry.3.single=4&submit=Submit
貼到網址列按下 Enter 後,再回到試算表一看:



Bingo! 這下找到聖杯了,儲存 Google 試算表的訣竅雖然最難發掘,實作上反而是最簡單的一部份。


3. 動態傳送資料:那麼要怎麼在 Blogger 中將資料傳到網址列並按下 Enter 送出呢?此時可以利用 Iframe 標籤,開一個 Iframe 視窗,先不設網址(src=''),等到要送出資料時,用 getElementById 的方式,命令此 Iframe 的網址為上一步驟的網址,就可以將資料寫入資料庫了。如果不熟 javascript 或許比較難理解此概念,請參照本文最後一章「四、資料庫的應用」來看這些動作怎麼實作了。


4. 小提醒 1:不過當初可不是這麼簡單就找到聖杯,因為一開始在製作問卷時,不小心按了「複製」的圖示來增加問題,結果導致「entry.2.single」、「entry.3.single」第三與第四個儲存格根本無法寫入任何資料,永遠是一片空白。因此再度殷切提醒,千萬要「手動」一筆筆增加問卷的問題,那麼寫入資料時才不會發生儲存格無法寫入的慘劇!


5. 小提醒 2: 必須注意的是這個小型資料庫並非萬能,除了 Google 有容量、欄位數的限制,如果輸入的字串有 "&"、"#"、"%" 符號,資料無法正常儲存(在網址列的以上三個字元字元有特別意義),因此在寫入資料庫之前,必須用 javascript 的 escape 函數轉換字元,資料才能正常儲存。


三、備份資料庫

學會本篇的技巧後,Blogger 的擴充性變得有無限的可能,小到計數器、統計流量,大到留言板、聊天室、討論區等等,只要擁有資料庫都是做的出來的,不過為了效能著想,Google 試算表的資料庫還是用於小型功能的程式比較好。

為了預防萬一,定期備份資料庫的資料是必須的動作,可參考此網頁安裝「Google Docs文件免費備份軟體」,讓自己放心一些。


四、資料庫的應用

除了前面提到的應用,WFU 想到一個實用的功能──把 PTT 著名的推文系統搬上 Blogger,如此只有一、兩句的留言就可以改用推文,不必再佔用一篇留言了,實作以及程式碼請參考此篇「Blogger留言系統 __(八)推文功能安裝說明


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


1. Google資料庫方案–Spreadsheet–(1)資料呈現
2. Using Google spreadsheets as your database
3. 使用 Google Spreadsheets API 搭建在线应用
4. How do I send external data from a non-Google form field into a Google Spreadsheet to be stored?

8 則留言:

  1. 多謝版主分享這兩篇文章,非常精彩!!
    文中提到還有"本文最後一章「四、資料庫的應用」",遍尋不著三四章, 可否佛心補足, 一次看個過癮!!

    感恩!!

    回覆刪除
  2. <6132124261370805575>(以上內容請勿刪除)hi, 關於資料庫的應用,就是文中提到的「推文功能」,文中已經附上超連結,請直接對著「Blogger留言系統 __(八)推文功能安裝說明」按下去就可以看到這篇文章了。

    回覆刪除
  3. 其實entry.2.single等等,是表格中的input的name

    回覆刪除
  4. 我們將之做點修正,刪掉「hl=en_US」,把「formkey=」後面改為我們自己的試算表代號「dHlSSnRRR0w5eGFzRExRTWRjbWNLN0E6MQ」,這個代號就是「上一篇」→「三、製作資料庫」→ 第 2 個步驟得到的試算表身份證;

    hi WFU
    這一段我沒看明白,試算表的代號是試算表的action的值嗎?我嘗試將ACTION的值填進去但是提示頁面無法找到。是我找錯這個試算表的代號還是GOOGLE改變的原因導致無效了?

    回覆刪除
  5. <3595656323597810747>(以上內容請勿刪除,從括號之後開始留言)
    請看文章開頭的公告
    也請看更新版開頭的公告

    回覆刪除
  6. 呵呵,其實表單中form的網址是不會變的,
    我的即時聊天室到現在表單還是正常的 : )

    回覆刪除
  7. <6797274313055058886>(以上內容請勿刪除,從括號之後開始留言)您的聊天是是我以前觀摩的對象啊 ^^
    Google 試算表已經改版好幾次,現在算是第三代,這篇第一代舊的方法若要套用到新的試算表,得另外重新研究了~

    回覆刪除
  8. 這個分享真的太棒了

    回覆刪除

張貼留言注意事項:

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