时间戳

首页 > 解决方案 > 如何解决 Python 中的 Google Sheet API 错误

python - 如何解决 Python 中的 Google Sheet API 错误

问题描述

我正在使用以下代码将 Gsheet 下载到 Python 中。它适用于某些工作表，但不是全部。

import pickle
import os.path
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from googleapiclient.discovery import build

def gsheet_api_check(SCOPES):
    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('credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    return creds


def pull_sheet_data(SCOPES,SPREADSHEET_ID,RANGE_NAME):
    creds = gsheet_api_check(SCOPES)
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()
    #result = copy_file(service, SPREADSHEET_ID, copy_title)
    result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        rows = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()
        data = rows.get('values')
        print("COMPLETE: Data copied")
        return data

在导致错误的工作表上，我收到以下消息：

HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1bI_X2ssOj9gTYnKsa_CDajYP0FcFMr0E/values/3-7-14days?alt=json returned "This operation is not supported for this document">

我相信错误可能是因为原始电子表格是从 .xlsx 格式复制的。我无法实现复制功能来解决此错误。提前致谢。

更新

I changed the functions so that it incorporate both Drive API and Sheets API

    def gsheet_api_check(SCOPES):
    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('credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    return creds

def drive_api_check(SCOPES):
    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('drivecredentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    return creds

def pull_sheet_data(Sheets_SCOPES, Drive_SCOPES,SPREADSHEET_ID,RANGE_NAME):
    sheet_creds = gsheet_api_check(Sheets_SCOPES)
    drive_creds = drive_api_check(Drive_SCOPES)
    sheet_service = build('sheets', 'v4', credentials=sheet_creds)
    drive_service = build('drive', 'v3', credentials=drive_creds)
    drive_service.files().copy(fileId=SPREADSHEET_ID, body={"mimeType":"application/vnd.google-apps.spreadsheet"}).execute()
    #service.files().copy(fileId=SPREADSHEET_ID,convert=true, body={"title": "specifyName"}).execute()
    sheet = sheet_service.spreadsheets()
    copy_title = 'temp'
    #result = copy_file(service, SPREADSHEET_ID, copy_title)
    result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, 
   range=RANGE_NAME).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        rows = sheet.values().get(spreadsheetId=SPREADSHEET_ID, 
   range=RANGE_NAME).execute()
        data = rows.get('values')
        print("COMPLETE: Data copied")
        return data

现在我收到以下错误：

<HttpError 403 when requesting https://www.googleapis.com/drive/v3/files/1bI_X2ssOj9gTYnKsa_CDajYP0FcFMr0E/copy?alt=json returned "Insufficient Permission: Request had insufficient authentication scopes.">

标签： pythongoogle-apigoogle-sheets-api

解决方案

在 Excel 文件上使用 Sheets API 之前，您需要将其转换为 Google 电子表格

使用 Google 表格打开 Excel 文件并不等同于转换它。

您可以通过程序转换文件，例如使用 Drive API 的Files: copy方法。

样品要求：

service = build('drive', 'v3', credentials=creds)
service.files().copy(fileId=ID_OF_THE_EXCEL_FILE, body={"mimeType"="application/vnd.google-apps.spreadsheet"}).execute()

id根据您的要求使用转换后的文件。SPREADSHEET_ID

推荐阅读