2022年5月25日

操作 Sheet API 讀取 Google 試算表取得 JSON 資料

操作 Sheet API 讀取 Google 試算表取得 JSON 資料

Wayne Fu 0 A+
Google Sheet API」可用來存取 Google 試算表資料,但卻是我少數不推薦使用的 Google API,原因可參考之前寫的「利用 Google 試算表當小型資料庫 (4)使用 SQL 語法讓搜尋功能更強大」→「1. Sheet API 功能不佳」,因為 Sheet API 無法對資料進行篩選、搜尋,導致每次都得讀取所有資料後,再自己寫程式進行過濾。 然而去年 Google 試算表進行某次更新後,之前寫的「利用 Google 試算表當小型資料庫 (2)讀取資料庫」方法已然失效,無法從試算表 feed 讀取資料。根據官方的說法「GData API Directory」→「Google Spreadsheets Data API → Deprecated. Replaced by the Google Sheets API v4.」,意思就是 Google 宣布舊的試算表 API 已失效,建議即日起改用 Sheet API V4 取代原本的讀取方式。 因此本篇整理如何操作 Sheet API,取代原本的操作方式,來取得 JSON 格式的試算表資料。 (圖片出處: pexels.com)

一、Sheet API 準備動作

以下流程請參考這篇「取得 Google API Key(金鑰) 流程」進行操作: 1. 啟用 Sheet API
  • 按照該篇文章完成「一、建立專案」
  • 進行「二、啟用 Google API 服務」時,可輸入 Sheet API,找到後「啟用」
2. 取得 API KEY
  • 接著依照「三、取得 API Key」的流程,建立一組「API 金鑰」,請記住這組字串,之後會用到。
  • 如果這組「API 金鑰」在前端使用的話,會被使用者看到,那麼建議按「限制金鑰」進行其他設定。
  • 同上,為了避免金鑰被別人拿去用,建議「應用程式限制」設定為自己網址,「API 限制」設定為「Sheet API」才能使用。
3. 調整試算表共用權限 要讀取的試算表,記得要開放權限,不能只有自己才可讀取,至少要開放為「任何知道這個連結的網際網路使用者都能查看」。

二、Sheet API 讀取資料

1. 官網文件 讀取試算表資料的操作語法說明請見「spreadsheets.values.get」: GET https://sheets.googleapis.com/v4/spreadsheets/[試算表 ID]/values/[工作表名稱及範圍] 官方也提供了「Sheet 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 金鑰:填入前面「一、Sheet API 準備動作」取得的金鑰字串

三、操作範例

本篇同樣以這個試算表當做範例說明: 以下為 jQuery 範例程式碼: <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 試算表」相關文章:
0 0
如這篇文章對你有幫助,歡迎「分享」到 FB、「追蹤」粉絲團、「訂閱」最新文章

4 則留言:

  1. 請問這跟用AppScripts寫doGet doPost相比,哪個比較好?

    回覆刪除
    回覆
    1. 我覺得看需求 主要是我最近有個狀況想用簡便的方式 像以前一樣取得 feed json 資料
      sheet api 操作起來可以滿足我的需求 而且不需考慮安全性 (sheet api 在前端操作 試算表資料會被看光光)

      文章開頭有說 sheet api 的缺點 前端想要篩選的話 用 sheet api 就比較麻煩
      且 sheet api 寫入功能 看了說明書 覺得難用

      而 GAS 是後端每次有修改 得重新部署 但我覺得對試算表操作比較方便 而且可以確保安全性

      所以就看您怎麼抉擇囉

      刪除
  2. 終於找到一個我可以看懂的.....說明,謝謝您!
    但是我按照您上面給的測試工具
    沒有填入API KEY,為何一樣也能印出結果?

    回覆刪除
    回覆
    1. 官方測試工具會自動產生 api key 方便使用者測試
      自己寫的程式就要填 api key 了 否則 run 不了的~

      刪除

張貼留言注意事項:

◎ 勾選「通知我」可收到後續回覆的mail!
◎ 請在相關文章留言,與文章無關的主題可至「Blogger 社團」提問。
◎ 請避免使用 Safari 瀏覽器,否則無法登入 Google 帳號留言(只能匿名留言)!
◎ 提問若無法提供足夠的資訊供判斷,可能會被無視。建議先參考這篇「Blogger 提問技巧及注意事項」。
◎ CSS 相關問題非免費諮詢,建議使用「Chrome 開發人員工具」尋找答案。
◎ 手機版相關問題請參考「Blogger 行動版範本的特質」→「三、行動版範本不一定能執行網頁版工具」;或參考「Blogger 行動版範本修改技巧 」,或本站 Blogger 行動版標籤相關文章。
◎ 非官方範本問題、或貴站為商業網站,請參考「Blogger 免費諮詢 + 付費諮詢
◎ 若是使用官方 RWD 範本,請參考「Blogger 推出全新自適應 RWD 官方範本及佈景主題」→ 不建議對範本進行修改!
◎ 若留言要輸入語法,"<"、">"這兩個符號請用其他符號代替,否則語法會消失!
◎ 為了過濾垃圾留言,所有留言不會即時發佈,請稍待片刻。
◎ 本站「已關閉自刪留言功能」。

TOP