Park Lam的每日分享

Python实用工具：pygsheets轻松操作Google Sheets

作者：

parklam

在

一、pygsheets库核心概述

pygsheets是一款专门用于Python程序与Google Sheets进行交互的第三方库，它的核心用途是实现对Google表格的创建、读取、修改、更新等操作，无需借助繁琐的手动操作或复杂的API调用流程。其工作原理是基于Google Sheets API v4进行封装，将复杂的接口请求转化为简洁的Python方法，开发者只需通过简单的代码调用即可完成与Google表格的交互。该库的优点在于语法简洁、功能全面，支持批量数据操作和单元格格式设置，同时兼容多种数据类型；缺点是需要配置Google Cloud平台的相关凭证，对新手而言存在一定的入门门槛。pygsheets采用MIT开源许可证，允许开发者自由用于商业和非商业项目。

二、pygsheets库安装与环境配置

2.1 库的安装

对于技术小白来说，pygsheets的安装流程非常简单，只需使用Python的包管理工具pip即可完成。打开命令行终端，输入以下命令：

pip install pygsheets

执行完毕后，pip会自动下载并安装pygsheets及其依赖的相关库，如google-api-python-client、oauth2client等。安装完成后，我们可以在Python环境中通过导入语句验证是否安装成功：

import pygsheets
print(pygsheets.__version__)

如果运行后能够输出pygsheets的版本号，说明安装已经成功。

2.2 Google Cloud凭证配置

由于pygsheets操作的是Google Sheets云端表格，因此必须先完成Google Cloud平台的凭证配置，获取对应的授权文件，具体步骤如下：

  1. 登录Google Cloud Console，创建一个新的项目，项目名称可以自定义，例如“pygsheets-demo”。
  2. 在项目中搜索并启用Google Sheets API，搜索框输入“Google Sheets API”，找到后点击“启用”按钮。
  3. 进入“API和服务”->“凭据”页面，点击“创建凭据”->“服务账号密钥”。
  4. 创建一个新的服务账号，填写服务账号名称，角色选择“Editor”（编辑权限），密钥类型选择“JSON”，点击创建后，浏览器会自动下载一个JSON格式的凭证文件，我们需要将这个文件保存到本地，例如命名为“credentials.json”。
  5. 打开下载的JSON凭证文件，找到其中的“client_email”字段对应的邮箱地址，将这个邮箱地址添加到目标Google Sheets表格的共享列表中，并授予编辑权限，这样pygsheets才能通过该凭证操作这个表格。

三、pygsheets核心功能与代码实例

3.1 连接Google Sheets并打开表格

使用pygsheets的第一步是通过凭证文件建立与Google Sheets的连接，然后打开指定的表格。这里我们需要用到pygsheets.authorize()方法，该方法会读取本地的凭证文件完成授权。

import pygsheets

# 授权连接，传入凭证文件路径
gc = pygsheets.authorize(service_file='credentials.json')

# 方式1：通过表格名称打开已存在的表格
sh = gc.open('My Google Sheet')  # 'My Google Sheet'是Google云端的表格名称

# 方式2：通过表格的ID打开表格（表格ID在表格URL中，格式为https://docs.google.com/spreadsheets/d/表格ID/edit）
# sh = gc.open_by_key('1Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')

# 方式3：打开最近使用的表格
# sh = gc.open_last()

代码说明：authorize()方法会根据传入的凭证文件完成身份验证，返回一个授权后的客户端对象gc；通过gc的open()、open_by_key()等方法可以打开云端的表格，返回表格对象sh，后续所有操作都基于这个对象展开。

3.2 创建新表格与工作表

pygsheets支持创建新的Google Sheets表格，也可以在已有表格中创建新的工作表（Sheet）。

import pygsheets

gc = pygsheets.authorize(service_file='credentials.json')

# 创建新的云端表格，参数为表格名称
new_sh = gc.create('New Pygsheets Sheet')
print(f'新表格创建成功，URL为：{new_sh.url}')

# 在新表格中创建新的工作表，参数为工作表名称、行数、列数
new_ws = new_sh.add_worksheet('New Worksheet', rows=100, cols=20)

# 创建工作表后，可以删除默认的第一个工作表（名为Sheet1）
default_ws = new_sh.worksheet_by_title('Sheet1')
new_sh.del_worksheet(default_ws)

代码说明：gc.create()方法会在Google云端创建一个新的表格，返回新表格对象new_sh，通过new_sh.url可以获取表格的访问链接；add_worksheet()方法用于在表格中添加新的工作表，指定名称、行数和列数；del_worksheet()方法则用于删除指定的工作表，删除前需要通过worksheet_by_title()方法获取对应的工作表对象。

3.3 工作表的基础操作

工作表是我们存放和操作数据的主要载体，pygsheets提供了丰富的工作表操作方法，包括选择工作表、获取工作表属性、清空工作表等。

import pygsheets

gc = pygsheets.authorize(service_file='credentials.json')
sh = gc.open('My Google Sheet')

# 选择指定名称的工作表
ws = sh.worksheet_by_title('Sheet1')

# 选择索引为0的工作表（索引从0开始，对应第一个工作表）
# ws = sh[0]

# 获取工作表的行数和列数
rows = ws.rows
cols = ws.cols
print(f'当前工作表行数：{rows}，列数：{cols}')

# 获取工作表的所有数据，返回二维列表格式
all_data = ws.get_all_values()
print(f'工作表所有数据：{all_data}')

# 清空工作表的所有数据
ws.clear()
print('工作表数据已清空')

代码说明：worksheet_by_title()方法通过工作表名称选择目标工作表，也可以通过索引的方式直接选择；rows和cols属性分别返回工作表的行数和列数；get_all_values()方法会读取工作表中的所有数据，以二维列表的形式返回，每一行对应列表中的一个子列表；clear()方法用于清空工作表的所有内容。

3.4 单元格数据读写操作

单元格是工作表的最小数据单元，pygsheets支持对单个单元格、多个单元格进行数据的读取和写入操作。

3.4.1 单个单元格操作

import pygsheets

gc = pygsheets.authorize(service_file='credentials.json')
sh = gc.open('My Google Sheet')
ws = sh[0]

# 方式1：通过行列索引获取单元格（索引从1开始）
cell = ws.cell('A1')
# 写入数据到单元格
cell.value = 'Hello pygsheets'
# 读取单元格数据
print(f'A1单元格数据：{cell.value}')

# 方式2：直接使用get_value和update_value方法
# 读取A2单元格数据
a2_value = ws.get_value('A2')
print(f'A2单元格数据：{a2_value}')
# 向A2单元格写入数据
ws.update_value('A2', 'Python Google Sheets')

代码说明：cell()方法通过单元格地址（如A1）获取单元格对象，通过修改对象的value属性写入数据；get_value()和update_value()方法可以直接读取和修改指定单元格的数据，无需获取单元格对象，操作更加简洁。

3.4.2 多个单元格批量操作

当需要处理大量数据时，批量操作可以显著提高效率，pygsheets支持对单元格区域进行批量读写。

import pygsheets

gc = pygsheets.authorize(service_file='credentials.json')
sh = gc.open('My Google Sheet')
ws = sh[0]

# 定义要写入的数据（二维列表，对应多行多列）
data = [
    ['姓名', '年龄', '城市'],
    ['张三', 25, '北京'],
    ['李四', 30, '上海'],
    ['王五', 28, '广州']
]

# 批量写入数据到A1:C4区域
ws.update_values('A1:C4', data)
print('批量数据写入完成')

# 批量读取A1:C4区域的数据
range_data = ws.get_values('A1:C4')
print(f'读取的区域数据：{range_data}')

# 批量修改单元格格式（将A1单元格设置为加粗，字体大小14）
ws.cell('A1').set_text_format('bold', True)
ws.cell('A1').set_text_format('fontSize', 14)

代码说明：update_values()方法接收单元格区域和二维列表数据，将数据批量写入指定区域；get_values()方法读取指定区域的所有数据，返回二维列表；通过set_text_format()方法可以设置单元格的文本格式，如加粗、字体大小等。

3.5 数据筛选与排序

pygsheets支持对工作表中的数据进行筛选和排序，方便快速处理和分析数据。

import pygsheets

gc = pygsheets.authorize(service_file='credentials.json')
sh = gc.open('My Google Sheet')
ws = sh[0]

# 假设工作表中已有数据：A列姓名，B列年龄，C列城市
# 筛选年龄大于25的行数据
# 第一步：获取所有数据
all_data = ws.get_all_values(include_tailing_empty=False)
# 第二步：筛选数据（跳过表头）
filtered_data = [all_data[0]] + [row for row in all_data[1:] if int(row[1]) > 25]
print(f'年龄大于25的数据：{filtered_data}')

# 将筛选后的数据写入新的工作表
new_ws = sh.add_worksheet('Filtered Data', rows=len(filtered_data), cols=3)
new_ws.update_values('A1:C{}'.format(len(filtered_data)), filtered_data)

# 对数据按年龄降序排序
# 跳过表头，对数据行排序
sorted_data = [all_data[0]] + sorted(all_data[1:], key=lambda x: int(x[1]), reverse=True)
print(f'按年龄降序排序后的数据：{sorted_data}')

代码说明：get_all_values()方法的include_tailing_empty参数设置为False，可以忽略末尾的空行；通过列表推导式可以实现简单的数据筛选；sorted()函数结合匿名函数可以对数据按指定列进行排序；最后将处理后的数据写入新的工作表，完成数据的二次整理。

四、pygsheets实际应用案例：数据统计与报表生成

4.1 案例场景

假设我们需要从一个Google Sheets表格中读取销售数据，统计每个产品的总销售额，然后将统计结果写入新的工作表，生成销售报表。

4.2 案例代码

import pygsheets

def generate_sales_report(credential_path, sheet_name):
    # 授权连接Google Sheets
    gc = pygsheets.authorize(service_file=credential_path)
    sh = gc.open(sheet_name)
    # 读取销售数据工作表
    sales_ws = sh.worksheet_by_title('销售数据')
    # 获取所有销售数据，跳过表头
    sales_data = sales_ws.get_all_values(include_tailing_empty=False)[1:]

    # 统计每个产品的总销售额
    sales_report = {}
    for row in sales_data:
        product_name = row[0]  # A列：产品名称
        quantity = int(row[1]) # B列：销售数量
        price = float(row[2])  # C列：单价
        total_sales = quantity * price

        if product_name in sales_report:
            sales_report[product_name] += total_sales
        else:
            sales_report[product_name] = total_sales

    # 准备报表数据
    report_data = [['产品名称', '总销售额（元）']]
    for product, total in sales_report.items():
        report_data.append([product, round(total, 2)])

    # 创建报表工作表
    if sh.worksheet_by_title('销售报表'):
        report_ws = sh.worksheet_by_title('销售报表')
        report_ws.clear()
    else:
        report_ws = sh.add_worksheet('销售报表', rows=len(report_data), cols=2)

    # 写入报表数据
    report_ws.update_values('A1:B{}'.format(len(report_data)), report_data)
    # 设置报表表头格式
    header_cell = report_ws.cell('A1')
    header_cell.set_text_format('bold', True)
    header_cell.set_text_format('fontSize', 12)
    header_cell = report_ws.cell('B1')
    header_cell.set_text_format('bold', True)
    header_cell.set_text_format('fontSize', 12)

    print('销售报表生成完成！')

# 调用函数生成报表
generate_sales_report('credentials.json', '产品销售统计')

代码说明：该案例定义了一个generate_sales_report函数，接收凭证文件路径和表格名称作为参数；函数首先读取“销售数据”工作表中的数据，然后通过字典统计每个产品的总销售额；接着创建或清空“销售报表”工作表，将统计结果写入其中，并设置表头格式；最后完成销售报表的生成。这个案例充分体现了pygsheets在数据处理和报表生成场景中的实用价值。

五、pygsheets相关资源地址

  • Pypi地址：https://pypi.org/project/pygsheets
  • Github地址：https://github.com/nithinmurali/pygsheets
  • 官方文档地址：https://pygsheets.readthedocs.io/

关注我，每天分享一个实用的Python自动化工具。

更多文章