1、背景

• 作为一个sql boy 其实基本上大部分的数据均在数据产品做自动化更新了，但是有某些数据因为没有落库(需要excel各种公式计算)，或者是给ceo出日报等，需要比较灵活没有办法放到数据产品上提供服务，这个时候需要把数据放到excel上给到需求方。。。久而久之这样确实是很浪费时间，所以最好的方式是python自动处理计算逻辑，自动写入google sheet(云文档)，需求方打开即阅。

2、申请google sheet API

2.1 进入Google cloud控制台

2.2 创建新项目

[图片上传失败...(image-8b5d7-1663489452044)]

[图片上传失败...(image-26cad7-1663489452044)]

[图片上传失败...(image-b19cd1-1663489452044)]

2.3 启用API和服务

[图片上传失败...(image-7fbe95-1663489452044)]

[图片上传失败...(image-12a234-1663489452044)]

[图片上传失败...(image-b32dd7-1663489452044)]

2.4 创建凭证

[图片上传失败...(image-d074d3-1663489452044)]

[图片上传失败...(image-fb4c34-1663489452044)]

[图片上传失败...(image-f2c67d-1663489452044)]

2.5 获取私钥文件

[图片上传失败...(image-619a93-1663489452044)]

[图片上传失败...(image-516b96-1663489452044)]

[图片上传失败...(image-ec7b8c-1663489452044)]

[图片上传失败...(image-9863a1-1663489452044)]

3、创建Google sheet

[图片上传失败...(image-5cd94a-1663489452044)]

3.1 共享编辑权限

• 打开上一步操作获取的json文件拿邮箱地址

"client_email": "test-api@plucky-haven-318207.iam.gserviceaccount.com"
# test-api@plucky-haven-318207.iam.gserviceaccount.com

[图片上传失败...(image-1ef58b-1663489452044)]

4、python操作

4.1 模块安装

import pygsheets # pip install pygsheets 
import pandas as pd
from pyhive import presto # python连接presto

# 关联参数
conn = presto.connect(protocol='https', host='', port=,username="", password = '')

# 获取dataframe
df = pd.read_sql_query(
'''
select * from xxx 
'''
, conn)

# df = pd.read_sql_query(open('/data/jupyter/Data.sql','r').read() , conn)

4.2 数据操作

client = pygsheets.authorize(service_file = "plucky-haven-318207-0eb060f3f328.json")

# 获取Google sheet
sh = client.open('test') # test为gs的表名
# 指定对应的sheet
wks = sh.worksheet_by_title('title1') # title1 为对应sheet名

# 数据写入
 wks.set_dataframe(df,(1,1),nan='', fit = True)

# 读取对应sheet的数据
df = pd.DataFrame(wks.get_all_records())

延伸阅读

pygsheet官方文档：https://pygsheets.readthedocs.io/en/stable/

pygsheets github：https://github.com/nithinmurali/pygsheets

Python 串接 GoogleSheet 新增、讀取、更新和刪除：https://www.maxlist.xyz/2018/09/25/python_googlesheet_crud/#%E4%BA%8C_Python_%E6%96%B0%E5%A2%9E_GoogleSheet_%E8%B3%87%E6%96%99