Python 读写 google excel

用户684211961257
269 阅读1分钟

读取 google Excel

目前工作中经常会遇到读写。google excel 的情况，这个时候如果每次都下载下来再进行读写，是十分耗费时间和精力的，因此记录一下，利用 python 自动读写的过程，希望能帮助到大家～

Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=XXX-XXX.apps.googleusercontent.com&redirect_uri=http%3A%2F%2Flocalhost%3A57731%2F&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets.readonly&state=XXX&access_type=offline

遍寻解决方法无果，后来发现，这个东西如果在 server 机器上跑，就会出现这个问题，但是如果在本地跑，然后用浏览器打开上面的链接，哎嘿，就 okay了

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow,Flow
from google.auth.transport.requests import Request
import os
import pickle
from sklearn.metrics import precision_score, recall_score, f1_score, classification_report, confusion_matrix


SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

# here enter the id of your google sheet
SAMPLE_SPREADSHEET_ID_input = '1dlw2Srm8998Rq65FIgCeLNi1dkObc_wvFrh-B4xtO7I'
SAMPLE_RANGE_NAME = 'A1:E151'

def main():
    global values_input, service
    creds = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                '/Users/xiaoxue.liu/Downloads/credentials.json', SCOPES) # here enter the name of your downloaded JSON file
            creds = flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)

    service = build('sheets', 'v4', credentials=creds)

    # Call the Sheets API
    sheet = service.spreadsheets()
    result_input = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID_input,
                                range=SAMPLE_RANGE_NAME).execute()
    

    values_input = result_input.get('values', [])

main()
df=pd.DataFrame(values_input[1:], columns=values_input[0])

写入 google excel

需要注意的是，写入的创建流程是，创建 api 秘钥，而且需要把文档权限分享给获取的秘钥 JSON 中的邮箱； 写入的代码demo 如下：

client = pygsheets.authorize(service_file = "google.json")
# 打开谷歌表格testPygSheets
sh = client.open('training-data')

#获取表格中的而第一张工作表
wks = sh.sheet1

# 写入A1数据
j = 0
start = time.time()
for i, ii in rr.iterrows():
    test = ii["text"]
    source = ii["source"]
    j += 1
    wks.update_value('A' + str(j), source)
    wks.update_value('B' + str(j), test)
end = time.time()

print(end-start)
avatar
文章
阅读
粉丝