No.054-记录一次谷歌表格的操作过程

关键词:谷歌表格

作者：昼夜

一、问题背景

       在日常工作中，在线表格是我们经常使用到的一种办公工具，咱们影刀也有集成大量的指令来帮助我们实现自动化操作各种在线表格。但是工具总是千奇百怪的，最近就遇到了某客户小伙伴在操作一款名为谷歌表格的在线文档，由于这个表格是Google的一个功能组件，所以在操作的时候还是遇到了一些问题，在这里就分享一些操作这个表格的经验。

二、解决方案

       1.使用什么样的自动化操作谷歌表格

       在影刀中没有对应的指令来直接操作谷歌表格，想要实现自动化的话肯定得依托于我们万能的代码模块。好在python对于这部分还算友好，我们有一些现成的库可以直接使用它，例如pygsheets、gspread等。在这里我们选择使用gspread库来完成，但是这里就遇到了第一个注意点，使用影刀install这个gspread的库的时候，安装的是6.0.0的版本，但是这个版本并不适配影刀内置的3.7版本的python，所以在安装的时候要注意，需要指定可以兼容python3.7的最新版本5.10.0。

       2.谷歌表格的链接

       想要实现自动化操作肯定就得先链接到在线表格，首先挂上VPN之后，我们需要到库中，开启对应的Google Sheets API，随后再创建服务账户和密钥json文件，只有通过这个json文件我们才能顺利的链接上表格，对应的步骤过程如下：

• 启用 API：
• 在项目中，导航到“导航菜单”- > “库”。
• 搜索并启用你想要使用的 API，例如“Google Sheets API”和“Google Drive API”。
• 创建服务账户：
• 在“导航菜单” -> “IAM & 管理” -> “服务账户”中，点击“创建服务账户”。
• 输入服务账户的名称，选择适当的角色（“Editor”或“Viewer”），然后点击“创建”。
• 创建密钥：
• 创建服务账户后，会提示要求为其创建密钥。
• 选择“JSON”作为密钥类型，然后点击“创建”。
• 下载生成的 JSON 文件，并将其保存在安全的地方。这个文件包含了你的服务账户的私钥。

       链接到表格使用的函数:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
def connect_gc(file_name):
    # 定义 OAuth2 认证的作用域
    scope = ['https://spreadsheets.google.com/feeds',
             'https://www.googleapis.com/auth/drive']

    # 从 JSON 文件加载 OAuth2 凭据
    credentials = ServiceAccountCredentials.from_json_keyfile_name(file_name, scope)
    
    # 使用凭据授权访问 Google Sheets
    gc = gspread.authorize(credentials)

    return gc

       3.操作时的代理问题

       在成功链接上表格之后并且定义好一些操作函数（读、写等），想要通过调用模块去对于表格进行处理的时候，出现了连接方没有响应的报错，随即想到可能是因为没有开启代理的原因，打开代理之后运行又提示HTTPSConnectionPool(host='oauth2.googleapis.com', port=443): Max retries exceeded with url: /token (Caused by ProxyError('Unable to connect to proxy', OSError(0, 'Error')))，大概率是因为代理导致的报错，多方

查找询问之后，发现是因为3.7版本的python不会自动去同步代理请求，所以导致两端连接一直失败。要解决这个问题的话需要在操作开始之前去同步一下环境变量，将代理的端口记录上去（感谢虚竹哥提供的方法！）

具体函数如下：

import os
def sync_proxy(proxy_address):
    os.environ['http_proxy'] = proxy_address
    os.environ['HTTP_proxy'] = proxy_address
    os.environ['https_proxy'] = proxy_address
    os.environ['HTTPS_proxy'] = proxy_address

       4.一些操作函数

# 向单元格中写入内容
def write_gs(gc_object, doc_url, location, data, sheet_name):
    workbook = gc_object.open_by_url(doc_url) # 通过在线表格网页url来连接表格
    # worksheet = workbook.sheet1
    worksheet = workbook.worksheet(sheet_name)
    worksheet.update(location, data)
   
 # 读取表格中某一列的内容
def read_gs_col(gc_object, doc_url, sheet_name, col):
    workbook = gc_object.open_by_url(doc_url)
    worksheet = workbook.worksheet(sheet_name)
    data = worksheet.col_values(col)

    return data

# 读取表格中某一行的内容
def read_gs_row(gc_object, doc_url, sheet_name, row):
    workbook = gc_object.open_by_url(doc_url)
    worksheet = workbook.worksheet(sheet_name)
    data = worksheet.row_values(row)

    return data

# 列数据生成图表
def share_sheet(gc_object, doc_url, sheet_name, email_id):
    workbook = gc_object.open_by_url(doc_url)
    worksheet = workbook.worksheet(sheet_name)
    worksheet.share(email=email_id, role='reader')

5.影刀流程搭建

       整体顺序为：同步代理请求->连接到表格->进行数据操作

       ps.由于客户隐私数据不便透露，这边模仿场景简化搭建了一个数据指标对比图表的场景。稍有简陋，还请见谅~

       整体逻辑：

       1.同步请求，通过url连接到表格

       2.读取前一天的数据当成对比数据，并写入上一行对标行

       3.将今日数据写入更新图像（这边图像是之前人工设置好，只需要更新数据便可改变图像）

       4.图像更新后截图进行保存

三、效果演示

四、其他事项

       1.链接表格的方法有很多，这边演示的是比较直观网页链接，其实还可以通过表格名称、key等

       2.对于开启表格权限的以及生成json密钥的步骤可以参考如下文章
       https://cloud.tencent.com/developer/news/1198569