iT邦幫忙

2022 iThome 鐵人賽

0
Software Development

學 Python 到底可以幹麻勒?系列 第 36

( Day 36 ) 讀取 Google 試算表

  • 分享至 

  • xImage
  •  

Google 試算表是 Google 提供的線上 excel 服務,不僅能雲端編輯儲存,更能配合 Apps Script 當作簡單的資料庫使用,這篇教學將會介紹如何透過 Python 串接 Google 試算表,實現讀取試算表資料的功能。

原文參考:讀取 Google 試算表

本篇使用的 Python 版本為 3.7.12,所有範例可使用 Google Colab 實作,不用安裝任何軟體 ( 參考:使用 Google Colab )

Python 教學 - 串接 Google 試算表

編輯 Apps Script

開啟 Google 雲端硬碟,新增一個 Google 試算表檔案。

Python 教學 - 串接 Google 試算表

在儲存格輸入一些內容後,點擊上方「擴充功能 > Apps Script」,開啟與這份試算表連動的 Apps Script。

Python 教學 - 串接 Google 試算表

開啟 Apps Script 的編輯畫面後,複製下方的程式碼貼入「程式碼.gs」裡,如果試算表中「工作表」的名稱有更動,請修改程式碼內「工作表1」的名稱,完成後,點擊上方「執行」按鈕 ( Apps Script 撰寫的語言為 JavaScript )。

javascript >>>>
function doGet(e) {
  var SpreadSheet = SpreadsheetApp.getActive();         // 讀取目前的試算表
  var SheetName = SpreadSheet.getSheetByName('工作表1'); // 開啟工作表1
  var data = SheetName.getSheetValues(1,1,SheetName.getLastRow(),SheetName.getLastColumn());
  // 取得所有資料,組成 JSON 的形式,用純文字回傳
  Logger.log(data)  // 印出資料 ( 第一次執行時必須有這一行 )
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}

Python 教學 - 串接 Google 試算表

如果是第一次執行,會出現需要授權的畫面,點擊「審查權限」。

Python 教學 - 串接 Google 試算表

點擊「進階設定」,點擊「前往未命名的專案 ( 不安全 )」( 因為這個應用程式是自己開發的,尚未通過審核,所以會出現警告視窗 )

Python 教學 - 串接 Google 試算表

點擊後,點擊「允許」這個應用程式存取試算表的資料。

Python 教學 - 串接 Google 試算表

完成後就能在應用程式裡,看見讀取到的試算表資料。

Python 教學 - 串接 Google 試算表

部署 Apps Script

確認能讀取資料後,點擊右上方「部署」,選擇「新增部署作業」。

Python 教學 - 串接 Google 試算表

點擊設定的齒輪圖示,設定為「網頁應用程式」。

Python 教學 - 串接 Google 試算表

設定「誰可以存取」為「所有人」,點擊「部署」。

Python 教學 - 串接 Google 試算表

部署成功後,會看到一串網址,表示可以使用 Get 的方法呼叫的網址 ( 因為剛剛 Apps Script 使用 doGet 的方法 )。

Python 教學 - 串接 Google 試算表

使用瀏覽器開啟網址,就能看到試算表的資料。

Python 教學 - 串接 Google 試算表

Python 讀取 Google 試算表

開啟 Colab,輸入下方的程式碼,執行後就能透過 Python requests 函式庫的 get 方法,讀取 Google 試算表的所有資料。

參考:Requests 函式庫

import requests
web = requests.get('你的應用程式網址')
print(web.json())

Python 教學 - 串接 Google 試算表

Apps Script 加入參數設定

修改 Apps Script 程式碼,加上可以讀取網址參數的功能,就能指定讀取某個範圍的資料,或讀取不同工作表的資料。

function doGet(e) {
  var SpreadSheet = SpreadsheetApp.getActive();
  var params = e.parameter;                           // 讀取網址參數
  var name = params.name || '工作表1';                 // 如果有 name 就使用,否則 name 等於「工作表1」
  var SheetName = SpreadSheet.getSheetByName(name) ;  // 讀取工作表名稱為 name 的資料
  var start_row = params.start_row || 1;              // 如果有 start_row 就使用,否則 start_row 等於 1
  var start_col = params.start_col || 1;              // 如果有 start_row 就使用,否則 start_col 等於 1
  var row = params.row || SheetName.getLastRow() - start_row + 1;   // 如果有 row 就使用,否則 row 等於 SheetName.getLastRow()
  var col = params.col|| SheetName.getLastColumn() - start_col + 1; // 如果有 col 就使用,否則 col 等於 SheetName.getLastColumn()
  var data = SheetName.getSheetValues(start_row,start_col,row,col);  // 使用變數
  Logger.log(data)
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON); 
}

更新部署後,就可以使用下方 Python 程式,讀取特定工作表或特定範圍的資料。

import requests
url = '你的應用程式網址'
name = '工作表1'
row = 2
web = requests.get(f'{url}?name={name}&row={row}')
print(web.json())
name = '工作表2'
web = requests.get(f'{url}?name={name}')
print(web.json())

Python 教學 - 串接 Google 試算表

更新部署 Apps Script

如果有修改 Apps Script,直接部署會發生奇怪的現象 ( 讀取到舊的檔案、無法讀取檔案...等 ),建議按照下列步驟重新部署:

  • 修改後,點擊上方「存檔」按鈕存檔。

    Python 教學 - 串接 Google 試算表

  • 封存正在進行中的 Apps Script。

    Python 教學 - 串接 Google 試算表

  • 重新部署 Apps Script。

  • 如果還是不行,等待一分鐘後,重新執行上述三點步驟。

參考資料

更多 Python 教學

大家好,我是 OXXO,是個即將邁入中年的斜槓青年,我已經寫了超過 400 篇 Python 的教學,有興趣可以參考下方連結呦~ ^_^


上一篇
( Day 35 ) 串接 Gmail 寄送電子郵件
下一篇
( Day 37 ) 寫入 Google 試算表
系列文
學 Python 到底可以幹麻勒?41
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言