python 操作gsheet 实现读写操作脚本实战

最新推荐文章于 2024-09-14 08:25:16 发布

南湖渔歌

最新推荐文章于 2024-09-14 08:25:16 发布

阅读量176

点赞数 3

文章标签： python 开发语言

本文链接：https://blog.csdn.net/weixin_44976611/article/details/140391678

版权

# 尝试版本: Credentials.from_authorized_user_file

from google.oauth2.credentials import Credentials  
from googleapiclient.discovery import build

# If modifying these scopes, delete the file token.json.
SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
KEY_FILE_LOCATION = './Auto/gaforpython.json'

credentials = Credentials.from_authorized_user_file(KEY_FILE_LOCATION, SHEET_SCOPES)
service = build('sheets', 'v4', credentials=credentials)
# ValueError: Authorized user info was not in the expected format, missing fields refresh_token, client_secret
# ---------------------------------------------------------------------------------------------

from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

SHEET_SCOPES = ['https://www.googleapis.com/auth/spreadsheets']  # .readonly,只读权限, 无.readonly读写操作的权限
KEY_FILE_LOCATION = 'xxx.json'

credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SHEET_SCOPES)
service = build('sheets', 'v4', credentials=credentials)

import pandas as pd
from datetime import datetime

# 创建一个简单的 DataFrame 测试数据
data = {'Name': ['MIN', 'Bob', 'Charlie'],
        'Age': [99, 30, 35],
        'City': ['Shenzhen', 'London', 'Paris']}
df = pd.DataFrame(data)
df["time"] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

list_df = df.values.tolist()
# SheetDef.new_row(service, '1ZkXZYlA2yrMXTTa9DlWsgOB3sF2tVQGlmzExsokXDFY', '0', len(list_df))
range_ = '工作表1!A1:D' + str(len(list_df)+ 0)  # '工作表1!A1:D{num}'
# range_ = '工作表1!A1:D1'  # 建议写成默认最好

# range_ = '工作表1!A1:A1'  # 锚定点位
# range_ = '工作表4!H1:H1'  # 锚定点位
print(range_)

SheetDef.insert_value(service, '1ZkXZYlA2yrMXTTa9DlWsgOB3sF2tVQGlmzExsokXDFY',range_, list_df)

工作表1!A1:D3
{'spreadsheetId': '1ZkXZYlA2yrMXTTa9DlWsgOB3sF2tVQGlmzExsokXDFY',
 'tableRange': "'工作表1'!A1:D19",
 'updates': {'spreadsheetId': '1ZkXZYlA2yrMXTTa9DlWsgOB3sF2tVQGlmzExsokXDFY',
  'updatedRange': "'工作表1'!A20:D22",
  'updatedRows': 3,
  'updatedColumns': 4,
  'updatedCells': 12}}



# 只读: .readonly  即可
SheetDef.get_sheet_value(service, '1ZkXZYlA2yrMXTTa9DlWsgOB3sF2tVQGlmzExsokXDFY', '工作表1!A1:D3')

[['Name', 'Age', 'City', 'update_time'],
 ['MIN', '99', 'Shenzhen', '2024-07-13 00:54:15'],
 ['Bob', '30', 'London', '2024-07-13 00:54:15']]