「Google Sheets API」可用來存取 Google 試算表資料，但卻是我少數不推薦使用的 Google API，原因可參考之前寫的「利用 Google 試算表當小型資料庫 (4)使用 SQL 語法讓搜尋功能更強大」→「1. Sheets API 功能不佳」，因為 Sheets API 無法對資料進行篩選、搜尋，導致每次都得讀取所有資料後，再自己寫程式進行過濾。
然而去年 Google 試算表進行某次更新後，之前寫的「利用 Google 試算表當小型資料庫 (2)讀取資料庫」方法已然失效，無法從試算表 feed 讀取資料。根據官方的說法「GData API Directory」→「Google Spreadsheets Data API → Deprecated. Replaced by the Google Sheets API v4.」，意思就是 Google 宣布舊的試算表 API 已失效，建議即日起改用 Sheets API V4 取代原本的讀取方式。
因此本篇整理如何操作 Sheets API，取代原本的操作方式，來取得 JSON 格式的試算表資料。
(圖片出處: pexels.com)
一、Sheets API 準備動作
以下流程請參考這篇「取得 Google API Key(金鑰) 流程」進行操作： 1. 啟用 Sheets API- 按照該篇文章完成「一、建立專案」
- 進行「二、啟用 Google API 服務」時，可輸入
Sheets API ，找到後「啟用」
- 接著依照「三、取得 API Key」的流程，建立一組「API 金鑰」，請記住這組字串，之後會用到。
- 如果這組「API 金鑰」在前端使用的話，會被使用者看到，那麼建議按「限制金鑰」進行其他設定。
- 同上，為了避免金鑰被別人拿去用，建議「應用程式限制」設定為自己網址，「API 限制」設定為「Sheets API」才能使用。
二、Sheets API 讀取資料
1. 官網文件 讀取試算表資料的操作語法說明請見「spreadsheets.values.get」：GET https://sheets.googleapis.com/v4/spreadsheets/[試算表 ID]/values/[工作表名稱及範圍]
官方也提供了「Sheets API 測試工具」，進入後切換到「HTTP」可看到以下語法：
GET https://sheets.googleapis.com/v4/spreadsheets/[試算表 ID]/values/[工作表名稱及範圍]?key=[API 金鑰]
自行輸入以上參數就可看到取回的資料，JSON 格式大致長得像這樣：
{
"range": "sheet1!A1:Z4850",
"majorDimension": "ROWS",
"values": [
[
"時間",
"稱謂",
"性別",
"註解"
],
[
"2016/11/29 0:00:00",
"Wayne",
"male",
"測試寫入功能"
],
[
"2016/11/29 0:00:00",
"Mary",
"female",
"成功！"
]
]
}
2. 參數說明
這裡說明前述的參數如何填寫：
- 試算表 ID：試算表網址字串中
~/d/xxxxxxxxxxxx/edit~ xxxx 這一長串就是試算表 ID - 工作表名稱及範圍：這部分的詳細說明可參考官網文件「Google Sheets API Overview」→「Cell」→「A1 notation」或「R1C1 notation」，最簡單就是填入工作表名稱就好，例如「工作表1」，代表選擇全部範圍
- API 金鑰：填入前面「一、Sheets API 準備動作」取得的金鑰字串
三、操作範例
本篇同樣以這個試算表當做範例說明：- https://docs.google.com/spreadsheets/d/106tP9D89pnEgvZTuM3_ahMJRPjCSD3qthth-GEhGMIE/edit?usp=sharing
<div id="wfu_sheet_json"></div>
<script src='//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js'></script>
<script>
var spreadsheet_id = "106tP9D89pnEgvZTuM3_ahMJRPjCSD3qthth-GEhGMIE", // 填入試算表 ID
tab_name = "sheet1", // 填入工作表名稱
api_key = "xxxxxxxxxx", // 填入 API 金鑰
url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheet_id + "/values/" + tab_name + "?key=" + api_key;
$.getJSON(url, function(json) {
var values = json.values, // 所有試算表資料
html = "";
html += "<table>";
values.forEach(function(rows) {
html += "<tr>";
rows.forEach(function(item) {
html += "<td>" + item + "</td>";
});
html += "</tr>";
});
html += "</table>";
$("#wfu_sheet_json").html(html);
});
</script>
以下是程式碼的執行效果範例，只取前 5 筆顯示：
| 時間 | 稱謂 | 性別 | 註解 |
| 2016/11/29 0:00:00 | Wayne | male | 測試寫入功能 |
| 2016/11/29 0:00:00 | Mary | female | 成功！ |
| 2017/01/12 16:06:13 | |||
| 2017/01/13 0:01:00 | test | fe | jj |
用 Google Apps Script 操作試算表系列文章：
更多「Google 試算表」相關文章：
請問這跟用AppScripts寫doGet doPost相比，哪個比較好？
回覆刪除終於找到一個我可以看懂的.....說明，謝謝您！
回覆刪除但是我按照您上面給的測試工具
沒有填入API KEY，為何一樣也能印出結果？