這幾天在寫一個新的遊戲，自訂的題庫打算直接讀取 Google 試算表的內容，所以重新研究了一下如何利用 JavaScript 擷取試算表的資料。時間投入了，又有新的發現，去年玩「雄: HTML5 FUN:自製遊戲啟動器」用過的東西，感覺好像忘了不少，還是把一些關鍵的東西記錄一下比較保險。
擷取試算表資料的網址
Google 試算表只要在共用的設定中，將「一般存取權」中，「知道連結的任何人」設為「檢視者」，就會變成「任何知道這個連結的網際網路使用者都能查看」的權限。以此共用方式，我們就可以利用 JavaScript 來擷取試算表中的某一個工作表。用類似這樣的網址來擷取：
https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/gviz/tq?tqx=out:json&tq
網址中，{SPREADSHEET_ID} 是 Google 試算表的 ID，例如我月來當成語題庫的試算表，它的共用網址為：
https://docs.google.com/spreadsheets/d/1kBueULlojPOH9E3EZYEUcUAv1HfJm_wULQT1hT2m1nM/edit?usp=sharing
{SPREADSHEET_ID} 就是在「/d/」和「/edit?」之間那一長串藍色粗體的字串。
想要直接擷取試算表的資料，就組合為這樣的網址：
https://docs.google.com/spreadsheets/d/1kBueULlojPOH9E3EZYEUcUAv1HfJm_wULQT1hT2m1nM/gviz/tq?tqx=out:json&tq
試算表擷取的資料分析
如果我們以前面所述的網址，可以下載試算表中最左側，第一個工作表的資料。
內容會類似這樣：
/*O_o*/
google.visualization.Query.setResponse({..........});
觀察一下，第一行的 /*O_o*/ ，看起來是不是就是 JavaScript 的註解；
而 google.visualization.Query.setResponse( ) ，是不是就是一個 function ？
我們要的資料是在小括號中的 {..........}
是的！它是 JSON 的格式，也就是試算表的資料會被當成一個參數，傳給名為 google.visualization.Query.setResponse 的 function。
以字串處理來取得資料
如果我們是用檔案下載的方式來取得，回傳的會是字串(假設給了 data 變數)，這樣子我們可以用這樣的指令來取得文字內容：
data.substring(47, data.length - 2)
從第 47 的大括號「{」，取子字串到倒數第 3 字的大括號「}」，因為結果是文字型態，所以還得再利用 JSON.parse 來將字串型轉換為物件，整合起來的指令為：
JSON.parse( data.substring(47, data.length - 2) )
以 JSONP 來取得資料
前面分析過，我們擷取的資料，其實內容就是 JavaScript 的指令，如果將內容指定給一個網頁面頁中的 script ，接收完資料，瀏覽器會呼叫「google.visualization.Query.setResponse」，並將試算表的資料傳給它，因此我是利用 JSONP 呼叫 callback 來取得並處理資料，先寫了個 callback ，再利用下面幾行來讓它們建立關係：
if(typeof(google)=='undefined') google = {};
if(typeof(google['visualization'])=='undefined') google['visualization'] = {};
if(typeof(google['visualization']['Query'])=='undefined') google['visualization']['Query'] = {};
google['visualization']['Query']['setResponse'] = callback;
牛刀小試
底下來做個小實驗，可以在 Google 雲端硬碟中建立一個試算表，輸入一些資料以後，將該檔案設定為「任何知道這個連結的網際網路使用者都能查看」的共用方式，並複製連結，貼到底下的輸入區後按「擷取」鈕，看看能不能抓到資料。
試算表共用網址:
轉換後的網址:
試算表的資料： 縮排：
擷取指定的工作表
一個試算表中，可以有多個工作表，如果照前面格式，只修改了 {SPREADSHEET_ID} 而成的網址，它是抓取試算表最左側的工作表，想要擷取別的工作表，就需要利用 sheet 或是 gid 的參數來指定。這兩個參數如果同時放到網址中，我的操作經驗，是看哪一個放在前面，就優先使用該參數。
使用 sheet 參數來擷取指定名稱 {SHEET_NAME} 的工作表資料：
https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/gviz/tq?tqx=out:json&tq&sheet={SHEET_NAME}
例如我前面的成語題庫試算表有「工作表2」，我可以用這樣的網址來擷取資料：
https://docs.google.com/spreadsheets/d/1kBueULlojPOH9E3EZYEUcUAv1HfJm_wULQT1hT2m1nM/gviz/tq?tqx=out:json&tq&sheet=工作表2
使用 gid 參數來擷取指定代號{SHEET_ID} 的工作表資料：
https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/gviz/tq?tqx=out:json&tq&gid={SHEET_ID}
例如當我在編輯前面成語題庫試算表「工作表2」時，它的網址是：
https://docs.google.com/spreadsheets/d/1kBueULlojPOH9E3EZYEUcUAv1HfJm_wULQT1hT2m1nM/edit#gid=543013940
網址中有 gid=543013940 ，所以 543013940 就是{SHEET_ID}，我可以用這樣的網址來指定要擷取資料的工作表：
https://docs.google.com/spreadsheets/d/1kBueULlojPOH9E3EZYEUcUAv1HfJm_wULQT1hT2m1nM/gviz/tq?tqx=out:json&tq&gid=543013940
以這個試算表來說，因為「sheet=工作表2」和「gid=543013940」指的都是同一個工作表，所以抓到的資料都是一樣的。
擷取 SQL 指令篩選過的資料
在前面擷取資料的網址中，是不是都有一個參數「tq」？
如果想要先以 SQL 語篩選試算表中的資料，再擷取，可以用 tq={SQL} 指令的方式來進行篩選。預設不指定。例如我只想找出 B 欄成語有「一」這個字的，可以用這樣的語法：
select * where B like '%一%'
例如我只想要回傳 C 欄的資料就好，就用這樣的語法：
select B
例如只想回傳前 10 筆資料，就用這樣的語法：
select * limit 10
此部份有興趣可以再研究。
手動設定「標題」有幾列
在 Google 試算表回傳的資料中有一個 parsedNumHeaders ，它會指出有多少列資料是「標題」，資料會被放到 cols 各欄的 label 中，而 rows 中就少了那幾列。
我們可以在請求資料的網址中利用 headers 的參數來設定有多少資料要當「標題」。
所以如果有使用 gid 參數，又使用 headers 參數，就會變成類似這樣網址：
https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/gviz/tq?tqx=out:json&tq&gid={SHEET_ID}&header={HEADERS}
上面的 {HEADERS} 置換成想設定的數字即可。
進階測試版
試算表共用網址:
工作表名稱：
工作表識別碼(gid)：
SQL：
轉換後的網址:
試算表的資料： 縮排：
資料內容
試算表傳回的資料，幾個重要的項目：
- status : 如果內容是 "ok"，表示有擷取到資料。
- table
- cols
- id : 欄位的代碼 A、B、C...。
- label : 如果 parsedNumHeaders = 1，內容就是最上面一列的內容；如果 parsedNumHeaders = 0，label 會是空字串。
- rows : 一列列儲存格的資料，要注意 parsedNumHeaders 的值，不然會有少幾列的情形。
- parsedNumHeaders : 是否有取某幾列當欄位的「標題」，當它不等於 0 時，rows 會少那幾列內容，因為拿去放到 cols 中了，這一部份要注意。
發表文章
沒有留言:
張貼留言