读取 Google 试算表

Google 试算表是 Google 提供的线上 excel 服务，不仅能云端编辑储存，更能配合 Apps Script 当作简单的数据库使用，这篇教学将会介绍如何透过 Python 串接 Google 试算表，实现读取试算表数据的功能。

快速导览：

• 编辑 Apps Script
• 部署 Apps Script
• Python 读取 Google 试算表
• Apps Script 加入参数设定
• 更新部署 Apps Script
• 参考数据

本篇使用的 Python 版本为 3.7.12，所有范例可使用 Google Colab 实作，不用安装任何软件 ( 参考：使用 Google Colab )

编辑 Apps Script

打开 Google 云端硬盘，新增一个 Google 试算表文件。

在储存格输入一些内容后，点击上方“扩充功能 > Apps Script”，打开与这份试算表连动的 Apps Script。

打开 Apps Script 的编辑画面后，复制下方的程序码贴入“程序码.gs”里，如果试算表中“工作表”的名称有更动，请修改程序码内“工作表1”的名称，完成后，点击上方“执行”按钮 ( Apps Script 撰写的语言为 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);
}

如果是第一次执行，会出现需要授权的画面，点击“审查权限”。

点击“进阶设定”，点击“前往未命名的专案 ( 不安全 )”( 因为这个应用程序是自己开发的，尚未通过审核，所以会出现警告视窗 )

点击后，点击“允许”这个应用程序存取试算表的数据。

完成后就能在应用程序里，看见读取到的试算表数据。

部署 Apps Script

确认能读取数据后，点击右上方“部署”，选择“新增部署作业”。

点击设定的齿轮图示，设定为“网页应用程序”。

设定“谁可以存取”为“所有人”，点击“部署”。

部署成功后，会看到一串网址，表示可以使用 Get 的方法呼叫的网址 ( 因为刚刚 Apps Script 使用 doGet 的方法 )。

使用浏览器打开网址，就能看到试算表的数据。

Python 读取 Google 试算表

打开 Colab，输入下方的程序码，执行后就能透过 Python requests 函数库的 get 方法，读取 Google 试算表的所有数据。

参考：Requests 函数库

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

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())

更新部署 Apps Script

如果有修改 Apps Script，直接部署会发生奇怪的现象 ( 读取到旧的文件、无法读取文件...等 )，建议按照下列步骤重新部署：

• 修改后，点击上方“存档”按钮存档。

  

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

  

• 重新部署 Apps Script。

• 如果还是不行，等待一分钟后，重新执行上述三点步骤。

参考数据

• Google 试算表 (1) - 读取试算表信息
• Google 试算表 (2) - 读取储存格数值
• Google 试算表 (3) - 网页显示储存格数值

意见反馈